Use extended events instead of trace when trace initialized due to alert
However, I'm expanding this. If the SQL version is high enough the trace of current activity should be using extended events instead of a trace, this would reduce the total impact to the server performance while also expanding the available information.
Thanks for the suggestion – we’ll monitor how popular it gets
Sheldon Hull commented
Followup. This is really important to support going forward. The overhead for servers is much lower, so the monitoring can actually be more detailed with less impact to server performance. Reducing the monitoring load while gathering more metrics would be a great gain. Any idea of the roadmap for this?
Sheldon Hull commented
The triggering of a trace should not be at any alert, but instead you should specify in the alert configuration that the high, medium, or low level alert would qualify for a quick trace. For instance, dbcc_integrity check alerts shouldn't trigger a trace, but a long running job at the high level could be set to run the trace.
This is a big deal, as some competing products build this in, allowing you to not impact server performance to trace except on the most important issues.
Brian McCullough commented
The profiler can sometimes collect too much data, I would like to be able to restrict the profiler to a specific database or login to better focus my troubleshooting abilities while minimizing impact of the trace.
This does sound useful and has been mentioned to us before - it's logged as SRP-17
Thanks for the suggestion.
I'd like to enable the Profile Trace on a SQL instance during a certain timeframe in the middle of the night that is generating alerts, without leaving the trace on all night.
Priya Sinha commented
Thanks a lot for the suggestions!
The GUI of SQL monitor is not easy to use as SQL Response, In SQL Response, I can easy highlight the statement in Profiler Trace, but SQL Monitor not. Also, if the statement is longer than the width of browser, the end of statement can't be displayed.
When I troubleshoot a dead lock/ blocking issue, I need switch over 2 process in Profiler Trace, it will be great if 3.0 conatin a timeline to compare 2 statement.