Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Есть крупная 24/7 OLTP система. На проблемой таблице куча индексов.
При этом все индексы используются в разных хранимках (но нигде нет прибивания индекса хинтом INDEX).
Требуется посмотреть на то как поведет себя система если бы какого-то индекса "не было".

При этом удалять индекс нельзя. Отключить индекс тоже нельзя, что равносильно DROP но с сохранением метаданных об обьекте.
Фактически нужно глобально запретить оптимизатору использовать определенный индекс.

Причина таких извращений в том, что пересоздание индекса крайне сильно ударит по перфомансу.

Взываю к великим мастерам своего дела.
Кто поможет поможет или хотя бы на умную мысль натолкнет?

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Developer Edition (64-bit)
26 фев 18, 15:34    [21219254]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Интуиция подсказывает что-то подшаманить в статистике, чтобы оптимизатор не захотел такой индекс использовать.
А после экспериментов обновить статистику и все бы "вернулось на круги своя".

В общем хотелось бы попросить совета у сообщества.
26 фев 18, 15:39    [21219291]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
Эксперементируйте - https://sqlsunday.com/2016/02/27/update-statistics-with-rowcount/
26 фев 18, 17:05    [21219580]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
invm, такой вариант уже пробовал, но будет работать не всегда:

USE [master]
GO

IF OBJECT_ID('dbo.test1') IS NOT NULL
    DROP TABLE dbo.test1
GO

CREATE TABLE dbo.test1 (a 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 -- как тут быть?
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


У кого какие еще есть идеи?
26 фев 18, 17:33    [21219669]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
KRS544
Member

Откуда:
Сообщений: 497
AlanDenton, дубль таблицы нельзя создать?
26 фев 18, 17:41    [21219690]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
KRS544, можно конечно. Потом переименовать и подменить. Но штука в том что таблица весит под 1Тб (без индексов).
Плодить еще одну таблицу - печаль. И данные в ней могут апдейтится хоть за вчера, хоть за сегодня, хоть за год назад.
Долго потом синхронизировать. Как вариант триггер и тогда синхронизация упроститься. Но у клиента такие финты ушами не пройдут.

Взываю к адептам тайным трейс-флагов )))
26 фев 18, 17:49    [21219717]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Идеальный вариант к которому стремлюсь. Как-то хитро отключить индекс, чтобы он продолжал жить и апдейтится.
Но не использовался никак оптимизатором. Если за некое время по метрикам не будет печали, то удалить его.
В противном случае - вернуть в строй.
26 фев 18, 17:52    [21219731]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
AlanDenton,

Plan guide?
26 фев 18, 18:06    [21219775]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
invm, хм. Вариант, но тут вопрос как мне план закрепить за 500 хранимок? Планов с участием этой таблицы - тьма.
26 фев 18, 18:09    [21219782]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Сорри за орфографию. Реально буду благодарен помощи.
26 фев 18, 18:10    [21219784]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
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


Но это требует добавления хинта с NOEXPAND на все вызовы, и удаления его потом при откате, так что не вариант, наверное, просто направление "на подумать".
26 фев 18, 18:19    [21219800]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
KRS544
Member

Откуда:
Сообщений: 497
AlanDenton,

ALTER INDEX .. DISABLE
Помечает индекс как отключенный и недоступный для использования компонентом Компонент Database Engine.
26 фев 18, 18:19    [21219801]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Комменты ваши не убрал, у меня там в плане везде [pk_view]
26 фев 18, 18:20    [21219805]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
KRS544,

лол :)


AlanDenton
имхо нет такого... извращениями переместить индексы в отдельную группу и отключить... но моя не уверена в здравости :)
26 фев 18, 18:21    [21219807]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Minamoto
Комменты ваши не убрал, у меня там в плане везде [pk_view]

материализовать терабайтную таблицу? ну ну
26 фев 18, 18:22    [21219810]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
KRS544, выше написал что юзать DISABLE не вариант увы. Если мне нужно потом включить индекс - это будет ребилд.
26 фев 18, 18:22    [21219811]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
TaPaK, ну да, туплю ))
26 фев 18, 18:23    [21219812]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
TaPaK, вот так и живем )))

В общем я не теряю боевого настроя. Только начал исследовать и всех мучать. Может ребята с майкрософта что скажут :)
26 фев 18, 18:24    [21219815]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Кстати, способ таки есть, не добавляя хинты везде, но с понятными ограничениями в виде второй копии этой таблицы:

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
26 фев 18, 18:26    [21219821]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
KRS544
Member

Откуда:
Сообщений: 497
AlanDenton
KRS544, выше написал что юзать DISABLE не вариант увы. Если мне нужно потом включить индекс - это будет ребилд.

А ну да. Ну тут похоже индекс или жив или мертв.
26 фев 18, 18:27    [21219823]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Minamoto,

автор
Кстати, способ таки есть, не добавляя хинты везде, но с понятными ограничениями в виде второй копии этой таблицы:

ещё раз, затраты на материализацию таблицы будут даже больше чем ребилд индекса после DISABLE
26 фев 18, 18:31    [21219832]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Minamoto, идея конечно хорошая. Не буду спорить. Но ix не должен юзаться в Вашем примере.
Фактически мы приходим к ситуации что делаем вью с таким именем как у таблицы и хардкодим там кластреный индекс.
Но в таком случае другие сценарии начинают нагибать сервер. Всего на таблице 4 NC индекса.
Проверить "на проф пригодность" нужно лишь один.

Сорри что детали по кусочкам так даю. Не подумал что будет важно сказу сказать.
26 фев 18, 18:32    [21219837]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
имхо,
1. подобные задачи нужно решать на тестовом окружении где объём данных не терабайты
2. не вижу смысла изобретать хитро выдуманные велосипеды. если суть теста действительно нужна, убирайте индекс и пересоздавайте если нужно.
26 фев 18, 18:33    [21219839]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Дедушка,

тут 100% согласен, на тестовом окружение пробуем потом переносим...
26 фев 18, 18:35    [21219843]     Ответить | Цитировать Сообщить модератору
 Re: Как временно попросить оптимизатор не использовать тот или иной индекс?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Дедушка, если бы все было так просто - я бы народ честной не мучал своим идиотизмом :)
Но механизм как описал вначале хотел бы найти. Если он существует конечно.
26 фев 18, 18:35    [21219846]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить