April 27, 2013 at 4:36 pm
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!
April 28, 2013 at 4:19 am
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
April 28, 2013 at 6:58 am
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
April 28, 2013 at 7:00 am
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
April 28, 2013 at 10:35 am
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
April 28, 2013 at 11:09 am
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!
January 23, 2020 at 7:36 pm
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?
January 28, 2020 at 7:57 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 28, 2020 at 8:09 pm
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?
January 28, 2020 at 10:00 pm
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
February 3, 2020 at 11:25 pm
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.
February 4, 2020 at 5:46 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply