The most critical metrics for SQL Server monitoring

Tools to optimize, metrics to monitor

Key metrics for SQL Server monitoring

When it comes to answering queries and allocating resources, the behavior of SQL Server makes several metrics particularly critical to keep track of.

In this post, we’ll go through metrics for the following:

• SQL statements in T-SQL
• The buffer cache is a temporary storage area for data.
• Resources about tables
• Resource pools, indexes, and connections are all locked.

We’ve included a table with the metrics at the beginning of each segment. For each measure in the “Availability” column, we will provide the source from which you can obtain this information. This series will continue with a demonstration of how to collect metrics from different sources in Part 2.

We rely on metric language from our Monitoring 101 series throughout this post, which provides a foundation for data collection and alerting using metrics.

T-SQL performance metrics

​Batching, compiling, and caching T-SQL statements are some of the techniques SQL Server uses to reduce the latency of your queries. Monitoring these metrics will allow you to get the most out of this behavior and ensure that SQL Server responds to the questions in the manner you want.

Metrics to keep an eye on: 

Requests are processed in batches per second.
​
Measure the number of batch requests that the database engine receives per second to gain a high-level picture of how your database is being used in general and how it has changed over time. Sudden fluctuations in the rate at which batch requests are processed can reveal concerns with availability or unanticipated changes in demand. 

However, the number of batch requests per second tells you very nothing about the cost of a single batch of queries. This is because collections can contain an endless number of T-SQL statements, as well as the fact that a set that calls stored procedures might still be considered a single batch request. Batch requests per second and other work metrics such as the elapsed duration of an execution plan and resource metrics such as the amount of memory utilized by your database server should be monitored to assess the overall performance of your batch processing operations. 

While SQL Server maintains track of the mistakes that occur in your queries, there are no ready-made metrics that can be used to report these in the form of counts and rates. The built-in function @@ERROR returns an error code if the previous T-SQL command resulted in an error; otherwise, the function returns 0. In batches or stored procedures, you can use this function to deal with mistakes that have occurred. However, because its value resets with each subsequent execution, you can’t use it to track error counts over time unless you include manual instrumentation, such as programming your batches to call the function @@ERROR before they end and storing the results along with a timestamp in the results file. 

​
last elapsed time

Execution plans, which are batches of T-SQL statements compiled and cached by SQL Server, help increase query performance. You’ll want to ensure that the process behaves as expected and that it generates execution plans that are ideal for your system, mainly because it’s an automatic process. It is possible to determine the performance benefits of compilation by keeping track of the time it takes for your execution plans to be fully executed. A row of statistics data for each query plan stored in the cache is returned by the sys.dm exec query stats view, which includes the elapsed time of the plan’s most recent execution (last elapsed time) and the number of times the project was executed.

The amount of time it takes for an execution plan to complete is a helpful indicator of how well SQL Server’s optimization algorithms are performing. A query hint can be used to optimize the compilation process if your execution plans are taking longer to complete than you had anticipated. You can, for example, optimize your execution plan to obtain the first n rows (FAST), stay within a memory limit (MAX GRANT PERCENT), or perform any other action that differs from the default optimization method.

If you’re using the dynamic management view sys.dm exec query stats to determine how long your query plans are taking, keep in mind that the view only maintains statistics for execution plans in the cache. If a project is deleted from the cache, any statistics associated with it will be lost in this view. These statistics can be stored in your tables if desired, with the plan generation num field in SQL Server being used to link different recompilations of a single plan.

Per second, SQL compilations are performed.
​
After a batch is executed for the first time, SQL Server converts it into an execution plan and stores it in a temporary storage location. In an ideal scenario, SQL Server only needs to create an execution plan a single time. The worst situation is one where no order to meet the challenges is reused. Batches compile every time they are executed in this case, and the number of batch requests per second equals the number of batch compilations per second. To properly evaluate this parameter, it is necessary to compare it to the rate of batch requests received per second.

Depending on how quickly these metrics are concurrent, you may want to consider using query hints to set PARAMETERIZATION to FORCED. SQL Server’s query compilation is configured to substitute literal values in some T-SQL statements (SELECT, INSERT, UPDATE, and DELETE) with parameters, resulting in more reusable query plans. Forced parameterization is enabled by default.

The “SQL compilations/sec” statistic includes statement-level recompilations, which are a feature introduced in SQL Server 2005 that recompiles only the statements responsible for recompiling an entire batch of SQL statements. If forcing parameterization does not reduce the number of SQL compiles per second, consider monitoring the number of recompilations per second, as we will detail in the following section.

In cases where the rate of compilations approaches the speed of batch queries, the caching behavior of SQL Server becomes less efficient.
Recompilations of SQL statements per second

SQL Server recompiles execution plans whenever it restarts or whenever the contents or structure of a database has changed sufficiently to make an execution plan no longer be suitable for use. While it is frequently required to recompile your T-SQL batches for them to run, doing so can destroy any time savings achieved by batch execution. Keep an eye on the number of recompilations per second to determine if it correlates with a decrease in performance or merely a sign that SQL Server has optimized execution plans for changes in the tables you are monitoring.

Batch recompilation is performed by SQL Server based on automatically calculated thresholds. It is possible to change these thresholds and reduce the rate of recompilation by using query hints. One of the thresholds is based on the number of adjustments to a table that have been made using the UPDATE, DELETE, MERGE, and INSERT commands. When a table is updated, the query hint KEEP PLAN decreases the frequency of the database recompiling. In addition, the number of rows returned by a query is predicted by the statistics that SQL Server keeps track of the distribution of values in a specific table, which are stored in a SQL Server database. The query hint KEEPFIXED PLAN avoids recompilation from occurring due to changes in the underlying statistical data.

Before you make significant changes that could impact the recompilation threshold, it’s important to remember that because SQL Server will automatically recompile execution plans based on changes within your tables, recompilations may improve query latency to the point where the benefits outweigh the initial performance cost.

Metrics for the buffer cache
​
A large portion of the work is needed to execute your queries between the buffer cache and the database itself. Monitoring the buffer cache lets you verify that SQL Server is doing as many read and write operations as possible in memory, rather than relying on disk to complete the more time-consuming and inefficient processes.

Metrics to keep an eye on:

The hit ratio of the buffer cache

The hit ratio of the buffer cache evaluates how frequently the buffer manager can retrieve pages from the buffer cache as opposed to how often it must read a page from the disk. The greater the buffer cache, the more probable it that SQL Server will locate the carriers it is looking for within memory. SQL Server automatically determines the size of the buffer cache based on a variety of system resources, such as physical memory, that are available. Suppose your buffer cache hit ratio is meager. In that case, one method is to see if increasing the size of a memory buffer by assigning extra memory space might improve the situation. A second alternative, if this is not practicable, is to increase the capacity of the buffer cache by allocating additional system memory than currently exists.

The anticipated lifespan of a web page

​Pages with a long life expectancy and the buffer cache hit ratio demonstrate how successfully the buffer manager keeps read and write operations inside the bounds of the memory cache in good working order. There is an expectation that a page will remain in the buffer cache for a certain number of seconds in this metric. The buffer cache comprises one or more buffer nodes. Each supports a non-uniform memory access (NUMA) architecture consisting of multiple memory allocations and supports various memory allocations. Each buffer node provides this information, and the buffer manager performance object takes the average of these values to determine the page life expectancy for each buffer node.

SQL Server flushes pages when a checkpoint is reached or when the buffer manager demands extra space in the buffer cache, depending on the situation. The latter procedure is referred to as lazy writing, and it is used to flush out dirty pages that are accessed only occasionally. In general, a longer page life expectancy suggests that your database can read, write, and update pages in memory rather than on disk, indicating that it is more efficient.

By default, SQL Server employs indirect checkpoints, which flush dirty pages as frequently as necessary to ensure that the database recovers within a specified time frame (see below). The buffer manager may cause performance degradation by flushing excessively when using indirect checkpoints because they rely on dirty pages to decide whether or not the database is within the specified recovery time. A different checkpoint setting can lengthen the life expectancy of your pages by a significant amount.

It is beneficial to determine whether a poor page life expectancy is caused by a small buffer cache or by a high number of checkpoints on a given page. If it is the former, you can enhance the page life expectancy of your SQL Server instances by increasing the amount of physical RAM available to them. A checkpoint can be used to monitor the number of pages flushed to disk every second, allowing you to identify the source of high page turnover and take appropriate action (see next section).

Pages per second for checkpoints

The buffer management accomplishes this by writing all dirty pages to disk while also running a checkpoint to ensure that the process does not continue. The fact that SQL Server only writes a few pages during lazy writing allows the buffer manager to free up space in the buffer cache so that additional pages can be written can be demonstrated in the following example. You can start considering whether to add system resources or alter your checkpoints (for example, by selecting a recovery period) as you optimize the buffer manager by monitoring the rate at which pages are transported from the buffer cache to disk, particularly during checkpoints.

Resource metrics are listed in a table.

When working with large datasets, it is vital to keep an eye on resource utilization in SQL Server columns to make sure that you have enough space for your information in storage or, based on your SQL Server configuration, in memory.

Metrics to keep an eye on 

memory used by table kb

Having enough storage space for your data is essential with any relational database management system. Memory-optimized tables in SQL Server elevate memory to the same priority level as storage in the database. Memory-optimized tables in SQL Server 2016 and later can be of any size as long as they fit within the constraints of your system’s RAM.

It’s crucial to compare the size of your memory-optimized tables to the amount of RAM available on your system before proceeding. Microsoft recommends that you have adequate system memory available to support data and indexes within a memory-optimized table that is double the size of the data and indexes expected to be used. Memory-optimized tables are necessary because they must accommodate the indexes and data themselves and permit concurrent reads and writes by storing many versions of a single row in memory. Because memory-optimized tables can expand to be as large as the available memory allows, it is critical to set aside sufficient resources to accommodate their expansion.

Transactions with high throughput and low latency are accommodated by tables that have been optimized for memory. You may keep track of the number of queries to in-memory tables, as well as the amount of resource they consume, to check if your use case meets this profile.

Utilization of disk space

If your server’s disk space is running low, you must get told as soon as possible so that you can take appropriate measures. When the stored procedure sp space used is called, it returns the disk space used by a particular table or database. The example below shows how to forecast the growth of a custom disk space metric generated by the stored method sp space used in the future (see Part 4 to learn more).

As your data grows in size, you’ll want to think about how you’re going to organize your storage. SQL Server gives you the option of customizing how your tables use storage space. You can save time and money in the long run by dividing your data files among multiple disks and assigning them to a logical unit called a filegroup. A filegroup can be declared in T-SQL, and files can be associated with it depending on their paths. You can assign a table to a file group when you create it. Because of their results, queries to the table will read and write data to the files in the filegroup. You can work around a limited amount of system space by incorporating files from other disks because files in a filegroup might be local or remote. Because files in a filegroup can be local or remote, you can work around a limited amount of system space by including files from other drives. Filegroups can also enhance performance because SQL Server can access numerous disks simultaneously.

Locks are measured in terms of metrics.

A transaction locks specific resources, such as rows, pages, or tables and prevents subsequent operations from accessing those resources until the initial transaction is completed and committed. SQL Server’s locking behavior is intended to ensure that transactions are atomic, consistent, isolated, and durable (ACID), that is, that each transaction represents a self-contained operation, as defined by the ACID principle. The SQL Server query processor creates locks for the relevant data at various levels of granularity (rows, pages, tables) and isolation (e.g., whether to lock on a read operation) in response to a query. Keep track of the locks utilized to identify the extent to which tables (together with rows or pages) act as bottlenecks.

Metrics to keep an eye on:

lock delays per second

You may do a few things if other resources are always waiting for your locks to open. You can, for example, change a transaction’s isolation level. A lock’s reach may occasionally extend beyond the ideal amount of granularity, a scenario known as lock escalation. In this instance, you can break up your transactions to encourage the query processor to use less restrictive locks.

Blocked processes
​
When a job in SQL Server waits for locked resources from another task, that task is blocked. While the number of lock waits per second can tell you how often a request has had to wait for a resource, it’s also helpful to know how much blocking your system is now experiencing. Monitoring the number of blocked processes is one technique to keep track of this.

To determine how much-stopped processes are hurting your queries, you might wish to compare this indicator to others, like the elapsed time of your query plan executions.

If the number of stopped processes continues to rise, look for deadlocks, which occur when many transactions wait for one another’s locks to be released.

Metrics for the resource pool
​
If you’ve set up resource pools in SQL Server, double-check that they’re distributing resources correctly and aren’t limiting your users needlessly.

One approach to do so is to track resource usage inside each pool. You can set limits for RAM, CPU, and disk I/O in the resource pool. You can define and assess your limitations with the use of resource-specific metrics.

Metrics to keep an eye on:

Memory was employed.

A SQL Server instance has a specific amount of RAM available for query execution. The parameters MIN MEMORY PERCENT and MAX MEMORY PERCENT provide a hard floor and ceiling, respectively, for the percentage of a SQL Server instance’s memory that a resource pool can utilize within a resource pool. A resource pool must use its MIN MEMORY PERCENT at the very least and its MAX MEMORY PERCENT at the very most.

It’s simple to see how increasing the maximum percentage of RAM affects resource pool use. The memory used by two resource pools is shown in the graph below: the internal pool (purple), which represents SQL Server’s processes and cannot be changed, and the standard pool (blue), which itself is defined within SQL Server but has capacity restrictions that can be changed.

Memory is consumed at a constant pace by the internal pool. We decreased the MAX MEMORY PERCENT of the default pool from 30 to 10 at 10:23. The effect on memory utilization may be seen in the default pool, which drops from 100 KB to roughly 25 KB. MAX MEMORY PERCENT is a strict limit, and lowering it below the value used by a collection will have immediate consequences. It’s critical to ensure that you’re configuring your resource pools while keeping an eye on the resource utilization you wish to limit.

percentage of CPU usage
​
When you specify a CPU consumption minimum and maximum, the limit only applies when numerous pools would otherwise consume more CPU than is available. In other words, if no other collection is utilizing it, a pool can finish more than its MAX CPU PERCENT. On the other hand, CAP CPU PERCENT can be used to set a hard limit. You’ll want to observe what kind of CPU use is prevalent among your users, then determine whether you can optimize your resources by imposing hard or soft limitations, much like you did with memory within a resource pool.

IO/sec disk read IO/sec disk write.
​
Disk I/O rules are expressed in terms of I/O operations per second (IOPS). You can control I/O utilization per disk volume by setting MIN IOPS PER VOLUME and MAX IOPS PER VOLUME within a resource pool. If you’re enforcing these limits, measuring disk reads and writes per second by resource pool can show how frequently your collections approach their limits and whether another solution is needed.

Metrics for the index
​
Indexes are a common aspect of how tables work in RDBMSs, allowing you to filter through production-level information at an acceptable speed. SQL Server gives you a lot of flexibility regarding index construction. These index-related metrics can help you keep your searches as efficient as possible.

Metrics to keep an eye on:

Page divides per second.

Your indexes will grow in size as your data grows. Indexes, like data, are kept on pages. A page split occurs when an index page becomes too full for new data. SQL Server replies by building an index page and migrating around half of the old to the new rows. I/O resources are used in this procedure.

You can avoid page splits by selecting the fill factor of an index or the percentage of an index page to maintain filled. The fill factor is set to zero by default, and when an index page is loaded, a page split occurs. When a fill factor is used, a portion of each page is left empty, adding new rows without dividing the page. SQL Server will store an index across more pages if you specify a fill factor so that each page has someplace left aside for future development.

You can evaluate if you should increase or decrease the fill factor by connecting high or low rates of page splits with other metrics. The lower the fill factor (but not zero), the more pages are consumed by an index. Read operations will need to view more pages if an index is stored across a more significant number of pages, increasing latency. However, since SQL Server cordons off the splitting page, a more excellent fill factor or a fill factor of zero will result in more frequent page splits, and a spike in lock waits. These measurements might help you determine which settings are best for your infrastructure.

Because memory-optimized tables utilize eight-byte pointers instead of pages, indexes for memory-optimized tables don’t have a fill factor, and page splits aren’t an issue.

avg fragmentation in percent
​
When the sequence of data within an index differs from the order in which data is stored on a disk, fragmentation occurs, impeding performance. Fragmentation is a common side effect of a growing, changing database, whether due to page splits or SQL Server’s index adjustments as you insert, update, and remove information.

B-trees are the structure of indexes in SQL Server. A leaf node can be either a data page or an index page, depending on the design of your index. To see how much fragmentation your database has suffered, look at the average fragmentation in percent, which is the percentage of out-of-order leaf pages in your index. If database fragmentation is causing performance to fall short of expectations, you should rebuild your index. Fragmentation, like fill factor, is irrelevant for memory-optimized tables.

Metrics for connections
​
Query execution in any RDBMS is contingent on establishing and maintaining client connections. Monitoring your links is a brilliant place to start when figuring out why something isn’t working.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.