September 8, 2014 at 5:07 pm
I have an application which one of the query (within stored procedure) execution plan keeps changing. I am running rebuild index and update stats on regular basis on this database. This export batch runs 2hrs with good plan and 14hrs with bad plan. Even when I check estimated execution plan it keeps changing without any changes.
I understand since this is on SQL 2008, I can use query hint or parameter sniffing but why would my query plans keep changing and why do I force to use query hint or parameter sniffing? I would rather rely on sql engine to use statistics and build efficient execution plan rather than using query hint or sniffing as it may not be better after data growth. Any idea why I see this behavior?
Any input greatly appreciated.
September 8, 2014 at 11:03 pm
Amu (9/8/2014)
I have an application which one of the query (within stored procedure) execution plan keeps changing. I am running rebuild index and update stats on regular basis on this database. This export batch runs 2hrs with good plan and 14hrs with bad plan. Even when I check estimated execution plan it keeps changing without any changes.I understand since this is on SQL 2008, I can use query hint or parameter sniffing but why would my query plans keep changing and why do I force to use query hint or parameter sniffing? I would rather rely on sql engine to use statistics and build efficient execution plan rather than using query hint or sniffing as it may not be better after data growth. Any idea why I see this behavior?
Any input greatly appreciated.
Quick question, can you share the DDL including indexes and constraints and both good and bad actual execution plans?
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply