Вход |  Регистрация

Все Тэги

Online-курс “Основы APM”: Оптимизация производительности SQL сервера

18.11.2013928 просм.

Оптимизация буферного кэша

Почему так важно оптимизировать кэш? Да потому, что при выполнении любых запросов SQL сервер читает данные не из дисковой подсистемы, а из буферного кэша. Даже если необходимых данных там нет, сначала они будут загружены в кэш, а уж потом SQL их оттуда возьмет, а все это время запрос будет ожидать.

Обновления и удаления тоже производятся в кэше, а какое-то время спустя их выгрузят на диск. Такой механизм позволяет SQL серверу оптимизировать операции ввода/вывода:

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

Есть два индикатора работы буферного кэша:

MSSQL$Instance:Buffer Manager\Buffer cache hit ratio – соотношение запрошенных страниц, которые были найдены в кэше, к страницам, которых там не было (и которые пришлось загрузить с диска). Чем больше это соотношение, тем более эффективен кэш. Но следует учитывать, что даже при большом соотношении может присутствовать эффект перегрузки кэша (cache thrashing).

MSSQL$Instance:Buffer Manager\Page Life Expectancy – время жизни страниц в кэше. Считается, что если этот показатель больше пяти минут, то это нормально. Если значение ниже, то либо не хватает памяти, либо присутствует перегрузка кэша.

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

Если после анализа счетчиков появилось подозрение на перегрузку кэша, можно попытаться оптимизировать работу с кэшем. Чтобы понять, какие таблицы и индексы занимают много места в кэше, можно обратиться к sys.dm_os_buffer_descriptors.

SELECT o.name, i.name, bd.*
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.allocation_units a
ON bd.allocation_unit_id = a.allocation_unit_id
INNER JOIN
sys.partitions p
ON (a.container_id = p.hobt_id AND a.type IN (1, 3))
OR (a.container_id = p.partition_id AND a.type = 2)
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.indexes i
ON p.object_id = i.object_id AND p.index_id = i.index_id

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

Поиск «плохих» индексов

Для определения полезности индексов надо сперва понять, как сервер использует имеющиеся индексы. sys.dm_db_index_operational_stats содержит информацию о низкоуровневых операциях ввода/вывода, блокировках, обращениях для каждого индекса. Эту информацию можно использовать для ответа на вопросы:

  • Имеются ли популярные индексы? Есть ли борьба за них? Есть ли ожидание блокировок?
  • Имеется ли индекс, используемый неэффективно? Какие индексы приводят к узкому месту в работе с вводом/выводом? page_io_latch_wait_ms покажет, были ли ожидания загрузки страниц индекса в буферный кэш – хороший индикатор сканирования индексов.
  • Как применяются индексы, какие шаблоны доступа используются?

А с помощью sys.dm_db_index_usage_stats можно узнать, сколько и каких операций применялось к индексу и когда последний раз была применен определенный тип операции. Это поможет ответить на вопросы:

  • Как пользователи работают с индексом? Какие операции наиболее часто используются?
  • Как поддерживается и насколько часто обновляется индекс?
  • Когда индекс использовался в последний раз и для чего?

С помощью различных запросов можно получать и уточнять информацию об индексах и в конечном итоге получить кандидатов на оптимизацию. Причем оптимизировать можно и запросы, которые используют индекс – начиная с SQL сервера 2005, эта информация доступна для любого индекса.

Есть различные стратегии оптимизации – правильная архитектура, объединение индексов, изменение поддержания. Это один из самых сложных, но и самых продуктивных аспектов оптимизации, и гораздо более подробно освещен на соответствующих сайтах и в web-кастах.

Нельзя рассказать все и сразу, но предоставленная информация должна дать хороший старт любым инициативам в вопросах оптимизации производительности SQL сервера.

Команда ALG DevOps Team будет благодарна за Ваши ответы на следующие вопросы:


ALG_DevOps_Team