For technical questions about the internal workings of the database engine.

learn more… | top users | synonyms

17
votes
2answers
2k 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 ...
12
votes
1answer
1k 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 ...
11
votes
1answer
1k 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 ...
9
votes
1answer
570 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 ...
6
votes
1answer
350 views

SQL Server: Are there any performance benefits from indexing a table <8kb in size?

If a table is <8kb in size in SQL Server then presumably it is all stored on a single page. Thus - aside from enforcing a unique constraint - is there any performance benefit that could be obtained ...
5
votes
3answers
656 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> ...
5
votes
2answers
2k 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 ...
5
votes
1answer
2k 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 ...
5
votes
1answer
389 views

How does Berkeley DB manage its files?

I'm using Berkeley DB (BDB) as a persistent store for a JMS queue. When I consume an entry from the queue the underlying BDB files do not immediately shrink, but do eventually. I'm running into ...
4
votes
2answers
3k views

Why does MySQL allow HAVING to use SELECT aliases?

In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way: FROM WHERE GROUP BY HAVING SELECT ORDER BY ...
3
votes
1answer
94 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 ...
3
votes
1answer
276 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 ...
2
votes
1answer
515 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 ...
2
votes
1answer
116 views

How SQL determine page exists in Buffer pool (internal)

When we execute SQL query, then Relational Engine optimize it and pass query plan to Storage Engine which then ask for a page with actual data. If data page isn't in memory, then SQL pull those pages ...
2
votes
1answer
242 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, ...
2
votes
0answers
305 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
80 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 ...
1
vote
2answers
166 views

Where is the list of attached databases stored?

I know database instance names are stored in the registry in subkeys of HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL and the path to master.mdf is stored in ...
1
vote
1answer
350 views

Does SQL Server allow INSERTs into tables and indexes to be written in parallel?

If I have a table that has many indexes, and I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or will the rows be inserted using parallelism?
1
vote
2answers
1k 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 ...
1
vote
1answer
86 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
3answers
182 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 ...
1
vote
1answer
44 views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
1
vote
0answers
82 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 ...
0
votes
1answer
212 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
2answers
175 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, ...
0
votes
0answers
14 views

If I were to peak under the hood, how does MySQL's GET_LOCK function work?

I am curious about how MySQL's GET_LOCK function actually works. I understand what it means when I ask for a lock, and what the potential return values are. But I want to get a better understanding ...
0
votes
0answers
68 views

Page count differences between sys.dm_db_index_physical_stats and sys.allocation_units

In doing research for index maintenance, I found some significant differences between the page counts reported by sys.dm_db_index_physical_stats and sys.allocation_units in some of the clustered ...