SQL Trace - How to interpret the Reads column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Trace - How to interpret the Reads column...

SQL Trace - How to interpret the Reads column

Rate Topic

Display Mode

Topic Options

Author

Message

Wandrag

Posted Tuesday, January 08, 2013 3:22 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:08 AM
Points: 505, Visits: 961

Hi,
I'm playing around with SQL traces, and trying to understand what exactly it's returning.
For now, I'm looking at the Reads column, which is the number of logical page reads.

My question is, can one determine the amount of bytes/MB that the query reads per day from the database?

Looking at the following data:
TraceEvent - RPC:Completed
Reads - 621024

I've tried this:
621024 (Reads) * 8192 (page size) = 5,087,428,608 bytes
which seems a bit way to much....

Looking at the total amount of reads, it looks as if I'm reading the database in size a couple times...

Or is my understanding of the number of Reads not correct?

The same goes with the Writes column - which is the number of physical writes.

Can one calculate the data size read/write using the traces?

Thanks.

Post #1404093

Wandrag

Posted Tuesday, January 08, 2013 3:39 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:08 AM
Points: 505, Visits: 961

Never mind...
Looking at this article helped: (Some previous articles misguided me on the values in this column...)
http://www.sql-server-performance.com/2010/logical-reads/

Post #1404100

Wandrag

Posted Tuesday, January 08, 2013 4:50 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:08 AM
Points: 505, Visits: 961

Question: The number of reads recorded in a trace file, is that the number of logical PAGE reads (meaning a full 8k read).
OR is it a number of logical read operations done, and a logical read can maybe be a single record/column value?

The second option makes a bit more sense when calculating the numbers out a bit.
But I've read some more articles that is not really clear on this. Some "suggests" that it's page reads, others refer to read operations...

Thx

Post #1404158

GilaMonster

Posted Tuesday, January 08, 2013 5:11 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 37,734, Visits: 30,004

Logical reads - pages read.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1404169

Wandrag

Posted Tuesday, January 08, 2013 5:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:08 AM
Points: 505, Visits: 961

GilaMonster (1/8/2013)

Logical reads - pages read.

Ok - so this is correct:
621024 (Reads) * 8192 (page size) = 5,087,428,608 bytes ?

Post #1404180

GilaMonster

Posted Tuesday, January 08, 2013 6:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 37,734, Visits: 30,004

Looks right. Bear in mind those are logical reads, not physical, and if a page is read twice is it counted twice.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1404198

« Prev Topic | Next Topic »

Permissions