SQL Server™ 2005 Bible

Chapter 49. Measuring Performance

In This Chapter

• Establishing baselines

• Viewing performance data

• Defining a Database Performance KPI

The first optimization strategy has to be measuring performance. Without the objective evidence of hard numbers, performance is a subjective feeling, and while a positive perception of performance is a good thing, bottlenecks, trends, and optimizations are best defined by the facts. Optimization theory—the framework for designing high-performance databases—works. Solid measurements are the proof.

Fortunately, some excellent methods are available for measuring SQL Server performance:

• SQL Server installs several counters within Windows' Performance Monitor, which can graph the aggregate performance of SQL Server and other system resources, or save the data in logs for later analysis.

• SQL Server Profiler exposes nearly every event within SQL Server with incredible detail. This tool is a database optimizer's dream.

• Dynamic management views expose many details about resource usage within SQL Server 2005.

• The Database Engine can return time and I/O statistics following any T-SQL command execution.

• T-SQL batches or stored procedures using GetDate() can easily calculate their overall execution time to three milliseconds accuracy and write the duration to a log table.

Measuring Accuracy

I can't write about performance testing without first mentioning testing for accuracy. One of my pet peeves is the tendency of management to readily spend money for hardware, rarely spend ...