Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ms sql перестает использовать нужный индекс  [new]
andrew_1812
Member

Откуда:
Сообщений: 4
Постоянно сталкиваюсь с ситуацией – при увеличение кол-ва записей в таблице оптимизатор перестает использовать оптимальный индекс и начинает сваливаться на сканирование таблицы. Пока в таблице несколько млн. записей – все ок, таблица разрастается хотя бы до 20-30 млн – сплошное сканирование. Пример – таблица звонков за пару лет, 50 млн. записей
CREATE TABLE [dbo].[Calls](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TimeRingBegin] [datetime] NOT NULL,
	[TimeRingEnd] [datetime] NOT NULL,
	[RealTime] [int] NOT NULL,
	[TimeRoundOff] [int] NOT NULL,
	[Rate] [money] NOT NULL,
	[Cost] [money] NOT NULL,
	[Service] [int] NULL,
	[LineNumber] [int] NULL,
	[DestPoint] [int] NULL,
	[Client] [int] NULL
	[ClientType] [smallint] NULL,
	[Direction] [smallint] NULL,
	[Result] [smallint] NULL,
 CONSTRAINT [PK_Calls] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
) 
CREATE NONCLUSTERED INDEX [IX_Calls_TimeRingBegin] ON [dbo].[Calls] 
(
	[TimeRingBegin] ASC
)

В какой-то момент любой небольшой запрос вида
select DATEPART(day, TimeRingBegin), DATEPART(hour, TimeRingBegin), COUNT(*), SUM(RealTime)
from Calls
where TimeRingBegin between DATE_FROM and DATE_TO
group by DATEPART(day, TimeRingBegin), DATEPART(hour, TimeRingBegin)
для периода в несколько дней начинает сканировать таблицу и работает несколько минут. При явном указании индекса – всё отрабатывает практически мгновенно. Дефрагментация/пересоздание индекса никак не влияет.
Это происходит на MS SQL 2005/2008/2008 R2.
Можно ли что-то сделать для направления оптимизатора в нужное русло, кроме хинтов index = или создания всё и вся покрывающего индекса?

Сообщение было отредактировано: 13 ноя 12, 02:19
13 ноя 12, 01:23    [13462328]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Forceseek ftw. Покрывающие большую часть выборок индексы (желательно, инклудами) - тоже, хотя зависит от интенсивности модификаций.

Сообщение было отредактировано: 13 ноя 12, 02:21
13 ноя 12, 02:21    [13462379]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
andrew_1812
Member

Откуда:
Сообщений: 4
всё покрывающий индекс - да, помогает, но - индексов несколько, каждый из них приходится делать "обильно" покрывающим - объем увеличивается чрезвычайно быстро
forceseek - да, помогает, но тоже есть свой нюанс - запросы могут писать разные люди, не всегда имеющие представление о хинтах. Помогает работа через вьюшку с указанным хинтом, но там тоже возникают свои нюансы. Поэтому хотелось бы как-то решить проблему не на уровне хинтов в запросе. а, в идеале, на уровне настроек сервера/базы.
ок, в любом случае спасибо за ответ
13 ноя 12, 03:21    [13462405]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
max44
Member

Откуда: МОСКВА
Сообщений: 280
Как вариант: sp_create_plan_guide
13 ноя 12, 06:20    [13462447]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
aleks2
Guest
max44
Как вариант: sp_create_plan_guide


Вот не проще ли статистику обновлять регулярно?
13 ноя 12, 07:30    [13462506]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
aleks2
Guest
andrew_1812
Можно ли что-то сделать для направления оптимизатора в нужное русло, кроме хинтов index = или создания всё и вся покрывающего индекса?

Это те оптимизатор, как бе, ненавязчиво намекает: такие деле так НЕ делают.
13 ноя 12, 07:34    [13462514]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Можно еще ПК сделать некластерным, а индекс по дате - кластерным.
13 ноя 12, 09:36    [13462757]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
нужно больше информации - может по какой-то причине большинство записей попадает в эти несколько дней? (-:
13 ноя 12, 10:52    [13463166]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34703
andrew_1812
всё покрывающий индекс - да, помогает, но - индексов несколько, каждый из них приходится делать "обильно" покрывающим - объем увеличивается чрезвычайно быстро
forceseek - да, помогает, но тоже есть свой нюанс - запросы могут писать разные люди, не всегда имеющие представление о хинтах. Помогает работа через вьюшку с указанным хинтом, но там тоже возникают свои нюансы. Поэтому хотелось бы как-то решить проблему не на уровне хинтов в запросе. а, в идеале, на уровне настроек сервера/базы.
ок, в любом случае спасибо за ответ


Это глобальная проблема всех оптимизаторов, всех субд. Универсально она на решается, она решается только для каждого запроса отдельно. Путем проставления хинтов.

Собственно это и не проблема вовсе - оптимизаторы не могут принципиально строить хорошие планы для всех запросов. — 80% они решают нормально, 20% надо оптимизить руками.
Это —нормальная ситуация.
Это не баг, не недостаток конкретного оптимизатора, а просто принципиальное ограничение оптимизаторов вообще.
13 ноя 12, 11:53    [13463630]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34703
Почему в данном случае все слетает — это отдельная тема, прежде всего, ты дал мало информации, надо включать вывод промежуточной информации оптимизатора и смотреть, почему он так решает в каждом случае.

Кстати, параллельное выполнение запросов у тебя разрешено?
13 ноя 12, 11:58    [13463672]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
MasterZiv
Кстати, параллельное выполнение запросов у тебя разрешено?

Ага, если на сервере 40 ядер, то сканирование пойдет в 40 раз быстрее, так что не стоит особо расстраиваться по поводу ошибок оптимизатора!
13 ноя 12, 12:17    [13463852]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
на 99% уверен - протухает статистика
13 ноя 12, 12:25    [13463930]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Crimean
Member

Откуда:
Сообщений: 13147
или индекс сильно фрагментирован, наш оптимизатор такие индексы очень не любит
13 ноя 12, 14:41    [13465330]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
andrew_1812
Member

Откуда:
Сообщений: 4
кластерный по дате - перетягивает одеяло на себя, отрезает другие индексы. Ну и со вставкой проблемы.
Записи распределены равномерно, параллельное выполнение разрешено, 40 ядер у клиентов нет, дефрагментация происходит, статистика обновляется.
ок, то есть хинты и еще раз хинты

aleks2
andrew_1812
Можно ли что-то сделать для направления оптимизатора в нужное русло, кроме хинтов index = или создания всё и вся покрывающего индекса?

Это те оптимизатор, как бе, ненавязчиво намекает: такие деле так НЕ делают.


а как такие дела делаются, если не секрет?
13 ноя 12, 17:18    [13466901]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Crimean
Member

Откуда:
Сообщений: 13147
andrew_1812
ок, то есть хинты и еще раз хинты


ой не всегда. статистики + индексы + корректное (зачастую, избыточное!) написание условий
очень часто разработчику в WHERE уже "все понятно", но серверу очень даже стоит сказать, что "масло таки масляное". бывает, что и более 2 раз в одном WHERE..
а хинты эта зараза временами "назло" использует. то есть и не скажешь, что не выполнил, но лучше бы вообще не выполнял..
13 ноя 12, 17:25    [13466962]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
aleks2
Guest
andrew_1812
а как такие дела делаются, если не секрет?


Для твоего конкретного примера показано Indexed View.
13 ноя 12, 19:41    [13467723]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
andrew_1812
Member

Откуда:
Сообщений: 4
ну только для данного и конкретного
13 ноя 12, 19:48    [13467756]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34703
ROLpogo
MasterZiv
Кстати, параллельное выполнение запросов у тебя разрешено?

Ага, если на сервере 40 ядер, то сканирование пойдет в 40 раз быстрее, так что не стоит особо расстраиваться по поводу ошибок оптимизатора!


Я не говорил, что его нужно включать, даже наоборот, имел в ввиду, что лучшее бы выключить.
13 ноя 12, 19:49    [13467758]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
перекластерный
Guest
andrew_1812
кластерный по дате - перетягивает одеяло на себя, отрезает другие индексы. Ну и со вставкой проблемы.
Записи распределены равномерно, параллельное выполнение разрешено, 40 ядер у клиентов нет, дефрагментация происходит, статистика обновляется.

ниччо не понял.
13 ноя 12, 19:51    [13467763]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Crimean
Member

Откуда:
Сообщений: 13147
aleks2
показано Indexed View.


главное чтобы модификаций было немного. а то они ой как проседают под такой зверушкой
13 ноя 12, 21:42    [13468100]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Crimean
Member

Откуда:
Сообщений: 13147
перекластерный
ниччо не понял.


а тут все такие, пока реального плана запроса не покажут
13 ноя 12, 21:47    [13468115]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
или индекс сильно фрагментирован, наш оптимизатор такие индексы очень не любит
Смешная шутка. Никогда оптимизатор такого не учитывал.
14 ноя 12, 04:01    [13468927]     Ответить | Цитировать Сообщить модератору
 Re: Ms sql перестает использовать нужный индекс  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
andrew_1812
Постоянно сталкиваюсь с ситуацией – при увеличение кол-ва записей в таблице оптимизатор перестает использовать оптимальный индекс и начинает сваливаться на сканирование таблицы.
У вас запрос или процедура?
Где план?
Как часто обновляется статистика?
andrew_1812
периода в несколько дней
Каких дней? Последних? По которым статистика еще не посчиталась?
14 ноя 12, 04:06    [13468929]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить