SQL Server: The Important Performance Counters (dm_os_performance_counters)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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 basis 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 for monitoring the SQL Server Performance.

Memory Information Counters:

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)' GO

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%Memory%' GO

Page Information Counters:

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' GO

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 is above, you may get a timeout in your application.

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'page reads/sec' GO

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'Page lookups/sec' GO

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%Page%' GO

User Connection Counter:

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'User Connections' GO

Buffer Information Counters:

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer Cache Hit Ratio' GO

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%Buffer%' GO

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.

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/Sec' GO

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.

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Compilations/Sec' GO

Block Transaction Information Counters:

This blocking related counters are essential for finding the different information of the blocked transactions so that as much as we can avoid the deadlock situation.

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%block%' GO

CPU Counters:

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

1 2 3

SELECT *FROM sys.dm_os_performance_counters WHERE counter_name = 'CPU usage %' GO