SQL Profiler Reads vs Set Statistics IO logical reads

  • 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.

  • 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).

  • 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

  • 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

  • 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?

  • 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 5 (of 5 total)

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