Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

High READS and WRITES number in profiler Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 8:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hello,
When I see high numbers in READS and WRITES columns in Profiler, what does that mean? How can I improve that?

Thanks.
Post #1047319
Posted Thursday, January 13, 2011 10:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 15,517, Visits: 27,898
It basically means that the statement in question is moving lots of data, either in (writes) or out (reads) of the server. What you do about it really depends on the query in question. You need to understand if it's working as designed, is the design right, are there performance problems, all kinds of stuff. There's no switch or single answer that can help you out. It really depends on the situation, the code, and the structure.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1047435
Posted Thursday, January 13, 2011 11:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thanks Grant, that helps.
Post #1047478
Posted Thursday, January 13, 2011 12:50 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:08 PM
Points: 449, Visits: 1,333
ramadesai108 (1/13/2011)
Hello,
When I see high numbers in READS and WRITES columns in Profiler, what does that mean? How can I improve that?

Thanks.


Reducing READS tends to be simpler as improved indexing may reduce this. You need to be aware that the READS and WRITES you see in profiler are not only to disk, but include cache reads and writes, so they may be a bit misleading.

Leo
Post #1047531
Posted Thursday, January 13, 2011 1:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hello Leo,
What do you mean by cache reads and writes. It is misleading.

Thanks.
Post #1047559
Posted Thursday, January 13, 2011 2:00 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:08 PM
Points: 449, Visits: 1,333
ramadesai108 (1/13/2011)
Hello Leo,
What do you mean by cache reads and writes. It is misleading.

Thanks.


I know, and so is trying to work out what exactly Profiler is reporting. From the reading I've done it looks like profiler reports not only physical IOs, but also any reads to the cache, even those reads it does to determine if the required page is actually in the cache. So Profiler is returning logical and physical IOs, plus some.

You can see this if you run a query with STATISTICS IO on and trace the query at the same time. Then try reconcile the IO counts. You will see that Profiler almost always returns higher counts, particularly on the reads.

Cheers

Leo
Post #1047570
Posted Thursday, January 13, 2011 2:05 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:08 PM
Points: 449, Visits: 1,333
ramadesai108 (1/13/2011)
Hello Leo,
What do you mean by cache reads and writes. It is misleading.

Thanks.

Here are two articles, unfortunately both a bit old that expand on this. I haven't seen anything current to indicate this has changed, and everything I've tested indicates it's the same.
http://support.microsoft.com/kb/314648
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/12/11/492.aspx

Cheers

Leo
Post #1047572
Posted Thursday, January 13, 2011 2:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thanks for the articles. So there is no way of knowing whether the object is cached or not. But what I can look for is consistency whether the same object is showing roughly the same count of read or write and then optimize.

Thank you.
Post #1047586
Posted Thursday, January 13, 2011 2:53 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:08 PM
Points: 449, Visits: 1,333
ramadesai108 (1/13/2011)
Thanks for the articles. So there is no way of knowing whether the object is cached or not. But what I can look for is consistency whether the same object is showing roughly the same count of read or write and then optimize.

Thank you.


That's about it, but I would think you realy mean the query showing the reads and writes, then optimise the query or create better indexes on the underlying objects. There's no real way that I know of, of telling if an object is cached from the Profiler results, but if you use STATISTICS IO you start getting an idea as logical IOs go up and physical IOs go down after cacheing. This is why you should do a DBCC DROPCLEANBUFFERS between tests to get consistent results.

In recent years I've tended to use Profiler as a tool to ID poorly performing queries, then pulled the code and run it in MS with STATISTICS IO and TIME ON, and looked at the actual execution plan. This, in conjunction with the DMVs in SQL 2005 gives a good combination of tools to improve performance.

Cheers

Leo
Post #1047594
Posted Friday, January 14, 2011 6:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Yes, that's what I meant. That is certainly great information. Thanks for your time.
Post #1047841
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse