February 13, 2009 at 5:52 am
Unfortunately I can never actually get the actual execution plan, when I run the query manually it always gives me the good plan. The bad plan was extracted out of the DMV for the executing statement while it was hammering my DB. I assume the actual execution plan of the good plan is no use as it won't have the stats you need either? I guess I can never exactly match the generated code so it always evaluates me a new plan rather than re-using the one that it has cached.
The statement is out of profiler and comes from our dotNet application which generated paramaterized queries, so the @0-@5 stuff are parameters to the query which is not a stored procedure, if that makes sense.
Ken
February 13, 2009 at 6:02 am
You can get the actual plans from profiler. I don't recall offhand what event it is, so check Books Online.
Ok, so they are parameterised, which means the optimiser should be able to get a good estimate of the rows affected (which it can't with variables)
if you can get the actual plan for both good and bad, it will be of great help. You're right, the plans from the cache contain no run-time information (which makes sense)
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
February 13, 2009 at 6:12 am
Ah no chance there. When you profile to get plans I can never get any filter to work that doesn't cripple the system. It only ever happens in prod and we have gazzilions of statements a second so it just never works out to well. The only way I've found to be able to do it is by host which means when it happens I have to take a production server out of the main service pool and then get the trace set up and then get a user with a production login to come and log in locally to the server and do the business, it is just takes too long.
However I'm good with the whole OPTIMIZE FOR thing and I'll see how that goes.
Thanks for your input.
Ken.
February 13, 2009 at 6:31 am
How about querying sys.dm_exec_query_plans and pull it out of there? That would be the best way to go for something of this nature.
"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 13, 2009 at 6:49 am
That's where I got the bad plan in the first place I'm afraid. I was a little surprised to see it was an estimated plan though.
February 13, 2009 at 7:12 am
Ken Gaul (2/13/2009)
That's where I got the bad plan in the first place I'm afraid. I was a little surprised to see it was an estimated plan though.
Really? An estimated plan from the cache? Was it for a query that was actively executing? I wasn't aware that the estimated plans were stored in the cache.
"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 13, 2009 at 7:22 am
Something went wrong when Query Engine delivered Query Tree to Storage Engine?
N 56°04'39.16"
E 12°55'05.25"
February 13, 2009 at 7:25 am
This is what I ran to get the plan of the statement as it was running, is this right?
select r.plan_handle,p.query_plan
from sys.dm_exec_requests r
cross apply sys.dm_exec_query_plan(r.plan_handle) p
where session_id = 143
February 13, 2009 at 7:43 am
Yeah, that's how you get the actively executing plan. It must show the estimated plan while executing... I can't find documentation that says it would show the estimated plan though... Anyway, try getting a completed plan by using one of the other DMV's that will show the plan handle for a completed plan so we can get an actual execution plan, sys.dm_exec_query_stats for example.
"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 13, 2009 at 8:21 am
Sorry, shouldn't have used the term 'estimated'. I seem to have confused everyone. I'll explain better.
The terms estimated and actual for plans are actually a bit of a misnomer. A better description would be 'execution plan with run-time information' and 'execution plan without run-time information'
When, in management studio, someone clicks the 'display estimated execution plan' button, the query is compiled but not run, therefore there is no run-time information contained within it, only compile-time information.
When, in management studio, the query is run with the execution plan option enabled, the plan does contain the run-time for that specific execution, hence the plan contains things like 'actual row count, actual IO cost', etc
When a plan is cached, only the compile-time information is cached. The detailed run-time information is (I believe) discarded after updating the aggregated query stats. Hence, when you retrieve a query from the plan cache, it will not contain the run-time information. Consider a plan that's been used 20 times. Which execution's run-time information would it contain? Remember that there's only one plan in cache per procedure.
Profiler can capture (depending on event) the plan without the run-time information or the plan with the run-time information.
Does that make any sense to anyone?
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
February 13, 2009 at 8:27 am
Grant Fritchey (2/13/2009)
Yeah, that's how you get the actively executing plan. It must show the estimated plan while executing... I can't find documentation that says it would show the estimated plan though... Anyway, try getting a completed plan by using one of the other DMV's that will show the plan handle for a completed plan so we can get an actual execution plan, sys.dm_exec_query_stats for example.
I just figured out what I was looking at. It's the compiled plan, which is the plan that was used, but sans statistics and actual execution measurements because the plan itself is generic for use with other queries that will have other row counts, etc.
Man, I can be slow sometimes.
Yeah, you'll need to capture the plan using a trace or by executing it. Since it's iffy as to when you get the bad plan, I'd try the trace. If filtering the trace is slowing things down, don't. Just make sure you're running it as a server side trace and not through the Profiler gui.
"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 13, 2009 at 8:30 am
GilaMonster (2/13/2009)
Sorry, shouldn't have used the term 'estimated'. I seem to have confused everyone. I'll explain better.The terms estimated and actual for plans are actually a bit of a misnomer. A better description would be 'execution plan with run-time information' and 'execution plan without run-time information'
When, in management studio, someone clicks the 'display estimated execution plan' button, the query is compiled but not run, therefore there is no run-time information contained within it, only compile-time information.
When, in management studio, the query is run with the execution plan option enabled, the plan does contain the run-time for that specific execution, hence the plan contains things like 'actual row count, actual IO cost', etc
When a plan is cached, only the compile-time information is cached. The detailed run-time information is (I believe) discarded after updating the aggregated query stats. Hence, when you retrieve a query from the plan cache, it will not contain the run-time information. Consider a plan that's been used 20 times. Which execution's run-time information would it contain? Remember that there's only one plan in cache per procedure.
Profiler can capture (depending on event) the plan without the run-time information or the plan with the run-time information.
Does that make any sense to anyone?
The plans that Ken posted do not contain the run-time information (actual row count, actual executions, actual io cost, etc). I opened them and checked.
Yeah, perfect sense. It's just confirming what I went through. Man, I was confused for a bit because I was experimenting away and wondering why I was looking at the estimated plan, not realizing I was looking at the compiled plans...
There's a blog post. Do you get it or do I?
"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 13, 2009 at 8:41 am
Grant Fritchey (2/13/2009)
There's a blog post. Do you get it or do I?
Why not both? (Like we did with the table-valued functions)?
I'll write one over the weekend. Title's written, but I'm getting on a plane in about half an hour.
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
February 13, 2009 at 8:44 am
GilaMonster (2/13/2009)
Grant Fritchey (2/13/2009)
There's a blog post. Do you get it or do I?Why not both? (Like we did with the table-valued functions)?
I'll write one over the weekend. Title's written, but I'm getting on a plane in about half an hour.
Works for me. Have a safe trip.
"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 14, 2009 at 3:49 am
Ken, one other thing that came to mind yesterday.
When a query can get either a good plan or a bad depending where and when it's called, it can be due to parameter sniffing, rather than bad stats.
Before you try the optimise for (which only really helps if you know a typical set of parameters, try OPTION (RECOMPILE) on the query, and see if you're still getting bad plans.
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
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply