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.
After creating objects, 49999 records will be inserted by following scripts.
Now we are checking logical reads from the script which are going to be run.
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.
Finally we are on the stage where we need to review logical reads after creating indexes on tables
Hope you understood the logical reads difference and performance impact from the examples given above. You can share your knowledge for the same.
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
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
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
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