December 20, 2011 at 7:01 am
Good day all -
I seek some assistance in understanding the attached execution plan. Another thing, the attached exec plan shows some clustered index scans and seeks, as well as some full text operations and a missing index impact. My question are
- What does the impact of missing index mean, and how can I measure it after resolving it?
- Clustered index seek vs scan, what's the difference? I don't think so its the same as table scan
- Exec plan shows a lot of operations, nested loops, scalar, merge, stream aggregation. does these impact production level queries?
Regards,
Kazim
December 20, 2011 at 7:08 am
This is an estimated plan, almost useless to debug performance.
You see a ton of operators because you are selecting from a view, not a table.
There's a timeout error in the plan. That means that the optimizer gave up trying to optimize this query.
I can't give suggestions without seeing an actual execution plan.
A scan is a scan, it means that all pages are read and processed on that object. The thing with NC index is that they usually have a lot less pages than the whole table. So that often makes them a better choice.
For the missing index impact, start profiler then in 3 batches (separate with go terminator), run the query, build the index, rerun the query. See which one is faster and by how much. From that decide if you want to keep the index.
Keep in mind the cost of keeping that index in sync. It's often benefecial but it can be harmful.
No way to teach you all those operators, here read this for a better understanding. => http://www.sqlservercentral.com/articles/books/65831/
December 20, 2011 at 8:09 am
To add to what Ninja said the clustered index scan is just a table scan. If you want to measure the impact of an index you could also set statistics io on and look at the differences in scan count, logical reads, physical reads etc. with and without the index.
December 20, 2011 at 8:48 am
bwoulfe (12/20/2011)
If you want to measure the impact of an index you could also set statistics io on and look at the differences in scan count, logical reads, physical reads etc. with and without the index.
However bear in mind that scan count is not the number of times the table was scanned. It's a little on the meaningless side, focus on logical and physical reads. On the 2nd execution and after, there should be no physical reads as all the reads will come from cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 8:53 am
GilaMonster (12/20/2011)
bwoulfe (12/20/2011)
If you want to measure the impact of an index you could also set statistics io on and look at the differences in scan count, logical reads, physical reads etc. with and without the index.However bear in mind that scan count is not the number of times the table was scanned. It's a little on the meaningless side, focus on logical and physical reads. On the 2nd execution and after, there should be no physical reads as all the reads will come from cache.
Hmm...I always thought it was. This MSDN page makes it sound as though it is too.
http://msdn.microsoft.com/en-us/library/ms184361(v=SQL.105).aspx
December 20, 2011 at 9:06 am
bwoulfe (12/20/2011)
GilaMonster (12/20/2011)
bwoulfe (12/20/2011)
If you want to measure the impact of an index you could also set statistics io on and look at the differences in scan count, logical reads, physical reads etc. with and without the index.However bear in mind that scan count is not the number of times the table was scanned. It's a little on the meaningless side, focus on logical and physical reads. On the 2nd execution and after, there should be no physical reads as all the reads will come from cache.
Hmm...I always thought it was. This MSDN page makes it sound as though it is too.
http://msdn.microsoft.com/en-us/library/ms184361(v=SQL.105).aspx
I've seen queries with only index seeks (no scans) that show a non-zero scan count (hence its not scans only)
I've seen queries with a scan count of 0 and a non-zero logical reads. (a little harder to repo)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:13 am
Please receive the updated plan, it is the actual plan (I selected the include actual exec plan). I do see a lot of operators in this plan too, so what's the difference?
I am also including client stats.
Regards,
Kazim Raza
December 20, 2011 at 9:15 am
Very little difference actually. The actual plan includes the actual row counts, actual execution count and actual data size in the operator and data flow properties.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:19 am
GilaMonster (12/20/2011)
bwoulfe (12/20/2011)
GilaMonster (12/20/2011)
bwoulfe (12/20/2011)
If you want to measure the impact of an index you could also set statistics io on and look at the differences in scan count, logical reads, physical reads etc. with and without the index.However bear in mind that scan count is not the number of times the table was scanned. It's a little on the meaningless side, focus on logical and physical reads. On the 2nd execution and after, there should be no physical reads as all the reads will come from cache.
Hmm...I always thought it was. This MSDN page makes it sound as though it is too.
http://msdn.microsoft.com/en-us/library/ms184361(v=SQL.105).aspx
I've seen queries with only index seeks (no scans) that show a non-zero scan count (hence its not scans only
I have seen that too and it boggled my mind. I actually did some digging online and couldn't find a good explanation. Oh well. I'll keep that in mind going forward. Thanks Gail. 😉
December 20, 2011 at 9:21 am
Absolutely, very little difference. Now I am kinda lost..too.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply