Chapter 49. Measuring Performance
In This Chapter
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.
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 ...