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."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
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:
No comments:
Post a Comment