Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2004-03-19 : 16:16:42
|
Hey everyone.In Query Analyzer, for SQL Server 2000 sp3, I am comparing "SET STATISTICS IO ON" with turning on the server trace via the "Show Server Trace" option. Now, when I do:select * from northwind..orders I get the following results for reads: SET STATISTICS IO ON: 21 Show Server Trace: 28I read recently that the trace will show all reads, including metaschema, system tables, statistics, etc, while SET STATISTICS only returns reads only for the actual table. Fine, the results above are consistent with that theory. But what about when the numbers are reversed? We have a very complicated stored procedure. When I do the same comparison the numbers I get are: SET STATISTICS IO ON: 18,342 Show Server Trace: 650Whoa. This difference is a little too big to try and sweep under the rug. Yet I have no idea why there is a massive difference between the two, especially when the supposedly more comprehensive trace has the smaller number. There are no UDFs in the proc. Any ideas?3P==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2004-03-19 : 17:20:01
|
| I've been working on this a while now, and I have more information. At the same time I ran a Profile trace filtered on just the SPID from my QA session. The Profile trace's read numbers are also drastically off from QA's Show Server Trace option.In other words, SET STATISTICS IO ON and the Profile trace are consistent, but neither of them is consistent with QA's Show Server Trace.Lastly, it would appear that they are drastically off only when the statement involved uses a temp table or table variable. Interesting.3P==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
 |
|
|
|
|
|
|
|
Diff: "set statistics io" and "show server trace"?