patternsqlMinor
SQL profiler running on prod
Viewed 0 times
sqlprodrunningprofiler
Problem
we have got performance experts analyzing our SQL server by running profiler traces in production. The profiler trace creates 20 GB of trace files everyday since 2 weeks. Is this a correct approach? Please suggest .
Solution
You've got a few different questions in here.
Q: Is it really a good practice to run Profiler on a production system?
Generally, you want to avoid doing it, but there can be situations where it's the most efficient way to solve a problem. For example, if you've got a SQL Server 2005 box, and every query comes in with OPTION RECOMPILE so it doesn't stick around in the plan cache, and you need to troubleshoot a particular sequence of queries, then Profiler can be your best bet.
You also mentioned dynamic queries - depending on how the dynamic queries are built, Profiler might indeed be the only way to capture the problem.
Some monitoring programs are even configured to automatically run a trace whenever the server comes under heavy load so that they can help diagnose the problem.
Q: If the server dies under load, how should we troubleshoot it?
You'll probably need to be more specific about what "dies" means. If it stops accepting any query requests, I'd start by checking the error logs, not by running Profiler.
Q: After running traces for 2 weeks, we haven't solved the problem. Are we using a good approach?
If you haven't solved it after two weeks, that would be no.
Q: Is it really a good practice to run Profiler on a production system?
Generally, you want to avoid doing it, but there can be situations where it's the most efficient way to solve a problem. For example, if you've got a SQL Server 2005 box, and every query comes in with OPTION RECOMPILE so it doesn't stick around in the plan cache, and you need to troubleshoot a particular sequence of queries, then Profiler can be your best bet.
You also mentioned dynamic queries - depending on how the dynamic queries are built, Profiler might indeed be the only way to capture the problem.
Some monitoring programs are even configured to automatically run a trace whenever the server comes under heavy load so that they can help diagnose the problem.
Q: If the server dies under load, how should we troubleshoot it?
You'll probably need to be more specific about what "dies" means. If it stops accepting any query requests, I'd start by checking the error logs, not by running Profiler.
Q: After running traces for 2 weeks, we haven't solved the problem. Are we using a good approach?
If you haven't solved it after two weeks, that would be no.
Context
StackExchange Database Administrators Q#89037, answer score: 3
Revisions (0)
No revisions yet.