Tuesday, 28 April 2015

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

Recently, when I was working on the 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 current 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
)

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 from 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

/* Output :

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, 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 = 59 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, 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 = 15 ms, elapsed time = 4 ms.

*/

You can see here, logical reads are high.

How can we reduce it?
There are many factors 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

/* Output :
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 2, 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 = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TblLogicalReads'. Scan count 1, logical reads 2, 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 = 0 ms, elapsed time = 0 ms.
*/

Hope you understood the logical reads difference and performance impact from the examples given above. You can share your knowledge for the same.

No comments:

Post a Comment

Please Use Good Leanguage