The SQL Server Party Wagon
Well, I made it. After a long trek through Nebraska, Iowa, and part of Illinios I arrived early into Warrenville and to the Quest offices for Kevin Kline’s (blog | twitter) pre-conference on Performance Tuning & Troubleshooting.
Kevin ran us through his “Rocks, Gravel, and Sand” methodology. This is an approach by which you take performance troubleshooting and go from tackling the large problems to the very small.
Some key takeaways today were:
1. Check for the stupid stuff first. We sometimes find ourselves jumping in too deep when the problem was really superficial. Check the Windows Event Viewer and PerfMon. See if there are any problems that are occurring that have nothing to do with SQL Server first then start looking at SQL specific issues.
2. Insure your setup is correct. Are the data and log files physically separate? Are you treating your TempDB like a second class citizen? Is your SAN/Disc configured properly? Finally don’t just talk to your SAN admin about disc size, talk about I/O too.
3. Use SQL Profiler but be aware of its overheard. Script out your traces in Profiler and run them in a query versus in Profiler itself.
4. Use DMVs to check SQL Server performance and waits. People like Glenn Barry and Jimmy May have great resources on their blogs to help.
5. Baselining. The hardest question to answer is “how can you know if performance is abnormal if you don’t know what normal is”? Know how your SQL Server is trending and be able to easily answer inquiry about why it’s slow or fast during different times. You can also easily tell if something is really going awry if you have that data on-hand.
6. There are a ton of tools at a DBA’s disposal, maybe too much at times. If you get in the habit of “sifting down” to where the problem resides rather than spend your time hunting willy-nilly you will be better off.
It was a very productive and informative first day for SQL Saturday #67. I feel like I walked away armed and ready to go back and attack my SQL Servers in a new (and possibly less stressful) way.
Tomorrow is the actual conference and I’m looking forward to a good night sleep, meeting tons of new people tomorrow, and learning even more.
Finally a big thanks to Kevin (blog | twitter) for all the tasty SQL goodness today.
