Skip to main content
32 votes
Accepted

Why is a scan faster than seek for this predicate?

Why is there a large difference in CPU time for these two queries? The scan plan evaluates the following pushed non-sargable (residual) predicate for each row: [two_col_key_test].[ID1]<>N'1' ...
Paul White's user avatar
  • 86.4k
27 votes
Accepted

Logical reads different when accessing the same LOB data

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case: ...
Paul White's user avatar
  • 86.4k
24 votes
Accepted

Hash keys probe and residual

If the join is on a single column typed as tinyint, smallint, or integer* and if both columns are constrained to be NOT NULL, the hash function is 'perfect' – meaning there is no chance of a hash ...
Paul White's user avatar
  • 86.4k
19 votes
Accepted

Nonclustered index storage on clustered columnstore

The "bookmark" is the columnstore index original locator (per "Pro SQL Server Internals" by Dmitri Korotkevitch). This is an 8-byte value, with the columnstore index's row_group_id in the first 4-...
AMtwo's user avatar
  • 16.2k
19 votes
Accepted

Why is this stream aggregate necessary?

You can see the role of this aggregate if no rows match the WHERE clause. SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = 1 /*To avoid auto parameterisation*/ AND Id%3 =...
Martin Smith's user avatar
  • 84.9k
18 votes
Accepted

Unclear update conflict

Why do I get update conflict in this situation instead of just blocking It is a product defect, which is fixed in SQL Server 2019. A snapshot write conflict occurs when a snapshot transaction ...
Paul White's user avatar
  • 86.4k
17 votes
Accepted

Difference between Physical Reads and Read-Ahead Reads

A query always reads data from memory (a logical read). Your example query scanning the TestLarge table touched 159,185 8KB memory pages during its execution. During execution, SQL Server does two ...
Paul White's user avatar
  • 86.4k
17 votes
Accepted

SQL Server LOB variables and memory usage

The 2GB LOB limit only applies to persistent storage (except FILESTREAM); variables have no limit. SQL Server starts with an pure in-memory implementation. It changes to a tempdb-based backup scheme ...
Paul White's user avatar
  • 86.4k
17 votes
Accepted

Is it possible for SQL Server to grant more memory to a query than is available to the instance

By default, SQL Server will let any query use up to 25% of max server memory for a memory grant, but in practice it's often closer to 20%. Resource Governor has something to say about this: But on my ...
Erik Darling's user avatar
  • 41.9k
16 votes
Accepted

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

Msg 8631, Level 17, State 1, Line xxx Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it. This error occurs with long ...
Paul White's user avatar
  • 86.4k
16 votes
Accepted

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

Probable bug on 9.6 and 9.6.1 This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (...
joanolo's user avatar
  • 13.5k
16 votes
Accepted

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

I don't think there's any real "internals" reason. The metadata is stored at column level not table level. It would need a rethink though of scalar functions such as scope_identity() and pseudo column ...
Martin Smith's user avatar
  • 84.9k
16 votes
Accepted

Index Uniqueness Overhead

I'm frequently involved in code reviews for the dev team, and I need to be able to give general guidelines for them to follow. The environment I'm currently involved in has 250 servers with 2500 ...
Hannah Vernon's user avatar
  • 70.2k
15 votes
Accepted

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

This is what I call Manual Halloween Protection. You can find an example of it being used with an update statement in my article Optimizing Update Queries. One has to be a bit careful to preserve the ...
Paul White's user avatar
  • 86.4k
14 votes
Accepted

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

SELECT INTO has two phases (not visible in execution plans). First, it creates a table that matches the metadata of the query used to create it. This happens in a system transaction, so the (empty) ...
Paul White's user avatar
  • 86.4k
14 votes
Accepted

Index Pages (page type 2)

Index pages (type 2) hold the non-leaf level(s) of the clustered index b-tree. The leaf level of the clustered index is just the underlying object's data pages themselves. In the special case where ...
Paul White's user avatar
  • 86.4k
14 votes

Why does nested loops join only support left joins?

What I don't like in the linked article is the statement that "nested loop join algorithm does not support the logical join operator of right join". While there is a limitation, the wording at this ...
ypercubeᵀᴹ's user avatar
14 votes
Accepted

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

If you're on SQL Server 2012 SP3 or better, you can use the MAX_GRANT_PERCENT hint to cap a single query's memory grant. However, you'd have to mess with the number to figure out which percent gives ...
Erik Darling's user avatar
  • 41.9k
14 votes
Accepted

EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan

There are various things we do inside the query optimizer that don't really have a reason that we can explain externally. The projects you see in the optimizer eventually get rewritten at the end of ...
Conor Cunningham MSFT's user avatar
14 votes
Accepted

Prevent THREADPOOL waits due to idle worker thread trimming

The trace flag is 8061. It is undocumented, so should only be enabled when suggested by Microsoft support. The flag needs to be enabled globally (or on start up): -- Enable globally DBCC TRACEON (8061,...
Paul White's user avatar
  • 86.4k
13 votes
Accepted

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

Pages are read into memory as required, if there is no free memory available, the oldest unmodified page is replaced with the incoming page. This means if you execute a query that requires more data ...
Hannah Vernon's user avatar
  • 70.2k
13 votes
Accepted

Bug in Rebuild on SQL Server 2016 and above?

The queries in question exercise the SQL Server read-ahead feature. With read-ahead performance optimization, the SQL Server storage engine prefetches data during scans so that pages are already in ...
Dan Guzman's user avatar
  • 28.3k
13 votes

Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

As noted in my previous post sys.sysobjects is deprecated: Note taken from sys.sysobjects (Transact-SQL) This SQL Server 2000 system table is included as a view for backward compatibility. We ...
John K. N.'s user avatar
13 votes
Accepted

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

Let's start by comparing execution plans: tinker=> EXPLAIN ANALYZE SELECT * FROM generate_series(1,1e7); QUERY PLAN ...
willglynn's user avatar
  • 401
13 votes
Accepted

Cardinality estimate outside the histogram

Based on my testing, the out-of-bounds cardinality estimate is simply the square root of the row count, bounded below by the number of added rows since the last stats update, and bounded above by the ...
Forrest's user avatar
  • 4,169
12 votes
Accepted

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

The first argument to the system stored procedure sp_helptext is: [@objname= ] 'name' Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required ...
12 votes
Accepted

Why does nested loops join only support left joins?

The main issue here is the implementation of an outer join, using nested loops, in a technical way which is opposite to the logical way, where the inner table is accessed through the outer loop and ...
David דודו Markovitz's user avatar
12 votes

Where can I find documentation on SQL Server internals?

I'm looking for docs on the internal format of the WAL log The SQL Server Log File is not publicly documented, but there is a partner program for 3rd parties that want to build solutions that use ...
David Browne - Microsoft's user avatar
12 votes
Accepted

Index Seek Operator Cost

The full cost derivation logic is complex, but for the relatively simple case in the question: Inputs The number of times the operator is executed This is the Estimated Number of Executions: 504 The ...
Paul White's user avatar
  • 86.4k
11 votes

What is the significance of the trailing dollar sign `$` on identifiers in SQL Server?

Even if only by convention, what is the rule here regarding this? There is nothing stopping you from using a $ in your code... generally, though, using reserved values is frowned upon. Most internal ...
Sean Gallardy's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible