We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
35
votes
1answer
21k 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 ...
35
votes
4answers
15k 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 ...
34
votes
2answers
13k 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)[...
30
votes
1answer
2k views

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 ...
27
votes
1answer
4k 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 ...
26
votes
1answer
1k 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 ...
25
votes
2answers
33k 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 ...
21
votes
1answer
3k views

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 ...
19
votes
4answers
5k 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 ...
18
votes
2answers
415 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 ...
17
votes
2answers
20k 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 ...
17
votes
1answer
714 views

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 ...
15
votes
4answers
1k views

PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method: Introduce 2 new columns as C and D2....
14
votes
2answers
1k views

Why is a temp table a more efficient solution to the Halloween Problem than an eager spool?

Consider the following query that inserts rows from a source table only if they aren't already in the target table: INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK) SELECT ...
14
votes
1answer
5k 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 ...
14
votes
2answers
20k 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 ...
14
votes
4answers
1k views

Index Uniqueness Overhead

I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our ...
14
votes
1answer
368 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
14
votes
2answers
952 views

In Search of FILESTREAM Insider Information

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system. The share is defined as follows: Sharename FILESTREAM_SHARE ...
13
votes
1answer
1k views

how to simulate a low-memory state when running a query

I am trying to better understand how SQL Server's execution engine behaves in a low-memory state. Speaking in terms of memory grants, I am wondering if there is some way to force GrantedMemory to be ...
13
votes
1answer
235 views

What allows SQL Server to trade an object name for a string passed to a system procedure

What causes it to be legal to pass an object name to the system stored procedure sp_helptext? What mechanism converts the object name to a string? e.g. -- works sp_helptext myproc sp_helptext [...
13
votes
1answer
1k 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 (...
13
votes
1answer
134 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 ...
13
votes
2answers
503 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
3answers
1k views

Concatenation Physical Operation: Does it guarantee order of execution?

In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like: select 'A' as c union all select 'B' Could return two rows in any order (although, in practice ...
12
votes
1answer
504 views

Why is this stream aggregate necessary?

Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script): SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = (SELECT 1); ...
12
votes
1answer
1k 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
2answers
1k views

Why does nested loops join only support left joins?

In Craig Freedman's blog, Nested Loops Join, he explains why the nested loops join cannot support a right outer join: The problem is that we scan the inner table multiple times – once for each ...
11
votes
2answers
3k 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)) ...
11
votes
4answers
2k views

What is the maximum number of local variables that can participate in SET operation?

I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of ...
11
votes
2answers
11k 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 ...
11
votes
1answer
125 views

List ROW_OVERFLOW_DATA pages for a specific table

I'm attempting to get a list of pages for a table that has rows with ROW_OVERFLOW_DATA. I can get the list of allocated pages from the undocumented DMV, sys.db_db_database_page_allocations, however, ...
11
votes
2answers
2k views

Database for efficient range aggregate queries?

As a simplified example, suppose I have a table like this: seq | value ----+------ 102 | 11954 211 | 43292 278 | 19222 499 | 3843 The table may contain hundreds of millions of records, and I need ...
11
votes
1answer
1k 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 ...
10
votes
3answers
3k views

How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

My question is how does SQL Server handle a query that needs to pull more volume of data into the buffer cache than there is space available? This query would contain multiple joins, so the result set ...
10
votes
1answer
3k 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 ...
10
votes
1answer
143 views

Hash aggregate bailout

A question that arose in a chat discussion: I know hash join bailout switches internally to a sort of nested loops thing. What does SQL Server do for a hash aggregate bailout (if it can happen at ...
9
votes
1answer
397 views

Index Seek Operator Cost

For the AdventureWorks sample database query below: SELECT P.ProductID, CA.TransactionID FROM Production.Product AS P CROSS APPLY ( SELECT TOP (1) TH.TransactionID FROM ...
9
votes
1answer
464 views

Measuring plan eviction

We have a SQL Server 2016 SP1 with max memory set to 24GB. This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored ...
8
votes
1answer
1k views

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem. SELECT * FROM ( SELECT 1 UNION ALL SELECT 2 ) AA ( A ) CROSS JOIN ...
8
votes
2answers
659 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 ...
8
votes
1answer
342 views

Why does a Set Returning Function (SRF) run slower in a FROM clause?

This is a database-internals question. I'm using PostgreSQL 9.5, I'm wondering why Set Returning Functions (SRFs), also known as Table Valued Functions (TVFs) run slower when in a FROM clause, for ...
8
votes
1answer
2k 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 GAM ...
8
votes
1answer
412 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 ...
8
votes
1answer
705 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 ...
8
votes
1answer
413 views

Understanding IAM Pages: extent intervals

I'm reading Itzik's book "Querying Microsoft SQL Server 2012" as well as reading/watching different educational material on the internet. My intention is to have a useful understanding of how the ...
8
votes
2answers
877 views

SQL Server 2014 compression and maximum row size

I need to create wide denormalized table with lot of decimal(26,8) columns (fewer than 1024 column limit, most of the columns would be null or zero). I know about 8060 bytes per row limitation, so i ...
7
votes
1answer
4k 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 ...
7
votes
1answer
534 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 -- ...
7
votes
1answer
812 views

Bug in Rebuild on SQL Server 2016 and above?

Question Summary A fragmented Clustered Index is not performing well, even after a REBUILD of the index. If the index is REORGANIZED then performance increases for the given table/index. I am seeing ...