I generated an execution plan for a query that I'm hoping to optimize. It suggested a missing index to create so, for grins, I created the index from the script it generated changing only the name. After that I re-generated the execution plan. It suggested that I create the same index. I thought that was odd. More correctly I thought that I must have made a mistake by creating the index on a different server, different database or something along those lines. I verified that I had the right index created on the right table. I even went so far as to "REORGANIZE" the index in the same script as the one I want to optimize just to insure that the objects were in the same location.
"Okay", I thought. So the optimizer is not seeing/using the new index. I'll flush the system using this scriptlet:CheckPoint;
DBCC dropcleanbuffers;
DBCC FreeProcCache;
No luck.
Okay, I'll restart the SQL Service. No luck.
Okay, I'll use the Actual Execution Plan and not the Estimated. No luck.
Okay, I'll re-build the index using the exact script generated by the execution plan. Don't change the name, just run it as is. No luck.
Any thoughts or suggestions?
=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Execution Plan: Suggested Index
Posted - 02/12/2013 : 17:05:53
