Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 11. Consolidating Data Collection with SQLDiag and the PerfStats Script

WHAT'S IN THIS CHAPTER

• An approach to collecting performance data for complex or challenging SQL Server problems

• When to use SQLDiag and the PerfStats script

• How to configure SQLDiag and the PerfStats script

• Extending the capability of SQLDiag and the PerfStats script

When handling complex SQL Server problems, gathering the right information to be able to solve the problem can be labor intensive, time consuming and error prone, especially when working remotely or in a multi-level support organization. This chapter contains information about tools that simplify and automate data collection to provide the best possible chance of collecting the data necessary to fix a problem, the first time.

SQLDiag and the PerfStats script provide data collection that can be used as input to SQLNexus, a GUI tool for processing data to assist identifying problem queries and performance bottlenecks. Chapter 13 focuses on getting the best results from SQLNexus. Combining the data collection functionality of SQLDiag and PerfStats with the analysis capabilities of SQLNexus and Performance Analysis for Logs (PAL) provides engineers with many of the same tools available to Microsoft support engineers. Using these tools at the right time and in the correct way enables engineers to tackle some of the most complex and hard to resolve SQL Server issues.

Someone once said that a DBA is only as good as his or her last outage, and these tools ...