February 5, 2015 at 8:45 am
I have a 2008R2 box that is randomly having a performance problem between certain times of the day. There is already a trace running for long running queries. teh trace was configured by someone else. I have little experience with Profiler. The trace does not capture blocking information, but I have been given a query to alter the trace to include the Blocked Process Report and the corrsponding sp_Configure statements . I am looking at an article from Brad McGehee that indicates capturing blocking in SQL 2005 is resource intensive so the Blocked Processes report is off by default. My questions are:
1.Is it less resource intensive to query DMv's for blocking every 3-5 seconds for 20 minutes, capture that to a temp table and write it to a file for later review or to have the blocked process report running? Which is the better option?
2. I know of Extended Events but have never used it. Could I do the same thing with Extended Events and how does that compare to the other two options above? Performance Problem - Use SQL Trace, Query DMVs or Extended Events
February 5, 2015 at 11:52 am
It does depend on the query you're running against the DMVs, but, generally, those are very lightweight, so it should be fine. The nice thing about them is that you can filter them by database id or whatever to ensure you limit the result sets.
Extended events do work a lot better than trace. Further, they have less of an impact on the system. Also, you can filter them too, making their impact even lighter. Personally, I'd probably capture the events without the blocking report first, then, only turn that on if I need to, but filter it so that it captures on specific blocking events.
Personally, I'd turn off the trace events. They just put more of a load on the system than your other choices.
If the system is already under stress, minimizing the observer impact is extremely important.
"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
February 5, 2015 at 10:09 pm
Thanks Grant. This is somewhat the answer I was expecting but having no real experience with these tools I wanted feedback. I think I will work toward convincing the powers that be to turn off the trace event. I think I am going to need to prove/demo the problem and solution before people will listen. Guess I need to get cracking on learning Extended Events!
I do see TRACEWRITE waits always in the top 1 or 2 on every server with an avg wait of 2 seconds. I will look up this wait type but I am guessing I am going to find ammunition to encourage people to turn off the company's standard SQL Trace that is always running on our servers. No one is sitting with Profiler open 24X7 but there is a defined server side trace that is running 24X7.
February 6, 2015 at 8:06 am
Prior to extended events, I had trace running 24/7 on a lot of systems too. It's work to deal with the data generated, but it's a great way to gather query metrics. It's just as much work dealing with the extended events data generated, but the impact on the system is less.
The real issue with ex events on 2008 is that there are a few events you can't get, like execution plans, and there's not a built-in gui for generating the scripts. But all the rest is head & shoulders better than trace events.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply