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

learn more… | top users | synonyms

27
votes
2answers
6k 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 ('(data/value)[...
26
votes
4answers
6k views

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
23
votes
1answer
425 views

Logical reads different when accessing the same LOB data

Here are three simple tests that read the same data, yet report very different logical reads: Setup The following script creates a test table with 100 identical rows, each containing an xml column ...
22
votes
1answer
11k 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 ...
19
votes
1answer
3k 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 ...
16
votes
2answers
300 views

Why is it taking longer to create an index after column size increases?

Our vendor changed column widths on almost every column in the entire database. The database is around 7TB, 9000+ tables. We are trying to create an index on a table that has 5.5billion rows. Before ...
15
votes
4answers
1k views

Should the index on an identity column be nonclustered?

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column? The reason is other indexes will be created for queries. A query which uses a ...
15
votes
2answers
5k views

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
13
votes
2answers
315 views

How are these two SQL Server rollbacks different?

In SQL Server 2008 R2, how are these two rollbacks different: Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely. Run the same ...
12
votes
1answer
938 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 (...
11
votes
1answer
192 views

Where does SQL Server physically store the IDENTITY VALUE for a table?

I'm hoping someone can point me in the right direction on this one. Here's my workings out so far. SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for ...
11
votes
1answer
116 views

Why do these similar queries use different optimisation phases (transaction processing vs quick plan)?

The example code in this connect item Shows a bug where SELECT COUNT(*) FROM dbo.my_splitter_1('2') L1 INNER JOIN dbo.my_splitter_1('') L2 ON L1.csv_item = L2.csv_item Returns ...
10
votes
2answers
436 views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
10
votes
2answers
9k 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 ...
10
votes
1answer
516 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
9
votes
1answer
448 views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
9
votes
2answers
11k 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 ...
9
votes
1answer
6k 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 ...
8
votes
2answers
232 views

Are columns which are not indexes, sorted on disk together with index?

Are columns which are not indexes, sorted on disk together with index, in MySQL, in MyISAM and InnoDB? An incorrect thought that I started to write: I think that probably not, since they are not ...
7
votes
1answer
242 views

Is there a way to see how SQL Server 2012 defines new identities?

On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO? SAMPLE DATA -- ...
6
votes
1answer
656 views

Why can a SQL Server table not have more than one IDENTITY column?

Why can a SQL Server table not have more than one identity column? CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY) Msg 2744, Level 16, State 2, Line 5 Multiple identity columns specified for ...
6
votes
1answer
505 views

Several Questions about SGAM & GAM & IAM & PFS in SQL Server

Have several questions about internal structure of SQL Server. Lets assume that we have a table in a database. SGAM & GAM pages keep track of shared & uniform extents within a single ...
6
votes
1answer
150 views

Set statistics I/O for nested loops

Consider the below query: CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key @pagesize AS BIGINT = 25 AS SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM ...
6
votes
1answer
502 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 ...
6
votes
1answer
82 views

Size of individual transactions in log file

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which ...
5
votes
1answer
810 views

Get minimal logging when loading data into temporary tables

Even after reading The Data Loading Performance Guide , I am still unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get ...
5
votes
3answers
1k 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
1answer
741 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, ...
5
votes
1answer
897 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
1answer
344 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 ...
3
votes
1answer
392 views

Storage size of a bigint table

I am working with a table that has this format : CREATE TABLE dbo.ID_STORE ( WORKING_ID bigint PRIMARY KEY CLUSTERED ) The table stores around 2 millions rows, but the stored ids are not ...
3
votes
1answer
4k 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?
3
votes
1answer
103 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
64 views

Allocation Unit ID calculation is incorrect?

I am reading the article http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/ on how to calculate the allocation unit ID based on m_indexId and m_objID....
3
votes
1answer
63 views

Why more (and varying number of) logical reads with read ahead (prefetch)?

After creating tpch database in my SQL Server, I tried below query: set statistics io on DBCC DROPCLEANBUFFERS; select top 100 * from dbo.lineitem order by l_partkey; The table ...
3
votes
1answer
45 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
3
votes
1answer
81 views

TempDB uses a lot of space in buffer pool

I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory. That's the query I used: SELECT COUNT(*) AS ...
3
votes
1answer
70 views

Remove mixed extents in SQL Server

In SQL Server you can make sure that the engine uses the whole extent for a single object (i.e. a table). I know how to enable the trace 1118, but that only applies to newly written data as I recall. ...
3
votes
1answer
537 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
1answer
968 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
4answers
335 views

Identical tables, identical columns, identical records, but why different record sizes?

Identical tables, identical columns, identical records, but why different record sizes? USE [test] GO CREATE TABLE [dbo].[mybit]( [col1] [bit] NOT NULL, [col2] [bit] NOT NULL, [col3] [...
2
votes
1answer
208 views

Plan cache behaviour

Can someone please explain me why the following is no getting cached use AdventureWorks2014 go DECLARE @id INT=43865 SELECT sp.[BusinessEntityID] , so.salesorderid FROM sales.salesorderheader so JOIN ...
2
votes
2answers
1k 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 HKLM:\SOFTWARE\Microsoft\...
2
votes
1answer
488 views

should we keep the log file or the data file on the fastest drive?

SSD = solid state drive We have a specific question about tempDB performance on server CRM01 (Marketing CRM server - running large queries): On CRM01, tempDB data and log files reside on the solid ...
2
votes
1answer
65 views

Difference size DBCC Page and DBCC Fileheader

I'm trying to read the current database size using dbcc command. In my test I execute following commands: DBCC Fileheader('tempdb',1) => size = 988152 DBCC Page('tempdb',1,0,3) => size = 5112 ...
2
votes
1answer
463 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
0answers
14 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
2
votes
1answer
512 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
1answer
124 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
1answer
124 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 ...