Tricks for Continuous Database Monitoring Improvement

Database performance issues aren’t uncommon. Fortunately, there is usually a fairly intuitive solution to most problems. The most important thing a DBA can do to fix issues quickly and improve SQL Server performance is to make sure your performance monitoring strategy is up to date and aligned with your database monitoring needs. If it isn’t, it’s time to take action. 

The first step to improving your database monitoring is assessing your current situation. You won’t know where your monitoring is lacking or if monitoring is improving any of your performance issues if you don’t take a baseline measurement of your starting point. 

To set your performance baseline, you will need to assess the health of your SQL Server databases by asking yourself a few questions:

Are queries optimized? Underperforming queries are a sure way to slow performance. There are many tricks you can try to clean up your queries, but your best option is to implement a query optimizer that will analyze and rewrite your queries until it finds one that is faster.

Are indexes in good shape? Indexing done properly is supposed to speed up data retrieval. However, there are some common issues that can slow performance, including inefficient fill factor settings and page splits. The best way to avoid these performance issues is to not set a system-wide fill factor value and look at the needs of each index individually.

Is a lot of data fragmented? Fragmented data affects performance by putting too much blank space on a page or by causing pages to be added. The server has to scan and search for the fragmented data, which causes the database to run slowly. Running Rebuild or Reorganize can clean up fragmentation.

Is something eating CPU? Databases slow down when the CPU is under strain from multiple resources. Assess the CPU load and decide whether the problem is that you need to add more CPU or that there are inefficient resources using more CPU than they should.

Once you have a snapshot of the starting line, you’ll have a better idea of the types of monitoring improvements you need to focus on. Here are five ways you can improve your SQL Server database monitoring.

1. Assess your monitoring intervals and purge data as frequently as possible

Performance monitoring tools provide endless choices for what metrics to monitor, when to pull data, and how long to keep the data. If you are monitoring multiple instances, the amount of data you are collecting is potentially staggering (and expensive to store). 

To keep your data to a manageable level, don’t collect data more often than you have to and only keep data long enough to spot trends. Once you have recorded anything of interest, archive or get rid of the data.

2. Check that your queries are performing efficiently

There are a lot of places for performance issues to hide in SQL Server. Because database applications frequently execute SQL queries, assessing the state of your queries is a great place to start eliminating problems. 

For example, monitoring how queries perform over time will help you isolate any that are using more resources than normal. Once you have identified problematic queries, you can run each query to determine the specific source of the problem and correct it.

3. Review Perfmon counters and settings

SQL Server Performance Monitor (Perfmon) is a valuable tool that helps DBAs isolate weak points in their SQL Server databases. Perfmon allows you to set up performance counters for each of your servers to identify CPU bottlenecks, disk performance, memory problems, and much more. 

This article by Brent Ozar explains how to get started using Perfmon and describes some best practices.

4. Configure your alerts based on severity

Though there are many alert configurations available, it’s best to be selective when configuring alerts so you don’t succumb to alert fatigue. A good rule of thumb is to set up alerts for issues Severity 17 or higher and for error messages 823, 824, and 825—in other words, those issues that can result in your servers going offline, severe data corruption, or data loss.

5. Take advantage of all the alarm management features your SQL Server performance monitoring tool offers

SQL Server monitoring solutions offer a wide range of management features. Take some time to get to know your tool’s features and be sure you are getting the most value for your investment. 

Some features to look for that will help you improve your performance monitoring process are: 

Rules: Lets you customize which alarms are triggered and when

Diagnostics: Tells you there’s a problem, then offers possible fixes

Issue prioritization: Focuses your attention on the highest-severity problems first

Mobile alerts: Allows you to access, assess, and start triaging alarms from anywhere at any time

Noise reduction: Helps you avoid alarm fatigue by receiving only those notifications you need