Press "Enter" to skip to content

Tracking How Many Row Versions an RCSI Query Reads

Louis Tochon works with a new bit of information:

The Table line looks like a normal scan of 330 pages. The Object line says the read walked through 220,000 row versions, 22 per row, because another session had updated these rows 22 times in a transaction it had not committed yet. The same read took about 117 ms of CPU, against 3 ms when nothing was pending.

The Object line is new. It comes from the version store read metrics Dimitri Furman announced on the Azure SQL blog last week, which also appear in the actual plan XML returned by the server (SSMS 22.12.0 drops them, both in the graphical plan and in Show Execution Plan XML; copy the XML cell from SET STATISTICS XML ON to see them), in four extended events, in sys.dm_exec_query_stats and its procedure, trigger and function counterparts, and in sys.dm_exec_requests and sys.dm_exec_sessions (Microsoft documentation). For now they exist in Azure SQL Database and SQL database in Microsoft Fabric; Managed Instance and the next SQL Server version are announced to follow.

When I recommend RCSI, the usual worry is the open transaction someone forgot in SSMS. These metrics let me check what it actually costs the readers.

The article looks to be AI-generated, but it does show off a new bit of functionality that I’d be happy to see spread beyond Azure SQL Database.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.