Friday, January 28, 2022

SQL 2019: Physical reads are counted two times for read-aheads in sys.dm_exec_query_stats


Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see 

"Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values."

So if you're using these management views to look at performance metrics for your queries you're going to get incorrect results and you might be thinking the queries are doing way more work than what they're actually doing.

In order to test this in a before and after update type scenario I'm going to first force SQL Server to clean out it's buffer pool using DBCC DROPCLEANBUFFERS and the procedure cache with DBCC FREEPROCCACHE (btw, don't do this on anything else other than a sandbox environment). This will ensure the next time I run a query it will have no pages in memory and will have to retrieve the pages from disk, and I'm clearing the procedure cache as I'm going to be querying the sys.dm_exec_query_stats DMV.

Test query: 

SELECT * FROM Person.Person  

I've set SET STATISTICS IO ON so I can see after my query has ran the logical, and in this case more importantly the physical reads of my query and it shows 3 physical reads and 3866 read-ahead reads (which are still from disk):

Table 'Person'. Scan count 1, logical reads 3821, physical reads 3, page server reads 0, read-ahead reads 3866

SELECT [text] AS QueryText, last_physical_reads FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [text] = 'SELECT * FROM Person.Person'

And I get the following results showing the last physical reads of my query was 7779, which is wrong:


I've now updated one of my test instances to CU15 and will run the same queries as before, and this time we have a much more accurate value returned from the DMV:


Now I should add there's still a difference between the statistics output and the sys.dm_exec_query_stats DMV for physical reads and to be totally honest I don't know exactly why but I've the question using #sqlhelp on Twitter and will update when I find out!

No comments:

Post a Comment

Breaking up with SQL Server

I was inspired to write this after reading a post from Dave Mason regarding breaking up with Big Tech companies. Yet again I haven't wr...