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

learn more… | top users | synonyms

2
votes
1answer
57 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 ...
1
vote
2answers
105 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 ...
4
votes
2answers
2k 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 ...
5
votes
1answer
255 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 ...
6
votes
1answer
1k 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 ...
10
votes
1answer
816 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 ...
0
votes
1answer
143 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 ...
2
votes
0answers
253 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
60 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 ...
5
votes
2answers
1k 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
77 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 ...
9
votes
1answer
484 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 ...
16
votes
2answers
1k 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 ...
2
votes
1answer
425 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
932 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
93 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
167 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
196 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, ...
-1
votes
2answers
152 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
248 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
83 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
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 ...
5
votes
3answers
565 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> ...