Фон
Недавно у нас возникла проблема с планами запросов, которые sql-сервер использовал на одной из наших больших таблиц (около 175 000 000 строк). Структура столбцов и индексов таблицы не изменилась в течение 5 лет.
Таблица и индексы выглядят следующим образом:
create table responses (
response_uuid uniqueidentifier not null,
session_uuid uniqueidentifier not null,
create_datetime datetime not null,
create_user_uuid uniqueidentifier not null,
update_datetime datetime not null,
update_user_uuid uniqueidentifier not null,
question_id int not null,
response_data varchar(4096) null,
question_type_id varchar(3) not null,
question_length tinyint null,
constraint pk_responses primary key clustered (response_uuid),
constraint idx_responses__session_uuid__question_id unique nonclustered (session_uuid asc, question_id asc) with (fillfactor=80),
constraint fk_responses_sessions__session_uuid foreign key(session_uuid) references dbo.sessions (session_uuid),
constraint fk_responses_users__create_user_uuid foreign key(create_user_uuid) references dbo.users (user_uuid),
constraint fk_responses_users__update_user_uuid foreign key(update_user_uuid) references dbo.users (user_uuid)
)
create nonclustered index idx_responses__session_uuid_fk on responses(session_uuid) with (fillfactor=80)
Запрос, который выполнялся плохо (~ 2,5 минуты вместо нормального и менее 1 секунды производительности) выглядит следующим образом:
SELECT
[Extent1].[response_uuid] AS [response_uuid],
[Extent1].[session_uuid] AS [session_uuid],
[Extent1].[create_datetime] AS [create_datetime],
[Extent1].[create_user_uuid] AS [create_user_uuid],
[Extent1].[update_datetime] AS [update_datetime],
[Extent1].[update_user_uuid] AS [update_user_uuid],
[Extent1].[question_id] AS [question_id],
[Extent1].[response_data] AS [response_data],
[Extent1].[question_type_id] AS [question_type_id],
[Extent1].[question_length] AS [question_length]
FROM [dbo].[responses] AS [Extent1]
WHERE [Extent1].[session_uuid] = @f6_p__linq__0;
(Запрос генерируется каркасом сущности и выполняется с помощью sp_executesql)
План выполнения в период плохой работы выглядел так:
Некоторая предыстория данных, выполняющих указанный выше запрос, никогда не вернет более 400 строк. Другими словами, фильтрация на session_uuid действительно уменьшает набор результатов.
Некоторая предыстория запланированного обслуживания - запланированное задание выполняется еженедельно, чтобы перестроить статистику базы данных и перестроить индексы таблиц. Задание запускает script, который выглядит следующим образом:
alter index all on responses rebuild with (fillfactor=80)
Разрешение проблемы производительности состояло в том, чтобы запустить индекс перестройки script (выше) в этой таблице.
Другие, возможно, релевантные лакомые кусочки информации... Распределение данных вообще не изменилось с момента восстановления последнего индекса. В запросе нет объединений. Мы являемся магазином SAAS, у нас есть 50 - 100 баз данных реального производства с точно такой же схемой, некоторые с большим количеством данных, некоторые с меньшим количеством, все с теми же запросами, которые выполняются против них, распространяются на нескольких серверах sql.
Вопрос:
Что могло случиться, чтобы сервер sql начал использовать этот ужасный план выполнения в этой конкретной базе данных?
Помните, что проблема была решена путем простой перестройки индексов в таблице.
Возможно, лучший вопрос: "Каковы обстоятельства, при которых сервер sql перестанет использовать индекс?"
Еще один способ взглянуть на это: "Почему оптимизатор не использовал индекс, который был перестроен несколько дней назад, а затем снова начал использовать его после выполнения экстренного восстановления индекса после того, как мы заметили плохой план запроса?"
