For technical questions about the internal workings of the database engine.
5
votes
2answers
167 views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer
used.
SQL Server does not maintain the index (as data in the table changes), and the index
cannot be used to ...
0
votes
1answer
61 views
Database atomic operations implementation
The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this ...
0
votes
0answers
69 views
SQL Server Internal Memory Pressure
I am trying to identify the cause of internal memory pressure. From what I have learned, Resource Monitor Ring Buffer returns an indicator for internal pressure. For example by using this query,
...
1
vote
1answer
44 views
How SQL Server detects a record internally?
I am following this SO post where the author (stack programmer) states
SQL server uses the identity column as the key value to refer to a
particular row. So only a single identity column can be ...
4
votes
2answers
200 views
What are the differences between leaf and non-leaf pages?
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
1
vote
0answers
50 views
Page Header DB Frag ID Property
I am using SQL Server version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64).
I created a single table containing one and only one integer-valued column. I then added one and only one row to the ...
13
votes
2answers
596 views
Optimising plans with XML readers
Executing the query from here to pull the deadlock events out of the default extended events session
SELECT CAST (
REPLACE (
REPLACE (
XEventData.XEvent.value ...
8
votes
1answer
263 views
DELETE vs TRUNCATE
I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of:
DELETE -> the database ...
2
votes
1answer
218 views
Index with multiple leaf levels
I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
12
votes
1answer
424 views
Where are Statistics physically stored in SQL Server?
Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool?
More specifically, is there a way to figure out the pages used by ...
3
votes
1answer
89 views
Directly acessing the NULL bitmap of a row in SQL Server
I was reading the question Checking a wide table for nulls and I'm wondering if it's possible to somehow directly access the NULL bitmap of a row to fast check if a row contains NULL values. Would ...
1
vote
3answers
111 views
How is a join performed by a database engine?
How is a join between two tables actually performed by a database engine?
I am sure that listing one tuple against all tuples of the other table cannot be the way to perform the join; it's just a ...
2
votes
1answer
143 views
What is a parse tree object?
I have looked into sys.syscachedobjects table. I found some records have an object type of parsetree.
Can anyone tell me in detail what a parsetree is? Is this a data structure used by SQL Server, ...
9
votes
1answer
310 views
Slot Array and Total Page Size
I continue to read in many forums and on many blogs that a page is comprised as shown below:
Page Size: 16 x 512B = 8192B
Page Header: = 96B
Maximum In_Row Row: = 8060B
This leaves ...
-1
votes
2answers
112 views
Which SQL Server system database is more important: master or resource? [closed]
Which SQL Server system database is more important: master or resource?
If the resource database is damaged, can the database engine work?
Can the system work with only the master database? If not, ...
3
votes
1answer
164 views
SQL Server Storage sql_variant
USE tempdb ;
GO
DROP TABLE tbl ;
GO
CREATE TABLE tbl
(
i SQL_VARIANT NOT NULL
) ;
GO
INSERT INTO tbl (i)
VALUES (1) ;
GO
SELECT i FROM tbl ;
GO
DBCC IND ('tempdb','tbl',-1) ;
GO
DBCC ...
1
vote
1answer
74 views
What is QP layer?
From blogs.innodb.com: InnoDB Memcached with Binlog Capability
In our earlier Labs release, we presented a MySQL NoSQL solution through InnoDB Memcached Daemon Plugin (see earlier Calvin’s and my ...
1
vote
2answers
721 views
Is the mapping between PostgreSQL system tables and information_schema views documented?
If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog).
I'm looking for documentation ...
5
votes
3answers
441 views
What could be the reason for disallowing a sub query in the values clause?
For example
SQL> create table dates(d date);
Table created.
SQL> insert into dates select sysdate from dual;
1 row created.
SQL> select * from dates;
D
---------
28-MAY-11
SQL> ...
