SQL Server Performance

SET STATISTICS IO vs SQL Profiler

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by colossus, Mar 12, 2007.

  1. colossus New Member

    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/314648
  2. joechang New Member

    yes, 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
  3. colossus New Member

    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?
  4. joechang New Member

    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

Share This Page