about the statistics io

• marcosjroig

  Mr or Mrs. 500

  Points: 532

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• Brigadur

  SSCrazy

  Points: 2097

  More actions

  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

• marcosjroig

  Mr or Mrs. 500

  Points: 532

  More actions

  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 !