Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Profiler Reads vs Set Statistics IO logical reads Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 11:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 160, Visits: 529
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.
Post #1394734
Posted Monday, December 10, 2012 12:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 160, Visits: 529
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).
Post #1394743
Posted Friday, September 13, 2013 7:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,025, Visits: 7,172
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"
Post #1494579
Posted Friday, September 13, 2013 7:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,025, Visits: 7,172
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"
Post #1494580
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse