SQL with Manoj

In my previous post [link] I talked about SET STATISTICS IO and Scan Count.

Here in this post I will go ahead and talk about Page Reads, i.e Logical Reads and Physical Reads.
 

–> Type of Reads and their meaning:

– Logical Reads: are the number of 8k Pages read from the Data Cache. These Pages are placed in Data Cache by Physical Reads or Read-Ahead Reads.

– Physical Reads: are the Number of 8k Pages read from the Disk if they are not in Data Cache. Once in Data Cache they (Pages) are read by Logical Reads and Physical Reads do not (or minimally) happen for same set of queries.

– Read-Ahead Reads: are the number of 8k Pages pre-read from the Disk and placed into the Data Cache. These are a kind of advance Physical Reads, as they bring the Pages in advance to the Data Cache where the need for Data/Index pages in anticipated by the query.
 

–> Let’s go by some T-SQL Code examples to make it more simple to understand:

USE [AdventureWorks2012]
GO

-- We will crate a new table here and populate records from AdventureWorks's Person.Person table:
SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, ModifiedDate
INTO dbo.Person
FROM [Person].[Person]
GO

-- Let's Clear up the Data Cache or memory-buffer:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Run the following block of T-SQL statements:
SET STATISTICS IO ON
SET STATISTICS TIME ON
	SELECT * FROM dbo.Person

	SELECT * FROM dbo.Person
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

Output Message:

(19972 row(s) affected)
Table 'Person'. Scan count 1, 
 logical reads 148, physical reads 0, read-ahead reads 148, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 297 ms.

(19972 row(s) affected)
Table 'Person'. Scan count 1, 
 logical reads 148, physical reads 0, read-ahead reads 0, 
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 186ms.

The Output Message above shows different number of Reads for both the queries when ran twice.
 

– On first execution of the Query, the Data Cache was empty so the query had to do a Physical Read. But as the optimizer already knows that all records needs to be fetched, so by the time query plan is created the DB Engine pre-fetches all records into memory by doing Read-Ahead Read operation. Thus, here we can see zero Physical Reads and 148 Read-Ahead Reads. After records comes into Data Cache, the DB Engine pulls the Records from Logical Reads operation, which is again 148.
 

– On second execution of the Query, the Data Cache is already populated with the Data Pages so there is no need to do Physical Reads or Read-Ahead Reads. Thus, here we can see zero Physical & Read-Ahead Reads. As the records are already in Data Cache, the DB Engine pulls the Records from Logical Reads operation, which is same as before 148.
 

Note: You can also see the performance gain by Data Caching, as the CPU Time has gone down to 16ms from 31ms and Elapsed Time to 186ms from 297ms.
 

–> At our Database end we can check how many Pages are in the Disk for the Table [dbo].[Person].
Running below DBCC IND statement will pull number of records equal to number of Pages it Cached above, i.e. 148:

DBCC IND('AdventureWorks2012','Person',-1)
GO

The above statement pulls up 149 records, 1st record is the IAM Page and rest 148 are the Data Pages of the Table that were Cached into the buffer pool.
 

–> We can also check at the Data-Cache end how many 8k Pages are in the Data-Cache, before-Caching and after-Caching:

-- Let's Clear up the Data Cache again:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Run this DMV Query, this time it will run on Cold Cache, and will return information from the pre-Cache'd data:
;WITH s_obj as (
	SELECT 
		OBJECT_NAME(OBJECT_ID) AS name, index_id ,allocation_unit_id, OBJECT_ID
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p
	ON au.container_id = p.hobt_id
	AND (au.type = 1 
		OR au.type = 3)
	UNION ALL
	SELECT OBJECT_NAME(OBJECT_ID) AS name, index_id, allocation_unit_id, OBJECT_ID
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p
	ON au.container_id = p.partition_id
	AND au.type = 2
	),
obj as (
	SELECT 
		s_obj.name, s_obj.index_id, s_obj.allocation_unit_id, s_obj.OBJECT_ID, i.name IndexName, i.type_desc IndexTypeDesc
	FROM s_obj
	INNER JOIN sys.indexes i 
	ON i.index_id = s_obj.index_id
	AND i.OBJECT_ID = s_obj.OBJECT_ID
	)
SELECT 
	COUNT(*) AS cached_pages_count, obj.name AS BaseTableName, IndexName, IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN obj
ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN sys.tables t
ON t.object_id = obj.OBJECT_ID
WHERE database_id = DB_ID()
AND obj.name = 'Person'
AND schema_name(t.schema_id) = 'dbo'
GROUP BY obj.name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;

-- Run following Query, this will populate the Data Cache:
SELECT * FROM dbo.Person

-- Now run the previous DMV Query again, this time it will run on Cache'd Data and will return information of the Cache'd Pages:

Query Output:

-> First DMV Query Run:
cached_pages_count	BaseTableName	IndexName	IndexTypeDesc
2			Person		NULL		HEAP

-> Second  DMV Query Run:
cached_pages_count	BaseTableName	IndexName	IndexTypeDesc
150			Person		NULL		HEAP

So, by checking the above Output you can clearly see that only 2 Pages were there before-Caching.

And after executing the Query on table [dbo].[Person] the Data Pages got Cache’d into the buffer pool. And on running the same DMV query again you get the number of Cache’d Pages in the Data Cache, i.e same 148 Pages (150-2).
 

-- Final Cleanup
DROP TABLE dbo.Person
GO

>> Check & Subscribe my [YouTube videos] on SQL Server.