Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
 Posted - 2013-02-12 : 17:05:53
|
| 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:[CODE]CheckPoint;DBCC dropcleanbuffers;DBCC FreeProcCache;[/CODE]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 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-02-12 : 17:59:16
|
| Major "Thank-you" for the link. I'm already red-lining on my Sanity Exhaustion Meter so this keeps me off the ledge another day. In re: "never just implement a recommended/missing index without testing" - Amen.=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
 |
|
|
|
|
|
Execution Plan: Suggested Index