SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Execution Plan: Suggested Index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 02/12/2013 :  17:05:53  Show Profile  Reply with Quote
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

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 02/12/2013 :  17:25:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
There's a known bug that could be the cause:

http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/

Advice is: never just implement a recommended/missing index without testing.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 02/12/2013 :  17:59:16  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000