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

Откуда: Ростов-на-Дону
Сообщений: 344
Привет!

Есть табличка
table UserHistory 
(HistoryId BIGINT IDENTITY(1,1),
UserId INT, 
HistoryDate Datetime, 
ActionId INT,
ActionURL Varchar(128))


где пишется активность пользователя

и на ней есть 2 некластерных индекса (кластерный по identity HistoryId)

1. UserId INLUDE (HistoryDate)
2. HistoryDate

Есть процедура, которая удаляет старые записи и помещает их в архивную таблицу, в которой есть запрос

DELETE TOP (@batchSize)
	FROM dbo.UserHistory
	OUTPUT DELETED.HistoryId
		,DELETED.UserId
		,DELETED.HistoryDate
		,DELETED.ActionId
		,DELETED.ActionURL
	INTO #deleted(HistoryId, UserId, HistoryDate,ActionId, ActionURL)
	WHERE HistoryDate < @dt;


И этот запрос почему то иногда использует индекс UserId INLUDE (HistoryDate)
Когда он это делает ему становится плохо и приложению тоже. Он это делает редко, но причины не понятны.

Куда можно посмотреть? Статистики есть по обоим и дата обновления одинаковая.
Я могу добавить хинт, но хотелось бы понять причину.
9 мар 17, 16:48    [20278475]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
Nimua
Я могу добавить хинт, но хотелось бы понять причину.
Вот и добавьте. По крайней мере узнаете есть ли такой индекс при компиляции запроса.
9 мар 17, 18:08    [20278958]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
aleks2
Guest
О! Теоретики секцинирования заплутали в трех соснах.

1. Если бы в голове был мозг - кластерный индекс выглядел бы так (HistoryDate, HistoryId).
2. А так - у оптимизатора нет особенного выбора.
3. Вот, от безысходности, у него крышу и сносит - лукапы делать.
9 мар 17, 18:09    [20278964]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
tazzman
Member

Откуда:
Сообщений: 15
Ответ на вопрос "почему?" не дам, но может быть дело в том, что статистика сэмплированная? Обработчик может быть по какой-то причине считает, что лучше вместо кучи seek сделать scan или вообще воспользоваться другим индексом...
9 мар 17, 18:17    [20278998]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
invm,

Индекс есть
9 мар 17, 18:55    [20279122]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
aleks2,

Вопрос, он пытается использовать индекс по UserId потому что там есть include поле из списка выборки в OUTPUT? И поэтому изменение кластерного ключа на HistoryDate, HistoryId приведет к счастью?

Блок удаления 5 тыс., таблица 30 млн записей, ежедневно добавляется примерно 1 млн.
9 мар 17, 19:20    [20279204]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
tazzman,

а как это проверить? для этой таблицы статистика не обновляется в JOBе, она обновляется автоматически, асинхронно
9 мар 17, 20:25    [20279333]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
Nimua
Индекс есть
Во-первых, как вы убедились, что он есть на момент компиляции?
Во-вторых, даже если есть, он может быть просто задизейблен.

Исходя из вашего описания и показанного запроса, оптимизатор может выбрать скан UserId INLUDE (HistoryDate) при живом HistoryDate, только если сойдет с ума.

Ну можно еще предположить, что некий диверсант установил для индекса по HistoryDate fill factor 1% и перестроил его. В результате полное сканирование по UserId оказалось выгоднее частичного по HistoryDate.

Возможно есть еще причины, но я о них не знаю.
9 мар 17, 21:53    [20279569]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
aleks2
Guest
Nimua
aleks2,

Вопрос, он пытается использовать индекс по UserId потому что там есть include поле из списка выборки в OUTPUT? И поэтому изменение кластерного ключа на HistoryDate, HistoryId приведет к счастью?

Блок удаления 5 тыс., таблица 30 млн записей, ежедневно добавляется примерно 1 млн.


К счастью "изменение кластерного ключа на HistoryDate, HistoryId" приведет потому, что это ПРАВИЛЬНЫЙ кластерный индекс.
Он делает ненужным индекс HistoryDate. От слова ваще.
И решает все проблемы DELETE.

ЗЫ. Нам не страшны "30 млн записей".
10 мар 17, 05:48    [20279953]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
Massa52
Member

Откуда:
Сообщений: 395
aleks2
К счастью "изменение кластерного ключа на HistoryDate, HistoryId" приведет потому, что это ПРАВИЛЬНЫЙ кластерный индекс.
Он делает ненужным индекс HistoryDate. От слова ваще.
И решает все проблемы DELETE.
".


Для HistoryDate нужно указыват DESCENDING?
10 мар 17, 06:34    [20279958]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
aleks2
Guest
Massa52
aleks2
К счастью "изменение кластерного ключа на HistoryDate, HistoryId" приведет потому, что это ПРАВИЛЬНЫЙ кластерный индекс.
Он делает ненужным индекс HistoryDate. От слова ваще.
И решает все проблемы DELETE.
".


Для HistoryDate нужно указыват DESCENDING?

Нет.
Точнее - безразлично.
10 мар 17, 08:18    [20280027]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
tazzman
Member

Откуда:
Сообщений: 15
Nimua
tazzman,

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


DBCC SHOW_STATISTICS (tblName, idxName);
Если количество строк в столбце отличается от значения в rows sampled, значит статистика сэмплированная.
10 мар 17, 10:11    [20280367]     Ответить | Цитировать Сообщить модератору
 Re: Почему может использоваться неправильный индекс?  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
tazzman,

Спасибо, проверила, нет не отличается - хотя опять же не ясно какова была ситуация в момент возникновения проблемы и кривого плана.

invm,

Проверяла, нет он не disabled, вообще я думаю была какая то проблема со статистикой в тот момент.

aleks2,

Посмотрела запросы в общем позволяют немного переделать и изменить ключ, поменяю.


Большое спасибо за советы!
10 мар 17, 11:54    [20280912]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить