For technical questions about the internal workings of the database engine.
Questions tagged [database-internals]
241 questions
42
votes
4 answers
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 element of good style.
That is clear.
What is not…
Nick Chammas
- 14,810
- 17
- 76
- 124
40
votes
2 answers
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)[1]', 'varchar(max)'),
'',…
Martin Smith
- 87,941
- 15
- 255
- 354
38
votes
2 answers
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 engine finds and removes the row from the relevant…
Stuart Blackler
- 4,540
- 7
- 30
- 43
30
votes
1 answer
Why is a scan faster than seek for this predicate?
I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals.
On my machine, the following query does a clustered index scan and takes about 6.8 seconds of CPU…
Joe Obbish
- 32,976
- 4
- 74
- 153
28
votes
1 answer
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 statistics using DMVs and/or DBCC?
I own both SQL Server…
ivanmp
- 2,323
- 3
- 24
- 36
26
votes
2 answers
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 INT). Just the nullability. The statement is as…
Randy Minder
- 2,032
- 4
- 22
- 41
26
votes
1 answer
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 with enough data to ensure it is stored off row. In…
Paul White
- 94,921
- 30
- 437
- 687
25
votes
1 answer
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 includes the space padding in the case
of…
keypress
- 353
- 1
- 3
- 5
21
votes
2 answers
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 Allocations. I really don't know what it means, or if one…
meltdownmonk
- 377
- 1
- 3
- 8
21
votes
1 answer
Hash keys probe and residual
Say, we have a query like this:
select a.*,b.*
from
a join b
on a.col1=b.col1
and len(a.col1)=10
Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len(a.col1)=10.
But while going…
TheGameiswar
- 2,999
- 4
- 30
- 50
20
votes
1 answer
Nonclustered index storage on clustered columnstore
In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all…
Paul White
- 94,921
- 30
- 437
- 687
19
votes
4 answers
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 nonclustered index (on a heap) and returns…
u23432534
- 1,565
- 4
- 20
- 31
18
votes
2 answers
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 the vendor's upgrade we could create the index in…
Daniel Miller
- 181
- 2
17
votes
1 answer
Constant Scan applying
This question is a continuation of optimizer behavior exploration in regards to VALUES construct started here and here. I want to ask about VALUES and APPLY this time.
Using CROSS APPLY to alias an expression that needs to be referenced in various…
i-one
- 2,374
- 2
- 14
- 23
16
votes
1 answer
Does SQL Server 2019 have task scheduling differences between enterprise and standard editions?
In How It Works: SQL Server 2012 Database Engine Task Scheduling, Bob Dorr explains some of the changes for worker scheduler assignment in SQL Server 2012. He mentions that some of the improvements are only available in enterprise edition. Do these…
Joe Obbish
- 32,976
- 4
- 74
- 153