My SQL Server 2005 is Slow

Testing code that queries SQL Server 2005, I kept observing long delays. Sometimes my tests would execute in less than one second, which was expected. Frequently, they would take 30 to 120 seconds!

A little digging into the SQL Server, I found the two top waits were SQLTRACE_BUFFER_FLUSH and RESOURCE_SEMAPHORE, both of which would grow in correlation to delays I observed when running my unit tests.

A little reading found: this forum topic and this kb article. The former being the most useful. It turns out SQL 2005 will run a trace by default; to assist in fixing problems later. (read "default trace enabled" in SQL Books Online) Unfortunately on my development box, it appears the trace is the source of my delays. So my solution is to turn off the default trace using:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'default trace enabled',0
reconfigure

After this, my tests always ran in less than one second!