3

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 .

5
  • 2
    What's the problem that the experts are trying to solve?
    – Brent Ozar
    Jan 11, 2015 at 18:02
  • 1
    We have slow running queries in our environment .. Sometimes sql server waits long time to respond... Lot of dynamic queries are fired..
    – Nimesh
    Jan 11, 2015 at 18:22
  • Since last 2 weeks server literally dies during more load .. And no requests are processed at all.. All we do is kill lot of sessions and then it comes back to bit stable
    – Nimesh
    Jan 11, 2015 at 18:24
  • Also from the trace files we get only the procs which are high on CPU and memory.. So is it really good practice to run profiler on prod? Does it not add the load on server?
    – Nimesh
    Jan 11, 2015 at 18:25
  • Profiler can have a much more dramatic impact on your workload than a server-side trace or extended events, and is universally discouraged. But don't be fooled - I can make a server-side trace or extended events session bring your server to its knees, too. So it depends on the nature of your workload and what the trace is configured to collect... Jan 11, 2015 at 19:37

2 Answers 2

3

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.

2
  • 1
    But a server-side trace can do the same thing Profiler can, with far less of the intensive overhead. Extended Events too. Generally. Jan 12, 2015 at 2:21
  • Thanks Brent & Aaron for your replies... We have stopped profiler and since 2 days server is running fine.. Have asked the experts to analyse the data which they have.
    – Nimesh
    Jan 13, 2015 at 16:24
3

Let's get one thing straight first - is the application Profiler running, or is it a server-side trace running? If it's the former, then stop doing that and just run the server-side trace instead.

Writing 20GB of trace file per day is not necessarily awful, but it's a lot of information to try to analyse, and some harsher filtering could be useful. For example, looking for queries that have a large number of reads or take a long time, your blocked process reports... But if you've got two weeks of trace file data, taking around 300GB, then stop the trace and analyse what you've got.

If you have filters on your trace, then as your system is tuned less information should be collected.

It sounds like your tuning experts have a lot of information to go on, including the fact that the system becomes unresponsive and some sessions need to be terminated. This could be a sign of blocking, and it could be useful to look in the trace to see what's blocking. It could also be other factors, such as hold-ups with mirroring or the like, which a trace is less likely to show. But they should have a lot of information to consume and should able to reduce the amount of logging for a while.

3
  • Hi Rob, thanks for your suggestion I did exactly what u said.. Will revert in coming days about how the progress is.
    – Nimesh
    Jan 13, 2015 at 17:31
  • Also is there any tool/script using which we can run the SQL trace automatically when load on the server increases?
    – Nimesh
    Jan 13, 2015 at 17:32
  • You can use SQL Agent to have an alert in response to certain conditions, and run a job at that time. That job could collect all kinds of useful things.
    – Rob Farley
    Jan 13, 2015 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.