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

Откуда: Israel
Сообщений: 995
MS SQL Management Studio при показе плана выполнения рекомендует добавить индекс на два поля фильтра, но добавить в include все поля таблицы.

Примерно так
Структура таблицы
CREATE TABLE Table1
(
 Id    INT PRIMARY KEY
 Col1 UNIQUEIDENTIFIER,
 Col2 BIGINT,
 M1 TINYINT, M2 TINYINT, M3 TINYINT, M4 TINYINT, M5 TINYINT, M6 TINYINT, M7 TINYINT
)
Запрос
SELECT TOP (150) * FROM Table1 WHERE Col1 = @Param1 AND Col2 BETWEEN @StartDateParam AND @EndDateParam
Рекомендация от MS SQL Management Studio
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON Table1 (Col1, Col2)
INCLUDE ([M1],[M2],[M3],[M4],[M5],[M6],[M7])

Всего в таблице около 10-20 млн. строк
95% операций это запись (update) - SLA < 50 ms
5% запросы в виде указаного выше - SLA < 10 sec.

Если созданю индекс на col1 и col2, то он не используется, происходит сканирование PK.
(хотя estimated plan, да предсказывает его использование)

Селективность Col1 - это 40-80 разных значений с одинаковым количеством записей на каждое значение
Col2 - это время переведённое в Ticks (наносекунды) - всего в таблице 3-10 месяцев с разбросом записей 10-15 секунд.

Средний запрос это от часа до двух недель - (но до 150 записей без сортировки)

С записью проблем нет - чукча не читатель, чукча писатель и специализируется в записи.
А вот почему при запросе не используется индекс и какой смысл делать include на все поля?

select * используется специально, нужны все поля.
23 авг 09, 17:52    [7570692]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36817
EvAlex
А вот почему при запросе не используется индекс и какой смысл делать include на все поля?
Потому что операция Bookmark, использующаяся для того, чтобы достать значения полей, не бесплатная. И в некоторых случаях сервер решает, что проще просканировать весь кластерный индекс, чем букмаркать каждую запись.

Сообщение было отредактировано: 23 авг 09, 18:00
23 авг 09, 17:58    [7570703]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
Гавриленко Сергей Алексеевич
EvAlex
А вот почему при запросе не используется индекс и какой смысл делать include на все поля?
Потому что операция Bookmark, использующаяся для того, чтобы достать значения полей, не бесплатная. И в некоторых случаях сервер решает, что проще просканировать весь кластерный индекс, чем букмаркать каждую запись.


Понимаю, но для 150 строк???
23 авг 09, 18:04    [7570710]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36817
EvAlex
Гавриленко Сергей Алексеевич
EvAlex
А вот почему при запросе не используется индекс и какой смысл делать include на все поля?
Потому что операция Bookmark, использующаяся для того, чтобы достать значения полей, не бесплатная. И в некоторых случаях сервер решает, что проще просканировать весь кластерный индекс, чем букмаркать каждую запись.


Понимаю, но для 150 строк???
Скорее всего, что-то не так со статистикой. Ну, и чтобы сервер реже придумывал не то, либо индекс делайте покрывающий, как сервер сам и советует, либо хинт рисуйте.
23 авг 09, 18:06    [7570713]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
Alexes
Member

Откуда:
Сообщений: 1100
Попробуйте построить индекс (Col2, Col1).
23 авг 09, 19:24    [7570829]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
Alexes
Попробуйте построить индекс (Col2, Col1).

пробовал, что col1, col2 что col2, col1 тот же эффект - не пользуется и всё.

Гавриленко Сергей Алексеевич

Ну, и чтобы сервер реже придумывал не то, либо индекс делайте покрывающий, как сервер сам и советует, либо хинт рисуйте.

Да итак нет ни одного места уже без хинтов :)
Возьмут какого-нибудь "умника" консультанта скажет hint abuse - докажи потом, что не верблюд.
Могу просто ORDER BY col2 добавить - тогда пользуется индексом. - index seek + bookmark

Какие последствия если сделать INCLUDE на все поля? сильная фрагментация индекса, замедление update и увелечение размера базы почти вдвое?
23 авг 09, 19:56    [7570875]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
ChA
Member

Откуда: Москва
Сообщений: 10991
EvAlex

Структура таблицы
CREATE TABLE Table1
(
 Id    INT PRIMARY KEY
 Col1 UNIQUEIDENTIFIER,
 Col2 BIGINT,
 M1 TINYINT, M2 TINYINT, M3 TINYINT, M4 TINYINT, M5 TINYINT, M6 TINYINT, M7 TINYINT
)
Запрос
SELECT TOP (150) * FROM Table1 WHERE Col1 = @Param1 AND Col2 BETWEEN @StartDateParam AND @EndDateParam
Если отталкиватся от указанного вида запроса, то я бы, пожалуй, сделал так
CREATE TABLE Table1 (
	Id INT
	, Col1 UNIQUEIDENTIFIER
	, Col2 BIGINT
	, M1 TINYINT
	, M2 TINYINT
	, M3 TINYINT
	, M4 TINYINT
	, M5 TINYINT
	, M6 TINYINT
	, M7 TINYINT
	, UNIQUE CLUSTERED (Col1, Col2, ID)
	, PRIMARY KEY(ID)
)
Желательно бы еще типичный запрос на обновление увидеть.
23 авг 09, 20:00    [7570880]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
ChA

Желательно бы еще типичный запрос на обновление увидеть.


Примерно такой вот...
BEGIN TRAN
 DECLARE @Id INT
 SELECT TOP(1) @Id = Id FROM Table1 (READPAST, UPDLOCK) WHERE Col1 IS NULL
 
 UPDATE Table1 SET col1 = @param1...(все поля кроме id) WHERE Id = @Id

IF @@TRANCOUNT > 0
 COMMIT
23 авг 09, 20:17    [7570898]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
ChA
, UNIQUE CLUSTERED (Col1, Col2, ID)

пропустил часть поста.
Я опасаюсь, что кластерный индекс не будет успевать перестраиваться с необходимой скоростью...

Ещё немного деталей -
Id это IDENTITY
создаётся сразу N количество записей
потом берётся свободная и апдейтится.
если таковых нет, то добавляется N*0.01 "свободных" записей
Время от времени записи освобождаются
UPDATE Table1 SET col1 = NULL WHERE Id = @Param1
23 авг 09, 20:28    [7570917]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
ChA
Member

Откуда: Москва
Сообщений: 10991
EvAlex
Примерно такой вот...
BEGIN TRAN
 DECLARE @Id INT
 SELECT TOP(1) @Id = Id FROM Table1 (READPAST, UPDLOCK) WHERE Col1 IS NULL
 
 UPDATE Table1 SET col1 = @param1...(все поля кроме id) WHERE Id = @Id

IF @@TRANCOUNT > 0
 COMMIT
Нда, обновление кластерного желательно избегать. Хотя для 2-х индексов это будет не очень страшно, если правильно помню, то в этом случае update заменяется на delete+insert. Я бы всё-таки попробовал.

P.S. Не знаю задачу, но мне показалось, что решение не самое удачное. Впрочем, на месте виднее.
23 авг 09, 20:29    [7570919]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
ChA
Member

Откуда: Москва
Сообщений: 10991
EvAlex
Ещё немного деталей -
Id это IDENTITY
создаётся сразу N количество записей
потом берётся свободная и апдейтится.
если таковых нет, то добавляется N*0.01 "свободных" записей
Время от времени записи освобождаются
UPDATE Table1 SET col1 = NULL WHERE Id = @Param1
Что-то в этом духе я и подозревал. Некто orunbek, если правильно помню ник, тоже шёл по-похожему пути. Мне кажется, что было бы разумнее разделить данные на 2 таблицы: первая - начальная генерация, во вторую данные перебрасываются из первой.
23 авг 09, 20:32    [7570923]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
ChA

P.S. Не знаю задачу, но мне показалось, что решение не самое удачное. Впрочем, на месте виднее.

Обеспечить максимально возможный многопоточный update rate.
При наименьшем использовании ресурсов сервера.

Update просто дешевле чем insert\delete.
23 авг 09, 20:34    [7570925]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
ChA
Member

Откуда: Москва
Сообщений: 10991
EvAlex
Обеспечить максимально возможный многопоточный update rate.
При наименьшем использовании ресурсов сервера.

Update просто дешевле чем insert\delete.
Спорный тезис, но обсуждать не готов, можно поэкспериментировать.
Мне просто кажется, что лучше было бы в таких ситуациях избегать UPDATE-в вообще, велика вероятность взаимоблокировок, из-за этого Вам пришлось хинты использовать. Ну да ладно, это уже oftop.
23 авг 09, 20:46    [7570932]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33364
Блог
Возможно, индекс вообще не стоит строить, учитывая, что
EvAlex
95% операций это запись (update) - SLA < 50 ms
5% запросы в виде указаного выше - SLA < 10 sec.
...
UPDATE Table1 SET col1 = @param1...(все поля кроме id) WHERE Id = @Id


хотя конечно на месте виднее)
24 авг 09, 08:47    [7571407]     Ответить | Цитировать Сообщить модератору
 Re: Странный missing index  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
Критик
Возможно, индекс вообще не стоит строить, учитывая, что

Учитывая, что он не используется....
24 авг 09, 11:46    [7572137]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить