I'm using MS Sql Server 2000, and for many queries when I run a query in Query Analyzer, with the statistics turned on, the numbers reported in the statistics tab are extremely different from the values shown in the Query Profiler. When the profiler and the analyzer are running at the same time, some queries report 200 reads in Analyzer, while the Profiler reports that the query preforms 14000 reads. Based on query performance, it appears to me that the Profiler is right. However, why is there such a large discrepancy between the two? Does anybody else have experience with the same thing occuring?
views:
754answers:
2
+1
A:
These two utilities do different things:
SQL Profiler watches what's happening and reports on it.
Query Analyzer looks at the SQL code and figures out how to run it.
Query Analyzer uses statistics to figure out what it's going to do, but the results are an action plan, even if statistics are shown.
SQL Profiler reports afterwards on what happened - it's more accurate, but is retroactive.
Try updating your statistics if you find that Query Analyzer becomes very inaccurate.
Keith
2008-10-09 14:37:14
"Query Analyzer uses statistics to figure out what it's going to do, but the results are an action plan, even if statistics are shown. " Huh? If you run the query with statistics on, the query is executed and the results are correct.
Jonas Lincoln
2008-10-09 15:18:48
Query Analyzer can show the exact plan, but the row counts for those steps still rely on statistics. Those counts can be out if the statistics are.
Keith
2008-10-09 16:23:13
I've actually never experienced that when running a query in QA and checking the results. The row counts have always been correct. They are very sketchy when just estimating the query, though.
Jonas Lincoln
2008-10-10 15:10:29
A:
The 'Include Actual Execution' plan contains the actual row counts.
The 'Include Estimated Execution' plan is estimated from the current statistics. Why is why it can differ when statistics are out of date.
Mitch Wheat
2009-01-24 01:04:58