4 ms·

I guess this is the client-side alternative to SQLZOO[0] which is a fantastic resource I used to use back in the day to sharpen my SQL skills. Been doing SQL f

I guess this is the client-side alternative to SQLZOO[0] which is a fantastic resource I used to use back in the day to sharpen my SQL skills. Been doing SQL for so long now but only recently have I been exposed to how powerful indices and learning how to read the execution plan is. I realize this is beginner stuff based on a local browser-based SQLite but I wish more learning resources like this and SQLZOO had an advanced section to discuss performance issues you may encounter once you have a large enough dataset. For years I just assumed we just had so much data that having to wait for the query to complete was natural until I tried figuring out how to resolve a deadlock issue we've been having an accidentally figured out how to tune queries to run instantaneously. There are probably many people out like my former self who know the basics and also have to maintain huge databases. By the way, if you're one of them, "Use the Index, Luke"[1] is where I learned everything I needed to know about creating indices (though I don't think it has much about reading execution plans). [0] http://sqlzoo.net http://sqlzoo.net [1] use-the-index-luke.com

hobs 12y ago
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/1430267437/ 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
- xtrumanx 12y ago
  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

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/1430267437/ 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

xtrumanx 12y ago
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.