Are You Measuring SQL Server Performance with These Metrics?

Application and system performance play a huge role in how a user perceives your product’s value and your company’s competence. If the same page constantly times out or it takes forever to access a critical report, your database system’s performance will create a confidence issue for your organization.

High availability and reliable performance are key to keeping customers and internal system users happy, and they can even affect your bottom line. With these goals in mind, implementing a comprehensive SQL Server performance monitoring strategy is a must. 

Benefits of SQL Server Performance Monitoring

The most commonly touted benefit of performance monitoring is ease of troubleshooting and resolving issues. A well-configured SQL Server monitoring tool is the most efficient means to find and fix server and database problems quickly before they impact end users or become large, expensive issues.  

In addition to maintaining system health, implementing SQL Server performance monitoring can help keep your infrastructure costs down. One example is unnecessary storage expenses. When SQL Server performance is optimized, file size growth is monitored continuously. An excessive increase in file size triggers an alert so the issue can be resolved before disk space is depleted and additional resources are thrown at the problem.

And if you opt for a cloud-based monitoring solution, you can increase ROI even further by saying goodbye to the expense of housing and maintaining physical servers.

Track These Metrics to Improve Your SQL Server Performance

We know SQL Server performance monitoring is a great strategy for fixing problems and saving money, but you won’t get the full benefit from your SQL Server monitoring tool unless you track the right metrics. To get optimal performance from SQL Server, here are five areas you’ll want to watch and the most important metrics to track from each.

1. Buffer Cache

The buffer cache stores copies of recently used database pages. SQL Server looks in the cache first, but if it doesn’t find what it’s looking for there, it goes to the disk. Reading from the disk is slow, which can negatively affect performance.

Cache Hit Ratio

Cache hit ratio shows how frequently SQL Server accesses pages from cache. Higher values indicate that the pages were accessed from memory and not from the disk. If you are consistently seeing a low cache hit ratio, check for a bottleneck in system memory. 

Page Life Expectancy

Monitoring page life expectancy shows how long a page stays in memory instead of going to disk. The longer the page life expectancy, the better the cache hit ratio.

Checkpoint Pages/Sec

SQL Server sets up checkpoints to free up space in the buffer cache by periodically flushing modified pages from the buffer to the disk. The number of flushed pages depends on configuration settings and server usage, so it’s important to set a baseline for comparison. If you notice an increase in pages being flushed, you may have an I/O problem.

2. Indexes

Database indexes help speed up operations in tables with a lot of records. Although indexes are a useful tool for improving some database functions, there are inherent issues with indexes that can degrade performance if not caught quickly.

Fragmentation

Index fragmentation occurs when records are added and deleted. Over time, these changes cause performance issues by leaving too much blank space on too many pages and adding new pages out of order. This can slow down scans and use excessive memory. 

Monitoring index fragmentation will let you know when you need to run Rebuild or Reorganize on an index to fix the fragmentation and improve performance.

Fill Factor and Page Splits

The fill factor setting lets your indexes know how full each page should be. If a page is too full, page splits occur; if a page isn’t full enough, you are wasting resources. 

Tracking page splits can help identify when a fill factor adjustment is needed. For the best performance, don’t set a system-wide fill factor value. Evaluate individual indexes and set the fill factor at an appropriate level for each. 

3. T-SQL

To optimize performance, SQL Server batches SQL statements, then compiles them into execution plans and caches them for reuse. If SQL Server can’t reuse the execution plan, the plan is recompiled, which uses a lot of CPU and degrades performance. Therefore, it’s best to keep the number of SQL statement recompiles as low as possible.

Monitoring rates of SQL compilation and recompilation will show whether you need to reconfigure some areas, such as stored procedures and query parameters.  

4. Blocking

SQL Server blocking is caused by multiple clients and processes requesting access to the same resources at the same time. If a resource is already being used, access is locked and the resource is unavailable for subsequent requests. This puts the process on hold until the resource is released and available again. 

Lock Waits

Requests normally don’t have to wait on locks, so this metric should stay around zero. An increase in lock waits indicates a problem with load time, so you will need to track down the root cause of the issue.

Blocked Processes

Tracking blocked processes is a good way to avoid a lot of emails informing you of a blocked process. Use SQL Server Profiler to capture data about blocked processes and review the report regularly. 

5. Resource Usage

Tracking resource usage metrics is an effective way not only to pinpoint performance issues but also to help with capacity planning. 

Row Counts

Monitoring row counts provides direct, easily trackable insight into the volume of data associated with a certain table over time. An unexpected increase or decrease in row count should prompt an investigation into the cause. 

Database File I/O

By measuring I/O metrics against database files, you can track how much data is being written and read by any given data file. If you observe a potential issue, this metric can help narrow down the root cause. Tracking database file I/O also helps with infrastructure planning because it can help you predict future data throughput and adjust resources accordingly.

Transaction Log Backup Size

When a server crashes because of overload, SQL Server uses the database log records stored in virtual log files (VLFs) to recover. It’s important to monitor how many VLFs are in the transaction log, because too many can slow down the recovery processes when restoring after an outage.

SQL Server performance monitoring is a crucial component of an organization’s quality control strategy. Tracking a few key performance metrics can give you a reputation for providing your users with a system they can trust and rely on; failing to do so can make your system just another buggy application users dread.