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'
...
- 95.6k
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 =...
- 88.4k
19
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 ...
- 95.6k
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 ...
- 95.6k
18
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 ...
- 95.6k
18
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 ...
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 ...
- 71.1k
16
votes
How does SQL Server maintain rowcount metadata?
To look into this I created a new database as below
CREATE DATABASE RowCountTest
GO
USE RowCountTest
SELECT physical_name FROM sys.database_files WHERE type_desc = 'ROWS'
CREATE TABLE T(Id INT ...
- 88.4k
16
votes
Accepted
How does SQL Server maintain rowcount metadata?
Does SQL Server really maintain this metadata in multiple places? If so which is the most reliable method?
All methods listed in the question ultimately retrieve the row count via a call to sqlmin!...
- 95.6k
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 Update Query Analysis and Optimization. One has to be a bit careful to ...
- 95.6k
15
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 ...
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,...
- 95.6k
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 ...
- 18.9k
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 ...
- 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 ...
- 4,189
12
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 ...
- 38.7k
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 ...
- 49.2k
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 ...
- 95.6k
11
votes
Why does an index rebuild requires a Sch-M lock?
The documentation for How Online Index Operations Work says:
SCH-M (Schema Modification) if any source structure (index or table) is dropped.*
* The index operation waits for any uncommitted update ...
- 95.6k
11
votes
Accepted
Hash aggregate bailout
Hash join and hash aggregate both use the same operator code internally, though a hash aggregate uses only a single (build) input. The basic operation of hash aggregate is described by Craig Freedman:
...
- 95.6k
11
votes
Accepted
SQL Server chooses unselective index
Given the unique indexes, your query will select at most one row.
The optimizer knows it will need to descend the index b-tree just once, and will not need to scan forward or backward from that point ...
- 95.6k
11
votes
EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan
I don't have a fully satisfying answer to this, but DISTINCT is translated to a project plus group-by aggregate.
Both EXCEPT and INTERSECT come with an implied DISTINCT on the first table expression. ...
- 95.6k
10
votes
Internally, how does OpenRowset(TABLE ...) work?
I think this article is the nearest you will come to determining what goes on under the hoods:
Where Does SQL Server Store the Source for Stored Procedures? (improve.dk)
In his article Mark S. ...
- 18.9k
10
votes
Accepted
List ROW_OVERFLOW_DATA pages for a specific table
Your demo is being hit by a limitation of REPLICATE:
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater ...
- 30.1k
10
votes
Unclear update conflict
And the second theoretical question:
How does SQL Server handle include columns update?
I mean how does SQL Server update all nonclustered index which have an include columns when we update this ...
- 30.1k
10
votes
Accepted
Bitmap Creation in Execution Plan Causes bad Estimate on Clustered Index Scan
A number of factors in play:
The index comes with full scan statistics. The auto-created ones were sampled.
Different cardinality estimation models and execution modes handle the calculation ...
- 95.6k
9
votes
Accepted
Understanding IAM Pages: extent intervals
You can see by the red arrow what seems to be 16 pages related to the same extent. How is that possible?
The red arrow points to an entry showing that extents starting at 1:176 and 1:184 are allocated ...
- 95.6k
9
votes
In what cases are BLOB and TEXT stored in-line on InnoDB?
Keep in mind that there are 4 "row formats". A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few ...
- 80.8k
9
votes
Accepted
In what cases are BLOB and TEXT stored in-line on InnoDB?
For InnoDB the rule goes like this:
A record can not be fragmented.
At least two records must fit in a page.
If a record size is less than ~7k, all field values are stored in-page.
If the record size ...
- 5,017
Only top scored, non community-wiki answers of a minimum length are eligible