SQL Server Performance

Index Usage

Discussion in 'Performance Tuning for DBAs' started by sunilthomas98, Sep 14, 2004.

  1. sunilthomas98 New Member


    I have a table with 1.3 million records and i have a clustered index and a additional index on it. I'm querying on the non-clustered field, but it is not using the index. The same set up on another machine does use the index. I'm pulling out my hair on this, cant get to the bottom of it. From Query analyser i could use the index hint to force the query to use the index. But the original query in a stored procedure which is wrapped around on some logic and it wouldnt be easy to use a hint there. Some more info on my non-clustered index.

    Field is varchar
    Explain plan tries to use Convert function. I think that is why it is not using the index. But why does it try to convert?

    Any help much appreciated.

  2. FrankKalis Moderator

    It would be helpful, if you post DDL, your query and maybe the execution plan.

  3. sundeip New Member

    Use Of Index is based on the query written. can u post ur query here ?
    That Would help us in analyzing your problem.
  4. sunilthomas98 New Member

    <br />Index:<br /><br />CREATE INDEX [IX_DRIVER_DETAILS_DRIVER_NO] ON [dbo].[DRIVER_DETAILS](DD_DRIVER_NO) ON [DD_INDEX]<br /><br />Query:<br /><br />SELECT * FROM DRIVER_DETAILS WHERE DD_DRIVER_NO = '010000011'<br /><br />Plan:<br /><br />|--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FCPCS].[dbo].[DRIVER_DETAILS]))<br /> |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FCPCS].[dbo].[DRIVER_DETAILS].[IX_DRIVER_DETAILS_DRIVER_NO]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />Convert([DRIVER_DETAILS].[DD_DRIVER_NO])=Convert([@1])))<br /><br />In my original post i said it doesnt use the index. Not true, it does, but does an index scan instead of index seek.<br /><br />thanks for the immediate response.<br /><br />Sunil<br />
  5. FrankKalis Moderator

  6. sundeip New Member

    Is there Only One Index On This Table? Do u have any other Index on the same column?


    (DB Developer)
  7. sunilthomas98 New Member


    I could try creating unique index. will post once done. But why this behaviour? I checked the statistics of the index. it shows high density. Does it mean anything?

    Nope, no other index on the same column.

  8. sunilthomas98 New Member

    Most weird thing. Despite of unique index it is doing the same thing. Same execution plan. Index scan after doing the convert!
  9. derrickleggett New Member

    There has to be more to that query than what you posted. It wouldn't be doing a bookmark lookup for that query. Is that then entire thing?


    When life gives you a lemon, fire the DBA.
  10. sunilthomas98 New Member

    Hi derrick,

    I got that plan by saying set showplan_text on. that is all it gave me back.

    I'm on Windows2003 SqlServer 2k. Any more ideas? I'm not very much on SQL server dba side. So, i might not have tried something which you might think i have already did!

  11. FrankKalis Moderator

    Just curious, what happens when you do:


  12. sunilthomas98 New Member

    Hi Frank,

    When i try this, it doesnt do the right hand side Convert, ie. doesnt do Convert(@1) from above plan, but does the convert on left side and does the index scan as before. I'm rebuilding the database with different scripts to see any difference. Meanwhile, is there any microsoft support site where i can directly get the help from Microsoft for issues like this? Or, should we be in a product support agreement and all that kind of stuff with microsoft to avail this facility?

  13. Adriaan New Member

    What data type is the DD_DRIVER_NO column?
  14. derrickleggett New Member

    You can contact PSS. It's $210 per incident. Here is the contact information:<br /><br /<a target="_blank" href=http://support.microsoft.com/common/international.aspx?rdpath=fh;en-us;offerprophone>http://support.microsoft.com/common/international.aspx?rdpath=fh;en-us;offerprophone</a><br /><br />You'll need to select a country. I'm not sure where you're from since it's not in your profile (hint, hint). <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  15. sunilthomas98 New Member

    thanks derrick. <br /><br />I'll update my profile <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  16. joechang New Member

    i am wondering if DD_DRIVER_NO is an nvarchar, hence the CONVERT is there because '010000011' is varchar,
    the following should not have a CONVERT, but i think it should also replace the index scan with a seek.
  17. derrickleggett New Member

    That's interesting. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Let us know the results on it.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  18. sunilthomas98 New Member

    Hi all,

    Finally we are there. The culprit was the database character set. There was a mix up between SQL_Latin_General_CP1_CI_AS and Latin_General_CP1_CI_AS. The table was created with one character set, index was using the default character set which is the other, so it had to convert when a query is raised and the index was therefore skipped. Not sure how the mix up happened. Suppose SQL_Latin.. should be the default collation?

  19. satya Moderator

    Thats a well investigated issue and glad to know the resolution.
    Yes the default will be SQL_LATIN_General.... while installing SQL server.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  20. Adriaan New Member

    Ah, the dreaded default collation! I've been hoping for some time now that the behaviour of SQL Server regarding assumed collations will be changed from server-based to database-based, but I guess that is just not going to happen.

    By the way, the SQL_Latin_General_CP1_CI_AS is the collation that SQL installs itself with when the Windows locale of the server is US English. If the locale is different, you get Latin_General_CP1_CI_AS. (This information is buried somewhere in BOL - can't seem to find it today.)
  21. skasam New Member

Share This Page