Exclude Specific Index From Query

  • This is driving me crazy! I want to exclude a specific index from a query but I can not remember how it's done, this may be an undocumented option as I can't seem to find any information about this feature. Does anyone know how to do this?

    Thanks!

  • Hi

    You can disable an index. Once you disable it, then you have to rebuild it in order to enable it.

    If your index is clustered, then after disabling it, your data will be read only and you must rebuild it for update operations.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Could you be a bit more specific about what you're trying to do and why?

    If you mean query execution, there's hints to force specific indexes but no hint to ignore certain indexes. Besides, in most cases the optimiser should be left to chose indexes based on its costing of the queries.

    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
  • IgorMi (4/28/2013)


    If your index is clustered, then after disabling it, your data will be read only and you must rebuild it for update operations.

    Disabling a clustered index prevents all access to the table, read or update, until the index is rebuilt

    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 (4/28/2013)

    Disabling a clustered index prevents all access to the table, read or update, until the index is rebuilt

    Correct. Just tried for clustered indexes. And, once you disable a clustered index, all nonclustered are also disabled because they use the clustered one.

    Thank You

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Thank you all so much for your replies. I wanted to remove a specific index from consideration when SQL Server is compiling it's query plan, kind of like an Option Index <>. This was a purely academic pursuit as I was certain we had done this in the past however, you are of course correct and this is not possible.

    Thanks!

  • This thread is old, but I'm trying to figure out if something like this is possible.

    I have a table that somewhat large (80M rows, 104GB) and has 31 indexes totaling 405GB! Many of these indexes were created over the years for tuning specific queries and in many cases are similar (ie: the first 2 or sometimes 3 keys are the same and some may include slightly different columns).

    I suspect I can probably remove and/or combine some of these indexes.

    I want to "pause" (disable) an index in a special way so that it is no longer used for queries yet is it kept up-to-date with any changes so that I can "un-pause" (re-enable) the index without a costly rebuild which would be impossible outside of a maintenance window.

    This would allow me to test index changes and see how the optimizer responds to the missing index (before I actually delete it).

    As far as I can see, there is no straight-forward way to do this. Any ideas?

  • Interesting q.  I've been looking into this, the best try I've found so far is to skew the row and page stats so high that SQL will almost certainly (?) ignore that index, something like this:

    UPDATE STATISTICS table_name  ( index_name_to_ignore )

    WITH ROWCOUNT = 10000000000, PAGECOUNT = 1000000000

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Can you run the tests on the indexes in a different environment, like UAT or system test?

    Can you paste in what indexes are on the table and which ones you intend to delete?

  • There are a lot of indexes, but an example would be:

    CREATE TABLE [dbo].[MyTable] (
    [TaskID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [Active] BIT NOT NULL DEFAULT(1),
    [TaskTypeID] INT NOT NULL,
    [UserID] UNIQUEIDENTIFIER NOT NULL,
    [LinkTo_UserID] UNIQUEIDENTIFER NULL,
    [DateTimeCompleted] DATETIME NULL,
    [DateCreated] DATETIME NOT NULL DEFAULT(GETDATE())
    )

    CREATE INDEX [IX_1] ON [dbo].[MyTable] (
    [Active],
    [TaskTypeID],
    [DateTimeCompleted],
    [DateCreated]
    )
    INCLUDE (
    [LinkTo_UserID]
    )

    CREATE INDEX [IX_2] ON [dbo].[MyTable] (
    [Active],
    [TaskTypeID],
    [DateTimeCompleted],
    [UserID]
    )

    In most use cases, Active & TaskTypeID are a constant in the predicate, although sometimes Active isn't specified. DateTimeCompleted can be a range, but it might also be a constant (NULL).

    My problem is: There are many reasons why the optimizer might select one index over the other, possibly as a key-lookup from a different index entirely, and I just don't know what effect removing or altering one will have. Perhaps something like this would suffice for both if, when a scan on UserID is needed, DateTimeCompleted is NULL:

    CREATE INDEX [IX_3] ON [dbo].[MyTable] ( 
    [Active],
    [TaskTypeID],
    [DateTimeCompleted],
    [UserID]
    )
    INCLUDE (
    [LinkTo_UserID],
    [DateCreated]
    )

    But, if DateTimeCompleted is NULL and UserID is not in the predicate, a scan on DateCreated will be a disaster.

    This is why I would like to be able to instruct the optimizer to ignore an index. Then I could experiment with combining indexes and "pausing" others without the need for a lengthy index rebuild.

    A testing environment is what I'll need to set up, but it's difficult to simulate use cases. It would be much simpler if MS provided a way to pause and un-pause indexes.

    @scottpletcher: I like your idea!  I'll see what kind of results I get on that in a test environment

  • I had great hopes for this idea of yours, @scottpletcher.

    Unfortunately, it doesn't seem to have affected index selection.  I tried increasing the values orders of magnitudes, to no avail.

  • Rats, I'd hoped it would work too.

    If it's keyed lookups, the size of the underlying table won't matter, SQL would still do the lookup, I understand that.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 12 posts - 1 through 11 (of 11 total)

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