July 20, 2017 at 7:21 am
SQL-DBA-01 - Thursday, July 20, 2017 7:08 AMOne question -
If there is no index vs an index scan / clustered index scan, will that be the same?
The answer to this is situational and nuanced. To a large degree, a scan is a scan. So a heap scan (table scan) vs. a clustered index scan, they're more or less the same. In fact, you can see, in some cases, places where a heap scan runs a little bit faster. Now, a nunclustered index scan vs. heap/clustered index scan, things get weird again. In many (most?) cases, the nonclustered index is smaller than the clustered index/heap. So a scan of the nonclustered index will be faster just because it's scanning less data.
I would not suggest you start planning your database design around scans though. By and large scans are something to be avoided. If you're getting lots and lots of scans, you need to reexamine your database table design, the index design, possibly your statistics maintenance, and the queries you're running against it.
When I stated earlier that scans are not necessarily bad, it's predicated on what the query is doing. SELECT * FROM Table. That's going to scan, and it's a good thing. A series of individual seek operations to retrieve all data from a table would be like writing a cursor that retrieves one row at a time. However, why are we retrieving all the data from the table? Why isn't there filtering? I would answer these questions before I started trying to figure out how to make my scan as fast as possible (the answer is getting good hardware, not picking a heap over a cluster).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 20, 2017 at 7:30 am
Awesome. Many Thanks Grant.
One more inputs from you. Even though I have a Non Clustered Index on a table, I still in Execution Plan that it is doing "Index Scan". Apparently it should be like a non Clustred Index scan. Why so? Are Index Scan different that Clustered Index Scan/Non Clustered Scan?
The reason for asking this is - do I need to take any special measurement on this.
Thanks.
July 20, 2017 at 7:51 am
SQL-DBA-01 - Thursday, July 20, 2017 7:30 AMAwesome. Many Thanks Grant.One more inputs from you. Even though I have a Non Clustered Index on a table, I still in Execution Plan that it is doing "Index Scan". Apparently it should be like a non Clustred Index scan. Why so? Are Index Scan different that Clustered Index Scan/Non Clustered Scan?
The reason for asking this is - do I need to take any special measurement on this.
Look at the properties of the operator. It'll tell you exactly which index it's scanning.
Why? Without seeing the query, the table & indexes, and the execution plan, I don't know. Guesses, the statistics are out of date, or, the column chosen for the index is not very selective (unique), or, there's a calculation of some type running on the column in the filtering criteria of the T-SQL leading to a scan, or... a bunch of other stuff.
Yes, as I said before, it's different than a clustered index scan, but also, still the same. It's a scan of the entire index, but that entire index might be smaller than the clustered index which would explain why the optimizer chose to scan it rather than the clustered index. Without knowledge, I'm just speculating.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply