Unnecessary Index?

  • The question I have is this: Is there any reason to have INDEX_2 in the following situation?

    Table and Primary Key:

    CREATE TABLE [dbo].[Table_1](

    [Col_1] [int] IDENTITY(1,1) NOT NULL,

    [Col_2] [datetime] NOT NULL,

    [Col_3] [datetime] NOT NULL,

    [Col_4] [int] NOT NULL,

    CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

    (

    [Col_1] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    Index_2:

    CREATE NONCLUSTERED INDEX [Index_2] ON [dbo].[Table_1]

    (

    [Col_1] ASC,

    [Col_4] ASC

    )WITH (IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF) ON [PRIMARY]

    My assumption: Col_1 has to be unique, so Index_2 (with Col_1 as the first column in the index) has to be unique also, even though it is defined as non-unique. Any lookup using Index_2 could be found using PK_Table_1 also.

  • The classic answer: "It depends" 🙂

    INDEX_2 might be chosen by a query since it's more narrow than the original table. But since there are only two more columns (8 byte each) I would most probably not add this index since the cost of updating the index might not be worth the benefit.

    For a table with much more columns including large VARCHAR() columns the decision might differ if there are just a few inserts/updates and a lot of selects using only those two columns.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Agreed with Lutz. It depends. He gave good reasons. I would look at execution plans involving the table and indexes in question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Syed Jahanzaib Bin hassan (7/1/2011)


    Dont use Clustered index column in the NonClustered index this is overhead

    No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not.

    and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2

    How are these two indexes the same?

    Nonclustered on Col1, Col4

    Nonclustered on Col2

    ??

    If you meant Col4, the two indexes still would not be the same as the leading column would be different.

    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
  • Syed Jahanzaib Bin hassan (7/1/2011)


    Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2

    I don't agree with that. These are not the same. It depends on the query.

    And it looks like Gail has also posted while I was posting. She explains it quite well.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Syed Jahanzaib Bin hassan (7/1/2011)


    Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2

    That is what I was thinking, but I don't know enough of the internals to know if there is an advantage of the nonclustered index in this case. The execution plan shows that the nonclustered index is selected in the query I'm checking. If I disable the nonclustered index, then the clustered one is used. A profiler trace shows the same I/O and CPU in either case.

    Chris

  • GilaMonster (7/1/2011)


    Syed Jahanzaib Bin hassan (7/1/2011)


    Dont use Clustered index column in the NonClustered index this is overhead

    No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not.

    and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2

    How are these two indexes the same?

    Nonclustered on Col1, Col4

    Nonclustered on Col2

    ??

    If you meant Col4, the two indexes still would not be the same as the leading column would be different.

    Clustered is Col_1

    NonClustered is Clo_1, Col_4

  • In this particular case there are very few advantages to having that exact index, seeing as the table is so small. Swapping the columns around (col4, col1) however could be exceeding useful, depending on the queries that run against the table.

    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
  • GilaMonster (7/1/2011)


    In this particular case there are very few advantages to having that exact index, seeing as the table is so small. Swapping the columns around (col4, col1) however could be exceeding useful, depending on the queries that run against the table.

    Can you think of any performance DISADVANTAGES from dropping the nonclustered (Col_1, Col_4) index?

  • croberts 36762 (7/1/2011)


    GilaMonster (7/1/2011)


    In this particular case there are very few advantages to having that exact index, seeing as the table is so small. Swapping the columns around (col4, col1) however could be exceeding useful, depending on the queries that run against the table.

    Can you think of any performance DISADVANTAGES from dropping the nonclustered (Col_1, Col_4) index?

    Yes - if an index hint is in use - you may break code. You should evaluate code to determine if you should drop it first. Maybe somebody created that index due to it creating a better execution plan and improving performance. Not likely but possible. I would evaluate your queries involved in that table first before deciding to drop it. And of course, do that in a dev environment first.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • No. Not with a table this narrow. If I was doing indexing work, that one would be on my list to drop without serious concern.

    Just do a check (query sys.sql_modules) and make sure that the index name doesn't appear in any stored procs (as index hints)

    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
  • The main advantage I see to the non-clustered index in this case, is that it will have more rows per page than the clustered index (since it's narrower), and if it's got everything you need for a query (covering index), then you're using less I/O, and less RAM, because of less pages.

    This will give the same I/O stats, in terms of logical and physical reads, if I'm not mistaken, but it will take less bandwidth on your I/O channel, et al. So it would be more efficient in that case.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (7/1/2011)


    The main advantage I see to the non-clustered index in this case, is that it will have more rows per page than the clustered index (since it's narrower), and if it's got everything you need for a query (covering index), then you're using less I/O, and less RAM, because of less pages.

    If the table was wider I'd agree with you, but 4 byte row (+ header & other bits) vs 12 byte row (+ header & other bits), not likely to make that much difference (unless we're talking billions of rows)

    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
  • GilaMonster (7/1/2011)


    No. Not with a table this narrow. If I was doing indexing work, that one would be on my list to drop without serious concern.

    Just do a check (query sys.sql_modules) and make sure that the index name doesn't appear in any stored procs (as index hints)

    Assuming the table is truly this narrow and that this is not an overly simplified example. I would hesitate little as well. But as Gail and I said - still perform the due diligence.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 23 total)

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