When you encounter slow SQL Server performance and bottlenecks, you're probably frustrated by the process of figuring out what's causing the problem and what you should do to solve it. Performance problems can stem from a variety of factors, including faulty hardware, inadequate indexing, poorly constructed SQL code, and insufficient memory. But how do you determine which of these factors is causing your specific problem? Until you know the cause, you can't make informed decisions about whether to spend money upgrading hardware or implementing a less expensive fix, such as tuning your SQL code.
Monitoring tools are available to help you catch problems before they arise and analyze them after they've occurred. Performance Monitor is a free tool in Windows 2000 Server and later, and SQL Profiler is included with SQL Server 2000 and later. However, you might find that these freebies lack essential features or are difficult to use. If so, you can turn to a variety of third-party tools on the market.The hitch? These third-party tools are often costly, and you'll need to invest some time in deciding which is the most appropriate tool for your environment. This Buyer's Guide aims to present some of your options and considerations for your decision-making process.
Performance Monitor Basics
Your performance monitor should address five fundamental areas of functionality:
- It should monitor memory usage to help determine whether an adequate amount of memory is available to SQL Server.
- It should monitor CPU processor utilization to help determine whether you need a CPU upgrade or need to install multiple processors.
- It should monitor disk I/O performance to determine whether your I/O subsystem is working efficiently and whether SQL Server can write or read data without waiting.
- It should monitor user connections to help determine whether the number of users is properly configured.
- It should monitor blocking locks, which help determine whether one process is forcing another process to wait.
In addition to these five key monitoring areas, the ability to establish a performance baseline and the ability to notify you of problems are basic ingredients of a performance monitor. Baseline measurements are crucial because they let you measure SQL Server's performance during normal circumstances. The best baselines display performance visually so that you can see at a glance how the server is performing. In his article "Proactive SQL Server Management" (June 2005, InstantDoc ID 46082), Kevin Kline discusses considerations to keep in mind when you're establishing a baseline. Finally, if a problem occurs, you'll require that the product contain an effective notification system—with such methods as email, pager, SNMP, and system broadcast.
More Features to Consider
Now, what about the bells and whistles? Most of the products in this Buyer's Guide provide lights-out monitoring—the ability to monitor a system even if it isn't booted or powered on—and remote-management capabilities that let you run the performance monitor from any remote computer. Not as common, however, is the ability to perform T-SQL performance analysis and tuning.As you know, poorly written code can have a big effect on performance, and a tool to analyze and tweak this code might be just what you need.
You might also consider the trade-offs inherent in the decision between agent and agentless monitoring. An agentless tool doesn't require that you locally install a software agent on the server and can reduce administrative costs and deployment time, whereas an agent-based tool enables highly customized monitoring and management. If you aren't sure which type best fits your needs, you might even consider a product that offers both options.
Do you want to be able to plan for future growth? Some of the products in this Buyer's Guide analyze monitored parameters and give you capacity-planning recommendations based on the analysis. Several other products feature automated problem resolution, such as the automatic execution of a SQL job in response to an alarm. Other products offer real-time monitoring, as well as the capability to monitor third-party databases. Finally, how important is it to you to produce reports in various formats? All the products in this Buyer's Guide let you either export to or produce reports in HTML format, and some offer a few more output options. The table on page 50 will help you compare the features of each of the products and assess which performance monitor might work for you.