Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Profiler Reads vs Set Statistics IO logical reads


SQL Profiler Reads vs Set Statistics IO logical reads

Author
Message
Steve-443559
Steve-443559
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 578
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
Steve-443559
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 578
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
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
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; They'll drag you down to their level and beat you with experience" ;-)
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
BTW, I am using SQL 2008 EE, not 7,2000

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Hans Lindgren
Hans Lindgren
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 366
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
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
It's possible but I haven't found any instances of that (I scrutinize heavily for those types of performance killers) :-D

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search