Database Reference
In-Depth Information
Performance Baseline
One of the main objectives of performance analysis is to understand the underlying level of system use or pressure on
different hardware and software subsystems. This knowledge helps you in the following ways:
•
Allows you to analyze resource bottlenecks.
•
Enables you to troubleshoot by comparing system utilization patterns with a preestablished
baseline.
•
Assists you in making accurate estimates in capacity planning and scheduling hardware
upgrades.
•
Aids you in identifying low-utilization periods when the database administrative activities can
best be executed.
•
Helps you estimate the nature of possible hardware downsizing or server consolidation. Why
would a company downsize? Well, the company may have leased a very high-end system
expecting strong growth, but because of poor growth, they now want to downsize their
system setups. And consolidation? Companies sometimes buy too many servers or realize
that the maintenance and licensing costs are too high. This would make using fewer servers
very attractive.
•
Some metrics make sense only when compared to previously recorded values. Without that
previous measure you won't be able to make sense of the information.
Therefore, to better understand your application's resource requirements, you should create a baseline for your
application's hardware and software usage. A
baseline
serves as a statistic of your system's current usage pattern and
as a reference with which to compare future statistics. Baseline analysis helps you understand your application's
behavior during a stable period, how hardware resources are used during such periods, and the characteristics of the
software. With a baseline in place, you can do the following:
•
Measure current performance and express your application's performance goals.
•
Compare other hardware or software combinations against the baseline.
•
Measure how the workload and/or data changes over time.
•
Ensure that you understand what “normal” is on your server so that an arbitrary number
isn't misinterpreted as an issue.
•
Evaluate the peak and nonpeak usage pattern of the application. This information can be used
to effectively distribute database administration activities, such as full database backup and
database defragmentation during nonpeak hours.
You can use the Performance Monitor that is built into Windows to create a baseline for SQL Server's hardware
and software resource utilization. You can also get snapshots of this information by using dynamic management views
and dynamic management functions. Similarly, you may baseline the SQL Server query workload using Extended
Events, which can help you understand the average resource utilization and execution time of SQL queries when
conditions are stable. You will learn in detail how to use these tools and queries in Chapters 2-5.
Another option is to take advantage of one of the many tools that can generate an artificial load on a given server
or database. Numerous third-party tools are available. Microsoft offers SQLIO (available at
http://bit.ly/1eRBHiF
)
,
which measures the I/O capacity of your system. Microsoft also has SQLIOSim, a tool for generating SQL Server-
specific calls and simulated loads (available at
http://bit.ly/QtY9mf
)
. These tools primarily focus on the disk
subsystem and not on the queries you're running. To do that, you can use the performance testing tool added to SQL
Server 2012, Distributed Replay, which is covered at length in Chapter 24.
