Блог

Я работаю на SQL Server 2008 R2 и пытаюсь точно настроить производительность. Я сделал все, что мог из:

• Обзор кода SQL code
• Создавайте или удаляйте индексы, как я считаю нужным
• Автоматическое создание статистики На
• Автоматическое обновление статистики На
• Автоматическое обновление статистики асинхронно При

У меня система 24/7, которая постоянно хранит данные. Иногда мы выполняем чтение, и в этом проблема. Иногда чтение занимает пару секунд или меньше (что было бы ожидаемо и приемлемо для нас). В других случаях чтение занимает несколько секунд, которые могут составлять минуту, прежде чем хранимая процедура завершится, и мы отобразим данные в пользовательском интерфейсе.

Если мы повторим чтение, это будет быстрее. Профилировщик SQL отслеживал конкретную хранимую процедуру или запрос, что занимало несколько секунд. Мы бы увеличили масштаб этой хранимой процедуры и сделали все возможное, чтобы оптимизировать ее, если сможем.

Я также отследил событие автоматической статистики и событие перекомпиляции. Трудно сказать, обновляется ли статистика, из-за чего чтение занимает много времени, или это вызвано перекомпиляцией. Иногда я вижу, что профилировщик отследил перекомпиляцию запроса на чтение, которая заняла несколько неприемлемых минут, в других случаях он не отслеживает перекомпиляцию.

Я пытался запретить оптимизатору запросов блокировать чтение до тех пор, пока он не перекомпилирует или не обновит статистику, используя опцию use plan XML и т.д. Но я столкнулся с ошибками компиляции, жалуясь на то, что XML плана запроса недействителен; это может быть правдой, потому что запрос не задействован: select joins, которые включают переменную локальной таблицы. Я вроде как взломал XML и, возможно, именно поэтому он посчитал его недействительным. Поэтому я отказался от использования подсказки плана.

Мы пробовали периодически (каждые 15 минут) запускать обновление статистики вручную, чтобы поддерживать статистику в актуальном состоянии настолько, насколько это возможно, но это снизило производительность. updatestats блокирует запись и, я уверен, даже считывает; updatestats похоже, вел кучу статистики, и в среднем это занимало около 80-90 секунд. Чтение, которое длится так долго, неприемлемо.

Итак, идея состоит в том, чтобы позволить чтению происходить и предотвратить ситуацию, когда статистика перекомпиляции / обновления блокирует его, правильно? Имеет ли смысл вообще отключать автоматическую статистику? Или, возможно, отключить автоматическое создание статистики после удаления всей автоматически созданной статистики?

Возможно, это противоречит рекомендациям Microsoft, поскольку они по умолчанию включают автоматическое создание статистики и автоматическое обновление статистики, и производительность может пострадать, но любые идеи / подсказки, которые вы можете дать, были бы оценены.

Из того, что вы объясняете, похоже, что может происходить приведенное ниже (все или некоторые).

1. Вы выполняете физическое чтение. Быстрый способ избежать этого — увеличить объем оперативной памяти, который вы загружаете в окно. Вы не упомянули спецификации оборудования вашего сервера. Пожалуйста, добавьте подробности.
2. Если вы отследите вызовы SQL, то сможете легко выяснить, почему произошла ПЕРЕКОМПИЛЯЦИЯ. Посмотрите на EventSubClass, чтобы выяснить причину и работать над ее устранением. ссылка:http://msdn.microsoft.com/en-us/library/ms187105.aspx
3. Вы упомянули табличные переменные. Они печально известны тем, что вызывают проблемы с производительностью при использовании НЕ в нужном месте. Если вы используете табличные переменные в соединении, о параллельном плане не может быть и речи, а также о статистике. Я не уверен, как и где вы используете, но попробуйте заменить их временными таблицами. И начиная с SQL Server 2005, вы получите в лучшем случае только перекомпиляцию STMT, а НЕ полную перекомпиляцию SP, как это произошло в 2000 году.
4. Вы упомянули опцию асинхронного обновления статистики, и это не заблокирует запрос.
5. Какова ЛУЧШАЯ СТАТИСТИКА ОЖИДАНИЯ на этом сервере? Вы определили дорогостоящие процедуры, основанные на процессоре, количестве логических операций чтения и выполнения?
6. Вы смотрели ожидаемую продолжительность жизни страницы, объем ввода-вывода с использованием статистики виртуальных файлов DMV?
7. Обновлять статистику каждые 15 минут — НЕ лучший план. Как часто данные вставляются в систему? Какую частоту дискретизации вы используете? Какова ваша стратегия обслуживания индекса?
8. Вы смотрели на недостающие индексы DMV?

Существует множество хороших запросов для выявления проблем более детализированным способом с использованием приведенных ниже запросов.

ссылка: http://dl.dropbox.com/u/13748067/SQL Server 2008 Diagnostic Information Queries (April 2011).sql

Есть так много других вещей, на которые нужно обратить внимание, но приведенное выше является хорошей отправной точкой.

Хорошо, вот мой вывод IMHO по этому поводу:

• DBCC INDEXDEFRAG стоит попробовать и является ONLINE функцией, следовательно, может использоваться в действующей системе

• Возможно, вы достигли максимальной производительности своего архитектурного проекта. Вы можете увеличить масштаб, что всегда может помочь, но, скорее всего, вам придется изменить архитектуру для достижения лучшей масштабируемости в ущерб простоте

• Распространенный трюк partitioning . Вы выполняете запись в таблицу, распределение индексов которой не похоже на то, что было несколько часов назад — следовательно, снижается производительность. Это массовая запись, такую таблицу можно было бы разделить на ежедневную запись и остальные данные с вечерними пакетами перемещения по ним.

• Все больше и больше людей переходят на CQRS. Возможно, вы следующий. Это решает проблему, отделяя чтение от записи (очень упрощенное объяснение).