SQL Server Performance Forum – Threads Archive
Performance improves with Trace onWe have a VFP app (using ODBC) that runs 100 rows in 22 seconds with the SQL Profiler Trace turned off, and 11 seconds with it turned on. It used to run fine, but the DBA’s created a new database and since then, the performance has sagged. Anyone heard of such a thing? I posted in the Microsoft Newsgroups but they’re all stumped. Thanks. -k
How about the indexes on that table?
And how about DBCC checks for that database? _________
Are you referring to a single query that is the problem, or a whole bunch of queries? If a single query, can you post the query for us to look at? —————————–
Brad M. McGehee, MVP
Also have you cleared the cache during runs? Try restarting the sql server before each query to see if it’s still faster when profiler is on. /Argyle
Wouldn’t DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE be enough? Bambola.
Not if you want to get rid of any effects from connection pooling by the app <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />/Argyle
quote:Originally posted by synapsevampire …a VFP app…that runs 100 rows in 22 seconds with the SQL Profiler Trace turned off, and 11 seconds with it turned on.
Just one worry-and it may seem petty. Which tool(s) did you use to get these performance measurements ? To be sure that performance readings are consistent and comparable, it is advisable to use the same tool for all readings. There is a famous Uncertainty Principle out there which might also go against the exact rational of measuring the performance of a query with the performance measurement tool itself as a variable. NHO