Post reply

Some Questions about Index Tuning

  • February 22, 2009 at 1:24 am

    #131147

    Hi,

    1. our server needs to be index tuned but it's too slow to run profiler. so I've queried DMVs a few times a few days and got different results about missing indexes (each lists different result for missing indexes) I now doubt which one to apply?

    2. If we define a PK on a table and don't create any clustered index on it, an index is automatically created on PK, as u know. However, it's better to have, for example, index on (last name, first name) instead of employee_ID (which is PK). I'd like to know if I should delete the index on employee_ID and then create index on (last name, first name) or let the two index remain on the table.

    3. As I know, it's better to create index on columns that comes after Groupby and order by. If we create index on such columns, does it still use tempdb for sorting? or the index provides the required space for that?

  • February 22, 2009 at 1:35 am

    #948251

    peace2007 (2/22/2009)

    1. our server needs to be index tuned but it's too slow to run profiler.

    Use a server-side trace. Much lighter weight than profiler

    so I've queried DMVs a few times a few days and got different results about missing indexes (each lists different result for missing indexes) I now doubt which one to apply?

    You shouldn't take missing indexes at face value. It's a suggestion. Test out the suggestions, see which ones work better

    I'd like to know if I should delete the index on employee_ID and then create index on (last name, first name) or let the two index remain on the table.

    You can't delete the index on a primary key without dropping the primary key.

    As for indexes on a table, put the ones you need on, and no more.

    3. As I know, it's better to create index on columns that comes after Groupby and order by.

    Depends. It's often hard to do that in a way that has the index usable. If the query doesn't use the index, there's no point in having it

    If we create index on such columns, does it still use tempdb for sorting? or the index provides the required space for that?

    Depends on the query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 22, 2009 at 1:41 am

    #948253

    Thanks Gail 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply