Is It safe to Run Trace on production server?

• Giridhar A

  SSC Veteran

  Points: 236

  More actions

  August 17, 2011 at 7:27 am

  #246444

  Hi Everyone,

  Could some one plesae tell me whehter its safe to run trace on production server?

  Thanks

  Giri

• muthukkumaran Kaliyamoorthy

  SSCoach

  Points: 19026

  More actions

  August 17, 2011 at 7:31 am

  #1369588

  Giridhar A (8/17/2011)

  ---

  Hi Everyone,

  Could some one plesae tell me whehter its safe to run trace on production server?

  Thanks

  Giri

  For waht you think its safe. Trace will decrease the server performance.

  Muthukkumaran Kaliyamoorthy
  https://www.sqlserverblogforum.com/

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  August 17, 2011 at 7:41 am

  #1369592

  A server side trace capturing a reasonable number of columns and events (depending on the events, statement level execs for example would be bad) will not be noticeable on most production systems. Just don't use the Profiler GUI on production. That can lead to issues.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 17, 2011 at 7:43 am

  #1369594

  a server side trace typically has no performance impact, unless you create a trace that is logging everything; every column and every event would probably be a bad idea.

  I'm in the same camp of not worrying about the trace impact after this article about the near-zero impact of a server side trace where someone actually compared the throughput compared to no trace vs profiler trace vs server side trace.

  take a look at how the graph from that article says the server side trace hardly affects the transactions per second: only when he starts emulating 300 users doing 1200 transactions per second do you see any deviance from no trace at all vs server side trace.

  [/quote]

  my production server has a DML trace keeping track of the last 500 meg of DML transactions, similar to the way the default DDL trace tracks stuff, and no impact in performance.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!