Spent the first day of the conference at the PSS Bootcamp. The PSS guys always put on a good show as they take people through what they do to solve customer’s problems.
The first part of the day was devoted to a performance tuning methodology. What do you do when the users are complaining that the server’s slow. The presenter went through the methodology that the PSS engineers use when presented with a performance problem.
Most of the process is aimed at finding the problem query or identifying a resource bottleneck on the server.
If the problem is currently occurring, one of the main tools is the performance dashboard, a new report introduced into Management studio with SQL 2005 SP2
If the problem is not currently occurring, then it’s necessary to use SQLDiag, profiler, perfmon or a combination of them. A very interesting new tool that they introduced is a data aggregation and reporting tool for performance data – SQL Nexus. The updated version is supposed to be available by end November.
The session finished with a brief look at some of the new features of SQL 2008 that would help out with performance issues. One of the big ones, at least for me, is the performance warehouse. SQL can be configured to collect performance related data continuously in the background and save that into a data warehouse. There are a collection of reports built into management studio that report off this data. Used properly, that should make finding performance problems much easier than currently.
The other feature in 2008 that looks fantastic – a dependency checker that actually works. Sounds great