Impact on production server
-
Hi Team,
Heard that "Running SQL Profiler on Production environments will degrade performance of server and it adds additional load on the CPU"
What is impact on production server if i ran the profiler thru remote.
Please suggest.
-
The correct way to trace production is to use a Server Side Trace.
There is an excellent stairway series on SQLServerCentral.
-
Minnu (10/9/2013)
Hi Team,Heard that "Running SQL Profiler on Production environments will degrade performance of server and it adds additional load on the CPU"
The 'running profiler on production environments' doesn't refer to where the profiler client is running, just where the trace is. Using SQL profiler to trace a production environment can degrade performance. Where the profiler client is located is irrelevant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
When running SQL Profiler be careful to set the filters to be as restrictive as possible while still capturing the data you're looking for. This will both minimize the peformance hit as as well as reduce the irrelevent information you have to scroll through.
If you have a very busy production envronment an unfiltered trace can make a user app grind to a halt in seconds.
-
dan-572483 (10/9/2013)
When running SQL Profiler be careful to set the filters to be as restrictive as possible while still capturing the data you're looking for. This will both minimize the peformance hit as as well as reduce the irrelevent information you have to scroll through.Reducing the events and columns helps, however the way the trace works, the filters are evaluated quite late and may not reduce the performance impact (may even increase it if they're complex). It's one of the differences with the extended events architecture where the filters are evaluated very early and the rest of the event only processed if the filters are true.
p.s. I've crashed a busy production server with a trace that had two events (RPC:Completed and T-SQL:BatchCompleted) and about 8 columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
A busy server is going to have a lot of T-SQL batches completed in a short amount of time. I've successfully ran traces against busy servers while filtering on the HostName (the end-user's computer) or ClientProcessID (looked up in Task Manager on the client machine) to capture statements orginated by a single user only.
Even when doing this, I still check the filters 2 or 3 times and cross my fingers before clicking Run.
-
dan-572483 (10/9/2013)
A busy server is going to have a lot of T-SQL batches completed in a short amount of time. I've successfully ran traces against busy servers while filtering on the HostName (the end-user's computer) or ClientProcessID (looked up in Task Manager on the client machine) to capture statements orginated by a single user only.Not too bad, it was a 'decision-support' type server. About 200MB of trace data in 30 minutes. Since I was doing a general trace for performance benchmarking, couldn't filter to a single user.
Be careful with the ClientProcessID, it's only unique within a client machine, not across multiple machines.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Sean Pearce (10/9/2013)
The correct way to trace production is to use a Server Side Trace.There is an excellent stairway series on SQLServerCentral.
+1
I configure a background server side trace on all my critical production SQL Servers with the appropriate filtering and event selection in place and have never had an issue with them regardless of how busy they were. On the other hand, I have seen numerous instances of wide open Profiler Traces running on a DBA's laptop / desktop that ended up crashing a production server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply