1

Topic: How temporarily to ask the optimizer not to use this or that index?

There is large 24/7 OLTP a system. On a problem to the table a heap of indexes.
Thus all indexes are used in different  (but anywhere is not present  an index  INDEX).
It is required to look at that as the system leads if any index "was not".
Thus to delete an index it is impossible. To disconnect an index too it is impossible that is equivalent DROP but with saving of meta data about .
It is actually necessary globally to forbid for the optimizer to use a certain index.
The reason of such perversions that index re-creation extremely strongly strikes on performance.
I appeal to great experts.
Who helps helps or at least on clever thought pushes?
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) A Developer Edition (64-bit)

2

Re: How temporarily to ask the optimizer not to use this or that index?

The intuition prompts something  in statistican that the optimizer did not want to use such index.
And after experiments to update statistics and all "would return into place".
In general it would be desirable to ask for suggestions community.

3

Re: How temporarily to ask the optimizer not to use this or that index?

- https://sqlsunday.com/2016/02/27/update … -rowcount/

4

Re: How temporarily to ask the optimizer not to use this or that index?

invm, such variant already tried, but will work not always:

USE [master]
GO
IF OBJECT_ID (' dbo.test1 ') IS NOT NULL
DROP TABLE dbo.test1
GO
CREATE TABLE dbo.test1 (an INT IDENTITY, b INT, INDEX pk CLUSTERED (a))
GO
INSERT INTO dbo.test1
SELECT number
FROM [master].dbo.spt_values
GO
ALTER INDEX ALL ON dbo.test1 REBUILD
GO
CREATE INDEX ix ON dbo.test1 (b)
GO
UPDATE STATISTICS dbo.test1 ix WITH ROWCOUNT = 1000000, PAGECOUNT = 10000000
GO
SELECT b
FROM dbo.test1 - pk
SELECT b
FROM dbo.test1 - ix - how here to be?
WHERE b = 0
GO
DBCC UPDATEUSAGE (' master ', ' dbo.test1 ', ix) WITH COUNT_ROWS
GO
SELECT b
FROM dbo.test1 - ix
SELECT b
FROM dbo.test1 - ix
WHERE b = 0

Who what else has ideas?

5

Re: How temporarily to ask the optimizer not to use this or that index?

AlanDenton, the table double cannot be created?

6

Re: How temporarily to ask the optimizer not to use this or that index?

KRS544, it is possible certainly. Then to rename and change. But the piece in that that the table weighs under 1 (without indexes).
To produce one more table - grief. And the data in it can  though for yesterday, though for today, though for one year ago.
Long then to synchronize. As a variant the trigger and then synchronization to become simpler. But at the client such feints ears do not transit.
I appeal to adherents secret trejs-flags)))

7

Re: How temporarily to ask the optimizer not to use this or that index?

Ideal variant to which I aspire. Somehow artfully to disconnect an index that he continued to live and .
But it was not used in any way by the optimizer. If for certain time under metrics there will be no grief to delete it.
Otherwise - to return in a system.

8

Re: How temporarily to ask the optimizer not to use this or that index?

AlanDenton;
Plan guide?

9

Re: How temporarily to ask the optimizer not to use this or that index?

invm, . The variant, but here a question as to me the plan to assign 500 ? Plans with involvement of this table - darkness.

10

Re: How temporarily to ask the optimizer not to use this or that index?

for spelling. I will be really grateful to the help.

11

Re: How temporarily to ask the optimizer not to use this or that index?

AlanDenton;

sp_rename ' test1 ', ' test1111 ', ' OBJECT'
go
create view dbo.test1
with schemabinding
as
select a, b from dbo.test1111 as t
go
CREATE UNIQUE CLUSTERED INDEX pk_view
ON dbo.test1 (a);
go
SELECT b
FROM dbo.test1 WITH (NOEXPAND) - ix
SELECT b
FROM dbo.test1 WITH (NOEXPAND) - ix
WHERE b = 0

But it demands adding  with NOEXPAND on all calls, and its removals then at rollback so not a variant the direction ", probably, is simple on to think".

12

Re: How temporarily to ask the optimizer not to use this or that index?

AlanDenton;
ALTER INDEX. DISABLE
Marks an index as disconnected and inaccessible to usage by a component of Components Database Engine.

13

Re: How temporarily to ask the optimizer not to use this or that index?

Your comments did not remove, at me there in the plan everywhere [pk_view]

14

Re: How temporarily to ask the optimizer not to use this or that index?

KRS544;
smile
AlanDenton
there is no such... Perversions to move indexes to separate group and to disconnect... But washing it is not assured of reasonableness smile

15

Re: How temporarily to ask the optimizer not to use this or that index?

Minamoto wrote:

your Comments did not remove, at me there in the plan everywhere [pk_view]

To materialize  the table? Well well

16

Re: How temporarily to ask the optimizer not to use this or that index?

KRS544, above wrote that  DISABLE not a variant alas. If I need to include then an index is will be .

17

Re: How temporarily to ask the optimizer not to use this or that index?

TaPaK, well, ))

18

Re: How temporarily to ask the optimizer not to use this or that index?

TaPaK, here and we live)))
In general I do not lose a fighting spirit. Only started to research and all to torture. Can children with  that tell smile

19

Re: How temporarily to ask the optimizer not to use this or that index?

By the way, the method  is, without adding  everywhere, but with clear restrictions in the form of the second copy of this table:

sp_rename ' test1 ', ' test3 ', ' OBJECT'
go
create view dbo.test2
with schemabinding
as
select a, b from dbo.test3 as t
go
CREATE UNIQUE CLUSTERED INDEX pk_view
ON dbo.test2 (a);
go
create view dbo.test1
as
select a, b from dbo.test2 WITH (NOEXPAND)
go
SELECT b
FROM dbo.test1 - ix
SELECT b
FROM dbo.test1 - ix
WHERE b = 0

20

Re: How temporarily to ask the optimizer not to use this or that index?

AlanDenton wrote:

KRS544, above wrote that  DISABLE not a variant alas. If I need to include then an index is will be .

And well. Well here similar index or is live or dead.

21

Re: How temporarily to ask the optimizer not to use this or that index?

Minamoto;

wrote:

By the way, the method  is, without adding  everywhere, but with clear restrictions in the form of the second copy of this table:

Once again, expenses on  tables will be even more than  an index after DISABLE

22

Re: How temporarily to ask the optimizer not to use this or that index?

Minamoto, idea certainly the good. I will not argue. But ix should not  in your example.
Actually we come to a situation that we do I twist with such name as at the table and  there  an index.
But in that case other scenarios start to bend the server. In total on table 4 NC of an index.
To check up "on  suitability" it is necessary only one.
that details on slices so I give. Did not think that will be important  to tell.

23

Re: How temporarily to ask the optimizer not to use this or that index?

,
1. Similar tasks need to be solved on a test surrounding where data volume not terabytes
2. I do not see sense to invent artfully invented bicycles. If the test essence is really necessary, remove an index and recreate if it is necessary.

24

Re: How temporarily to ask the optimizer not to use this or that index?

The grandfather;
Here 100 % it agree, on test a surrounding it is tried then it is transferable...

25

Re: How temporarily to ask the optimizer not to use this or that index?

The grandfather if all was so simply - I fair would not torture the people the idiocy smile
But the mechanism as described in the beginning would like to find. If it exists certainly.