Which Metrics should be tracked in SQL Server Monitoring?

Introduction to SQL Server Monitoring

SQL Server Monitoring has evolved into an important component of today’s applications because the bulk of those programs are now dependent to a major degree on a database. Thanks to this, it’s essential to stay an in-depth eye on your metrics and find the foremost out of the database services you provide.

SQL Server Monitoring provides users with a various selection of metrics from which to decide on. So as to induce a comprehensive understanding of the capabilities of SQL servers, we are visiting to dissect the five most significant categories that they provide.

• Metrics for Using Transact SQL
• Buffer Cache Metrics
• Locking Mechanism Metrics
• Metrics for the Monitoring of Resources
• Indexing

Along with examining the tools that are currently used for monitoring, we are going to also discuss the benefits of using SQL Server Monitoring. At the top of this text, we’ll evaluate and contrast the foremost important features and costs offered by various SQL Server Monitoring tools currently available on the market.

Important Metrics in SQL Server Monitoring

T-SQL metrics (Transact SQL Metrics)

Structured search language (SQL) is an extension of traditional SQL that was designed to interact with relational databases. Transact SQL, or T-SQL is an extension of SQL. The T-SQL extension provides the SQL server with a variety of additional features, a number of which are error handling, transaction control, and row processing, to call some.

But what exactly is the connection between these functions and SQL Server Monitoring? In any case, the functionality of row processing enables you to write down queries in batches, which enables you to pass a stream of statements that perform particular operations. After that, the SQL server processes them as one batch of information without delay.

Your application’s total latency is often reduced to a more manageable level by using batching, compilation, and caching of T-SQL streamline statements with the SQL server. The subsequent are three important metrics for Transact-SQL, all of which play a crucial part in the optimization of your server.

Batch requests per second

The term “batch requests per second” does exactly what its name suggests: it provides you with an overall, high-level view of the database in terms of the number of batch requests it processes each second. Due to this, an unanticipated change within the Batch requests is also advocating for your performance issues.

SQL compilations per second

The number of compilations that a SQL server must perform so as to store information within the cache for subsequent execution is indicated by the SQL Compilations per the second metric. In a perfect scenario, a question would only have to be compiled once, after which it can be used for future reference.

On the opposite hand, within the extremely unlikely event that every query is just used once, the number of compilations per second might become adequate for the number of batch requests per second. This is often undeniably a performance bottleneck, and also the optimization strategies that are used for it have to be evaluated all over again.

In this particular situation, both metrics, namely Batch Requests/second and SQL Compilations/second, play a big part in providing an in-depth comparison of the 2 options. Enteros’s SQL Server Monitoring should be included in your application for yet one more compelling reason.

Last time period

Following compilation, SQL Server stores T-SQL queries in a very cache with the aim of reducing latency and improving performance. You’ll be able to get a general idea of what quantity of time is spent in execution by viewing the aggregated statistics of comparable queries that are already stored in your cache. One of these statistics which will provide you with some additional insight into your SQL server is the “last time period.”

You can get an inspiration of how long the query will desire to execute by watching the quantity of your time that has already passed within the SQL server. If your execution plans run into a bottleneck, it’s a decent idea to review your optimization strategies to determine if there are any thanks to improve them.

The table that was just shown is by no means an exhaustive list. You’ll move to the official documentation that Microsoft supplies so as to accumulate information regarding the opposite metrics that are part of the sys. dm exec query stats view.

Note that these statistics are only stored within the sys. dm exec query stats view for execution plans that are currently present within the cache. This means that their lifetime is proportional to the length of your time that they’re kept within the cache, and once that point has elapsed, you may now not be ready to access them. If you propose to stay track of them during a table, you’ll be able to find additional details pertaining to the plan generation num column.

Metrics for the buffer cache

The optimization of query retrieval by reducing total latency to the maximum amount possible should be the first focus of any SQL server. Disk I/O is a costly operation, and it’s for this reason that it’s one of the first factors that determine how efficiently a SQL Server works overall. Thanks to this, a SQL Server offers a feature referred to as buffer management, which could be a process that consists of two primary components:

• Accessing and updating database pages requires the utilization of the buffer manager.
• To decrease the quantity of database file I/O, use either the buffer cache or the buffer pool.

As soon as SQL queries are executed, the Buffer Cache immediately becomes a vital component of the optimization process. This can be thanks to the actual fact that a major portion of the execution time is spent traveling between the Database and therefore the Buffer Cache. During this section, we are visiting to examine three primary metrics so as to see the factors that are ultimately answerable for the performance of a SQL Server.

Hit ratio for the buffer cache

The buffer hit ratio is set by the SQL Server by calculating the proportion of pages that are located within the buffer cache as compared to the full number of pages that are read directly from the disc. Because reading from a disc could be a time-consuming and resource-intensive process, you’ll want your SQL Server to locate the overwhelming majority of the pages from within its own buffer cache.

If the monitoring of the SQL Server returns an occasional hit ratio, one potential solution is to extend the scale of the buffer cache by assigning it more system memory. This might be a way to handle the problem. Aiming for a success ratio of a minimum of 90 percent is taking into account best practices within the industry. This means that the cache is ready to satisfy the bulk of the requests made.

Checkpoint pages per second

When a page is modified within the SQL Server’s buffer cache, the change isn’t immediately written to the disc. Instead, the SQL Server waits. Instead, it flags the page as being dirty and deletes it. When a checkpoint is performed, it’s the responsibility of the buffer manager to rewrite any dirty pages that are read to the disc.

But there’s a catch: the buffer manager only writes a number of the pages so as to release space within the buffer cache for innovative pages.

Because of this, checkpoints per second play a vital part in SQL Server Monitoring. This can be because you’ve got the flexibility to change the resource allocation in the event that the general efficiency doesn’t prove as anticipated. You can, for instance, change the quantity of memory allocated to the buffer cache, otherwise, you can reconfigure your checkpoints by altering the number of the time it takes for you to get over a failure.

The anticipated lifespan of Page

Within this category, SQL Server Monitoring offers two different metrics, namely:

Page expectancy is the number of seconds that a page is predicted to stay within the buffer pool without receiving any references from the buffer manager.

The page lifespan of a Buffer Node is the minimum number of seconds that a page is anticipated to stay within the buffer pool of this particular buffer node without being referenced.

Non-uniform access (NUMA) architecture is often followed by a Buffer Node, which supplies the user the power to watch the buffer page distribution for every node within the architecture. There may be one or more buffer nodes that compose the Buffer Cache. When calculating the Page lifetime, the Buffer manager takes the values obtained from the second category and takes a median of these values.

Pages are flushed either when a checkpoint occurs or when there’s a desire for more room within the buffer cache. Whether your database is being read, written to, or updated, if the Page lifetime metric features a higher value, this means that your database is accessing the buffer cache more frequently than it’s accessing the disc itself.

A higher value of Page lifetime is desired in SQL Server Monitoring because cache reference is a smaller amount expensive than disc reference.

Locking System Metrics

The typical length of the lockout wait

When there are multiple users accessing a SQL Server, the resources are locked to forestall any misreads from occurring. Additionally to the typical wait lock time, the SQL Server provides several other metrics regarding locks. Because you do not want your users to possess to attend for resources for an excessive amount of your time, the typical wait lock time must be as short as is humanly possible.

Lock takes one second for every wait

If the worth of the lock waits/second metric is on top of what was anticipated, it indicates that your resources frequently anticipate an extended period of your time for the locks to be lifted. It’s possible that this can be an instance of lock escalation.

Lock escalation could be a mechanism that’s followed by the SQL Server to convert multiple fine-grained locks into table locks so as to scale back the general latency of the system. The entire number of locks and therefore the amount of memory utilized by them are both taken under consideration by SQL. SQL won’t make this decision until it’s first determined with absolute certainty that the choice will end in improved performance.

On the opposite hand, there’s the chance of a situation within which this mechanism will block other users, which might not be desirable. Simply keep the length of your transactions short by dividing up large batch operations into several smaller ones. This may facilitate your avoiding lock escalations. Switching to locks with fewer restrictions is yet one more choice that will be made.

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 clouds, RDBMS, NoSQL, and machine learning database platforms.