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.
See associated table.
End of Article
