Просмотр кластерного индекса Поиск более 0,5 миллиона строк занимает 7 минут

Взгляните на этот план выполнения: http://sdrv.ms/1agLg7K
Это не оценивается, это актуально. От фактического исполнения, которое заняло около 30 минут.

Выберите второй оператор (занимает 47,8% от общего времени выполнения - примерно 15 минут).
Посмотрите на верхнюю операцию в этом операторе - Просмотр кластерного индекса Поиск по _Security_Tuple4. Операция стоит 51,2% от выписки - примерно 7 минут.

Представление содержит около 0,5M строк (для справки, log2(0,5M) ~= 19 - всего 19 шагов, учитывая, что размер узла дерева индекса равен двум, что в действительности, вероятно, выше).
Результатом этого оператора является ноль строк (не соответствует оценке, но пока не обращайте на это внимания).
Фактические казни - ноль.

Так что вопрос в том, как это могло занять семь минут?! (и конечно, как мне это исправить?)

РЕДАКТИРОВАТЬ: Некоторые разъяснения о том, что я спрашиваю здесь.
Меня не интересуют общие рекомендации, связанные с производительностью, такие как "просмотр индексов", "просмотр размеров", "анализ параметров", "разные планы выполнения для разных данных" и т. Д.
Я уже все это знаю, я могу сам провести такой анализ.

Что мне действительно нужно, так это узнать, что может привести к тому, что один конкретный кластерный индекс будет работать так медленно, и что я могу сделать, чтобы ускорить его.

Не весь запрос.
Не любая часть запроса.
Просто этот конкретный индексный поиск.
КОНЕЦ РЕДАКТИРОВАНИЯ

Также обратите внимание, что вторая и третья наиболее дорогие операции выполняются через _Security_Tuple3 и _Security_Tuple2 соответственно, и они занимают только 7,5% и 3,7% времени. Между тем, _Security_Tuple3 содержит примерно 2,8 млн строк, что в шесть раз больше, чем у _Security_Tuple4.

Также немного предыстории:

  1. Это единственная база данных этого проекта, которая плохо себя ведет. Есть пара дюжин других баз данных той же схемы, ни одна из них не демонстрирует эту проблему.
  2. Когда эта проблема была обнаружена впервые, оказалось, что индексы были фрагментированы на 99%. Перестройка индексов ускорила, но незначительно: весь запрос занял 45 минут перед перестройкой и 30 минут после.
  3. Играя с базой данных, я заметил, что простые запросы, такие как "select count(*) from _Security_Tuple4"), занимают несколько минут. WTF?!
  4. Тем не менее, они только заняли несколько минут на первом запуске, и после этого они были мгновенными.
  5. Проблема не связана ни с конкретным сервером, ни с конкретным экземпляром SQL Server: если я создаю резервную копию базы данных, а затем восстанавливаю ее на другом компьютере, поведение остается прежним.
Источник

3 ответа

Решение

Во-первых, я хотел бы указать на небольшое заблуждение: хотя оператор delete, как говорят, занимает почти 48% всего выполнения, это не означает, что он занимает 48% необходимого времени; на самом деле, 51%, назначенный в этой части плана запроса, определенно НЕ должен интерпретироваться как "половина времени" всей операции!

В любом случае, повторяя ваше замечание о том, что COUNT(*) для таблицы "в первый раз" занимает пару минут, я склонен сказать, что у вас есть проблема ввода-вывода, связанная с указанной таблицей / представлением. Лично мне не очень нравятся материализованные представления, поэтому у меня нет реального опыта работы с ними и того, как они ведут себя внутренне, но обычно я хотел бы предположить, что фрагментация наносит ущерб базовой системе хранения. Причина, по которой он работает быстро во второй раз, заключается в том, что гораздо быстрее получить доступ к страницам из кэша, чем при извлечении их с диска, особенно когда они повсюду. (Есть ли (макс.) Поля в представлении?)

В любом случае, чтобы выяснить, что занимает так много времени, я бы посоветовал вам убрать этот код из триггера, в котором он находится в данный момент, "подделать" вставленную и удаленную таблицу, а затем попытаться снова выполнить запросы, добавив временные метки и / или используя какая-то программа, такая как SQL Sentry Plan Explorer, чтобы увидеть, сколько ДЕЙСТВИТЕЛЬНО занимает каждая часть (у нее есть столбец продолжительности, когда вы запускаете скрипт изнутри программы). Вполне может быть, что вы смотрите не на ту часть; опыт показывает, что стоимость и фактическое время выполнения не всегда связаны так, как нам хотелось бы думать.

Источник

Наблюдения включают в себя:

  1. Это самая большая из этих баз данных, с которой вы работаете? Если это так, размер имеет значение для оптимизатора. Это сделает совершенно другой план для больших наборов данных по сравнению с меньшими наборами данных.
  2. Оценочные строки и фактические строки довольно расходятся. Это наиболее очевидно по четвертому запросу. "delete c from @alternativeRoutes....", где _Security_Tuple5 оценивает возвращаемые 16 строк, но фактически использовала 235 904 строки. Для такого количества строк сканирование индекса может быть более производительным, чем поиск индекса. Статистика в таблице актуальна или нуждается в обновлении?
  3. "Выбор количества (*) из _Security_Tuple4" занимает несколько минут, в первый раз. Второй раз мгновенно. Это связано с тем, что все данные теперь кэшируются в памяти (пока не устаревают), а второй запрос выполняется быстро.
  4. Поскольку проблема перемещается вместе с базой данных, в базе данных находятся статистика, отсутствующие индексы и т. Д. Я бы также предложил проверить, чтобы индексы совпадали с другими базами данных, используя ту же схему.

Это не полный анализ, но он дает вам кое-что посмотреть.

Источник

Федор,

Первый:

Проблема не связана ни с конкретным сервером, ни с конкретным экземпляром SQL Server: если я создаю резервную копию базы данных, а затем восстанавливаю ее на другом компьютере, поведение остается прежним.

Я предполагаю, что вы: а) выполняете этот запрос в изолированной среде, б) данные не подвержены мутациям.

Это правильно?

Второе: опубликуйте здесь свой скрипт CREATE INDEX. У вас есть забавный FILLFACTOR? SORT_IN_TEMPDB?

В-третьих: какой тип у вас ParentId, ObjectId? int, smallint, uniqueidentifier, varchar?

Источник
Другие вопросы по тегам