SQL SERVER – Identify Read Heavy Workload or Write Heavy Workload Type by Counters

The other day I had a very interesting experience with one of my client of Comprehensive Database Performance Health Check. It is a standard process of the health check to identify the workload of the server before making the necessary changes. Let us understand how we can quickly identify the workload type of read-heavy or write-heavy by counters.

Workload Type

While I help customers with SQL Server Performance Tuning, I must know that if I am tuning a server which is doing lots of reads or writes. It is impossible to give a single solution to both the different types of workloads. I have seen quite often that many SQL Server Performance Tuning experts jump into tuning queries and server without understanding what exactly is going on inside the server.

Now there are many different methods to know if your server is read-heavy or write-heavy. However, I use the following methods to read the counters in SQL Server. I have figured out no matter how many different tests I run the method, I have found my method of counters working pretty well.

Buffer and Counters

Here is the script which I ran frequently to query the buffer managers.

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND ([counter_name] = 'Page reads/sec' OR [counter_name] = 'Page writes/sec')
GO

If you see the higher value of the Page reads/sec then the server is read-heavy and if you see the higher value of the page writes/sec then the server is write-heavy. It is quite simple and easy to get the general idea of what kind of the work SQL Server is doing.

If you want to know the accurate count, you can always use Extended Events to capture the DML operations and make the educated decision. However, I know it is extremely complex process and trust me you will agree with me once you try to implement this method as well.

If you have any other method, please share with me in the comments section and I will be happy to post on the blog with due credits to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)