Post reply

about the statistics io

  • November 24, 2010 at 9:42 am

    #243210

    Hi guys

    I running a query that seems to be heavy.

    So I m checking the time used to complete, the execution plan and also checking the disk reads. in order to do that, first to lunch the query I run:

    set statistics io on

    so as result of this I got this:

    (32720 row(s) affected)

    Table 'legproce'. Scan count 0, logical reads 130880, physical reads 16530, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'legfasi'. Scan count 56, logical reads 10891908, physical reads 3168, read-ahead reads 984606, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    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 'legutent'. Scan count 2, logical reads 124, physical reads 9, read-ahead reads 39, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    So what I did was:

    I got all the logical read and tried to get the size in MB:

    SELECT ((130880 +10891908 + 124) * 8) / 1024 --86.116 around 84 gb ?

    So as risult it gives 86.116, so my question is it is really 84 gb of disk reads ?

    And how it impact into server memory, of course all this 84 GB are not loaded in the memory, but can it impact heavily in the memory space?

    I hope to be clear with my questions, Thanks

  • November 24, 2010 at 9:50 am

    #1255180

    Physical reads are reads from disk. Logical reads are reads from memory. If a page is read 10 times, it's counted 10 times.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 25, 2010 at 2:50 am

    #1255487

    Hi,

    I didn't check your calculation, but # reads means the number of pages that needs to be read to execute the query. One page is 8kB.

    Cheers

  • November 25, 2010 at 7:18 am

    #1255568

    Hi guys thanks a lot for both explanations !

    My calculation was just to take the number of logical read * 8 gives the number of kb, and /1024 the number in MB.

    SELECT (10891908 * 8) / 1024

    where 10891908 is the logic read, in my first post i did a sum of the logical reads of all tables of the join.

    Thanks again !

Viewing 4 posts - 1 through 3 (of 3 total)

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