Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


High READS and WRITES number in profiler


High READS and WRITES number in profiler

Author
Message
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
Hello,
When I see high numbers in READS and WRITES columns in Profiler, what does that mean? How can I improve that?

Thanks.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17529 Visits: 32252
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
Thanks Grant, that helps.
Leo.Miller
Leo.Miller
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 1497
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
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
Hello Leo,
What do you mean by cache reads and writes. It is misleading.

Thanks.
Leo.Miller
Leo.Miller
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 1497
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
Leo.Miller
Leo.Miller
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 1497
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
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
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.
Leo.Miller
Leo.Miller
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 1497
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
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
Yes, that's what I meant. That is certainly great information. Thanks for your time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search