December 10, 2012 at 11:56 am
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.
December 10, 2012 at 12:15 pm
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).
September 13, 2013 at 7:24 am
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
September 13, 2013 at 7:24 am
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
June 25, 2015 at 9:03 am
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?
June 25, 2015 at 9:18 am
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