Using Profiler Measurements

Question

Using Profiler Measurements

TRACEY-320982

SSChampion

Points: 13653
More actions
July 12, 2008 at 8:22 am

#195204

When using profiler. Example
Trying to determine what the measurements are in profiler in MG and GIG
Divide by 1024 but then the logical reads is not correct as this is 670 GIG ? and database not this large.
logical reads - 702949129 to get to MB is this 686473.75879 - 670.38453 IG
reads - 8130 = 8 KB
writes - 197352 = 192 MB
Suggestion was to / 128 but why 128.
Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The logical reads and writes are a count of pages, not MB, GB or other size measurements.

A page in SQL is 8kB, so if profiler tells you that for a single query it did 702949129 reads, that means that it read that many 8k pages. That does count multiples, so if SQL had to read 100 pages once and 15 pages 20 times, that would amount to 400 reads.

702949129 is an awful lot of reads for a single query. I would suggest either optimising the code or tuning the indexes or both.

Profiler doesn't have a logical read column.

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

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

A lot of data 🙂

I was trying to get it in MG to explain to people how much data they are requesting...blocks will not mean too much to them.

I am going to look at what it is doing in execution plans.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

TRACEY (7/12/2008)
A lot of data 🙂
I was trying to get it in MG to explain to people how much data they are requesting...blocks will not mean too much to them.

So convert it to megabytes. Each page is 8 kilobytes in size. So you take the number of pages, multiply by 8 and you have the number of kilobytes read during the query. Divide by 1025 to get it in megabytes, divide by 1024 again to get it in gigabytes.

You'll probably find multiple table scans in the exec plan. There's not much else that will produce that many reads in a single query.

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

"logical reads" indicates the total number of rows read from all tables referenced and cannot be converted to any size value unless there is only one table being read.

"Reads" are the number of read operations on the logical disk, which may be from memory or from physical disk, and can be converted to a size.

"Writes" are are the number of write operations to physical disk and can be converted a size.

For an individual connection, "set statistics io on" can be set, which will shows these counts by table.

"128" is the number of pages per megabyte ( ( 1024 * 1024) / 8192 bytes per page ), so it is easier to divide by a page count by 128 than perform the more complex calculation.

From your statistics,

Logical reads are 702,949,129

Reads are 8130 or about 64 Mb

Writes are about 1.5 Gb

Good Luck

SQL = Scarcely Qualifies as a Language

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

From your statistics,

Logical reads are 702,949, 129

Reads are 8130 or about 64 Mb

Writes are about 1.5 Gb

How did you get 129

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

TRACEY (7/12/2008)
How did you get 129

i'm assuming he got it from here

TRACEY (7/12/2008)
When using profiler. Example
Trying to determine what the measurements are in profiler in MG and GIG
Divide by 1024 but then the logical reads is not correct as this is 670 GIG ? and database not this large.
logical reads - 702949129 to get to MB is this 686473.75879 - 670.38453 IG
reads - 8130 = 8 KB
writes - 197352 = 192 MB
Suggestion was to / 128 but why 128.
Cheers

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 7

i read it wrong i thought that was size next to my value it was a space - wasn't looking clearly enough.

Got size in read/writes but logical reads are not in size it is in blocks so cannot put in MG /GIG

Carl Federl One Orange Chip Points: 25384 More actions · Answer 8

Logical reads are 702,949, 129
How did you get 129 ?

The "129" did not come from anywhere but the confusion may be my use of "commas" to seperate groups of three digits, which is the US convention, but other countries use a "period" as a seperator.

"702,949,129" is one number and without any seperators is 702949129.

SQL = Scarcely Qualifies as a Language