If you ever wondered if SQL Server Profiler can influence negatively your production database servers that you watch every day with love and attentions, then stop wondering because I have an empirical proof of the fact that it causes no harm.
The experiment had the target to demonstrate that SQL Server and Profiler were sufficiently independent during the profiling process; it required:
- any database with sufficient data to let you create queries of measurable length (i.e. > 100ms)
- a small app that calls the database continuously
- an opened Profiler on the database
- a debugger (like Visual Studio)
The test is simple: start the Profiler, start the small app, then attach the debugger to the Profiler process and suspend it for a few minutes. You notice that the response time of the queries does not change both with the Profiler running and with it stopped (besides a few milliseconds, as you obviously expect), and the memory occupied by SQL Server also does not change (so data sent to the Profiler seems lost.)
When you un-suspend the Profiler process, you discover that it restarts showing calls as nothing happened but, instead of the calls happened in the suspension period, you find a single line that says that “data was lost”.
I think that this proof is sufficient to demonstrate that the profiling implementation was done right by Microsoft. 😉
EDIT 20080219: … but I was wrong. In fact, we faced a “slowness” again on Production environment of a customer and, after some quick research, we killed the connections of a SQL Profiler and magically the slowness went away. Very empirical again, but we had to investigate and found that the Profiler in not as “light” as we thought in the very well done book Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization. Here I want only to cite this sentence from the book: “By far the easiest way to make sure that your trace activity does not cause issues on your busy production server is to avoid use of the SQL Server Profiler tool except as a scripting utility […] and for very small trace jobs […].”, because the authors did a test which showed that a Profiler can weigth as much as a 10 percent performance hit on the server even with a small number of users.