Verifying Estimated Execution Plan results

Question

Verifying Estimated Execution Plan results

Brandie Tarvin

SSC Guru

Points: 173108
More actions
April 13, 2012 at 5:45 am

#267321

So, I'm playing with a query and the Estimated Execution Plan. SQL suggested an index which I slightly disagreed with (right column, wrong order and INCLUDE). I added a composite NC index with two columns (no INCLUDE), and the execution plan changed. I think it changed for the better, but I'd like those with more experience in reading EPs to verify if I'm reading this correctly.
Prior to fix
Type: Index Scan
Estimated I/O Cost 1.50016
Estimated CPU Cost 0.185223
Estimated Executions 1
Estimated Operator Cost 1.68539 (89%)
Estimated Subtree Cost 1.68539
Estimated Number of Rows 1
Estimated Row Size 35B
Ordered False
Node ID 4
--Not sure what Node ID is.
Post fix
Type: Index Seek
Estimated I/O Cost 0.003125
Estimated CPU Cost 0.0001608
Estimated Executions 1
Estimated Operator Cost 0.0032858 (50%)
Estimated Subtree Cost 0.0032858
Estimated Number of Rows 1.07875
Estimated Row Size 11B
Ordered True
Node ID 4
--Not sure what Node ID is.
Now, even though Number of Rows went up, everything else went down and the Scan turned into a Seek. The Key Lookup (values not listed) in the task bumped up from 0% to 50%, but as the Scan / Seek went down from 89% to 50%, and all values in the execution plan must always equal 100%, I figure that's just a negligible re-balancing of the plan.
I did make things better, right? I'm a little confused as to why Row Size would have changed, though. Is it because it's not reading as much data from the table now that I've added this other NC index?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Without seeing the entire exec plan, no way to tell.

Check what statistics IO and statistics time tell you. If the reads are down and the duration is down, the performance is improved.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 2

Thank you. I knew I was overlooking something.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.