The query return different execution plan using this simple query
SELECT DLS FROM MFG_LotMoveData WHERE lot = 'K00747'. The query plan like below. There are 1 index with the name Lot but the index was unused. This will makes deadlock occured.
Sometimes the query plan return Index seek with key lookup. Which will not makes deadlock occured.
May I know why the execution plan are different from time to time even with same index created?
The plan depends on the specific lot value that caused the plan to be created.
For example, say lot = 'ABCDEF' is in 33% of the rows; lot = 'FEDCBA' is in 0.5% of the rows.
If a query with "WHERE lot = 'ABCDEF'" causes a plan to be created, SQL will do a table (clustered index) scan.
If a query with "WHERE lot = 'FEDBCA' causes a plan to be created, SQL will create a plan that does a lookup.
To avoid that issue, you can do this:
SELECT DLS FROM MFG_LotMoveData WHERE lot = 'xxxxxx' OPTION (RECOMPILE)
That will force SQL to create a plan that matches the current lot value.
Thank you for your reply. I got another scenario where I am having a complex query, server A will always return the best execution plan, however server B will return execution plan with several hash match. (Same query).
Both servers having different sets of data. If I use OPTION (RECOMPILE), server B will not get the exactly same plan as server A. If I update all statistics with STATS_STREAM to server B using statistic server A, the plan will become exactly same as server A.
May I know can we get the same plan with server A with other better method?
If statistics need updated, that is the best way to correct the query plan.
If running update statistic only (without stats_stream), will not solved the problem.
Same query running on different server, server A return result in 2 secs, server B return result in 1 mins+ , just because of different query plan.