4 ms·

It seems that the SQLPass requires registration to enter the "Session Recordings" page so I'll have to look into it later. I googled "execution plan lie" and t

It seems that the SQLPass requires registration to enter the "Session Recordings" page so I'll have to look into it later. I googled "execution plan lie" and the only stuff that comes up is regarding how Oracle's Explain Plan sometimes provided inaccurate information. Is that what you were referring to? I guess explains why Microsoft labelled their version of Explain Plan "Estimated Execution Plan". I mostly just include the actual execution plan when running the query so I can get the real execution plan and the io stats all in one go.

hobs 12y ago
Actual execution plan's costs in SQL Server still contain a lot of estimates, I have seen SQL Server guide you in the wrong direction if you are looking at the thing "costing" the most, when in fact I find when I am doing perf tuning my biggest first steps are: 1. Is the code pants on head stupid and not set based or not sargable 2. Do the query plans clearly misinterpret the data and why that is My current process is: 1. set statistics io on; set nocount on; 2. Grab actual execution plan, throwing it in sql sentry plan explorer (free edition) (holy crap good) 3. Finding which estimates are off in the breakdown in 2 4. Investigating specific code areas where estimates are vastly different than actual, fixing whatever issue (implicit conversions, udfs, old stats, etc) arises 5. Consider adding indexes if needed 6. Paste outputs of both stats into http://www.statisticsparser.com/ http://www.statisticsparser.com/ (you can have it print things as headers, so I like print 'test a' go exec sp_proc @params go print 'test b' go exec sp_proc2 @params ) 7. Compare if I did it right or not by checking overall reads, cpu time, etc Update: I have the videos in question on my dropbox, I dont know if they would like a public link, so if you dont want to sign up let me know via my email in my profile and I will send them to you.

hobs 12y ago

Actual execution plan's costs in SQL Server still contain a lot of estimates, I have seen SQL Server guide you in the wrong direction if you are looking at the thing "costing" the most, when in fact I find when I am doing perf tuning my biggest first steps are: 1. Is the code pants on head stupid and not set based or not sargable 2. Do the query plans clearly misinterpret the data and why that is My current process is: 1. set statistics io on; set nocount on; 2. Grab actual execution plan, throwing it in sql sentry plan explorer (free edition) (holy crap good) 3. Finding which estimates are off in the breakdown in 2 4. Investigating specific code areas where estimates are vastly different than actual, fixing whatever issue (implicit conversions, udfs, old stats, etc) arises 5. Consider adding indexes if needed 6. Paste outputs of both stats into http://www.statisticsparser.com/ http://www.statisticsparser.com/ (you can have it print things as headers, so I like print 'test a' go exec sp_proc @params go print 'test b' go exec sp_proc2 @params ) 7. Compare if I did it right or not by checking overall reads, cpu time, etc Update: I have the videos in question on my dropbox, I dont know if they would like a public link, so if you dont want to sign up let me know via my email in my profile and I will send them to you.