Hacker News new | past | comments | ask | show | jobs | submit login

It depends on what engine you are using once you are talking about xplans, if you are into SQL Server and you are looking for a great book to reference thats almost all practical "this is how stuff is going to be interpreted", check out http://www.amazon.com/SQL-Server-Query-Performance-Tuning/dp... by Grant Fritchey (the previous book had Sajal Dam)

Additionally, SQLPass puts out a great DVD every year and in the past year or two they had some great talks about how to read xplans, why they are actually lies, and going into things like statistics io, measuring recompile/cpu time, determining if implicit conversions are causing issues, etc etc etc

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.

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/ (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.

Applications are open for YC Winter 2021

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact