Chapter 7. Monitoring SQL Server Performance

Terms you'll need to understand:

System Monitor

Database Engine Tuning Advisor (DTA)

Profiler

Activity Monitor

Trace flag

Database console command (DBCC)

Statistics

Workload

Dynamic management view

Baseline

Techniques you'll need to master:

Using tools to monitor performance

Generating workload for DTA

Recording activity over time

This chapter investigates the tools that are provided with SQL Server to inspect and record activity in an installation. With the information gathered you can then attempt to provide for an optimal database and application environment. Several of these tools ship with SQL Server, and some are built in to the Windows operating system.

SQL Server Management Studio is the tool you use to access most of the other tools. The Activity Monitor is useful for gathering a basic level of information. The Log File Viewer provides access to every SQL Serverrelated log. Dynamic management functions and views provide a version-specific method for troubleshooting. Using the database console command (DBCC) has always been the preferred SQL Server troubleshooting and optimizing method. The Windows System Monitor provides access to quantitative analysis results. SQL Server Profiler provides the deepest and most granular level of information to aid in analysis. Many other individual tools are also presented in this chapter.