Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 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
15732 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
1834 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  
 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.02 seconds. Powered By: Snitz Forums 2000