High READS and WRITES number in profiler

  • Hello,

    When I see high numbers in READS and WRITES columns in Profiler, what does that mean? How can I improve that?

    Thanks.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks Grant, that helps.

  • 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

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Hello Leo,

    What do you mean by cache reads and writes. It is misleading.

    Thanks.

  • 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
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • 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

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • 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.

  • 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

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Yes, that's what I meant. That is certainly great information. Thanks for your time.

  • Leo.Miller (1/13/2011)


    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

    As per profiler definition:

    Writes:

    Number of physical disk writes performed by the server on behalf of the event.

    So they are physical writes.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply