As databases become larger and IT infrastructures become more complex, keeping databases running at optimal performance is a must in any enterprise. Factor in increasing demands placed on database systems for auditing, compliance, and security needs, and having a smoothly running database-driven infrastructure becomes a significant challenge. How can a DBA get a handle on monitoring and optimizing database performance?
Of course, DBAs can turn to a variety of free tools that provide basic performance-monitoring functions, but the growing complexity of database environments often demands a more robust performance-monitoring solution. A host of factors can affect SQL Server performance: Are your problems caused by defective hardware, stressed CPU resources, or limited system memory? Or could database fragmentation, poorly written software, or viruses be sapping performance?
Do Your Homework This Buyer’s Guide contains a comprehensive list of most of the SQL Server performance monitors on the market and attempts to provide as much information about each tool to help you make the right purchasing decision. Some of these products can be expensive, so be sure to perform some basic research to find out what solution would be right for you.
According to Chris Shaw, a senior database engineer with SQL on Call (www.sqloncall.com), doing a little homework before you make that purchase can save you lots of time and money. Every SQL Server performancemonitoring tool should be able to provide at least as much information as the aforementioned free SQL Server monitoring utilities. “Every monitoring tool should be able to provide—in one location—all the features that you would get from running SQL Server Profiler and Performance Monitor.” In addition, Shaw suggests that DBAs look for the following features when shopping for a performance monitor:
- Low overhead—A good performance monitor must be able to run unobtrusively in the background, monitoring performance while consuming as few system resources as possible. Some products are well behaved in this respect, but others can negatively affect the performance of the systems they’re designed to monitor.
- Customizability—SQL Server installations can vary widely, so it’s important that a performance monitor be easily customized to fit the specific needs of your environment. Even the most feature-packed program is useless if it can’t adapt to meet your site requirements.
- History functions—Having the ability to set a performance baseline for comparison purposes is a must. “If I were to tell you that your SQL Server CPU utilization is at 30 percent, that may not mean a whole lot,” said Shaw. “You have to be able to compare the figure to a historical baseline for the information to be meaningful.” The best monitors include some form of dashboard or reporting function that makes it easy to compare current and historical performance.
- Memory counters—Server performance problems can take many forms, but Shaw believes that memoryrelated problems are often overlooked. “Look for a monitor that includes a memory counter,” he said. “Of all the measurable items we can monitor, memory is one of the hardest to get a handle on.”
- Scalability—“The tool must be able to scale for an enterprise,” explained Shaw. “The ability to perform comparisons between different servers based on different criteria is important. For example, if I have a new database coming online and I want to piggyback that database onto another server, I need to know what the impact is going to be.”
Planning for the Future
Some performance monitors are more fully featured than others and include advanced features that might be useful as your database infrastructure continues to grow. These include the ability to set custom alerts and notifications, provide performance forecasting and capacity planning, and automatically resolve common problems.
The upcoming release of SQL Server 2008 is another important factor to consider. Will you be moving to SQL Server 2008 in the near-term, or is an upgrade decision still years in the making? Performance Monitor can help you squeeze additional performance out of your existing infrastructure, thus delaying your upgrade to the new platform. SQL Server 2008 will include updated tools for performance monitoring, so you also need to carefully evaluate the effectiveness of those forthcoming features.
