Wednesday 17 September 2014

Top 3 Tips to Effective SQL Server Monitoring


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.