Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server Logical Reads - What's it?

May 3 2012 12:00AM by Paresh Prajapati   

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
)

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

LogicalRead-1

(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

LogicalRead-2

(Click on image to enlarge)

I hope you understood the logical reads from the example give above. You can share you knowledge for the

Tags: sql, sql server, ms sql, ms sql server, #SQL Server, #sql, database, sql server general, SQL Scripts, Index, Logical reads, Reads


Paresh Prajapati
6 · 23% · 7485
12
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

4  Comments  

  • Hi Paresh,

    Nice blog!!! refreshed today morning with a cocept.

    It would be great if you could explain how adding index would reduce the number of logical reads.

    Thanks, SQLZealot

    commented on May 6 2012 1:32AM
    Latheesh NK
    51 · 4% · 1178
  • Without index it pulling large number of records and scan the whole table pages to read qualified records, so creating an index will help to reduce number of records set and seek only qualified records from page. It should have proper indexing.

    commented on May 8 2012 3:22AM
    Paresh Prajapati
    6 · 23% · 7485
  • HI paresh,

    Simply Superb Article.Past few days only am going through your blog nice posts.

    commented on Nov 27 2012 1:47AM
    Bala Krishna
    83 · 2% · 676
  • hi paresh nice article

    commented on Nov 29 2012 11:27AM
    Deepak kelath
    210 · 1% · 222

Your Comment


Sign Up or Login to post a comment.

"SQL Server Logical Reads - What's it?" rated 5 out of 5 by 12 readers
SQL Server Logical Reads - What's it? , 5.0 out of 5 based on 12 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]