asked
Performance tuning, where to begin?
I am not new to SQL Server but have mostly spent my time working on consolidation projects, building new environments, doing upgrades, and working on some ETL. What I seriously lack is the ability to jump on a troubled server and figure out what is or could be causing issues. Standard things like disk I/O, low PLE I can look at. What I don't know how to do is find out which queries are causing issues, where contention is, what queries are eating up my cache, crushing my disk, spiking my CPU, etc. I have ran the queries that Glenn Barry put out, but once I get some of the results I am not 100% sure what to do with them. What is the book, webinar, class that I should attend to really build out this skill set?
@mike, great plug on the DMV book. I have it in my collection as well. I found another great supplement to that is a book by Grant Fritchey on execution plans. [SQL Server Execution Plans](
http://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026/ref=sr_1_cc_3?s=aps&ie=UTF8&qid=1349232190&sr=1-3-catcorr&keywords=grant+fritchey) Once you find those pesty queires causing the issues you have to tune them (if you can). Knowing how to dissect the execution plan is key. @Scotty, you also should be looking at the SQL Server specific perfmon counters. They provide a great deal of information about memory and I/O.
Hey Scotty, depending on your version of SQL, if you are using 2005 and above your best friend are the DMV's. Check out this book by Tim Ford and Louis Davidson [Performance Tuning with DMV's](
http://www.amazon.com/Performance-Tuning-Dynamic-Management-ebook/dp/B004R1Q9G2/ref=sr_1_5?ie=UTF8&qid=1349231952&sr=8-5&keywords=louis+davidson). Glenn Barry offers a lot of explainations about his queries in additional blog post as well as others have referenced his material in their own posts. Performance tuning is as much as art as it is science. Hang in there and good luck. I am looking forward to the response from others on here as well. I am always learning myself.
This is often a problem with troubleshooting scripts, if they pump out a number - bing! 59! - there's often no context as to what that means or what it should be. Is a lower number better or worse? is the range 0-10 or -1000 to + 3million. Take for example a script that shows the top 10 wait types - even in the best performing server in the world, there will be 10 top wait types, so that information has to be taken, read and understood in context. I'd also look at [Brent Ozar's server takeover script - sp_blitz][1]. Whilst this is not a troubleshooting script per se, it does look for many common issues and not only reports them out but the website has a lot of supporting information about the what, why and where. [1]:
http://www.brentozar.com/blitz/
One issue that I've seen is that index/statistics maintenance is scheduled before jobs that rearrange data in databases, while optimal would have been the other way around. Not the answer to your question but make sure that your server is optimally configured. If you are looking for a class I would seriously have a look at:
http://www.sqlskills.com/T_ImmersionPerformanceTuning.asp While I haven't attended the class myself it looks really good and it seems to be addressing your needs. At this years PASS Summit the new version of sp_blitz will be released. It will be able to catch query issues such as implicit conversion, cursors, and bad functions. Sp_blitz is moving into troubleshooting territory maybe?
Might as well plug my other book, [SQL Server Query Performance Tuning][1]. There's a 2008 version and a 2012 version. It covers gathering performance metrics on the server, gathering query metrics, reading execution plans, understanding how the optimizer works, statistics, index fragmentation, common problems and solutions, pretty much the hole shebang. And, I'm teaching a [full day pre-conference seminar][2] at the PASS Summit in Seattle this fall on this exact topic. Another way to learn. [1]:
http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1349266989&sr=8-1&keywords=fritchey [2]:
http://www.sqlpass.org/summit/2012/Sessions/SessionDetails.aspx?sid=2929
Just to chime in a little... There is [Professional SQL Server 2008 Internals and Troubleshooting][1] written by a group of authors including: Brent Ozar, Jonathon Kehayias, and Cindy Goss. Likewise there is the *bible* of SQL Server nuts and bolts, [Microsoft SQL Server 2008 Internals][2], by Kalen Delaney, Paul Randal, Kimberly Tripp and Conor Cunningham. And you've made a good decision by coming here as well. Make sure you also browse [the main site][3]. The question for you now is how deep do you really want to go? The paths are available, enjoy the ride. [1]:
http://www.amazon.ca/Professional-Server-2008-Internals-Troubleshooting [2]:
http://www.amazon.ca/Microsoft-SQL-Server-2008-Internals/dp/0735626243 [3]:
http://www.sqlservercentral.com/
Another book recommendation regarding Troubleshooting and Performance is "Troubleshooting SQL Server: A Guide for the Accidental DBA" by Jonathan Kehayias and Ted Krueger. There you'll find also some hints. You can get it as a free ebook [
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/][1] [1]:
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
15 People are following this question.
