An interesting NonClustered Index Issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

An interesting NonClustered Index Issue

I have a scenario as follows: <br /><br />I have SP2(Stored Proc 2) to execute followed by SP1. I want to use one Non-Clustered index and drop the same there in SP2 only. To make the NonClustered Index useful I am using "Query Hint" in this case in SP2. The issue is where should I create this Non-Clustered Index? <br /><br />One of my colleagues told me not to create the index in SP2. He told me that this is the limitation of Yukon that it won#%92t be able to recognize the index if this is created in the same SP. Is he correct? [<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />]<br /><br /><br /><br />Ashish Johri
Yukon, is your colleague using BETA version? Why don;t you check the execution plan for both the SPs? I feel creating at second SP should be better. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hi Satya, Many execution plan also gives a wrong result. This shows that the index will be used but this doesn’t use the index in the real time execution. M I Correct? Ashish Johri
Usually SQL Server does a great job on picking the "best" execution plan under the given circumstances at that time. While you can easily force the use of an index with an index hint, you should carefully evaluate this decision before it is applied and, probably more important, you should review this decision from time to time if it is still appropriate and still the "best" choice. The reasons why SQL Server doesn’t seem to be using the "correct" index might be missing statistics which can lead the optimizer to wrong decision because of missing essential informations. Maybe you could post your SPs so we can see the actual code? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx http://sqlserver-qa.net/blogs/perftune/default.aspx – blogs on how you can achieve performance with available features in SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>