SQL Server Logical Reads - What's it?

--SQL Server Logical Reads - What's it?

Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

--Create table objects

CREATE TABLE TblLogicalReads
(
TranId INT,
TrnData VARCHAR(100),
TrnDate DATETIME
)
GO



--After creating objects, 49999 records will be inserted by following scripts,


DECLARE @cnt BIGINT
SET @cnt  = 1
WHILE (@cnt < 50000)
BEGIN
INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END
GO

--Now we are checking logical reads for the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
TranId,
TrnData,
TrnDate
FROM TblLogicalReads
WHERE TranId = 5

SELECT
TranId,
TrnData,
TrnDate
FROM TblLogicalReads
WHERE TrnDate = '2009-12-26 18:10:47.653'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO


-- Creating indexes on tables to reduce it

CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO



CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO