Exclude Specific Index From Query

  • richardaweiss

    Old Hand

    Points: 326

    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!

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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
  • Gail Shaw

    SSC Guru

    Points: 1004474

    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
  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • richardaweiss

    Old Hand

    Points: 326

    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!

  • dave-L

    SSCommitted

    Points: 1515

    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?

  • ScottPletcher

    SSC Guru

    Points: 98334

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jonathan AC Roberts

    SSCoach

    Points: 17161

    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?

  • dave-L

    SSCommitted

    Points: 1515

    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

  • dave-L

    SSCommitted

    Points: 1515

    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.

    • This reply was modified 2 weeks, 5 days ago by  dave-L.
  • ScottPletcher

    SSC Guru

    Points: 98334

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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