October 23, 2014 at 8:13 am
I've got an issue with an dynamics AX application side cursor creating a poorly performing query plan. I can search the query cache and find the bad plan, in the plan there is a missing index hint, the suggested index already exists. If I clear the plan from the cache, the plan it recreates is the same as the previous poorly performing one.
I've viewed the plan in SQL Sentry Plan Explorer and used the cached parameter values to run the query from SSMS and the plan created uses the index which covers the query WHERE clause the same as the missing index suggestion from the bad query plan.
I don't want to go down the Plan Guide because I believe there must be a way to fix it properly. The strange thing is some days it creates the right plan and other it doesn't and no amount of trying on a bad day work. Update all Statistics on table with full scan, clearing plan cache.
My theories were it's doing a clustered index seek because the all the columns are including in the query results so it assumes a clustered index seek would be preferable to key lookup, if so why the missing index hint. Other theories stale statistics the estimated rows in the bad plan is c500,000 good plan 1.8, but updating statistics doesn't effect the plan created.
Is it something to do with how the cardinality is worked out? Dynamics AX starts all it indexes with a column dataareaid which is very unselective which I know is undesirable for an index as this is the column the stats are worked out on. This column denotes the company and in our situation is the same across all records. The index I want it to use I've added manually and starts with a very selective column hence the estimated rows being 1.8. The big thing that still bugs me though is why does it always create the plan I want from ssms, it's only the application that causes the problem.
Please help I'm reaching breaking point.
October 23, 2014 at 8:24 am
Is the suggested index based on more than one column? if so, you might have the columns in the wrong order. The suggested index does not necessarily have the columns in the correct order.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 23, 2014 at 8:28 am
It is based on more than one column and the suggested order is the same as the existing index, and they are the only columns filtered by in the query. The order of the columns is most selective to least. What would your suggestion be for the correct order?
October 23, 2014 at 9:58 am
I'd suggest reading this article by Erland Sommerskag, http://www.sommarskog.se/query-plan-mysteries.html. It explains how different SET OPTIONS are used by different providers and can affect query compilation. I'd be willing to bet this is partly why the optimizer chooses a better plan in SSMS.
Also running the query in SSMS using a constant or a variable is not the same as a query being run using parameters as parameter-sniffing is not occurring. You can try using sp_executesql in SSMS to run the query and pass the value as a parameter and not a variable and you may see different results, I know I have in the past.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2014 at 10:12 am
Looks interesting I'll give it read thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply