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

learn more… | top users | synonyms

1
vote
1answer
101 views

Is database record access sequential after retrieving the indexed page from disk? [closed]

This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer. What I understand about databases that if a queried or requested record is found in the ...
0
votes
0answers
16 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 ...
1
vote
1answer
47 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 ...
6
votes
1answer
361 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 ...
0
votes
0answers
87 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 ...
1
vote
1answer
630 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?
2
votes
1answer
152 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
213 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
4k 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
432 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 ...
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 ...
0
votes
1answer
248 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
326 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
87 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
3k 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
87 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 ...
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 ...
11
votes
1answer
2k 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
544 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 ...
13
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 ...
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 ...
1
vote
3answers
192 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
256 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
609 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 ...
0
votes
2answers
182 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, ...
4
votes
1answer
288 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
88 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
716 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> ...