July 22, 2018 at 8:37 pm
Morning,
I have an issue where a recurring query occasionally uses a bad query plan and can take hours to run instead of seconds.
We can't alter the code from the app (Dynamics AX - Management Reporter) to add query hints - so instead I set up a process to:
1) check if the process had been running for > 15 mins
2) remove the plan from the plan cache (using sql_handle3)
3) kill the process
The process uses change tracking so killing it doesn't cause any loss of data. The problem is that on the next run, sometimes SQL decides to use an equally bad plan and again takes ages to run.
I can't then remove the plan using sql_handle as there isn't one until the query completes successfully once. So I was just wondering:
Is it better to remove a plan using plan_handle or SQL_handle? I understand using sql_handle is going to remove all the the plans using the same syntax. Should this not force a recompile?
If the slow query is related to parameter sniffing, why wouldn't a recompile lead to a better plan?
Some additional info:
We've added all the useful indexes we can to speed up this query.
Statistics are updated weekly.
One of the key tables in the process is temp table.
SQL version is sql2012 enterprise (11.05548).
Appreciate any help or suggestions on this,
Peter
August 8, 2018 at 9:15 pm
A quick update on this:
We have a query that's using a bad query plan.
Have tried setting up a process to remove the plan from the plan cache to force a recompile.
Have now found that the long running queries only occurs when a certain number of records are involved.
1 - 1000 records : query runs quickly
1000 - 5000: query can take hours to run
5000+ : SQL switches to a better plan and runs in seconds.
Is there any way to force sql to switch to using a better plan for fewer records?
The number of records is determined by how many records are inserted into a temp table and statistics are created on the temp table as part of the process.
Thanks,
Peter
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply