SQL Server: The Important Performance Counters (dm_os_performance_counters)

The SQL Server Performance Counters are the bread and butter for performance tuning exercise.

Using the performance counters, we can measure the current performance of the SQL Server bases on different criteria like: Memory Information, Storage Information, CPU Information, Pages Information and other.

In this post, I am going to share some of the important Performance Counters to monitor SQL Server Performance.

Memory Information Counters:

Using this script, you can find the total amount of physical memory in the computer.

Using this script, you can find a different memory related counters and its information.

Page Information Counters:

Using this script, you can find that how long pages stay in the buffer cache in seconds. If the value is larger, SQL Server does not read from disk and it serves the request from the buffer only.

It shows the value of the number of physical database page reads that are issued per second.
Around 80 per second is normal, but if it above, you may get timeout in your application.

It shows the number of requests to find a page in the buffer pool.

Using this script, you can find a different page related counters and its information.

User Connection Counter:

It shows the number of different users that are connected to the SQL Server.

Buffer Information Counters:

A greater value indicates that, greater number of requests are satisfied with the data cache and SQL Server is getting queries data from he memory instead of disk.

Using this script, You can find different information of the buffers.

Batch Requests Counter:

It shows the number of batches per second, which are received by the SQL Server. If the number is high, more queries are executed in SQL Server.

SQL Compilation Counter:

It shows the number of execution plan compilation per second by the SQL Server. The compilation of execution plan is resource intensive and it should be lesser than the number of batches per second.

Block Transaction Information Counters:

This blocking related counters is very important to find different information of the blocked transactions so that as much as we can avoid the deadlock situation.

CPU Counters:

Using this script, You can find usage of CPU in percentage.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz