Post reply

Index question

  • December 7, 2017 at 3:30 am

    #334039

    Hi All,

    Need to identify repeated/duplicate indexes.

    Sample table :

    create table test_tbl
    (id int not null primary key,
    userid int,
    deptid int,
    isFullAccess int
    )
     
    CREATE INDEX test_tbl_Index1
    ON test_tbl(userid);
    GO

    CREATE INDEX test_tbl_Index2
    ON test_tbl(userid) include(deptid);
    GO

    CREATE UNIQUE INDEX test_tbl_Index3
    ON test_tbl(userid, deptid);
    GO

    How can I identify the duplicate indexes. From the above structure, I feel I can drop index1 and retain Index2 so that it can satistfy index1 queries and index2 queries.

    Question1) How can I identify all such indexes ?
    Question2) What is the difference between Index2 & Index3 in terms of usage. Can give me some use cases between Index2 and Index3 in simple SELECT statements?

    I did came across below useful link but it is not serving my purpose. Does any has an alternative way of identify similar indexes so that I can retain 1 index and drop the remaining which is eating up disk space and can slow down the DML operations.

    Kimberly, is doing exact matches based on below conditions like columns in tree and columns at leaf has exact match.
    Something like this...
    INNER JOIN #FindDupes AS t2
       ON t1.columns_in_tree = t2.columns_in_tree
        AND t1.columns_in_leaf = t2.columns_in_leaf
        AND ISNULL(t1.filter_definition, 1) = ISNULL(t2.filter_definition, 1)
        AND PATINDEX('%unique%', t1.index_description) = PATINDEX('%unique%', t2.index_description)
        AND t1.index_id > t2.index_id

    Thanks,

    Sam

  • December 7, 2017 at 7:42 am

    #1971171

    For those indexes, you could drop both index1 and index2.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • December 7, 2017 at 11:00 am

    #1971228

    Why not retain index 2 and drop index1 and index3?

  • December 7, 2017 at 11:02 am

    #1971230

    Try this: https://www.sqlskills.com/blogs/kimberly/how-can-you-tell-if-an-index-is-really-a-duplicate/

  • December 7, 2017 at 11:23 am

    #1971241

    Hi Steve, I have gone through this article but my case is not considered. That's what I have said it in my inital post with that WHERE condition.

  • December 7, 2017 at 11:58 am

    #1971258

    vsamantha35 - Thursday, December 7, 2017 11:00 AM

    Why not retain index 2 and drop index1 and index3?

    Because Index3 is a UNIQUE index and will sometimes out-perform non-unique indexes AND it's enforcing uniqueness either as an alternate-key for the table or possibly involved with a Foreign Key or other DRI.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 7, 2017 at 12:44 pm

    #1971265

    Jeff Moden - Thursday, December 7, 2017 11:58 AM

    vsamantha35 - Thursday, December 7, 2017 11:00 AM

    Why not retain index 2 and drop index1 and index3?

    Because Index3 is a UNIQUE index and will sometimes out-perform non-unique indexes AND it's enforcing uniqueness either as an alternate-key for the table or possibly involved with a Foreign Key or other DRI.

    And in the end the actual answer is "It depends."  You are asking a question with trivial table and little context regarding what you are really trying to determine.  There are really no set rules when it comes to indexing tables and which should be kept and which shouldn't be.  You really need to understand the data in the tables and the relationship of that data to data in other tables as well as the access paths used to query and/or update the data.  Of the indexes on a table which ones are being used and how.

  • December 7, 2017 at 3:12 pm

    #1971285

    Lynn Pettis - Thursday, December 7, 2017 12:44 PM

    Jeff Moden - Thursday, December 7, 2017 11:58 AM

    vsamantha35 - Thursday, December 7, 2017 11:00 AM

    Why not retain index 2 and drop index1 and index3?

    Because Index3 is a UNIQUE index and will sometimes out-perform non-unique indexes AND it's enforcing uniqueness either as an alternate-key for the table or possibly involved with a Foreign Key or other DRI.

    And in the end the actual answer is "It depends."  You are asking a question with trivial table and little context regarding what you are really trying to determine.  There are really no set rules when it comes to indexing tables and which should be kept and which shouldn't be.  You really need to understand the data in the tables and the relationship of that data to data in other tables as well as the access paths used to query and/or update the data.  Of the indexes on a table which ones are being used and how.

    For me, there are "rules" when it comes to indexing and removal of duplicate indexes.

    1.  If you have a UNIQUE index and two functionally "identical" non-unique non-clustered indexes, always keep the UNIQUE non-clustered index (possibly adding extra columns either in the KEYs or the INCLUDE) because it does have some advantages performance-wise.
    2.  If you have a non-clustered index that is keyed the same as the clustered index (UNIQUE or not), never arbitrarily delete the non-clustered index.  Someone put it there for performance reasons to overcome the logical read problems associated with clustered indexes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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