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?
asked Oct 03 '12 at 02:38 AM in Default
@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 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.
answered Oct 03 '12 at 02:45 AM
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. 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.
answered Oct 03 '12 at 02:41 AM
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. 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.
answered Oct 03 '12 at 08:02 AM
Kev Riley ♦♦
Might as well plug my other book, SQL Server Query Performance Tuning. 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 at the PASS Summit in Seattle this fall on this exact topic. Another way to learn.
Just to chime in a little...
There is Professional SQL Server 2008 Internals and Troubleshooting 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, 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. The question for you now is how deep do you really want to go? The paths are available, enjoy the ride.
answered Oct 03 '12 at 01:03 PM