Примеры с SQL

Время от времени натыкаюсь в нашей базе данных на MS SQL сервере на разные интересные моменты. Представим себе таблицу на почти 5 млн. строк, например, с заказами. Вся таблица нас не интересует, возьмём только поле Статус заказа.

Внимание вопрос: насколько и в каких случаях будет эффективным индекс по полю со следующим распределением значений:

-- пример запроса для получения такого распределения
select
       r.[Status],
       count(*) as cnt
from dbo.[TableOrder] r
group by r.[Status]  

Думаю, что не особо и только в очень частном случае.

Или представим себе таблицу на пару десятков млн. строк с индексом по трём битовым полям (да-да именно битовым, то есть максимум три значения у каждого).

Тут, как говорится, вообще без комментариев.

Предлагаю запрос, который отображает запросы с MS SQL-сервера, которые потребляют память в текущий момент (при выводе информации применяется фильтр, отсекающий запросы, потребляющие менее 500 МБ). Иногда с его помощью удаётся найти неэффективные запросы, которые следует рассмотреть повнимательнее и, возможно, оптимизировать или переписать.

Кратко приведу информацию о колонках:

• Данные о пользователе (имя и хост)
• Время запроса (request_time) и выделения (grant_time) памяти
• Объем запрошенной (requested_memory_kb) и выделенной (granted_memory_kb) памяти в килобайтах
• Информация по использованию памяти: сколько потребовалось (required_memory_kb), было использовано (used_memory_kb) и максимальное значение (max_used_memory_kb)
• Коэффициент "стоимости" запроса (query_cost)
• Время ожидания (wait_time_ms)
• Сам выполняющийся запрос (query_text)
• Информация о выполняемом запросе (event_info)
• Ссылка на план выполнения запроса (query_plan)

-- запросы в текущее время потребляющие память
SELECT mg.session_id,
       CAST(es.nt_user_name as varchar) as nt_user_name,
       CAST(es.host_name as varchar) as host_name,
       mg.request_time,
       mg.grant_time,
       mg.requested_memory_kb,
       mg.granted_memory_kb,
       mg.required_memory_kb,
       mg.used_memory_kb,
       mg.max_used_memory_kb,
       mg.query_cost,
       mg.resource_semaphore_id,
       mg.wait_time_ms,
       st.[text] as query_text,
       ib.event_info,
       qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
     INNER JOIN sys.dm_exec_sessions AS es
                ON es.session_id = mg.session_id
     CROSS APPLY sys.dm_exec_input_buffer(mg.session_id, NULL) AS ib
     CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
WHERE mg.requested_memory_kb > 500000
ORDER BY mg.requested_memory_kb DESC

С помощью этого запроса обнаружили у себя запросы с аномальным потреблением памяти (10Гб, 20Гб и даже 40Гб).

Возможно, многие сталкивались с исторически сложившейся за годы, до появления на проекте, ситуацией, когда на таблице создали все возможные индексы со всеми include’ами. Я видела индекс на доставшейся «в наследство» БД, который содержал все поля таблицы. При этом, не всегда есть возможность быстро поменять индексы, так как часто нужна гарантия, что изменения не повлияют на работоспособность системы.

https://habrahabr.ru/post/344284/

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

Рекомендую к прочтению статью Разреженные столбцы или sparse columns в MS SQL Server. Реальный опыт применения. В статье подробно описывается разбор проблемы с производительностью базы данных. Последовательно приводится анализ текущей ситуации, вариантов решения и описание преимуществ, ограничений и недостатков выбранного решения - использования разреженных столбцов (sparse columns).

Наткнулся на серию статей по анализу работы MS SQL Server.
Первая часть мне не очень понравилась, а вот вторую считаю вполне полезной:

• Анализ конкретного запроса - план выполнения, затраты процессора (SET STATISTICS TIME ON), дисковые операции сервера (SET STATISTICS IO ON), объем сетевого трафика.
• Анализ нагрузки от приложения - profiler, ключевые (Stored Procedures RPC:Completed, TSQL SQL:BatchCompleted - все внешние sql-вызовы) и просто полезные события.
• Анализ активности пользователей в целом по серверу - информация о сессиях (sys.dm_exec_sessions), запросы выполняющиеся в данный момент (sys.dm_exec_requests), сводная статистика (sys.dm_exec_query_stats).

Коллега по работе прислал ссылку на подборку статей (к сожалению, на английском языке), в которых приводились аргументы, почему не надо на MS SQL сервере делать сжатие (Shrink) баз данных, а тем более регулярно в автоматическом режиме.

Приведённые примеры показались убедительными и сжатие мы отключили, а заодно и поигрались с настройками файлов (добавили свободного места, изменили правила приращения).

Теперь в каждом файле основной базы данных (мы крупные связанные части базы данных выносим в отдельные файлы) у нас свободно места на полгода-год (учитывая прогнозируемую скорость роста), а также увеличение размера (приращение) файлов идёт по 512-1024МБ (про проценты лучше сразу забыть).

Интересная статья о хранении данных и кластерном индексе в MSSQL. Приведу здесь только алгоритм выбора индекса на роль кластерного:

Предлагается следующий алгоритм выбора:

1. Определить все индексы, по которым происходит поиск одиночного значения. Если такой индекс единственный – его и нужно кластеризовать. Если несколько – перейти к следующему шагу.
2. Добавить к индексам с предыдущего шага все индексы, по которым предполагается сканирование диапазонов. Если таковых нет – кластеризованный индекс не нужен, несколько индексов на куче будут работать лучше. Если есть – каждый из них следует сделать покрывающим, добавив все столбцы, которые нужны сканирующим запросам по этому индексу. Если такой индекс единственный – его следует кластеризовать. Если их больше одного – перейти к следующему шагу.
3. Однозначно лучшего выбора кандидата на кластеризацию среди всех покрывающих индексов нет. Следует кластеризовать какой-то из этих индексов, принимая во внимание следующее:
   • Длина ключа. Ключ кластеризованного индекса является ссылкой на строку и хранится на листьевом уровне некластеризованного индекса. Меньшая длина ключа означает меньше места на хранение и более высокую производительность.
   • Степень покрытия. Кластеризованный индекс содержит все поля «бесплатно», и покрывающий индекс с самым большим набором полей – хороший кандидат на кластеризацию.
   • Частота использования. Поиск одиночного значения в покрывающем индексе – самый быстрый возможный поиск, а кластеризованный индекс – покрывающий для любого запроса.

https://habrahabr.ru/post/188704/