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:
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