Seeks seeks seeks… The number of the beast. Seeks are cool though. I like seeks. Sometimes, at least. Perhaps some seeks are cooler than others. I just said seeks six times, now we’re ready to go.
Let’s begin by creating a temporary table that holds values from 1 to 10 000.
CREATE TABLE #T(id integer PRIMARY KEY CLUSTERED);
WITH n(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT INTO #T(id)
SELECT n FROM n ORDER BY n
OPTION (MAXRECURSION 10000);
Now, let’s assume that we need to retrieve a specific range of values – e.g. between 500 and 563. There are a couple of ways to do that, that’s not exactly rocket science, but let’s consider the following two approaches:
Using the BETWEEN operator:
SELECT
Id
FROM
#X
WHERE
Id BETWEEN 500 AND 563
Using the IN operator:
SELECT
Id
FROM
#X
WHERE
Id in
(
500,501,502,503,504,505,506,507,508,509,510,511,512,
513,514,515,516,517,518,519,520,521,522,523,524,525,
526,527,528,529,530,531,532,533,534,535,536,537,538,
539,540,541,542,543,544,545,546,547,548,549,550,551,
552,553,554,555,556,557,558,559,560,561,562,563
)
Uh, truly fascinating.
But, behind these boring and functional equivalent queries, there is a dirty seekret – Wow, I’m on fire today. Both result in a Clustered Index Seek, but one is sus. Discuss!
That’s quite a difference. The second query results in 64 times more logical reads. But why?
One Seek To Rule Them All
In the execution plan of the query that uses the BETWEEN operator, it’s possible to notice that there’s only one seek operation, which range starts at 500 and then traverses the leaf level of the index, until it finds a row beyond the range limit – 563 in this case.
The Meeseeks Box
The seek #2, in the execution plan of the query that uses the IN operator is rather different:
The list continues down to a total of 64 individual seeks.
The underlying reason for the 128 logical reads is related to the size of our table and consequently, the size of the index. Since it is large enough to need a separate root page, each one of the 64 seeks will imply 2 logical reads.
But I digress, the bottom line here is that Seek #2 is the impostor. It is not actually a seek, but a whole bunch of them.
Scan operations are processed on account of data reading operators that differ according to the underlying data structure:
Clustered Index Scan: Scan is done on the leaf-level pages of a Clustered Index
Index Scan (NonClustered): Scan is done on the leaf-level pages of a Non-Clustered Index
Table Scan: Scan is done on a Heap (table without a Clustered Index)
A Scan often implies reading all pages of a specific index or heap, but there are a couple of reasons why SQL Server may choose to do a Scan instead of a Seek. e.g.:
Queries where the estimated number of rows to be read exceed the threshold from which SQL Server thinks it’s more efficient to do a Scan instead of a Seek.
There is no index to support a Seek operation
A non-Sargable predicate forces SQL Server to perform a Scan. This behavior was discussed in a previous post.
Scans are not always evil nor Seeks are always good. In the next sections we’ll briefly overview different types of scans as well as which properties can help us to have a better understanding of them. We’ll be focusing on rowstore format and it’s important to emphasize that Columnstore and Memory-Optimized Indexes will not behave entirely the same way. If you want to know more about them, check it here.
Index Scan
As already stated, this operator reads data from a non-clustered index. The general algorithm it’s quite simple:
First row (or batch if we’re on Batch Mode) is read.
If there’s a predicate, it is evaluated against the row(s) and matches are returned.
Next row / batch gets evaluated and the cycle repeats itself until there are no rows left.
These rows can be read in three distinct ways and this access method is chosen at execution time:
Allocation Order Scan: Reads data in an unspecified order. This method is faster for large tables but has a higher startup cost.
Ordered Forward Scan: Reads data according to the order specified on the index columns.
Ordered Backward Scan: Reads data in the inverse order specified on the index columns.
The predicate mentioned above is one of the properties that is useful to analyze when reading execution plans, as it represents the filter that was applied to the rows. If no predicate is specified, every row is returned. You can also have predicates that don’t filter any rows. I don’t know why, but you can.
The following image shows a query execution that led to a non-Clustered Index Scan. Below the execution plan some of the operator’s properties are visible.
The property that evidences which of the previously three presented access methods was chosen is Ordered. In this case, this property holds the value “False” which means that an Allocation Order Scan was done, because no specific order was implied. When this value is “True”, another property named ScanDirection will specify the direction of the ordered scan.
The last highlighted property, TableCardinality, shows the total number of rows in the index when the plan was compiled.
Estimates Vs Actuals
Although these aren’t specific to Index Scans, they are important to inspect when troubleshooting execution plans.
The above image shows some of these properties and their respective values. Knowing what each means is the key to have a clear picture of what’s going on.
Estimated Operator Cost: Total operator cost estimation, usually and as in this case, it’s equal to Estimated CPU Cost + EstimatedI/O Cost. Another interesting fact is that, in this case, it’s also equal to Estimated Subtree Cost, simply because there are no other operators on the subtree.
Estimated Number of Executions: Estimated number of executions for the operator, in the current plan. Operators in the inner input of Nested Loops may cause values greater than 1, which is not the case.
Estimated Number of Rows per Execution (Formerly Known As “Estimated Number of Rows”): Number of rows returned per execution. Multiplying this number to Estimated Number of Executions will provide the estimated total number of rows.
Estimated Number of Rows to be Read: Estimated number of rows to be read by the operator.
Number of Rows Read: Number of rows read by the operator during the index scan, which typically equals to the total number of rows in the index. The difference between this property and the Actual Number of Rows property represents the number of rows that was read but not returned due to the Predicate property.
Actual I/O Statistics: I/O stats at the operator level, translating rows into more specific and measurable values. For example, the 357 Logical Reads presented, evidences that 357 8KB pages were read in this execution.
Actual Number of Rows for All Executions (FKA “Actual Number of Rows”): Total number of rows returned, across all executions
Actual Time Statistics: Actual time spent by this operator, divided into “Actual Elapsed CPU Time” and “Actual Elapsed Time”
But There’s Life Beyond This
In addition to the list presented above, there are other properties that also might be helpful:
Output List: Shows the list of all columns regarding the rows returned by the operator.
Forced Index: Its value will be “True” if an index is explicitly hinted in the query text, otherwise will be “False”.
ForceScan: Its value will be true if FORCESCAN is explicitly hinted in the query text, otherwise will be “False”.
ForceSeek: Its value will be true if FORCESEEK is explicitly hinted in the query text, even though the optimizer ended up choosing to perform a scan. Otherwise, will be “False”.
Be aware that forcing Indexes, Scans or Seeks can lead to unwanted outcomes. Sure, they can be lifesavers and are great tools when applying quick fixes, but we’re fundamentally force SQL Server to choose a route, that over time may not be the best one.
Clustered Index Scan
Not surprisingly, in a Clustered Index Scan data is read from a clustered index. Its behavior is basically the same as in a non-Clustered Index Scan and all previous properties can be seen and used under the same premises.
Table Scan
A Table Scan occurs when a scan is done over a Heap – a table without a clustered index. Its behavior is also similar to the previous scans but due to the different underlying structure, there are a couple of aspects to consider. Instead of having a B-Tree structure, with root, intermediate and leaf nodes, it just has unordered data pages that are not linked, hence sequential access needs to go through the Index Allocation Map (IAM) pages.
Heap structure (left) vs Index structure (right) Source
If records are updated on Heaps, in a way that they can’t fit on the current page no more, the row is moved to a new page, where is marked as “forwarded” and a “forwarding-pointer” to this new location is stored in the “old” page. Table Scans read forwarded rows when the scan encounters the original location, hence skipping them on their current location.
Heaps are suitable under specific conditions, because the absence of a clustered index means no overhead on index maintenance and less overhead on storage. Nonetheless, searching for specific data can be a pain in the buttocks, unless the Heap is not that large, or non-clustered indexes are created. The bottom line here is that you need to be careful, even though Heaps Don’t Lie. If you didn’t get this Shakira reference you are a lucky person. If you did, I’m sorry.
More Threads! Call the Proletariat
When the plan and specifically the Scan operator goes parallel, things get more interesting. Threads are listened for row requests and data gets returned to each thread that requested data and had already processed all data from last page received. We can also see more information of data distribution across threads in the operator properties.
Analyzing these numbers will help us to troubleshoot potential skewed distributions.
Parallelism in Table Scans is a bit different due to the specificities already discussed and, in theory, they are more prone to race conditions. See an awesome explanation from Hugo Kornelis here.
And that’s it! Thank you for your time and stay safe🖖
