Recently when i was working on tuning of stored procedures and i experienced with one performance issue, that was high Logical Reads.
Logical Reads?
"Number of pages read from the data cache" - It occurs every time when the database engine request a page from buffer cache, otherwise physical reads occurs if currently page is not available in buffer cache.
Let us go through the sample demo and get experience for the logical reads. First we need require objects, so we are creating database and tables inside it.
USE DEMO
GO
-- Creating a table
IF (OBJECT_ID('TblLogicalReads','U') > 0)
DROP TABLE TblLogicalReads
GO
CREATE TABLE TblLogicalReads
(
TranId INT,
TrnData VARCHAR(100),
TrnDate DATETIME
)
GOAfter 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
(Click on image to enlarge)
You can see here , logical reads are high.
How can we reduce it?
There are many factor to reduce it as it depends on But here for example we need to create some required indexes on columns which are used in the queries.
-- Creating indexes on tables
CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO
CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO
Finally we are on the stage where we need to review logical reads after creating indexes on tables
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
(Click on image to enlarge)
I hope you understood the logical reads from the example give above. You can share you knowledge for the