ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как временно попросить оптимизатор не использовать тот или иной индекс?
33 сообщений из 33, показаны все 2 страниц
  все  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607355
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Есть крупная 24/7 OLTP система. На проблемой таблице куча индексов.
При этом все индексы используются в разных хранимках (но нигде нет прибивания индекса хинтом INDEX).
Требуется посмотреть на то как поведет себя система если бы какого-то индекса "не было".

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

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

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

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Developer Edition (64-bit)
...
Рейтинг: 0 / 0
26.02.2018, 15:34
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607358
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Интуиция подсказывает что-то подшаманить в статистике, чтобы оптимизатор не захотел такой индекс использовать.
А после экспериментов обновить статистику и все бы "вернулось на круги своя".

В общем хотелось бы попросить совета у сообщества.
...
Рейтинг: 0 / 0
26.02.2018, 15:39
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607395
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Эксперементируйте - https://sqlsunday.com/2016/02/27/update-statistics-with-rowcount/
...
Рейтинг: 0 / 0
26.02.2018, 17:05
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607414
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
invm, такой вариант уже пробовал, но будет работать не всегда:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
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



У кого какие еще есть идеи?
...
Рейтинг: 0 / 0
26.02.2018, 17:33
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607419
KRS544
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KRS544
Участник
AlanDenton, дубль таблицы нельзя создать?
...
Рейтинг: 0 / 0
26.02.2018, 17:41
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607423
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
KRS544, можно конечно. Потом переименовать и подменить. Но штука в том что таблица весит под 1Тб (без индексов).
Плодить еще одну таблицу - печаль. И данные в ней могут апдейтится хоть за вчера, хоть за сегодня, хоть за год назад.
Долго потом синхронизировать. Как вариант триггер и тогда синхронизация упроститься. Но у клиента такие финты ушами не пройдут.

Взываю к адептам тайным трейс-флагов )))
...
Рейтинг: 0 / 0
26.02.2018, 17:49
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607426
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Идеальный вариант к которому стремлюсь. Как-то хитро отключить индекс, чтобы он продолжал жить и апдейтится.
Но не использовался никак оптимизатором. Если за некое время по метрикам не будет печали, то удалить его.
В противном случае - вернуть в строй.
...
Рейтинг: 0 / 0
26.02.2018, 17:52
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607439
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
AlanDenton,

Plan guide?
...
Рейтинг: 0 / 0
26.02.2018, 18:06
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607441
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
invm, хм. Вариант, но тут вопрос как мне план закрепить за 500 хранимок? Планов с участием этой таблицы - тьма.
...
Рейтинг: 0 / 0
26.02.2018, 18:09
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607442
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Сорри за орфографию. Реально буду благодарен помощи.
...
Рейтинг: 0 / 0
26.02.2018, 18:10
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607448
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
AlanDenton,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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 на все вызовы, и удаления его потом при откате, так что не вариант, наверное, просто направление "на подумать".
...
Рейтинг: 0 / 0
26.02.2018, 18:19
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607449
KRS544
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KRS544
Участник
AlanDenton,

ALTER INDEX .. DISABLE
Помечает индекс как отключенный и недоступный для использования компонентом Компонент Database Engine.
...
Рейтинг: 0 / 0
26.02.2018, 18:19
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607453
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
Комменты ваши не убрал, у меня там в плане везде [pk_view]
...
Рейтинг: 0 / 0
26.02.2018, 18:20
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607454
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
KRS544,

лол :)


AlanDenton
имхо нет такого... извращениями переместить индексы в отдельную группу и отключить... но моя не уверена в здравости :)
...
Рейтинг: 0 / 0
26.02.2018, 18:21
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607456
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
MinamotoКомменты ваши не убрал, у меня там в плане везде [pk_view]
материализовать терабайтную таблицу? ну ну
...
Рейтинг: 0 / 0
26.02.2018, 18:22
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607457
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
KRS544, выше написал что юзать DISABLE не вариант увы. Если мне нужно потом включить индекс - это будет ребилд.
...
Рейтинг: 0 / 0
26.02.2018, 18:22
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607458
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
TaPaK, ну да, туплю ))
...
Рейтинг: 0 / 0
26.02.2018, 18:23
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607459
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
TaPaK, вот так и живем )))

В общем я не теряю боевого настроя. Только начал исследовать и всех мучать. Может ребята с майкрософта что скажут :)
...
Рейтинг: 0 / 0
26.02.2018, 18:24
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607461
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
Кстати, способ таки есть, не добавляя хинты везде, но с понятными ограничениями в виде второй копии этой таблицы:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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
...
Рейтинг: 0 / 0
26.02.2018, 18:26
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607462
KRS544
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KRS544
Участник
AlanDentonKRS544, выше написал что юзать DISABLE не вариант увы. Если мне нужно потом включить индекс - это будет ребилд.
А ну да. Ну тут похоже индекс или жив или мертв.
...
Рейтинг: 0 / 0
26.02.2018, 18:27
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607465
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Minamoto,

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

ещё раз, затраты на материализацию таблицы будут даже больше чем ребилд индекса после DISABLE
...
Рейтинг: 0 / 0
26.02.2018, 18:31
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607466
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Minamoto, идея конечно хорошая. Не буду спорить. Но ix не должен юзаться в Вашем примере.
Фактически мы приходим к ситуации что делаем вью с таким именем как у таблицы и хардкодим там кластреный индекс.
Но в таком случае другие сценарии начинают нагибать сервер. Всего на таблице 4 NC индекса.
Проверить "на проф пригодность" нужно лишь один.

Сорри что детали по кусочкам так даю. Не подумал что будет важно сказу сказать.
...
Рейтинг: 0 / 0
26.02.2018, 18:32
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607467
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
имхо,
1. подобные задачи нужно решать на тестовом окружении где объём данных не терабайты
2. не вижу смысла изобретать хитро выдуманные велосипеды. если суть теста действительно нужна, убирайте индекс и пересоздавайте если нужно.
...
Рейтинг: 0 / 0
26.02.2018, 18:33
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607468
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Дедушка,

тут 100% согласен, на тестовом окружение пробуем потом переносим...
...
Рейтинг: 0 / 0
26.02.2018, 18:35
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607469
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Дедушка, если бы все было так просто - я бы народ честной не мучал своим идиотизмом :)
Но механизм как описал вначале хотел бы найти. Если он существует конечно.
...
Рейтинг: 0 / 0
26.02.2018, 18:35
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607471
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
AlanDenton,

Возможно, Вам нужно распилить таблицу для нормализации и вместо неё создать представление или набор представлений.
Изобилие разношерстных индексов как раз говорит о денормализованном хранении данных.
Соответственно, доработать процедуры.

Хорошим кодом, как известно, плохую архитектуру не исправить. Сиквел к этому особенно чувствителен.
...
Рейтинг: 0 / 0
26.02.2018, 18:35
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607472
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
TaPaKMinamoto,

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

ещё раз, затраты на материализацию таблицы будут даже больше чем ребилд индекса после DISABLE
Еще раз, я не предлагаю решение, я пытаюсь предложить варианты "на подумать".

Понятно, что накладные расходы не стоят того, скорее всего, особенно с учетом наличия еще доп. индексов, которые тоже необходимо будет продублировать. Да и увеличение затрат на запись никто не отменял.
...
Рейтинг: 0 / 0
26.02.2018, 18:37
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607477
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
Владислав Колосов, база недавно зашла. Формально нужно за неделю две порешать проблемы с размером.
Часть вещей вроде неиспользуемых таблиц / индексов - уже готова. А вот тут затык.
И нельзя симитировать на DEV нагрузку с PROD сервера.
Потому и такие вот попытки найти безболезненный способ.
...
Рейтинг: 0 / 0
26.02.2018, 18:40
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607482
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
AlanDenton,

а если просто изучить статистику использования индекса и по соотношению чтений/записей решить - нужен ли он или можно и без него обойтись? сервер же не от "нечего делать" эту статистику считает....
...
Рейтинг: 0 / 0
26.02.2018, 18:42
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607496
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
AlanDenton,

у меня не получилось найти другого решения, кроме как произвести нормализацию в такой ситуации. Со временем производительность деградирует и количество взаимоблокировок превышает терпение пользователей :)
Но это трудоёмкая задача.
...
Рейтинг: 0 / 0
26.02.2018, 19:01
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607543
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
AlanDenton,

В качестве идеи:
- сделать представление select * from Таблица with (index = 0)
- из dm_exec_query_stats, анализируя планы, определить процедуру и инструкцию в ней
- заменить в найденной процедуре таблицу на представление
...
Рейтинг: 0 / 0
26.02.2018, 21:31
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607581
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
Непонятен подход.
Если вы хотите это сделать на рабочей базе, то крайне не советую такие эксперименты.
Если не тестовой, то какие проблемы с перфомансом могут быть?
...
Рейтинг: 0 / 0
27.02.2018, 05:46
| Ответить | Цитировать | Написать  
Как временно попросить оптимизатор не использовать тот или иной индекс?
    #39607698
AlanDenton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlanDenton
Гость
SomewhereSomehowБыло бы полезно иметь такую функциональность, но в MSSQL ее нет.

Документированных хинтов, типа ораклового NO_INDEX() в сиквеле нет, как и недокументированных такого плана. Другие недокументированные возможности влияния на оптимизатор, типа флагов трассировки или правил преобразования, влияют на запрос в целом, но не на конкретный объект (индекс/таблица).

Информация о конкретном объекте хранится в метаданных этого объекта в системных таблицах. В тестовых условиях, ради интереса, можно было бы найти конкретные биты и поиграть с метаданными индекса, например, сделав его фильтрованным с таким фильтром из-за которого оптимизатор не сможет его использовать или гипотетическим или еще что-то. Но это ведет к модификации таблиц, т.е. переводу сервера в монопольный режим, что недопустимо в реальности, и главное к возможной и вероятной порче базы данных, что совсем не допустимо.

На мой взгляд, лучше использовать штатные средства и, либо отключать индекс, либо подменять объект.

МС в делает некоторые шаги к централизованному управлению подсказками оптимизатору, может быть эта задача и станет когда-то решаться нормально, но пока таких средств нет.
Тему можно закрывать.
Всем искренне признателен спасибо за помощь.
...
Рейтинг: 0 / 0
27.02.2018, 11:00
| Ответить | Цитировать | Написать  
33 сообщений из 33, показаны все 2 страниц
  все  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как временно попросить оптимизатор не использовать тот или иной индекс?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]