SQL Server Performance Forum – Threads Archive
SET STATISTICS IO vs SQL Profiler
How can I determine what the difference is between what I see when I use SET STATISTICS IO ON and what I see in the Reads column of SQL Profiler? I have a query that I’ve optimized and shows very little reads when I run SET STATISTICS IO. But when I monitor the same server using Profiler, the difference is drastic. Why such a dramatic difference? And how can I determine the cause? Here’s an INF article confirming the difference, but it’s too general for me (still a little wet behind the ears as a DBA):http://support.microsoft.com/kb/314648yes, the article pretty much explains it
how drastic? 10 IO’s or 1000’s is it because you are not using stored proc’s
or your stored proc is recompiling
both are much worse than a few IO
We are using Parameterized SQL (sp_executesql), not stored procs. If I run the query from SSMS and get this: Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0.
Table ‘table_tournament’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table ‘table_school’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table ‘table_contest_school_ref’. Scan count 94, logical reads 385, physical reads 99, read-ahead reads 0.
Table ‘table_contest’. Scan count 1013, logical reads 3258, physical reads 0, read-ahead reads 0.
Table ‘table_section_school_ref’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0. But when I see the query in Profiler it shows anywhere from 2.6 million – 6.4 million reads. If I run a trace against my SSMS session I see 3,790 reads in Profiler. Why such a huge difference?
lets see the query
as issued in parameterized form better yet,
follow my directions on the top post for what to do if you have a slow query
]]>