MS profiler

Фабиано Аморим (https://blogfabiano.com/) рассказывает о работе оптимизатора запросов (Query Optimizer) MS SQL Server. Прочтение навсегда изменит ваши представления о статистике, планах выполнения и оптимизаторе запросов, и мы думаем, что вы поймете их работу лучше.

Вы не считаете, что тринадцать — это хороший номер? Хорошо, здесь, в Бразилии, у нас есть известный футболист и тренер по имени Марио Загалло, которому он очень нравится. Да, это явно не имеет отношения к Оптимизатору запросов, поэтому давайте начнем.

Когда мы говорим о планах запроса и оптимизации, есть некоторые вещи, которые вы должны знать. Оптимизатор запросов MS SQL Server использует множество методов для создания оптимального плана выполнения, и сегодня я расскажу о некоторых терминах и понятиях.

1. Селективность (Selectivity)

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

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

В терминах базы данных предположим, что у нас есть таблица под названием Internationally_Renown_Players со столбцом Country. Если мы напишем

SELECT * FROM Internationally_Renown_Players WHERE Country = ‘Бразилия’

… мы можем сказать, что этот запрос не очень селективен, потому что он вернет много строк (к сожалению, это не помогло нам в чемпионате мира 2010 года). Однако если мы напишем

SELECT * FROM Internationally_Renown_Players WHERE Location = ‘Tuvalu’

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

Другим хорошим примером может быть таблица под названием «Клиенты» с столбцами «Пол» и, скажем, «Номер_Паспорта». Селективность столбца «Пол» очень мала, потому что мы не можем создать узкий критерий отбора только с помощью значений «М» и «Ж», и поэтому в результате любого запроса будет возвращено много строк. Напротив, фильтрация запросов по полю «Номер_Паспорта» всегда будет возвращать только одну строку, и поэтому селективность этого столбца очень высока.

Почему вы должны это знать

Оптимизатор запросов использует информацию о селективности для создания планов выполнения и может выбрать оптимальный план, основанный на селективности данного столбца. Очевидно, что хорошей практикой является создание индексов в полях с самым высоким уровнем селективности. Это означает, что лучше создать индекс в поле «Имя», чем в поле «Пол». Более того, лучше создать составной индекс, чтобы сначала использовать наиболее селективное поле и тем самым увеличить вероятность того, что MS SQL Server будет использовать этот индекс.

Плотность (Density)

Термин «плотность» исходит из физики и рассчитывается путем деления массы вещества на объем, который он занимает, как это представлено ниже.

D = m/V , где D = плотность, M = масса и V = объем.

Понятие «плотность» может быть более широким, как в случае с «Плотностью населения». Например, плотность населения Бразилии вычисляется путем деления числа жителей (или «массы») на размер географического района («объем»), который составляет 187 000 000 человек, разделенных на 8 514 213,3 км2, что дает нам 21,96 жителей на км2.

В MS SQL Server мы можем понимать это так: чем поле «плотнее», тем больше строк оно вернёт при выполнении запроса.

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

SELECT (1.0 / COUNT (DISTINCT <ColumnName>)) FROM <TableName>

Чем большее число вернет запрос, тем больше плотность вашего поля, тем больше дубликатов оно содержит.

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

У нас получилась таблица с 25 000 строк, и каждое значение дублируется в пяти строках; поэтому плотность определяется как 1,0 / 5000 = 0,0002.

И так, первый вопрос, на который мы можем ответить, используя нашу таблицу, это: «сколько уникальных значений у нас есть в столбце Col1?» Используя данные о плотности, мы можем сделать следующее: 1,0 / 0,0002 = 5000.

Второй вопрос: каково среднее число дубликатов на значение в столбце Col1?» Мы можем это вычислить: 0,0002 * 25000 = 5.

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

DECLARE @I INT
SET @I = 999999
SELECT * FROM Test WHERE Col1 = @I

SQL сервер использует информацию о средней плотности для оценки того, сколько строк будет возвращено (в данном случае 5 строк). Больше о том, для чего нужна информация о плотности, мы узнаем в разделе 7.

Другой пример:

SELECT COUNT(*) FROM Test
GROUP BY Col1

На первом шаге согласно оценке оператора Table Scan будет возвращено 25 000 строк, а после того, как оператор Hash Match применит Group By, вернется только 5000 строк. Здесь SQL Server использует информацию о том, сколько уникальных строк находится в столбце.

Информация о плотности используется оптимизатором запросов для понимания количества дубликатов строк в столбце, что позволяет ему выбирать оптимальный оператор или алгоритм соединения в плане выполнения. В некоторых случаях Query Optimizer (QO) также использует информацию о плотности для предсказания, сколько строк будет возвращено запросом. Более подробно мы рассмотрим это в разделе 5.

Кардинальность (Сardinality)

Это понятие используется для определения количества строк, удовлетворяющих конкретному условию. Например, представьте одну таблицу с 500 строками и запрос с условием «…WHERE NAME = ‘Joao’» — оптимизатор запросов обращается к статистике и читает с гистограммы, что «Joao» представляет 5 % таблицы, поэтому кардинальность 5 % x 500 = 25. В контексте плана выполнения мы можем понимать кардинальность, как «оценочное числе строк», отображаемое в подсказках инструмента для каждого оператора. Стоит ли говорить, что плохая оценка кардинальности может легко привести к неэффективному плану.

Почему вы должны знать это

На мой взгляд, кардинальность столбца является одной из наиболее важных составляющих информации для создания эффективного плана выполнения. Чтобы создать план выполнения, нам действительно нужно знать (или, по крайней мере, иметь возможность выполнить адекватную оценку), сколько строк будет возвращено каждому оператору. Например, запрос соединения, который возвращает много строк, будет лучше обрабатываться с хеш-соединением; но если QO не знает кардинальность, он может ошибочно принять решение использовать Loop Join или, что еще хуже, выбрать, чтобы ключевые столбцы использовали объединение слияния!

Search Arguments (SARG)

SARG (поисковые аргументы) — это условия вашего запроса. Значения, которое вы включаете в WHERE = «…», — это аргументы, переданные поиску. Исходя из них, оптимизатор будет оценивать, сколько строк должно быть возвращено и как лучше всего прочесть эти данные. Другими словами, на основе SARG оптимизатор анализирует селективность, плотность и кардинальность, чтобы получить наилучший возможный план выполнения.

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

Во многих книгах по SQL Server и статьях про Оптимизатор запросов используются описанные выше четыре основных термина (селективность, плотность, кардинальность и SARG), чтобы объяснить построение плана запроса. Надеюсь, этот материал сделает их для вас немного более понятными.

Сворачиваемые выражения (Foldable Expressions)

В начале фазы оптимизации QO пытается изменить ваш запрос, чтобы оценить выражение, а затем преобразовать это выражение в константу. Константа — это буквальное значение, такое как «1», «Peter», «10,50», «100500» и т. д. Этот метод известен как «сворачивание констант».

Запросы станет легче писать, если вы будете знать, где Query Optimizer может свернуть выражение. Также стоит иметь в виду, что для больших типов объектов сделано исключение. Если результатом вывода предполагается Large Object (текст, изображение, nvarchar (max), varchar (max) или varbinary (max)), то SQL Server выражение не сворачивает.

Несворачиваемые выражения (Non-foldable Expressions)

Выражение считается «несворачиваемым», когда SQL Server не может получить его значение, что приводит к неверной оценке кардинальности и не оптимальному плану выполнения. В разделе 7 мы увидим несколько примеров, когда использование некоторых выражений блокирует использование статистики для столбца.

Главным образом для того, чтобы избегать использования подобных конструкций в вашем запросе, так как они усложняют вам жизнь. Чтобы избежать этого, вместо использования, скажем, ABS (-1000), вы можете использовать (-1000 * -1) или передать выражение в виде переменной, а затем использовать эту переменную с оператором RECOMPILE подобным образом:

DECLARE @Var INT
SET @Var = ABS(-1000)
SELECT * FROM CONVE002 WHERE ID_Pessoa < @Var OPTION(RECOMPILE))

Что происходит, когда оптимизатор запросов не может оценить кардинальность SARG?

Кардинальность, плотность и селективность являются ключевыми параметрами, и Query Optimizer всегда будет пытаться использовать эту информацию в процессе построения плана выполнения. Когда это возможно, на этапе оптимизации QO пытается упростить ваши аргументы поиска, переведя их в константы. Мы уже рассмотрели этот в разделе 5 и знаем, что:

— на этапе оптимизации QO изменяет выражения вида WHERE Value = 1 + 1 на WHERE Value = 2, и с этим рассчитанным значением он пытается прочитать статистику, чтобы получить информацию, необходимую для создания хорошего плана выполнения.

— QO изменяет выражения вида WHERE <column> = REPLACE (‘123XXX’, ‘XXX’, ‘456’) на WHERE <column> = ‘123456’

Но иногда Query Optimizer по какой-то причине не может распознать значение аргумента поиска. Вот несколько таких случаев:

1. Когда используется функция. Будьте внимательны, потому что некоторые функции препятствуют оценке QO. Нижеперечисленные функции используются Оптимизатором запросов для преобразования констант в литералы, но если вы используете иную функцию, вы должны запустить тест, чтобы точно понять, что QO собирается сделать:
Lower, Upper, RTrim, Datediff, Dateadd, Datepart, Substring, Charindex, Len, SUser_SName, IsNull, GetDate, GetUTCDate, Replace, Left, Right.

Что я имею в виду:

WHERE <column> = ROUND (123.4545, 2): приводит к плохой оценке кардинальности.

WHERE <column> = LEN (88888888): приводит к хорошей оценке кардинальности.

* — разумеется, данный способ имеет весьма ограниченное применение. Здесь он приводится в качестве упрощенного примера (прим. переводчика).

2. Когда вы используете локальные переменные, как например:

DECLARE @I INT
SELECT * FROM <TABLE> WHERE <COLUMN> = @I

3. Когда вы используете скалярные пользовательские функции.

SELECT * FROM <TABLE> WHERE <COLUMN> = dbo.fn_FormatText (‘Текст’)

Когда оптимизатор не может определить кардинальность, он использует функцию «Magic Density» (мне нравится именно это название), которая более известна под названием «Угадай» (Guess). Другими словами, он попытается предсказать, сколько строк будет возвращено, и для этого он использует плотность одного столбца или жестко запрограммированный процент.

Давайте рассмотрим некоторые примеры того, как реализовано это «волшебство» в SQL Server 2005:

для «=», используется COUNT(*) × ПлотностьCтолбца, то есть COUNT(*) × (1.0 / (COUNT (DISTINCT <column>)))
SELECT * FROM <TABLE> WHERE <COLUMN> = @I

для «BETWEEN», берется 9%: (COUNT(*) × 9.0) / 100
SELECT * FROM <TABLE> WHERE <COLUMN> BETWEEN @I AND @X

для «>, >=, < and <=», берется 30 %: (COUNT(*) × 30.0) / 100
SELECT * FROM <TABLE> WHERE <COLUMN> «>, >=, < and <=» @I

Примечание: если используется оператор равенства и статистика для столбца отсутствует, это означает, что SQL не может знать плотность столбца, поэтому он использует фиксированное 10-процентное значение.

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

Обнаружение противоречий (Contradiction Detection)

Обнаружение противоречий — это механизм оптимизации, который распознает, когда запрос написан таким образом, что он никогда не будет возвращать какие-либо строки вообще. Когда QO идентифицирует такой запрос, он переписывает его, чтобы удалить ту часть, в которой содержится противоречие. Вот наглядный пример такого противоречия:

SELECT * FROM Tab1 WHERE Tab1_Col1 BETWEEN 10 AND 0
OPTION(RECOMPILE)

Посмотрев на план, вы увидите, что он не осуществляет доступ к таблице Tab1; вместо этого в плане используется оператор Constant Scan. Я так же использую директиву RECOMPILE, чтобы заставить SQL сервер не использовать параметризацию, потому что это может помешать выявлению противоречия и, следовательно, оптимизация не состоится.

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

Внешние ключи (Foreign Keys)

Оптимизатор запросов достаточно умён, чтобы определить, когда запрос обращается к таблице, к которой доступ не требуется. Например, если у меня есть таблица Customers (Клиенты) и таблица Orders (Заказы), внешний ключ Orders → Customers говорит мне, что каждый Заказ имеет Клиента. Поэтому, если у меня есть запрос с применением соединения с таблицами Customers и Orders, возможно, мне не нужно читать все столбцы таблицы. Следующий код показывает, это поведение:

Отлично! Теперь в следующем запросе план выполнения не будет использовать таблицу Tab1:

SELECT * FROM Tab2
INNER JOIN Tab1
ON Tab1.Tab1_Col1 = Tab2.Tab1_Col1

Как мы видим, оптимизатор запросов создает план выполнения с обращением только к таблице Tab2, потому что у меня есть внешний ключ (fk), который гарантирует, что все строки Tab2 уже связаны с соответствующей строкой Tab1.

Имейте в виду, что внешний ключ должен быть надежным. У вас может быть ненадежный внешний ключ и в этом случае QO будет читать обе таблицы. Вы можете проверить, надежный ли ваш fk, обратившись к таблице sys.foreign_keys, поле is_not_trusted которой сообщит нам, является ли fk надежным или нет.

Это, безусловно, очень крутая функция, но, на мой взгляд, существует множество способов ее улучшения. Например, QO будет использовать это поведение только в том случае, если ваш внешний ключ одноколоночный. Если у вас есть многоколоночный внешний ключ, Оптимизатор будет читать обе таблицы. Я отправил электронное письмо моему наставнику и большому другу Конору Каннингему с описанием некоторых моментов, которые, на мой взгляд, могут быть улучшены. Он пишет об этом в своем блоге, так что вот вам еще к прочтению, если вы хотите узнать больше.

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

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

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

Проверка ограничений (Constraints)

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

Query Optimizer знает, что строка со значением «X» просто не может существовать в столбце Status, поэтому он создает план, который вообще не осуществляет доступ к таблице.

Если у вас их еще нет, обязательно создайте ограничения и убедитесь, что они надежны. Как и при использовании внешних ключей, QO будет использовать эту информацию только в том случае, если ограничения надежны, и вы можете проверить это в sys.check_constraints. Еще один интересный момент, касающийся параметризации: обратите внимание, что я использовал оператор RECOMPILE, чтобы заставить SQL Server не использовать параметризацию, поскольку, как мы уже знаем, это может препятствовать обнаружению противоречия.

Апдейт – не апдейт

Теперь еще одна очень интересная тема. Вы никогда не спрашиваете себя, что происходит, когда вы обновляете столбец с тем же значением, что и раньше? Как известно, когда столбец принадлежит индексу, вместе с ним мы должны обновлять эти данные по кластерному индексу и всем некластерным индексам, которые используют столбец. И это именно тот случай, когда вступает в игру оптимизация «обновления без обновления».

Начиная с SQL Server 2005, у QO существует оптимизация, которая проверяет, изменилось ли значение в некластерном индексе перед его обновлением. Почему это важно? Потому что некластерные индексы очень дороги и их стоимость возрастает экспоненциально в зависимости от того, сколько некластерных индексов ссылается на данный столбец.

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

Давайте рассмотрим несколько примеров:

IF OBJECT_ID(‘Tab1’) IS NOT NULL
BEGIN
DROP TABLE Tab1
END
GO

Представление sys.dm_db_index_operational_stats показывает нам количество вставок, которые появляются в индексе ix_Test.

Теперь давайте сделаем обновление в столбце Tab1_Col2, который имеет некластерный индекс:

UPDATE Tab1 SET Tab1_Col2 = ‘ABC’

Теперь у нас есть два значения, вставленные в некластерный индекс, давайте сделаем обновление, используя одно и то же значение «ABC»:

UPDATE Tab1 SET Tab1_Col2 = ‘ABC’

Вы можете запускать обновление сколько угодно — QO достаточно умен, чтобы идентифицировать значение «ABC» как такое же, что хранится в некластерном индексе; он выявляет отсутствие изменений и избегает обновления.

В книге «Внутренние свойства SQL Server 2008» Кэйлен Делэйни (Kalen Delaney. «SQL Server 2008 Internals», 2009) озвучена интересная точка зрения относительно данной оптимизации. Многие базы данных имеют общую парадигму осуществления обновления данных, и если я делаю обновление, динамически используя только те строки, которые были изменены, у меня будет по одному плану запроса для каждого обновляемого столбца.

Чтобы избежать подобной неэффективности, мы можем написать один запрос, используя все столбцы, передавать значения столбца в качестве параметров (что является обычным способом) и тот же план будет использован повторно. Но если мы сделаем всего лишь одно обновление, используя все столбцы таблицы (даже те столбцы, которые не изменились), мы можем столкнуться с проблемой, когда дело доходит до обновления всех некластерных индексов в ситуации, когда в действительности ничего не изменилось. Знание этих особенностей может помочь избежать «грубой неэффективности».

RowModCtr

В таблице sysindexes есть поле RowModCtr, значение которого показывает сколько изменений было внесено в столбец или таблицу с момента последнего обновления статистики. В SQL Server 2005, когда INSERT, DELETE или UPDATE происходит в столбце, по которому строится статистика, SQL Server обновляет это значение в таблице sysindexes. В SQL Server 2000 этот столбец обновляется, когда события происходят на уровне таблицы, а не на уровне столбца. В обоих версиях, когда статистика обновляется, столбец RowModCtr сбрасывается до нуля.

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

Когда запускается автоматическое обновление статистики?

Каждый раз, когда столбец, по которому строится статистика, получает достаточное количество модификаций, MS SQL Server запускает автоматическое обновление статистики, чтобы поддержать её актуальность. Это работает следующим образом:
— если кардинальность таблицы меньше шести, а таблица находится в базе данных tempdb, автоматическое обновление наступает после каждых шести изменений в таблице;
— если кардинальность таблицы больше 6, но меньше или равна 500, обновление происходит через каждые 500 модификаций;
— если кардинальность таблицы превышает 500, обновление через каждые (500+20 % количества строк) изменений.

Для табличных переменных изменение кардинальности не вызывает автоматическое обновление статистики.

Как я могу узнать, что началось автоматическое обновление? Вот несколько способов:

— если параметр rowmodctr равен нулю, это означает, что автообновление только запустилось или таблица пуста.

— в Профайлере, в столбцах SP: StmtCompleted и SP: StmtStarting если автообновление статистики работает, вы увидите подобный код:

— вы можете включить флаг трассировки 205 или 8721 и SQL Server будет записывать информацию в журнал ошибок.

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

В данной теме я хочу поговорить об очень полезном инструменте — SQL Server Profiler.

Как описано на MSDN, приложение SQL Server Profiler — это графический пользовательский интерфейс для трассировки SQL, с помощью которого можно наблюдать за экземпляром компонента Database Engine. Приложение позволяет собирать и сохранять данные о каждом событии в файле или в таблице для последующего анализа. Данное приложение представляет исключительную важность в задачах анализа производительности исполняемых запросов, а также при анализе проблем параллельности работы в базе данных.

На текущий момент Microsoft продвигает другой аналогичный инструмент — Extended Events и рекомендует пользоваться им, тем не менее я считаю полезным уметь работать и с инструментом Profiler.

Настройка приложения

В профайлере, начиная с версии 2005, в настройках приложения присутствует флажок «Показывать значения в столбце «Продолжительность» в микросекундах» (Show values in Duration column in microseconds). Данный флажок управляет как отображением значения в соответствующей колонке, так и значением, устанавливаемым для отбора по данной колонке. На мой взгляд, при работе с Profiler удобнее использовать микросекунды, поэтому советую данный флажок установить. Настройка находится в меню Сервис (Tools) → Параметры (Options).

Общие параметры

Запуск трассировки в Profiler

Для того чтобы запустить новую трассировку в Profiler необходимо:

  1. Открыть приложение SQL Server Profiler
  2. Выбрать пункт основного меню «Файл» (File), в нем «Создать трассировку» (New Trace)
  3. В открывшемся диалоге подключиться к нужному экземпляру SQL Server
  4. В открывшемся окне настроить трассировку
  5. Запустить трассировку

Настройка трассировки

Из вышеприведенного списка действий, самым сложным (а по своей сути — единственным) является настройка трассировки. Она имеет множество вариантов, попробуем разобрать основные из них.

Вкладка общие

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

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

Вывод данных трассировки может происходить:

  1. На экран в новом окне — вывод происходит на экран, при этом в дальнейшем трассировку можно будет сохранить как в файл, так и в таблицу в СУБД (даже если опции записи в файл и/или таблицу не были включены)
  2. Записывать в файл на диске (опционально) — дополнительно к выбранным опциям, данные будут записываться в файл на диске. Далее этот файл можно открыть через профайлер. Эта опция удобна для сохранения и/или для передачи трассировки.
  3. Записывать в таблицу базы данных (опционально) — дополнительно к выбранным опциям, данные будут записываться в таблицу базы данных. Далее, посредством возможностей предоставляемых СУБД, можно произвести анализ данных, например, найти самые длительные события или просуммировать общую длительность.

Последним пунктом настройки предлагается установить время остановки трассировки, если это требуется.

Перед продолжением настройки установим шаблон «Пустой» (Blank), имя трассировки может быть произвольным, все остальные флажки могут быть сняты.

Основные свойства трассировки

Вкладка выбора событий

Событие — это действие экземпляра SQL Server Database Engine. Для анализа проблем, возникающих при работе с 1С, существуют определенные наборы событий, с которыми необходимо уметь работать.

Выбор событий — это основная часть настройки трассировки, он предполагает работу с матрицей: «Событие» — «Свойство события». Таким образом, в этой матрице надо установить флажки по тем событиям и их свойствам, которые мы хотим трассировать.

Помимо матрицы событий и их свойств, на форме присутствуют флажки: «Показать все события» (Show all events) и «Показать все столбцы» (Show all columns). При установленном флажке в матрице раскрываются все события/столбцы, при снятом остаются только выбранные. Помимо этого, флажок «Показать все столбцы» влияет на отображение данных в «Фильтры столбцов» — отображаемый список соответствует отображаемым столбцам в матрице. При этом, даже если столбец скрыт (не выбран в матрице и снят флаг «Показать все столбцы»), но отбор на него был установлен — отбор сработает.

«Фильтры столбцов» (Column Filters) — открывает список столбцов по которым можно установить отборы. Если значение события при трассировке не подходит под значение отбора в столбце, данное событие не будет отражено в трассировке. Таким образом, можно установить отбор на информационную базу, по которой необходимо произвести трассировку.

«Упорядочить столбцы» (Organize Columns) — используется для изменения (организации) порядка следования выводимых колонок.

Выбор событий трассировки

События для получения плана выполнения запроса

Для того чтобы получить план запроса в Profiler следует добавить следующие события:

Событие Описание
Showplan All Выводит подробную информацию о предполагаемом плане запроса в текстовом виде
Showplan Statistics Profile Выводит подробную информацию о действительном плане запроса в текстовом виде
Showplan XML Выводит подробную информацию о предполагаемом плане запроса в XML формате (может быть представлен графически)
Showplan XML Statistics Profile Выводит подробную информацию о действительном плане запроса в XML формате (может быть представлен графически)

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

Событие Описание
RPC:Completed Происходит при завершении удаленного вызова процедуры
SQL:BatchCompleted Возникает при завершении выполнения инструкции Transact-SQL

Среди столбцов, выводимых в трассировке, рекомендуется включить: TextData, BinaryData, Reads, Writes, CPU, Duration, SPID.

События для получения плана выполнения запроса

Также полезно установить фильтры по длительности и базе данных. Как это сделать описано ниже в статье.

Другие способы получения плана запроса (без использования Profiler) описаны в статье «Методы получения плана запроса в СУБД MS SQL Server»

События для получения графа взаимоблокировки

Для получения графа взаимоблокировки достаточно добавить одноименное событие Locks: Deadlock graph.

Событие Deadlock graph возникает одновременно с классом событий Lock: Deadlock. Класс событий Deadlock graph предоставляет XML-описание взаимоблокировки.

Среди столбцов, выводимых в трассировке, рекомендуется включить: EventSequence, SPID, StartTime, TextData.

События для получения графа взаимоблокировки

События для получения информации об эскалации

Для получения информации об эскалации достаточно добавить событие Locks: Escalation.

Событие Escalation возникает при эскалации блокировки, т.е. когда блокировка более мелких фрагментов преобразуется в блокировку более крупных фрагментов.

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

События для получения информации об эскалации

Установка фильтров столбцов

Установить фильтры можно нажав на кнопку «Фильтры столбцов».

Установка отборов по столбцам

Важно понимать, не все события содержат те или иные колонки. Если событие не содержит колонку по которой установлен фильтр, данное событие отфильтровано не будет.

Очень полезным фильтром является отбор по имени базы или ее идентификатору (если в экземпляре находится несколько баз, а трассировать необходимо какую-то определенную). Для установки фильтра по имени базы необходимо для колонки DatabaseName установить значение «Похоже на» или «Не похоже на». Стоит отметить: если установленному значению будут отвечать несколько баз, тогда события будут собираться по каждой из них. Второй вариант фильтрации событий по определенной базе — установка отбора по колонке DatabaseID. Узнать идентификатор базы данных можно выполнив запрос в SQL Server Management Studio:

1 SELECT DB_ID(‘MyBase’)

где MyBase — имя базы, для которой необходимо получить идентификатор.

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

Обычно задача получения плана выполнения запроса связана с тем что запрос выполняется достаточно долго и для его оптимизации необходимо разобраться с планом выполнения. При этом настройка для получения плана выполнения собирает большое количество коротких по длительности событий. И именно для того чтобы отсечь ненужные короткие события можно применить отбор по длительности. Для установки фильтра по длительности необходимо установить значение отбора для колонки Duration. Отмечу что значение используемое для отбора может быть выражено в миллисекундах или микросекундах. То, какое значение используется, устанавливается в «Общих параметрах» профайлера (см. раздел «Настройка приложения»).

Работа с трассировкой

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

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

Если трассировка приостановлена или остановлена, тогда ее можно возобновить.

Помимо этого, в любой момент существует возможность очистить окно трассировки.

Для всех вышеприведенных действий на панели инструментов присутствуют необходимы кнопки.

Панель инструментов

Для изменения настройки уже созданной трассировки необходимо ее приостановить (или остановить) и перейти в пункт меню Файл (File) → Свойства (Properties). Выполнив изменения в трассировке, ее можно запустить вновь.

Работа с шаблонами трассировки

Наличие готовых шаблонов трассировки экономит время на настройке новой трассировки, поэтому я рекомендую сохранять необходимые для работы шаблоны. Шаблоны можно создавать, изменять, экспортировать и импортировать; для данных действий предназначен раздел меню «Файл» (File) → «Шаблоны» (Templates).

Создание шаблона

Для создания шаблона трассировки можно воспользоваться пунктом меню «Файл» (File) → «Шаблоны» (Templates) → «Новый шаблон» (New template).

Вторым (и на мой взгляд наиболее удобным) вариантом создания шаблона является сохранение настройки текущей трассировки в виде шаблона. Для этого требуется воспользоваться пунктом меню «Файл» (File) → «Сохранить как» (Save as) → «Шаблон трассировки» (Trace template)

Сохранение трассировки

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

Для того чтобы сохранить трассировку в файл, в меню присутствует пункт «Файл» (File) → «Сохранить как» (Save as) → «Файл трассировки» (Trace File). Для сохранения трассировки в таблицу существует аналогичный пункт меню: «Файл» (File) → «Сохранить как» (Save as) → «Таблица трассировки» (Trace Table)

Другие статьи по оптимизации 1С:

«Как ускорить 1С – Многопоточная обработка данных»

«Как ускорить 1С за 5 минут – Протокол Shared Memory»

«3 главных вопроса про временные таблицы 1С»

Анализ запросов с помощью SQL Profiler

Что Вы узнаете из этой статьи?

  • Предназначение инструмента трассировки SQL Profiler
  • Как отследить текст запроса к СУБД, в который транслируется запрос 1С
  • Настройки фильтров трассировки
  • Как выполнить персональную настройку SQL Profiler

Зачастую в работе возникает ситуация, когда запрос в 1С по каким-то причинам работает медленно, но анализ текста запроса не говорит нам о каких-либо проблемах.

В таком случае приходится изучать эту проблему на более низком уровне. Для этого нам нужно посмотреть текcт SQL-запроса и план запроса. Для этого можно использовать SQL Profiler.

SQL Profiler – предназначение

SQL Profiler – это программа, входящая в MS SQL Server, которая предназначена для просмотра всех событий, которые происходят в SQL-сервере. Иначе говоря, она нужна для записи трассировки.

В каких случаях данный инструмент может быть полезен 1С программисту? Прежде всего, можно получить текст запроса на языке SQL и посмотреть его план. Это также можно сделать и в технологическом журнале (ТЖ), но план запроса в ТЖ получается не таким удобным и требует наличия некоторых навыков и умений. К тому же в профайлере можно посмотреть не только текстовый, но и графический план выполнения запроса, что является более удобным.

Также профайлер позволяет узнать:

  • запросы длиннее определенного времени
  • запросы к определенной таблице
  • ожидания на блокировках
  • таймауты
  • взаимоблокировки и т. д.

Зачастую Profiler применяется именно для анализа запросов. И при этом нужно анализировать не все исполняемые запросы, а то, как определенный запрос на языке 1С транслируется в SQL, и обращать внимание на его план выполнения.

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

Для отслеживания запроса в трассировке выполняем следующие шаги:

1. Запускаем SQL Profiler: Пуск — Все программы — Microsoft SQL Server 2008 R2 — Средства обеспечения производительности — SQLProfiler.

2. Создаем новую трассировку: Файл – Создать трассировку (Ctrl+N).

3. Указываем сервер СУБД, на котором находится наша база данных и нажимаем Соединить:

Нам ничто не мешает выполнять трассировку сервера СУБД, находящегося на любом другом компьютере.

4. В появившемся окне Свойства трассировки переключаемся на закладку Выбор событий:

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

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

Описание этих событий:

  • ShowplanStatisticsProfile– текстовый план выполнения запроса
  • ShowplanXMLStatisticsProfile– графический план выполнения запроса
  • RPC:Completed– текст запроса, если он выполняется как процедура (если выполняется запрос 1С с параметрами)
  • SQL:BatchCompleted– текст запроса, если он выполняется как обычный запрос (если выполнялся запрос 1С без параметров)

6. На этом этапе необходима настройка фильтра для выбранных событий. Если фильтр не установлен, то мы будем видеть запросы для всех БД, расположенных на данном сервере СУБД. По кнопке Фильтры столбцов устанавливаем фильтр по имени базы данных:

Теперь мы видим в трассировке только запросы к БД «TestBase_8_2».

Также можно поставить фильтр и по другим полям, наиболее интересные из них:

  • Duration (длительность)
  • TextData (обычно это текст запроса)
  • RowCounts (количество строк, возвращаемых запросом)

Допустим, нам необходимо «отловить» все запросы к таблице «_InfoRg4312» длительностью более 3-х секунд в базе данных «TestBase_8_2». Для этого необходимо:

a) Установить фильтр по базе данных (см. выше)
b) Установить фильтр по длительности (устанавливается в миллисекундах):

c) Установить фильтр по тексту запроса:

Для задания фильтра по тексту запроса используем маску. В случае необходимости отслеживать запросы, которые обращаются к нескольким таблицам, создается несколько элементов в разделе «Похоже на». Наложенные условия фильтров работают совместно.

7. Теперь запускаем трассировку с помощью кнопки Запустить в окне Свойства трассировки и наблюдаем события, попадающие под установленные фильтры, отображение которых было настроено.

Кнопки командной панели служат для управления трассировкой:

Назначение кнопок:

  • Ластик – очищает окно трассировки
  • Пуск – запускает трассировку
  • Пауза – ставит трассировку на паузу, при нажатии на Пуск трассировка возобновляется
  • Стоп – останавливает трассировку

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

9. Запустим на выполнение запрос в консоли запросов 1С и посмотрим, как он отразится в профайлере:

По поведению трассировки видно, что запросов в итоге получилось несколько, и только один из них нам интересен. Остальные запросы – служебные.

10. Свойства событий дают возможность оценить:

  • сколько секунд выполнялся запрос (Duration)
  • сколько было логических чтений (Reads)
  • сколько строк запрос вернул в результате (RowCounts) и т.д.

В нашем случае запрос выполнялся 2 миллисекунды, сделал 4 логических чтения и вернул 1 строку.

11. Если взглянуть на одно событие выше, то можно увидеть план запроса в графическом виде:

Из плана видно, что поиск осуществляется по индексу по цене, этот план нельзя назвать идеальным, так как индекс не является покрывающим, поля код и наименование получаются с помощью KeyLookup, что отнимает 50% времени.

Используя контекстное меню, полученный графический план запроса возможно сохранить в отдельный файл с расширением *.SQLPlan и открыть его в профайлере на другом компьютере или с помощью программы SQL Sentry Plan Explorer, которая является более продвинутой.

12. Если подняться еще выше, то мы увидим тот же план запроса, но уже в текстовом виде. Именно этот план отображается в ТЖ, ЦУП и прочих средствах контроля производительности 1С.

13. Через меню Файл – Сохранить как можно сохранить всю трассировку в различные форматы:

  • В формат самого профайлера, то есть с расширением *.trc
  • В формат xml
  • Сделать из трассировки шаблон (См. следующий пункт)
  • Cохранить полученную трассировку в виде таблицы базы данных. Это весьма удобный способ, когда, к примеру, нужно найти самый медленный запрос в трассировке или отфильтровать запросы по какому-либо параметру.

Используем меню Файл – Сохранить как – Таблица трассировки – Выбираем сервер СУБД и подключаемся к нему.

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

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

Также нужно помнить, что Duration сохраняется в таблицу в миллионных долях секунды, и при выводе результата нужно переводить значение в миллисекунды. Также в таблице присутствует столбец RowNumber, показывающий номер данной строки в трассировке.

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

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

Для создания шаблона используем меню Файл – Шаблоны – Новый шаблон:

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

На второй закладке делаем выбор нужных событий и осуществляем настройку фильтров (как было показано выше).

Дополнительно рекомендуется выполнить настройку порядка столбцов в трассировке, что экономит время при последующем анализе запросов. Удобным представляется следующий порядок:

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

Бурмистров Андрей

PDF-версия статьи для участников группы ВКонтакте

Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.

Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.

Ссылка доступна для зарегистрированных пользователей)
Ссылка доступна для зарегистрированных пользователей)
Ссылка доступна для зарегистрированных пользователей)

Если Вы уже участник группы – нужно просто повторно авторизоваться в ВКонтакте, чтобы скрипт Вас узнал. В случае проблем решение стандартное: очистить кеш браузера или подписаться через другой браузер. Станьте экспертом по оптимизации 1С, изучив наш курс
«Ускорение и оптимизация систем на 1С:Предприятие 8.3 (2016). Подготовка на 1С:Эксперт по технологическим вопросам»

Содержание курса и форма заказа: https://курсы-по-1с.рф/1c-v8/optimization/

35 учебных часов, подготовка к 1С:Эксперт, правильная настройка серверной части, оптимизация кода, мониторинг загруженности оборудования и прочие взрослые вещи.