July 21, 2011 at 9:56 am
Hey guys, I'm trying to setup a trace to catch long running queries. Works fine, I get all the columns I want for queries that ran over 1 second.
Now I also want to save the actual plan used. Easy right? >> add the event performance / showplan xml statistics profile.
Wrong! The 1 little glitch is that now I see the plan for every darn query that hits the server (1000s / second).
Anybody got an idea to go around that?
P.S. I use option recompile quite often so using the plan cache is not really an option AFAIK.
July 21, 2011 at 10:32 am
Have you tried using extended events. I'm know you can filter better there.
"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
July 21, 2011 at 10:36 am
Grant Fritchey (7/21/2011)
Have you tried using extended events. I'm know you can filter better there.
I'm on SQL 2K5. And apparently those features is new on sql 2K8.
July 21, 2011 at 10:41 am
Ninja's_RGR'us (7/21/2011)
Grant Fritchey (7/21/2011)
Have you tried using extended events. I'm know you can filter better there.I'm on SQL 2K5. And apparently those features is new on sql 2K8.
sorry, I always miss which forum we're on.
"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
July 21, 2011 at 10:43 am
Well let's explore it this way.
Couldn't I install express 2008 R2 [With advanced services] and configure the monitoring from there to an older version (I'd be very surprised if it were possible, but you never know).
July 21, 2011 at 10:57 am
There's no extended event for actual execution plans, not even in Denali.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 10:59 am
I'd do this in 2 phases
1) catch the long running queries. Make note of the names/text of the ones you want to work on
2) A second profiler trace that grabs the exec plan and filters for the names/text that you want.
p.s. Even if you weren't using recompile, what you get from the plan cache has no runtime information.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2011 at 11:05 am
GilaMonster (7/21/2011)
There's no extended event for actual execution plans, not even in Denali.
Ok so one none option eliminated.
July 21, 2011 at 11:14 am
GilaMonster (7/21/2011)
I'd do this in 2 phases1) catch the long running queries. Make note of the names/text of the ones you want to work on
2) A second profiler trace that grabs the exec plan and filters for the names/text that you want.
p.s. Even if you weren't using recompile, what you get from the plan cache has no runtime information.
Ya that's why I wanted to use that particular event, I get all I need to tune.
My challenge here is that I'm tuning to the point of non-regular bad queries on a 3rd party app where I cannot change the code (but I can add indexes). This is far past the point of your regular tuning articles. Users are starting to complain but this is very sporadic.
We have regular events of long(er) running queries but they are all over the map, I can't really see any pattern for now and I'd have to trace on dozens of different names to get all I want. That's why I'd ideally want to catch both at the same, but only on log running events.
P.S. I have been using Sql monitor for 2 months and we've had less than 2 blocking event per day on average so I know the problem is most likely in the plans.
I have almost 1 TB of hd laying around on 2nd san... maybe I can just byte the bullet for 1 day and see what kind of impact this has on the server...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply