Introduction
In a discussion with an infrastructure administrator at one
of our clients I was asked how they could become more pro-active about
monitoring their SQL Server environment. I think this is a good question because although almost all companies are monitoring whether their SQL Server environment is operational, not all of them are monitoring the key elements that indicate whether the environment might not be operational shortly or to dramatize, indicate imminent disaster.
Effective SQL Server monitoring has to include the ability
to forecast any resource issues, monitor the individual environments and being
informed in an appropriate response time of any potential issues. Below are
some of the key elements of that conversation in an article.
Get the right tools
Having a monitoring tool is the first important point to
make, I don't really mind whether its bought off-the-shelf or custom-built, but
it needs to be configurable, robust and allow the easy monitoring of the key
elements without the need for loads of work every time a SQL Server database is
a added to the environment. Always use a separate environment for the
monitoring tool. Both the server and database need to be separate, it seems
obvious, but if your server has failed and your monitoring service is on that
server, it doesn't help much. Less importantly, but something to be kept in
mind is to choose your monitoring intervals so that they are frequent enough to
give you an appropriate response time, but not so frequent as to affect your
servers performance. Routinely test your monitoring tool and also the impact
the monitoring is having on your server's performance, especially after the
addition of a new server or database to your environment. On that note any new
servers in your environment should be automatically added to the monitoring
tool.
Devil is in the documentation
Documentation is an ugly word to most, but a necessary evil.
In order to effectively monitor your SQL Server environment you need to know
database and server configurations, any changes to these, how they deviate from
the normal default values and the company standards. In summary we need to
monitor server properties, database properties, instance configuration,
database configuration and security. This data should be collected across all
servers and then stored in a central repository and the collection should be
automated.
Watch this space
So we have our monitoring tool, but what do we need to be
sure we are watching? Backups is the first and most obvious port of call. Are
the various database backups running, that includes the full backups, any
differential backups and the log backups. Monitoring of the storage location
for availability is also recommended. Next would be the SQL Agent jobs and
monitoring whether any failed, did any take abnormally long and any jobs that
failed to run. A potentially crucial difference exists between jobs that don't
run and jobs that run, but fail. Speaking of space, monitoring of database file
sizes is essential. You should monitor not only certain set milestones (i.e.
80% ), but also the rate of growth over time, which is vital in planning for
extra disk-space.
Next, we would list memory, CPU and I/O, and again these
would need to be tracked over time. These metrics, looked at as a collection of
information, especially over time can help diagnose any problem or potential
problem areas. Then lastly, we would definitely want to monitor any specific
issues such as deadlocks, extended blocked processes, long running queries,
index issues, high severity errors and timeouts.
Conclusion
The main objective is to enable you to identify and respond
to problems as quickly as possible, ideally before they even occur. This
strategy will enable you to find the problem areas and improve the performance
of the servers and the applications that use them over time. I have seen many
instances, if you will excuse the pun, of organisations with properly
configured monitoring tools in place identify and fix SQL Server issues before users
are even aware of them.
George Toursoulopoulos
is a technology specialist and Director at Synetec, one of the UK’s leading
providers of software services and solutions.
