SQL Server Profiler issue

views:

answers:

SQL Server Profiler issue

Hello everyone,

I am using SQL Server 2008 Enterprise and I want to use SQL Server profiler to capture deadlock event only. And when deadlock occurs and event captured, I will stop SQL Server profiler.

My question is, since I launched SQL Server profiler, and before deadlock occur (event captured), any impact (more about performance impact) to database?

thanks in advance, George

+1 A:

SQL profiler is another client of the database so it will impose some load, but quite little.

You can see how much CPU etc is used by looking as sys.sysprocesses for the SPID used by profiler.

However, you can also trap deadlock information to the SQL Server error log by using trace flags 1204 and 1222. And this link too for an overview.

gbn 2010-06-22 05:11:49

If using Profiler before deadlock happens, how much percent do you think there will be performance impact? Any existing number public?

George2 2010-06-22 06:54:41

If no numeric data is also fine, if there could be some documents which claim performance impact is low, it will be also ok. Any available ones?

George2 2010-06-22 07:07:13

@George2: I don't know of any hard numbers or papers on the impact of profiling.

gbn 2010-06-22 07:19:09

Thank you gbn, so "SQL profiler is another client of the database so it will impose some load, but quite little." -- how do you have this conclusion, from your experiment or experience or? :-)

George2 2010-06-22 07:24:58

@George2: experience. of course, if you choose to write every XML query plan to file then you'll kill the server (seen it happen), but if you're only tracing deadlocks then it's be acceptable. I'm running a trace now on my prod server... :-)

gbn 2010-06-22 07:55:22

Very cool, thank you gbn! I fully trust you guru guys. :-)

George2 2010-06-22 08:14:29

ansaurus

tags:

views:

answers:

SQL Server Profiler issue

related questions