Improving SQL Performance Tuning by Using an SQL Server Monitor, Part 1

SQL Server is a complex product when it comes to performance tuning. There are so many moving parts and statistics to keep track of that DBAs often don’t know where to start with their SQL performance tuning efforts. The confusion can easily be managed, however, with OEM and third-party SQL Server monitor products on the market.

SQL Server monitors can be used to capture a variety of statistics that relate to the performance of SQL Server databases. DBAs can use the captured data to determine where performance issues exist and the best way to address them.

Here are five ways to use a SQL Server monitor for improved SQL performance tuning efforts:

1. Collect Wait Events
Whenever an SQL Server attempt to perform an action but is forced to wait on another action to occur, this is a wait time event. An accurate picture of database performance can be gleaned by reporting, recording, and analyzing all wait time events. All SQL performance tuning efforts should begin by cataloging and analyzing wait events.

2. Locate I/O Bottlenecks
Wait time event statistics can be used to locate troublesome I/O issues. Upon finding an inordinate number of physical I/O bottlenecks, you should first attempt to tune away the problems before adding more physical resources.

3. Find and Tune Problem Queries
Your SQL Server monitor can be used to locate and identify queries and stored procedures that are affecting database performance. Adequately tuning these troublesome queries will provide your database with significant performance improvements.

4. Reuse Query Plans
Your SQL Server uses a lot of memory and CPU resources when compiling query plans. Applications that don’t use stored procedures or correctly parameterized queries make it nearly impossible to reuse the query plan. Planning queries for reuse will help to reduce the transaction rate and improve overall SQL Server performance.

5. Keep an Eye on Index Usage
Use your SQL Server monitor to determine which indexes are being used. It can even tell you the elapsed time for processes like latching and locking.

In the next part of this article, we’ll look at five more ways to use your SQL Server monitor to improve your SQL performance tuning efforts.