So last week I talked about all the different ways that Microsoft uses the word scans when in SQL Server. I got together a quick reference guide that describes scans terminology in terms of the SQL Server performance counters (because those counters were granular and trustworthy). But I didn’t really go into as much detail as I could have when I dealt with query plans. Or as Rob Farley prefers execution plans
Showplan Operators That Read From Tables
So here are the execution plan operators that access data from tables. I give a short description of each operator and I explain how that affect the performance counters (Full/Range/Probe). I won’t explain what each one means, I’ll let others do that. But if you know a bit about internals, the icons are actually quite well designed.
This operator corresponds with the Full Scans/sec performance counter. I actually don’t encounter table scans very often and that’s mostly because most of the tables I deal with have clustered indexes defined. You’ll only ever see table scans on heaps.
This operator corresponds with the Full Scans/sec performance counter. This is your run-of-the-mill full scan on a clustered index. Notice the brackets in the showplan icon. Those brackets are supposed to indicate a clustered index. Contrast this with the nonclustered index scan:
This operator corresponds with the Full Scans/sec performance counter. And this is a full scan of a nonclustered index. You’d probably see this if the nonclustered index contains all the columns to select. It’s easier to scan because it’s a narrower index than the clustered index.
This operator corresponds with the Probe Scans/sec performance counter when the clustered index is unique (as with a primary key) and the “seek predicate” includes the key columns needed to return at most one record. But if that’s not the case, then this operator will count towards the performance counter Range Scans/sec.
Exactly like its Clustered Index counterpart, this operator corresponds with the Probe Scans/sec performance counter when SQL Server can determine that when it looks up the requested row it is sure to get at most one row (i.e. the index is unique etc…) Otherwise it counts towards the performance counter Range Scans/sec.
This operator is also known as a bookmark lookup operator. It always counts towards the Probe Scans/sec performance counter. It’s interesting that even though it gets a single record at a time, this operator is often seen as a symptom of poor performance. That’s because the number of executions can get big. Many executions can kill the performance of the query. If you focus on the performance counters, you’ll notice that each of these executions will count towards the Probe Scans performance counter.
Just like the key lookup, this lookup counts towards the Probe Scans/sec performance counter. I’ve written about this operator before in an article called Get Rid of RID Lookups.
Update On Scan Quick Reference
In the meantime, thanks to Kendra Little’s script to search system tables, I discovered one more dmv that I had overlooked last week, sys.dm_db_index_operational_stats, and so I include it here:
I was soooo close to doing a cartoon where a muppet crook with an icon in his hand says “Let’s see, I take the clustered scan from the query plan and I give it to Dan in the tan van.”
But I thought that that might have been too obscure an in-joke for those who remember that Sesame Street bit. http://www.youtube.com/watch?v=1ZpjH1WcWOM
Comment by Michael J. Swart — March 23, 2011 @ 1:53 pm
I should have ran!
I blame Lefty for my unwavering love of trench coats
Comment by Claire — March 23, 2011 @ 2:09 pm
@Claire
Riiiiiight.
Comment by Michael J. Swart — March 23, 2011 @ 2:26 pm
I love this post! I don’t know that anyone’s ever made the operators look so good, and also it *really* is cool to see the details in them, many of which I hadn’t noticed before.
Comment by Kendra Little — March 23, 2011 @ 4:10 pm
Love the graphics!
There’s an interesting case where multiple seeks on a unique index are reported as range scans:
https://connect.microsoft.com/SQLServer/feedback/details/643581/multiple-singleton-seeks-reported-as-range-scans-by-access-methods-object
I think this is a bug, but it has been closed as Won’t Fix without explanation so far. If any of your readers are interested in some more detail around the differences between range scans and seeks, I would humbly suggest:
http://sqlblog.com/blogs/paul_white/archive/2011/02/16/when-is-a-seek-not-a-seek.aspx
http://sqlblog.com/blogs/paul_white/archive/2011/02/17/so-is-it-a-seek-or-a-scan.aspx
http://sqlblog.com/blogs/paul_white/archive/2011/02/17/Seeking-Without-Indexes.aspx
Cheers,
Paul
Comment by SQL Kiwi — March 23, 2011 @ 8:00 pm
Thanks Kendra,
I created them because the original icons were so low resolution. So after some squinting and some messing around with Illustrator, voila!
Comment by Michael J. Swart — March 24, 2011 @ 8:39 am
[…] @mjswart posts Last Word On Scans Posted on March 24, 2011 by sqlmashup https://michaeljswart.com/2011/03/last-word-on-scans/ […]
Pingback by @mjswart posts Last Word On Scans | sqlmashup — March 24, 2011 @ 11:49 am
[…] Last Word On Scans – A fantastic post this week from Michael J. Swart (Blog|Twitter) continuing his recent theme of looking at execution plan scan operators and their associated performance counters. Worthy of a permanent spot on the SQL Server Performance reference page no less. […]
Pingback by Something for the Weekend – SQL Server Links 25/03/11 | John Sansom - SQL Server DBA in the UK — March 25, 2011 @ 6:05 am
@Paul, Thanks for the insight. It’s too bad that MS is a little light on the feedback over at Connect. It’s a common pet peeve unfortunately.
And don’t be so humble about the suggested links. Those articles of yours are great. I’m both encouraged and discouraged by them. Encouraged because now I know I’m not the only one who got confused by Microsoft’s fuzzy terminology and discouraged because I feel I should have done my due diligence and found your stuff before posting mine. At the very least linking to them myself.
Comment by Michael J. Swart — March 25, 2011 @ 1:43 pm
Ha! Be encouraged, not discouraged!
Comment by SQL Kiwi — March 25, 2011 @ 5:33 pm
Hi I have one question
Schenerio :
============
create table emp (id int , [name] varchar(20) , sal money ,dept int)
Create clustered index empid on emp(id)
Create index [empname] on emp([name])
Create index empdept on emp(dept)
select * from emp — using cluster index
select count(*) from emp — using nonclusterindex “empname”
Question :
==========
why for Count(*) optimizer is using nonclustered index
If possible, please reply via mail
Comment by Saurabh Sinha — July 6, 2011 @ 1:36 am
Hi Saurabh,
Well, it’s off topic, and I’m hoping the question isn’t homework.
But the reason the optimizer uses the nonclustered index is because it’s smaller (in fact it’s the smallest nonclustered index).
In the first query, we need all the data and all the data is stored in a clustered index (That’s what it means for an index to be clustered).
In the second query we need only the number of rows. And the number of rows can be calculated from the nonclustered index. Since the non-clustered index takes up less space, it’s quicker to read it and calculate the count from that.
Comment by Michael J. Swart — July 6, 2011 @ 8:04 am
Thanks michael
Comment by Saurabh Sinha — July 7, 2011 @ 12:31 am
[…] following was inspired by comments at “Last Word On Scans” It was drawn for drawing’s […]
Pingback by Some Failed Blog Topics - SQL Server - SQL Server - Telligent — February 8, 2013 @ 5:15 pm
[…] good write-up by Michael J. Swart seems to indicate that a key lookup will count toward the Probe Scans/sec counter under the Access […]
Pingback by How to measure Key Lookups per Second on SQL Server — March 2, 2023 @ 12:47 am