When we asked two popular LLMs to "rank top RDBMS on the breadth and depth of the engine monitoring data they provide", the SQL Server database engine was ranked first by one and second by the other. With hundreds of DMVs and catalog views, thousands of extended events, query plan XML, and Query Store, you or your agent can diagnose and fix nearly any performance problem.
Yet sometimes we find a blind spot.
A mysterious CPU increase
Imagine a busy OLTP database happily humming at 30 percent CPU as it does on any given day. Then, CPU starts climbing up without any increase in the workload. At 60 percent, it sets off an alarm. You start looking for the most common culprit - a query plan regression - but find none. Query plans are unchanged, but some queries just consume more and more CPU and run slower and slower.
At this point you might capture an XPerf trace and look at the top CPU consuming call stacks. This is a powerful technique that finds the culprit in this case, but it's nowhere near as quick and simple as querying a DMV. You need access to the OS to capture the trace, you need to install the Windows Performance Toolkit, and you need to download symbols. Often that's just not an option.
The culprit in this case: the version store, or rather row versions that had to be kept around for much longer than usual.
Row versions are needed only while some transaction might still read them. After that, a background process cleans them up. Several things can hold that cleanup back, most commonly a long-running transaction, often an idle session that started but never committed a transaction. While cleanup is blocked, things get steadily worse:
- Version chains get longer. Every update to a frequently modified row adds another version, and queries that need an older version of the row must walk back through more and more of them. Until now, there was no practical way to detect this without an XPerf trace or a debugger.
- Versions move off-row. A row can hold only one in-row version. While cleanup is blocked, further updates to a row must store their versions off-row instead, which is more expensive both to write and to read. For frequently updated rows, nearly all new versions end up off-row. Until now, you couldn't correlate a slowdown in query execution with this shift from in-row to off-row versions.
The net effect is that CPU usage and logical reads creep up across the whole workload with no single query or plan standing out.
This matters because row versioning is the foundation for the RCSI and snapshot isolation levels, which avoid concurrency issues due to lock blocking. Accelerated database recovery (ADR) also uses row versioning to provide fast recovery, instant rollback, and to keep the transaction log small. Both RCSI and ADR are enabled by default in Azure SQL Database, and we recommend the same configuration for most SQL Server workloads.
What was missing until now is a simple way to detect when version reads start to slow you down.
Shining a light on the problem
In Azure SQL Database, we added version store read metrics in SET STATISTICS IO, DMVs, extended events, and query plan XML, covering both the older version store in tempdb and the newer persistent version store (PVS) in each user database.
- Tempdb version store: versions created for databases without ADR.
- In-row PVS: small versions that are stored inside the data row itself.
- Off-row PVS: versions that are stored in a special-purpose system table in the user database.
- Online index PVS: versions that are stored in a different system table while an online index operation runs. An index operation might require these versions for as long as it runs. Keeping them apart lets the regular PVS be cleaned up in the meantime.
The new metrics are now exposed as follows:
SET STATISTICS IO. When an object had version reads, a new line follows the familiar IO lines:
Object '[dbo].[t]'. Tempdb versions read 0, in-row PVS versions read 0, off-row PVS versions read 1000, online index PVS versions read 0.Query plan XML. In a plan with runtime statistics, the RunTimeCountersPerThread element of each operator that reads a table or index has four new attributes: ActualTempdbVersionsRead, ActualInRowPvsVersionsRead, ActualOffRowPvsVersionsRead, and ActualOnlineIndexPvsVersionsRead. This shows which operator in the plan read row versions, what kind, and how many.
Extended events. The sql_statement_completed, sp_statement_completed, sql_batch_completed, and rpc_completed events have four new fields each: tempdb_versions_read, inrow_pvs_versions_read, offrow_pvs_versions_read, and online_index_pvs_versions_read.
Query and module statistics. sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats, and sys.dm_exec_function_stats have new total_version_reads, last_version_reads, min_version_reads, and max_version_reads columns. These columns report the total, last, minimum, and maximum of the sum of all four counters. You can rank cached queries and modules by version reads and see the correlation with the CPU (worker) time, elapsed time, and logical reads.
Sessions and requests. sys.dm_exec_requests and sys.dm_exec_sessions have a new version_reads column, also combining the four counters. Use the first to see how many version reads a request has done so far. Use the second to see the total for all completed requests during the session's lifetime.
When version cleanup is blocked, version reads increase along with CPU time and logical reads across the workload, and you might also see a shift from in-row to off-row versions in PVS. That steers your investigation to the version store rather than to query plans or the workload itself. From there, the Monitor and troubleshoot accelerated database recovery article describes how to find and remove whatever is blocking cleanup.
To learn more, see Version read metrics in documentation.
Is this about CPU or IO?
You might wonder - if the cost of version traversal is higher CPU, why expose it under STATISTICS IO? Indeed, there was considerable debate: should we add a new SET STATISTICS command (STATISTICS VERSION_CHAIN perhaps) specifically for these metrics? In the end, we settled on STATISTICS IO for three reasons:
- We didn't want you to memorize and type an extra command.
- We wanted to make the new statistics easy to discover.
- Logical reads in STATISTICS IO are already about CPU work (buffer pool access, not disk IO). Version reads are the same kind of measure, only for rows instead of pages. Keeping them together is consistent.
Could we expose the IO cost of version reads instead, counting the pages read from a version store? Sure, but it would still leave a large blind spot: in-row versions. There is no extra IO cost for reading them because they are stored with the data row itself, and brought into memory when the data page is read. Yet traversing in-row versions and reconstructing older versions from in-row diffs adds nontrivial CPU cost. That cost would remain hidden if we only exposed IO statistics for off-row version pages.
Why did we make this improvement?
Besides the obvious reason - removing the blind spot in the database engine monitoring capabilities - we wanted to remove the uncertainty about version read overhead. While it can't be zero by its nature, the concurrency and availability benefits of RCSI and ADR are enormous. Yet we sometimes see customers hesitate to enable these features in SQL Server because it's hard to monitor and quantify the overhead.
We hope that this improvement takes that hesitation away :).
What's next
At this point, we anticipate two questions:
- What about Query Store? Why not expose version reads together with other query runtime statistics?
This is under consideration. Do you need version reads as a new metric in Query Store? Please leave a comment and describe how this would help. - What about SQL Server and Azure SQL Managed Instance?
The new metrics will soon be available in Azure SQL Managed Instance with the always-up-to-date update policy. As for nearly all database engine improvements that first appear in Azure SQL Database, we plan to include this improvement in the next version of SQL Server (and in the corresponding versioned Azure SQL Managed Instance) once they arrive.
Tell us what you think about this improvement in the comments. Are there other blind spots that make it harder to see what's going on in the database engine? Share what you run into in your daily work.
Example
Here's a quick way to see some of the new metrics. Connect to a database in Azure SQL Database, and execute as a single batch.
Note that the graphical query plan in SSMS doesn't yet show the new metrics even though they are present in the actual plan XML. That will be available in a future version of SSMS.
SET NOCOUNT ON;
/*
Create a table with 1000 rows.
*/
DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (id int PRIMARY KEY, v int NOT NULL);
INSERT INTO dbo.t (id, v)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0
FROM sys.all_columns;
/*
Create versions in Persistent Version Store (PVS).
With Accelerated Database Recovery (ADR), rollback is instant,
but the row versions from an aborted transaction
stay in place until background cleanup happens later.
*/
BEGIN TRAN;
UPDATE dbo.t SET v = v + 1;
ROLLBACK;
/*
Immediately, read the data from the table.
This requires version reads because PVS cleanup hasn't completed yet,
but the SELECT must see the data before the aborted transaction.
Version read statistics are returned after IO statistics.
Showplan XML contains four new attributes under RunTimeCountersPerThread.
*/
SET STATISTICS IO, XML ON;
SELECT COUNT(*) AS cnt FROM dbo.t WHERE v = 0;
SET STATISTICS IO, XML OFF;
/*
Version reads are also present in the current request statistics.
The value is doubled because of a statistics update by the previous SELECT,
which also reads the same row versions.
*/
SELECT version_reads
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
/*
Aggregate version reads are also available for queries with cached plans,
such as the previous SELECT (cached after auto-parameterization).
*/
SELECT qs.execution_count,
qs.total_version_reads,
qs.last_version_reads,
qs.min_version_reads,
qs.max_version_reads
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%SELECT COUNT(*) \[cnt\] FROM \[dbo\].\[t\] WHERE%' ESCAPE '\';
Microsoft