Read Heavy Workload or Write Heavy: Measure It in SQL Server

To tell a read heavy workload from a write heavy one, compare what the server reads with what it changes. Measure it over a window. The answer decides what you tune first, so it is worth five minutes before you open a single query plan.

Why the Mix Matters

A read heavy workload rewards indexes that cover the query and memory that keeps the data cached. A write heavy workload punishes every extra index, because each insert and delete must change them all. It also depends on a fast log. One tuning plan can’t serve both.

Many tuning attempts start with queries and servers before anyone asks what kind of work the server does. The mix is the first question. You can read it from the server in two ways, one per table and one for the whole instance.

Build a Mixed Workload

The demo database is named WorkloadMixDemo. The Catalog table gets read, and the AuditLog table gets written. The first script creates both. The second script reads Catalog 300 times and inserts into AuditLog 50 times. Both loops stop after a fixed count.

IF DB_ID(N'WorkloadMixDemo') IS NULL CREATE DATABASE WorkloadMixDemo;
GO
USE WorkloadMixDemo;
GO
DROP TABLE IF EXISTS dbo.Catalog, dbo.AuditLog;
CREATE TABLE dbo.Catalog (ItemID int NOT NULL PRIMARY KEY, Title nvarchar(60) NOT NULL);
CREATE TABLE dbo.AuditLog (LogID int IDENTITY(1,1) PRIMARY KEY, Note nvarchar(60) NOT NULL);
INSERT INTO dbo.Catalog (ItemID, Title) SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N'Item' FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

SET NOCOUNT ON;
DECLARE @i int = 1, @t nvarchar(60);
WHILE @i <= 300
BEGIN
    SELECT @t = Title FROM dbo.Catalog WHERE ItemID = @i;
    SET @i += 1;
END;
SET @i = 1;
WHILE @i <= 50
BEGIN
    INSERT INTO dbo.AuditLog (Note) VALUES (N'event');
    SET @i += 1;
END;
SET NOCOUNT OFF;

Count Reads and Writes Per Table

The view sys.dm_db_index_usage_stats keeps a tally per index. Reads are seeks, scans and lookups. Writes are the updates, which include inserts and deletes. An insert changes every index of a table once. The query therefore takes the highest update count of the table, not the sum.

SELECT OBJECT_NAME(us.object_id) AS TableName,
       SUM(us.user_seeks + us.user_scans + us.user_lookups) AS Reads,
       MAX(us.user_updates) AS Writes,
       CAST(100.0 * SUM(us.user_seeks + us.user_scans + us.user_lookups) / NULLIF(SUM(us.user_seeks + us.user_scans + us.user_lookups) + MAX(us.user_updates), 0) AS decimal(5,1)) AS ReadPercent
FROM sys.dm_db_index_usage_stats AS us
WHERE us.database_id = DB_ID() AND OBJECTPROPERTY(us.object_id, 'IsUserTable') = 1
GROUP BY us.object_id
ORDER BY TableName;

Catalog is a read table, and AuditLog is a write table. One detail needs care. The write count is one per statement, not one per row. The single INSERT that loaded 5,000 rows into Catalog counted as one write. Read the numbers as a mix of statements, not as a count of rows.

The tally starts at zero when the instance restarts, and when a database closes. Run the query after a normal business day. A night with batch jobs then shows up in the tally too.

Read the Counters Over a Window

The instance counters answer the same question for the whole server. They are totals, in spite of the /sec in their names. Take a snapshot, wait five seconds, take another, and divide the difference by five. The script keeps four counters. They are the page requests, the physical page reads, the page writes and the log flushes that follow commits.

DROP TABLE IF EXISTS #Before;
SELECT RTRIM(counter_name) AS CounterName, RTRIM(instance_name) AS InstanceName, cntr_value
INTO #Before
FROM sys.dm_os_performance_counters
WHERE (object_name LIKE N'%Buffer Manager%' AND counter_name IN (N'Page lookups/sec', N'Page reads/sec', N'Page writes/sec'))
   OR (object_name LIKE N'%:Databases%' AND counter_name = N'Log Flushes/sec' AND instance_name = N'_Total');
WAITFOR DELAY '00:00:05';
SELECT RTRIM(c.counter_name) AS CounterName, (c.cntr_value - b.cntr_value) / 5 AS PerSecond
FROM sys.dm_os_performance_counters AS c
JOIN #Before AS b ON b.CounterName = RTRIM(c.counter_name) AND b.InstanceName = RTRIM(c.instance_name)
WHERE (c.object_name LIKE N'%Buffer Manager%' AND c.counter_name IN (N'Page lookups/sec', N'Page reads/sec', N'Page writes/sec'))
   OR (c.object_name LIKE N'%:Databases%' AND c.counter_name = N'Log Flushes/sec' AND c.instance_name = N'_Total')
ORDER BY CounterName;
DROP TABLE #Before;

On the shared test server, the window shows 197,965 page requests a second and 113 physical page reads. Far less than 1 percent of the requests went to disk, so memory served almost all of them. Page writes ran at 6,817 a second, in bursts, and log flushes at 315. Page requests dwarf the log flushes, so this window leans toward reads. Your numbers will differ, because they include every session on the instance.

What Each Lens Misses

Physical page reads measure the cache, not the workload. A read heavy workload that fits in memory shows few of them. A small cache turns the same workload into a flood of reads. Compare the physical reads with the page requests, as above, before you call a workload read heavy.

Page writes are flushes by the checkpoint and the lazy writer. They come in bursts and say little about user transactions. Log flushes follow commits, so they are the better sign of a write heavy workload.

The per table tally counts statements, not rows or bytes. It also starts again after a restart, so a server that restarted this morning has little to show. Windows has its own counters for disk reads and writes per second. They cover every file on the drive, not only SQL Server’s.

Extended Events can capture every statement. That is exact, and it is heavy, so keep it for a short window when the other lenses disagree.

You could argue that a read to write ratio hides the cost. One large write can cost more than a thousand small reads. That’s right. The ratio tells you where to look. The waits and the reads per query tell you what each side costs.

What to Remember

To judge a read heavy workload, read the mix per table and over a window, and never from one total. Check the cache before you trust the read counters, and use the log flushes to see the writes.

Measure on a normal day and again on a bad day. When you finish with the demo, drop the demo database.

USE master;
GO
IF DB_ID(N'WorkloadMixDemo') IS NOT NULL
BEGIN
    ALTER DATABASE WorkloadMixDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE WorkloadMixDemo;
END;

A workload is not a label, it is a mix that you have to measure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.