What do Profiler Reads really count ...

Question

What do Profiler Reads really count ...

Tim Walker.

SSCertifiable

Points: 5173
More actions
April 19, 2009 at 4:16 pm

#203725

It's clear that when looking at Profiler read counts that bigger is worse than smaller.
I also read that the official definition of this count is 'Number of logical disk reads performed by the server on behalf of the event.'
However I don't understand what this really means when talking about variables.
I have seen statements such as SELECT @vcStep = '10' take between 0 (zero) and 17 reads according the the profiler.
Once I gratutate to table variables this can rise to thousands of reads for something I think is already loaded in to data cache.
So what is this counter really counting? Can anyone help?
.

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Hey Tim,

MSDN
A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory. A logical write occurs when data is modified in a page in memory. A physical write occurs when the page is written to disk. It is possible for a page to remain in memory long enough to have more than one logical write made before it is physically written to disk.

The full article is here

Also see the documentation for SET STATISTICS IO ON in Books Online.

Assignment to a variable does not generate logical I/O.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

Thanks for that Paul, much appreciated.

The problem I have though is that the profiler is my preferred tool for analysing performance. I can rationlise what the CPU and Write counters are telling me (based on reading what they are supposed to be) because the numbers I observe make sense to me. CPU is clearly in milliseconds / microseconds depending how you do it and writes is clearly in pages.

Read don't look so obvious to me. I sometimes observe reads where I don't expect to, e.g. when setting a variable (SELECT @vcStep = '30'). The number of reads observed isn't my problem in this case because it is so small, but it does show I don't understand what is going on properly. And why did I observe 17 reads - can't be reading 17 pages can it?

I guess my bigger issue is that I'm not clear how to interpret reads (in terms of the unit of measure) in terms of a query involving table variables like this.

SELECT

table1.somecol,

variable1.anothercol

FROM

table1

INNER JOIN

@variable1 variable1

ON

table1.keycol = variable1.keycol

WHERE

table1.filtercol = 12345

Part of this is probably that I don't understand how table variables are actually held and whether they are effectively just part of the data cache or not.

Finally I've found you often can't balance these figures with STATISTICS IO but that's another story!

.

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

Table variables, just like temp tables are allocated space in TempDB. SQL tries as much as possible to keep them in memory (in the data cache) because it knows that they're very likely to be used again very shortly. If there's memory pressure or the temp table/table variable grows too big, then they can be forced to disk and out of memory.

I think part of your confusion is over the difference between logical reads and physical reads.

A logical read is a read from memory, from the buffer pool (typically). A physical read is a read from disk. So even if your tables and your table variables are all in cache, you will still see logical reads. You may not see any physical reads though.

Tim Walker (4/20/2009)
Read don't look so obvious to me. I sometimes observe reads where I don't expect to, e.g. when setting a variable (SELECT @vcStep = '30'). The number of reads observed isn't my problem in this case because it is so small, but it does show I don't understand what is going on properly. And why did I observe 17 reads - can't be reading 17 pages can it?

It can be. I suspect (but don't know 100% for sure) that profiler counts reads done as part of plan lookups and other stuff that statistics IO doesn't count. Personally I use profiler's reads count as a rough guide to which queries are doing the most reads, and then stats IO to do detailed breakdowns of exactly what they're reading and from where.

I guess my bigger issue is that I'm not clear how to interpret reads (in terms of the unit of measure) in terms of a query involving table variables like this.

Lower is better, always. If you want details as to what IOs come from where, you need to run the query with Stats IO on.

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

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

This kb may be of interest, even though it's supposedly just for SQL 7 and 2000.

http://support.microsoft.com/kb/314648

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

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 5

Thanks Gail, very useful, particularly how table variables are actually held and the kb article which explains where the 'extra' reads can come from.

I'm clear on the difference between logical and physical reads, I just feel that sometimes the logical reads look too high, particularly when compared to using a temporary table. However I've never done a controlled test on this and it may well come down to optimisation differences because of the lack of statistics on the table variable compared to the temporary table.

Thanks again for your help, it is much appreciated.

.

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

Tim Walker (4/20/2009)
However I've never done a controlled test on this and it may well come down to optimisation differences because of the lack of statistics on the table variable compared to the temporary table.

Very likely the case. Because the optimiser thinks that there's only 1 row in a table variable, it will often generate plans that repeatedly read the table variable because it thinks doing so is cheap. When you have a lot of rows in the table variable, that mis-estimate can become very costly.

I often suggest that if there's going to be more than 100 rows in a table variable, consider replacing it with a temp table, especially if it's going to be joined to other tables in later queries.

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

Thanks Gail.

I cannot believe I actually went off to see if there was any way I could get a variable assignment to generate reads in profiler :blush:

The guidance on rows for table variables versus 'real' temporary tables is important.

...though there are times when the fact that a table variable has no statistics and is treated as only ever holding one row is handy to rein in an over-ambitious QO! OPTION (FAST 1) or (FASTFIRSTROW) don't quite do the same thing. Anyway I digress. Thanks for helping out.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 8

Thanks very much to both of you for your assistance.

.