As a SQL Server DBA it is important to understand why and how the SQL engine works. Understanding as much as you can with the engine can help you answer some questions from application developers, like why did my execution plan change suddenly?
Execution plans change as the optimizer see’s fit. There are several reasons why the optimizer will create a new plan and some of those are laid out below.
- Data change – obviously data change will cause the optimizer to possibly change plans. If data increase or decrease meets the engines threshold hold for automatic stat updates then most the time this is not a problem.
- Out of date stats – Stats are key to making sure the optimizer is creating plans best fit for the stored procedure. If stats are outdated that means a plan could get built incorrectly.
- Out of shape indexes – I have seen it the past where an execution plan was using index “a” then 24 hours later not using the same index and using the clustered primary key index. This happened because the optimizer seen that it could get data more quickly out of the clustered index compared to index “a”. If you have a high volume, high transaction application I would suggest maintaining index fragmentation with an index maintenance plan.
- Bad query code – It is possible there could be some bad code in the query like a bad join. Be sure to review the code with the developer and optimize it.
A tool I commonly use called Foglight can help you track when query execution plans change, however this tool cannot predict when a plan is getting ready to change.
Understanding why the optimizer changes plan can help you identify an area to troubleshoot and improve performance.
The post Why did my execution plan change? appeared first on VitaminDBA.
