The Ideal Monitoring Strategy for Applications Powered by SQL Server

Sponsored Blog

Every business expects their critical applications to be running at peak performance round-the-clock. Databases are critical components for an application’s availability at a fundamental level. If the database is not up and running, the business will stop running. As a result, database administrators take special care in maintaining, monitoring, and troubleshooting their database systems.

If you are a Microsoft SQL Server administrator, you will agree with me when I say that a DBA’s job is challenging and stressful. Sometimes, a small mistake made by a DBA can lead to system downtime and result in financial losses. On other occasions, you may end up taking the blame for things that are beyond your control, such as poor application design that slows down the database operations.

All the applications used by a business are susceptible to performance problems due to various factors such as resource consumption, application design, or usage. There are countless numbers of applications added into the business infrastructure every year. The Microsoft SQL Server admin is expected to support the complete lifecycle of development, testing, quality assurance testing, integration testing, system testing, and production environments.

Identifying the root cause of performance issues is typically very time consuming. The administrator may end up spending several days sifting through all the available performance data, but still reach nowhere. If any performance metric is not available, the troubleshooting process may be delayed further. On most occasions, DBAs end up firefighting with no time left for proactive work.

Get the right monitoring systems in place

So, how do you ensure optimal performance of your applications and their underlying SQL databases without leading to DBA stress? You need a monitoring strategy that includes proactive monitoring of a hybrid set of applications and databases in your data center and on the cloud.

While most monitoring tools provide you with lots of performance data, it is important to understand where to start to troubleshoot performance issues effectively.  From a server monitoring standpoint, it would be ideal if you start with the basics:

Although there are numerous other parameters to monitor, you can start off by keeping track of these higher-level parameters.

If users complain that queries are taking longer than usual to execute, it could be due to new processes being added to your system thus contributing to the system load. Therefore, it is essential you keep track of the load on your system.

The typical performance bottlenecks in SQL Server are those involving CPU, memory, file I/O and locking, blocking or deadlocking. Your monitoring solution should be able to identify how these potential bottlenecks may affect the SQL Server. In addition, you need to be aware of slow running queries, non-optimal execution plans, check if your system is suffering from deadlocking as well as look out for rapidly growing disk space.

From a monitoring standpoint, you need to ensure that you are always on the lookout for possible bottlenecks on SQL Server resources. Set up threshold profiles for key performance indicators, look for repeated patterns that stress on any of your resources and then narrow down the window for further analysis. Once this has been identified, look for detailed profiling of the task and try to eliminate the bottleneck.

By getting your monitoring strategy right, you can be confident of ensuring peak performance of your applications and their underlying databases.

Learn More

Arun Balachandran is a senior marketing analyst at ManageEngine and currently works for ManageEngine’s application performance management solution. Arun is the author of application performance management white papers such as "Integrated Performance Management for Physical, Virtual and Cloud Infrastructure" and "Going Beyond Plain Virtualization Monitoring."  He has a masters degree in computer applications.