SQL Profiler Reads vs Set Statistics IO logical reads

  • Steve-443559

    SSCrazy

    Points: 2302

    I created a table with a single varchar(10) column and ran a select statement against it. In Profiler, it shows 25 reads but in SSMS using Statistics IO, it shows only 1 logical read. What is the difference between the two values? Also, I did the same thing against another SQL 2005 server and the reads in Profiler show only 3 and I'm trying to figure out why one shows 3 and one shows 25 for such a simple statement. The table has no triggers, indexes.

    Thanks.

  • Steve-443559

    SSCrazy

    Points: 2302

    more information: I found out that on the server with greater reads, it always generates a CacheInsert event, but on the server with lesser reads, it generates a CacheHit event. Maybe that's the cause of the extra reads. Further, the procedure cache hit ration is very low (20%) and the cache size is small (only a few MB). so now my question is - why is the query plan not being written to cache (or aged out immediately).

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    Did you ever find an answer to this? I have a similar situation: I have a query that consistently showing 2.4 million reads in profiler, yet running the exact same query in SSMS results in the following

    Table 'X'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    The execution shows a 100% nonclustered index seek...so not really sure what can be done to "optimize".

    In addition, in profiler the duration is always around 2.561 seconds, yet running via SSMS is constently < 1sec.

    ?

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    BTW, I am using SQL 2008 EE, not 7,2000

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Hans Lindgren

    SSChampion

    Points: 10494

    When you 'see' it in profiler, might you be experiencing an IMPLICT_CONVERSION of your column type?

    I.e. your application passes, for example, an NVARCHAR (for comparison) and the column type is a VARCHAR?

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    It's possible but I haven't found any instances of that (I scrutinize heavily for those types of performance killers) 😀

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 6 posts - 1 through 6 (of 6 total)

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