May 5, 2017 at 3:36 am
I have one query which is executing two days once, which is executing one day with 58 seconds next time same query executes 3:45 seconds , this is the exception it shows "historically records the plan that the query used to execute. In this case, SolarWinds DPA has detected a new plan. Review the plan, particularly if performance for this query has declined." , please let me know the what will be solution, also let me know what are the more details required for this to find the solution
May 5, 2017 at 3:56 am
There can be dosens of reasons why a plan will get invalidated.
e.g. changes to indexes (rebuilds), create/drop index, statistics update, ...
Compare the old and the new plan and see which path you consider the most optimal.
Keep in mind a query's execution parameters at compile time will influence the execution plan!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 5, 2017 at 4:00 am
we get this quite a lot but then its a big environment. Usually we can correct with an sp_recompile to get the plan rebuilt once we've analysed the issue. Often due to the data volume changes it will be down to statistics, or some developer doing some weird and wonderful thing with a related table.
May 5, 2017 at 5:45 am
These are the two query plans, please let me know how to solve this
May 5, 2017 at 6:42 am
These queries are pretty huge. Trying to unpack it all will take a lot of time. I'll focus on one thing. Both plans are timing out in the optimizer. Timeouts can lead to instability in the plans generated. You may just be seeing an instance where the optimizers choices are more limited on the number of iterations so that it arrives at a different plan for the same query, even with the same parameters, different times it gets run. This does happen when the optimizer doesn't complete optimization.
Looking through the plan, the main differences I'm seeing is when it chooses to retrieve the data and how it gets joined. Causes for this could be very deep in the details and I don't have the time to dig through something this complex. If you want to do it, get a copy of SQL Server 2017 SSMS. It has the "Compare Execution Plans" functionality that will highlight the similarities and differences between plans. Focus on the differences and the changes. That'll give you an idea what's up.
Suggested fixes?
Simplify the query. Update the stats. Beyond that, I'm not sure.
"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
May 5, 2017 at 6:43 am
Actually one other thing does come to mind. You could try using Plan Guides to force the good plan. On a query this complex and a plan this big... It might not work, but it's worth a shot of the simple fixes of stats updates & a recompile don't help.
"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
May 5, 2017 at 6:49 am
how to force a query to use particular query plan using plan guide, any sample relevant to this, please reply, thanks
May 5, 2017 at 7:05 am
You want to use the function sp_create_plan_guide. The basics are pretty simple. Copy your query, and it has to be a perfect copy, including all the white space (carriage returns, tabs, spaces) to use as the model. Copy the execution plan XML, and plug them into the function in the appropriate place. Then, recompile the plan in cache and check the first operator in the plan to see if the hint is being used. It's a property there. Here's a walk-through on this from Microsoft.
"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
May 5, 2017 at 7:05 am
there seem to be a couple more parallel operations in the 58 plan vs the 3:45plan
Do you also monitor blocking operations in your system ?
Do these queries run on cold or loaded cache?
I would investigate actual sqlplans ( the ones you posted are estimated sqlplans ).
Have there been a cleanup processes running on that system,
(Estimated number of rows differ)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 5, 2017 at 7:56 am
You don't have any parameters, do you? Just put NULL. Everything you need to know should be here.
John
May 5, 2017 at 11:04 am
There is any step by step process to do any query issue coming like this on query plan
May 5, 2017 at 11:47 am
GA_SQL - Friday, May 5, 2017 11:04 AMThere is any step by step process to do any query issue coming like this on query plan
That plan is absolutely enormous. There's not a "step by step" evaluation there. Truly, the best thing would be to find ways to simplify the query. Barring that, your options are pretty limited. Make sure you have statistics as up to date and as useful as possible (note, not accurate, sometimes sampled statistics are more useful than scanned statistics. The only way to know in your situation is testing). Beyond that, you have few options.
"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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply