Перейти к содержанию

Диагностика БД

Рекомендованный инструмент диагностики базы данных Первой формы — SQL Server Management Studio, но можно использовать и другие инструменты, например, Azure Data Studio и т. п.

1. Запустите сервер управления БД. Для этого нажмите Пуск и в строке поиска введите "ssms".

Перейдите в БД, которая указана в режиме администрирования Первой формы. На рисунке ниже DB-2019.1forma.net — имя сервера, D10Task_hd — база данных.

Проверьте, что вы перешли на нужный сервер. В списке на рисунке ниже первая строка — имя сервера. Убедитесь, что нужная БД присутствует в списке.

Основная диагностика БД

Правой кнопкой нажмите на нужную БД и выберите Properties. В открывшемся окне нажмите Files. Проверьте размер базы и log10.

Перейдите в Options и проверьте, что в Compatibility levels отображается самый последний сервер.

Посмотрите свойства SQL сервера: нажмите правой кнопкой на сервер и выберите Properties.

В открывшемся окне перейдите в Processors и убедитесь, что памяти достаточно и с ЦПУ все в порядке.

Диагностика базовыми инструментами

Обычно в master ставятся утилиты Blitz. Чтобы выявить проблемы, которые могут возникать на инсталляции, перейдите в Database/System Database/master/Programmability/Stored Procedures/ и запустите три утилиты — dbo.sp.Blitz, dbo.sp.BlitzCash, dbo.sp.BlitzIndex.

Правой кнопкой нажмите на каждую утилиту, выберите Execute Procedure, в открывшемся окне установите нужные параметры и нажмите ОК для выполнения.

Для dbo.sp.Blitz параметры не устанавливаются.

Для dbo.sp.BlitzCash:

  • в параметре Top установите значение 50;

  • в параметре SortOrder установите значение cpu.

Эти параметры нужны, чтобы выдать топ 50 запросов, которые больше всего потребляют ЦПУ сервера БД.

Для dbo.sp.BlitzIndex:

  • в параметре DatabaseName установите значение d10Task; -- Укажите имя вашей базы данных (обычно D10Task или D10Task_HD)

  • в параметре Mode установите значение 4.

Базовая проверка работы SQL сервера (Blitz)

Вернитесь на вкладку dbo.sp.Blitz. В таблице Results выведен список результатов проверки работы сервера утилитой:

  • в колонке Finding отображаются результаты проверки;

  • в колонке Details — дополнительная информация по проверке;

  • в колонке URL — ссылка на страницу в интернете, где есть информация, почему утилита считает это проблемой.

Самые значимые проверки всегда находятся вверху таблицы. К критичным проблемам, влияющим на работоспособность инсталляции, относятся отсутствие backup и блокировки.

ℹ️ Внимание! При обнаружении этих проблем необходимо обратиться в тех.поддержку

Проверка работы SQL сервера и процессорной загрузки (BlitzCash)

Откройте вкладку dbo.sp.BlitzCash. В нижней таблице выведен список результатов выполнения утилиты. В результатах можно увидеть количество планов в кеше (выделено курсором на рисунке ниже), посмотреть рекомендации.

В верхней таблице Results выведены топ 50 запросов. Отображается статистическая информация, какую нагрузку каждый их этих запросов дал на SQL сервер:

  • в колонке Query Text отображается текст запроса;

  • в колонке Query Type — что это за процедура;

  • в колонке Query Plan — план запроса;

  • в колонке Missing Indexes — есть ли отсутствующие индексы.

Для примера можно подробно рассмотреть статистику о Show Task Feed — базовой процедуре запроса списка задач:

  • # Executions — всего было 44187 запусков;

  • Executions / Minute — в среднем процедура запускается 19 раз в минуту;

  • Avg Duration — среднее время отдачи списка задач категории 188 миллисекунд, что является хорошим результатом;

  • Created At — дата и время создания;

  • Last Completion — дата и время последнего выполнения.

ℹ️ Всегда обращайте внимание на Avg Duration. Для разных типов запросов существует свое приемлемое время.

Пример диагностики и устранения недочетов на основании выявленных в кеше планов запросов

Запрос With TName (Lookup) выполнялся 52 раза со средней длительностью 223 миллисекунды. Утилита считает, что нужно добавить какой-то индекс (колонка Missing Indexes).

Для исправления проблемы необходимо выполнить следующие действия:

1. В колонке Query Plan нажмите на план запроса.

2. В открывшейся вкладке с планом нажмите правую кнопку и выберите Edit Query Text…

3. В открывшейся вкладке выберите нужную базу данных в панели инструментов и добавьте нужные строки в запрос.

Если непонятно, какие параметры добавлять: вернитесь в таблицу с результатами, и в строке с запросом нажмите на значении в колонке Cached Execution Parameters.

Откроется вкладка с параметрами, которые были переданы. Скопируйте переданные параметры.

Скопированные параметры пропишите в план запроса, затем удалите лишние строки.

4. Нажмите правую кнопку и выберите Include Actual Execution Plan.

5. На панели инструментов нажмите Execute.

6. Убедитесь, что все корректно: выборка 50 значений отсортированы по полю t.Text и отдаваемое в Lookup-поле значение доступно для переданного пользователя.

Обратите внимание, что необязательно все 52 запуска были именно с этими параметрами. В таблице с выборкой 50 запросов отображается план запроса, который закешировался для определенного ID пользователя.

Ключевой инструмент планов запросов — сам план. Обратите внимание, что:

  • высокий процент Cost — возможные проблемы;

  • Clustered Index Scan — медленно (перебор всех значений в таблице);

  • Clustered Index Seek — быстро (поиск по ключу нужного значения).

Можно навестись на любой узел плана курсором и получить подробную информацию. Параметр Actual Number of Rows Read — количество строк, которое нужно было прочитать, чтобы получить этот Clustered Index. Если записей много, то время чтения будет долгим. Также посмотрите параметр Estimated Operator Cost и сравните этот процент с величиной процента всего запроса.

Сканирование кластерного индекса

Чтобы разобраться со сканированием кластерного индекса, посмотрите на 16-ю категорию.

ℹ️ Приведённые значения (категория 16, пользователь 4790) — учебный пример. Замените на актуальные значения вашей системы.

1. Перейдите в Databases/D10Task/, нажмите правой кнопкой на Tables и выберите Filtes Settings. В открывшемся окне установите значение Name — f16d и нажмите ОК.

2. Раскройте отфильтрованную категорию D10Task и перейдите в денормализованную таблицу. Посмотрите на Columns/Task Text и в Indexes/ на отсутствие какого-либо индекса по слову TaskText.

Проиндексировать можно любое поле, если его длина меньше 867. Индекс нужен для того, чтобы при сканировании кластерного индекса использовался Seek, а не Scan.

Как ограничить длину поля скриптом и добавить индекс для увеличения быстродействия

Проверьте, какая максимальная длина контента, который лежит в поле TaskText в 16-й категории. Если длина меньше 867:

Откройте скрипт Lookup_TaskLeanth из инструкции, запустите его первый кусок и посмотрите по 16 и 17 категориям максимальную длину текста и количество задач. Затем выделите колонку со скриптами, нажмите правую кнопку и выберите Copy. Нажмите на панели инструментов New Query и вставьте в открывшуюся вкладку скопированные скрипты. Проверьте, что выполняется по этим скриптам. Максимальная длина поля TaskText должна быть установлена 200 и должен создаваться соответствующий индекс.

Нажмите Execute

Проверка результатов

1. После выполнения Execute обновите значения правой части интерфейса и проверьте в 16 категории, что:

В денормализованной таблице Columns Task Text имеет длину 200;

В индексах появился нужный индекс по Task Text.

2. Перейдите в запрос, выполните его и откройте Execution Plan. Убедитесь, что Cost уменьшился до 16, Index Scan теперь по специфическому индексу и значения Actual Number of Rows Read и Estimated Operator Cost существенно уменьшились.

Итак, вы ограничили длину поля до 200, добавили соответствующий индекс, и если включить Client Statistics, то в статистике вы увидите, что вы в 4 раза ускорили отдачу данных для лукапа.

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

Проверка небольших запросов

Пропишите в запросе в строке @SortOrder вместо "cpu" — "executions".

После запуска скрипта он выдаст ТОР50 запросов, какими бы легковесными они ни были. Часто бывает, что даже маленькие запросы могут создавать существенную процессорную нагрузку на сервере БД.

Например, какой-то запрос выполняется 1467 раз в минуту (столбец Execute), это очень много. Чтобы оценить степень критичности, посмотрите в таблице, сколько это занимает в процентах от общей нагрузки на сервер БД (столбец CPU Weight).

Базовая проверка индексов в БД (BlitzIndex)

1. Откройте вкладку dbo.sp.BlitzIndex.

2. В таблице Results в колонке Finding обратите внимание на индексы, начинающиеся с "Agressive". В колонке Details проверьте, какая проблема.

3. Для проверки на Missing Index установите значение в параметре Mode\=3.

4. Бывает, что с помощью Blitz отсутствующие индексы не находятся. В этом случае можно воспользоваться какой-либо утилитой, которая ищет все потребности в отсутствующих индексах, включая те, которые Blitz считает нерелевантными.

5. Выполните такой скрипт и оцените получившийся результат. Выдана одна индексная рекомендация.

6. Нажмите на значение в колонке query_plan, чтобы открыть план запроса. Зелёной строкой описан запрос и рекомендация по индексу: если добавить указанный индекс, Cost снизится на 66%. Cost — параметр, характеризующий стоимость выполнения запроса: время, дисковые операции и т.д. Высокое значение Cost сигнализирует о наличии проблем.

Также можно нажать на зеленую строку и в открывшейся вкладке по правой кнопке выбрать Missing Index Details… В открывшейся вкладке в скрипте заполните название индекса.

ℹ️ В платформенной таблице вносить изменения нельзя, обратитесь в техническую поддержку

Использование XEvent Profiler

В SQL Server Management Studio есть инструмент XEvent Profiler, который можно использовать для поиска и анализа запросов и воспроизведения полученных результатов.

ℹ️ SQL Server Profiler для этих целей использовать не рекомендуется

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

1. Нажмите правую кнопку и выберите Посмотреть код.

2. Обновите список задач.

3. На вкладке Network посмотрите значение в колонке Time. Если значение не устраивает, вам нужно посмотреть, какой запрос выполняется при открытии списка задач. Для этого:

  • в адресной строке посмотрите ID категории;

  • перейдите на страницу своего профиля и в адресной строке посмотрите ID пользователя.

4. Откройте XEvent и установите нужные фильтры.

5. На панели инструментов XEvent нажмите кнопку Start Data Filter.

  • откройте грид под нужным пользователем;

  • остановите поиск в XEvent (Stop Data Filter);

  • посмотрите каждый из полученных вызовов;

  • скопируйте первый полученный вызов и вставьте его в новый запрос;

  • нажмите Execute и посмотрите отдачу;

  • повторите с каждым вызовом

6. Оцените найденные проблемы и решите их сами или передайте в службу техподдержки.

Работа с XEvent Profiler на примере с запросом With TName

1. Перейдите на вкладку с запросом по Lookup-полю. Работа с запросом описывалась в пункте Проверка работы SQL сервера и процессорной загрузки (BlitzCash).

2. Скопируйте строку из запроса.

3. Запустите XEvent Profiler двойным кликом на Standard.

4. На панели инструментов нажмите Filters…, в модальном окне выберите нужные фильтры:

  • Field — [TextData];

  • Operator — Contains;

  • Value — Demormalized [Task Text]

5. Нажмите ОК.

6. Выберите запись, нажмите правой кнопкой на колонку [TextData] и выберите Copy – Cell.

7. На панели инструментов нажмите New Query и вставьте скопированный запрос.

8. Нажмите правую кнопку и запустите Include Actual Execution Plan, а затем Include Live Query Statistics.

9. Во вкладке Query Statistics проверьте Client Execution Time, перейдите в Execution Plans и посмотрите по правой кнопке Index Scan, а затем перейдите в Results.

10. Убедитесь, что добавленный вами индекс по Task Text user 4790 помог получить быструю отдачу Lookup.

Заключение

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

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