Простой запрос SQL Server занимает слишком много времени
Этот запрос требует слишком много времени для получения результата.
Таблица содержит около 150 миллионов строк, и в запросе нет отсутствующих индексов.
select
Number, BankContacts_ID
from
dbo.BankContactNumbers b with (nolock)
where
b.BankContacts_ID = 1234
order by
b.ID
offset 0 rows fetch next 10 rows only
Вот структура таблицы:
create table BankContactNumbers
(
ID int identity
constraint PK_BankContactNumbers primary key nonclustered
with (fillfactor = 70),
BankContacts_ID int not null,
Number char(11)
)
create index IX_BankContactNumbers_BankContacts_ID
on BankContactNumbers (BankContacts_ID) include (ID, Number)
План выполнения таков: https://www.brentozar.com/pastetheplan/?id=SJ8S1TiLo
with (nolock) <-- Не делайте этого
Кстати, в отличие от почти любого другого объекта в SQL Server, имена индексов ограничены таблицей (а не схемой или базой данных), поэтому вам не нужно включать имя таблицы в имя индекса.
Попробуйте добавить столбец ORDER BY в качестве ключевого столбца вместо включенного: CREATE INDEX IX_BankContactNumbers_BankContacts_ID ON BankContactNumbers (BankContacts_ID, ID) include (Number) WITH(DROP_EXISTING=ON);
Вы смотрели на фактический план выполнения? Если да: что это говорит вам? Можете ли вы опубликовать это здесь (используя Brent Ozar’s brentozar.com/pastetheplan)?
Большое спасибо, Дэн Гузман, теперь это супер быстро
План выполнения: brentozar.com/pastetheplan/?id=SJ8S1TiLo
Почему вы выбрали некластеризованный индекс для своего первичного ключа? Это кажется странным выбором для столбца идентификации (который является идеальным ключом кластеризации). Я ожидаю, что если бы это был кластеризованный индекс, а не некластеризованный, ваш запрос работал бы значительно лучше. В этом примере по крайней мере переход на кластеризованный индекс означает, что вы можете избежать поиска RID, который составляет 100% ваших затрат в опубликованном вами плане.
У меня нет сгруппированного PK, потому что есть CCI
Ответы 1
Существующий индекс для BankContacts_ID полезен для предиката равенства, но не для ORDER BY, поскольку включается столбец ID, а не ключевой столбец.
Измените существующий индекс, чтобы добавить ID в качестве второго ключевого столбца. Таким образом, BankContacts_ID можно использовать для условия WHERE, а строки возвращаются в последовательности ID для ORDER BY без сортировки в плане запроса. Кроме того, включенный столбец Number позволит некластеризованному индексу покрыть запрос.
CREATE INDEX IX_BankContactNumbers_BankContacts_ID ON BankContactNumbers (BankContacts_ID, ID) include (Number) WITH(DROP_EXISTING=ON);
Я просто заменил предложение where следующим: «where BankContacts_ID = 12680 или BankContacts_ID = 126806», и запрос снова стал медленным!
Не знал, что мы пытаемся поразить движущуюся цель ;-) Чтобы оптимизировать составное условие OR, столбец ORDER BY в идеале должен быть таким же, как WHERE. Попробуйте изменить запрос на ORDER BY BankContacts_ID, ID вместо столбца идентификаторов.
движущаяся цель! :)))
«и в запросе нет отсутствующего индекса». - Вы неправильно понимаете, как индексы работают с w.r.t.
WHEREиORDER BY. ПредложениеINCLUDEне делает того, что вы думаете. Вам нужен составной индекс... и лучшее форматирование операторовCREATE TABLE.