Sunday, February 19, 2012

Can a trace slow down a server?

Hi

I was wondering. Can a trace slow down a server? If so, is it significant?

I got a trace that catch about 300-400 event /hour with the filter on that write its result in a .trc file used later to help analyse indexes.

Could this trace slow down a server?

Any sort of monitoring is going to have a performance hit. Unfortunately it is difficult to quantify. I would guess that the more events you capture the more impact it will have.
Try running performance monitor to assess the impact of your trace. Include the usual counters Processor, Memory and Hard disk counters (e.g. Memory : Available MBytes, Memory : Pages/sec, Processor : %Processor time, Process: Page Faults/sec, Hard disk : % Disk time, System : Context switches).
So stop the trace, start Performance monitor with your counters added, wait for a baseline to be established then restart your trace, then assess the impact.
You could use the "Index Tuning Wizard" or "Display Estimated Execution plan" in Query Analyser to analyse the effectiveness of your queries.

No comments:

Post a Comment