Создание простых базовых показателей производительности с SQL Server Profiler

.

Совокупных значений вполне достаточно, а процесс их получения прост

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

К счастью, процесс получения метрик весьма тривиален. Я предлагаю решать эту задачу следующим образом:

• Откройте приложение SQL Server Profiler: Start, Programs, Microsoft SQL Server, Performance Tools, SQL Server Profiler.
• Создайте новую трассировку Trace: File, New Trace – и после этого подключитесь к серверу, для которого вы хотели бы создать «базовый показатель».
• Из шаблона Trace Properties задайте имя для своей трассировки (например, baseline или post index changes и т.д.), удостоверьтесь, что выбран предлагаемый по умолчанию шаблон трассировки Standard, и дайте команду сохранить трассировку в файл (будет работать где угодно), как показано на экране 1.

Экран 1. Создание новой трассировки

ПРЕДУПРЕЖДЕНИЕ: Profiler — очень мощный инструмент. Он постепенно вытесняется технологией расширенных событий, но пока остается привычным и достаточно простым в использовании. Однако при всех возможностях, предоставляемых данным инструментом, использовать его надо аккуратно. Так, каждый раз, когда вы включаете трассировку на сервере, на нем возникает дополнительный процесс обработки кода и дополнительная нагрузка (обычно называемая «эффектом наблюдателя»). В большинстве случаев предполагается, что использование решения Profiler повышает нагрузку на процессор на 1-3% и лишь «слегка» нагружает ваши диски в процессе записи в файл, так как профилировщик эффективно буферизует данные, записываемые на диск. Однако если вы чересчур активно работаете с наблюдаемыми/профилируемыми событиями (или захватываете событие, которое происходит несколько/десятки/сотни раз за один запрос, возможно возникновение ситуации, в которой решение Profiler будет быстро генерировать огромные объемы данных. Также я рекомендую никогда не использовать функцию Save to table — лишь потому, что слишком просто случайно сохранить данные трассировки в таблицу на профилируемом сервере, а это означает, что вы можете создать очень неприятную «обратную связь», которая может негативно повлиять на производительность.

На вкладке Events Selection снимите флажок со всех событий, кроме RPC: Completed и SQL: BatchCompleted (подразумевается, что вы хотите только наблюдать за пакетами T-SQL и вызовами удаленно выполняемых процедур RPC), как показано на экране 2.

Экран 2. Настройка трассировки

• Далее, щелкните мышью по кнопке Run.
• На сильно загруженных серверах в окне трассировки полетит шквал записей queries/sprocs. На менее загруженных серверах у вас будет возможность увидеть каждый вызов batch/rpc, который выполняется на сервере (точнее, на профилируемом экземпляре).
• Позвольте трассировке поработать какое-то время — сколько вы считаете целесообразным. На интенсивно используемых серверах этот период может составлять всего 5-10 минут. При этом просто помните, что когда необходимо будет собрать результаты, вы сможете выполнить ту же трассировку в течение примерно того же времени. Хотя, как мы сейчас увидим, фактическое количество времени действительно не имеет большого значения.
• Как только трассировка завершится (то есть после того, как она проработала достаточно долго и вы решили остановить ее), вы можете скопировать/вставить файл(ы). trc на другой сервер SQL Server (за исключением производственных), на котором сможете применить запросы непосредственно к файлам (не лучший вариант, с моей точки зрения). Или же вы можете сохранить результаты своей трассировки на другой сервер SQL Server в виде базы данных с именем test, dba или meddling. По сути, вы можете сохранять результаты и на производственный сервер, но я настоятельно рекомендую не сохранять что-либо не связанное с производственными задачами на производственный сервер. Так что при худшем варианте развития событий — если вам необходимо сохранить результаты на своем производственном сервере, — по крайней мере, создайте новую/временную базу данных, отличную от производственных баз, для сохранения этих временных данных, не позволяя им засорять пространство настоящих производственных баз данных. И чтобы сохранить данные из приложения Profiler, просто используйте пункты меню File, Save As, Trace Table (и укажите сервер, базу данных и имя таблицы, в которую хотели бы сохранить данные), см. экран 3.

Экран 3. Сохранение собранных данных

Расчет значений

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

WITH Trace (
[StartTime],[EndTime],[TotalMinutes],[TotalOperations],
[TotalCPU],[TotalReads],[TotalWrites],[TotalDuration])
AS (
SELECT
CONVERT(varchar(10), MIN(StartTime),8),
CONVERT(varchar(10), MAX(EndTime),8),
DATEDIFF(n,MIN(StartTime),MAX(EndTime)),
COUNT(RowNumber),
SUM(CPU),
SUM(Reads),
SUM(Writes),
SUM(Duration)
FROM
)
SELECT
StartTime [Start],
EndTime [End],
TotalMinutes [Total Mins],
TotalOperations [Total Ops.],
--TotalCPU,
--TotalReads,
--TotalWrites,
--TotalDuration,
CAST((CAST(TotalOperations as decimal(18,4)) / CAST(TotalMinutes as decimal(18,4))) as decimal(18,4)) [Ops / Min.],
CAST((CAST(ISNULL(TotalCPU,0) as decimal(18,4)) / CAST(TotalOperations as decimal(18,4))) as decimal(18,4)) [CPU / Op],
CAST((CAST(ISNULL(TotalReads,0) as decimal(18,4)) / CAST(TotalOperations as decimal(18,4))) as decimal(18,4)) [Reads / Op],
CAST((CAST(ISNULL(TotalWrites,0) as decimal(18,4)) / CAST(TotalOperations as decimal(18,4))) as decimal(18,4)) [Writes / Op],
CAST((CAST(ISNULL(TotalDuration,0) as decimal(18,4)) / CAST(TotalOperations as decimal(18,4))) as decimal(18,4)) [Duration / Op]
FROM
Trace;

Обратите внимание, что описанный выше запрос использует синтаксис T-SQL Template, чтобы задать имя таблицы, к которой вы хотите обратиться (то есть вы должны будете указать имя таблицы вместо «заглушки»).

Более того, вам не обязательно сохранять результаты трассировки, выполненной с помощью приложения Profiler, на сервер SQL Server, чтобы применить к ним запрос, поскольку вместо этого вы можете использовать функцию fn_trace_gettable(‘<укажите путь к файлу и его имя>.trc’), но мне кажется, что обычно задача сводится к чему-то большему, нежели просто сохранение результатов в таблицу на сервере SQL. Использование этой функции не одобряется — но такое же отношение существует по отношению к приложению Profiler.

В противном случае, как только вы рассчитаете значения, вы получите результаты в виде, показанном на экране 4.

Экран 4. Результаты трассировки

Разберемся с результатами

В целом при создании этих базовых показателей ключевой является идея о том, что средние значения представляют собой достаточно хороший индикатор общей производительности. Соответственно, если средние значения возрастают или убывают от одного замера к другому, вы можете выяснить, какое влияние, хорошее или плохое, с точки зрения производительности оказали последние изменения (то есть теоретически, если вы добавляете индексы или оптимизируете код, на выходе вы должны увидеть понижение средних показателей, но если для вашего приложения были выпущены новые функции или компоненты, а средние показатели операций чтения или записи или средняя продолжительность «подскакивают», то вы знаете, что было добавлено что-то нехорошее). Исходя из этого, основным параметром, который вы будете отслеживать от одной трассировки к другой, является значение Operations/Minute — так как оно может выступать в роли индикатора того, сравниваете ли вы яблоки с яблоками или яблоки и апельсины от одного исполнения до следующего (с точки зрения различных характеристик рабочей нагрузки, увеличения/уменьшения активности и так далее). Или, другими словами, если значения Operations/Minute находятся примерно в одном диапазоне, то можно смело предположить, что значения других метрик должны выстраиваться в линейную зависимость достаточно хорошо, что любые серьезные пики или скачки в показателях чтения, записи, загрузки процессора или продолжительности могут сигнализировать о наличии проблемы, тогда как любые заметные спады в этих значениях должны служить индикатором того, что ваши усилия, направленные на повышение производительности, оправдались (так как все значения более или менее соответствуют друг другу, если вы наблюдаете примерно одно и то же число исполнений в минуту, но при этом видите уменьшение значений показателей Reads и Duration (например), следовательно, выполненные операции более эффективны).

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