SQL Server Profiler. В картинках, для самых маленьких

27.11.23

База данных - HighLoad оптимизация

Шпаргалка к экзамену "Эксперт по технологическим вопросам".

Для тех, у кого нет доступа к SQL Server Profiler (SQL 2012) или кому просто некогда. Материал составлен с оглядкой на статью Андрея Бурмистрова.

1. Настройка «Max degree of parallelism»

Если при анализе планов запроса у вас встречаются характерные операторы "Параллелизм",

Нужно сделать настройку в SQL Management Studio. Максимальная степень параллелизма должна быть 1. При изменении сервер перезапускать не нужно.

Разъяснение 1C. Уточнение: более тонкая настройка - использовать параметр "Стоимостной порог для параллелизма". Чтобы обработка в несколько потоков выполнялась только для достаточно больших запросов. Например, запросов, предварительный план которых дольше 3 минут. При этом установить количество ядер (степень параллелизма) кратно количеству ядер одного процессора.

2. Появляются записи Trace Skipped Records

Если при трассировке появляются записи Trace Skipped Records, которые нужно просмотреть, необходимо при создании трассировки указать "Сохранить в файл", имя файла на сервере СУБД, установить максимальный размер файла, "Сервер обрабатывает данные трассировки".

Ниже пример трассировки с Trace Skipped Records и то же из файла, где записи приведены полностью. (Выше записи Duration = 7565).

Кстати, если оценивать запрос по показателям "Duration", "Reads", "Reads" то этот запрос абсолютно неоптимален: за семь секунд произведено 160578 чтений и результат - 8 записей.

3.1. Программа в режиме автоматических блокировок. Выполним код

SQL Server Profiler мы обычно воспринимаем, как инструмент анализа запросов. Однако, объектное чтение тоже преобразуется в запросы на уровне СУБД. Как и ожидалось, команда набора записей "Прочитать" делает запрос в транзакции. Это видно в столбце Text data: "BEGIN TRANSACTION", "COMMIT TRANSACTION". Уровень изоляции - SERIALIZABLE - в тексте запроса.

3.2. Тот же самый код (Набор записей.Прочитать) в режиме управляемых блокировок, используется совместимость с 8.2 делает запрос в транзакции, уровень изоляции Read Commited

3.3. Тот же самый код (Набор записей.Прочитать) в режиме управляемых блокировок, НЕ используется совместимость с 8.2

делает такой-же запрос, только не использует команду SET TRANSACTION ISOLATION LEVEL READ COMMITED, поскольку уровень изоляции - READ COMMITED SNAPSHOT совпадает с уровнем изоляции MS SQL 2012 по умолчанию.

3.4. Как мы видели выше, объектное чтение набора происходит в транзакции. Прочитать «грязные данные», можно запросом вне транзакции. Конфигурация в режиме совместимости с 8.2. Одинаково в автоматическом и управляемом режиме блокировок.

Уровень изоляции - Read Uncommitted. В тексте запроса ключевое слово NOLOCK.

3.5. Автоматический режим блокировок. Чтение запросом в транзакции.

В командах видно начало и окончание транзакции. По тексту запроса _Const12024 - константа, _InfoRG7759 - регистр. В обоих случаях уровень изоляции Serializable. Для справочника _Reference62 уровень изоляции - Repeateble Read. Если в тексте запроса 1С добавить конструкцию "ДЛЯ ИЗМЕНЕНИЯ", то рядом с уровнем изоляции будет добавлено "UPDLOCK". В СУБД MS SQL конструкция "ДЛЯ ИЗМЕНЕНИЯ" препятствует чтению в транзакции данных не любыми запросами в транзакции, а только теми, которые тоже используют конструкцию "ДЛЯ ИЗМЕНЕНИЯ".

 4.1. Небольшое отступление от MS Profiler. Блокировки, автоматический режим для других СУБД. ИТС, подробнее

Режим
блокировки
в транзакции

СУБД

Файловая
база
данных

MS SQL Server

PostgreSQL

Автоматический

Вид
блокировок

Таблиц

Записей

Таблиц

Уровень
изоляции

Serializable

Repeatable Read
или Serializable

Read Committed

Управляемый

Вид
блокировок

Таблиц

Записей

Записей

Уровень
изоляции

Serializable

Read Committed

Read Committed

СУБД POSTGRES конструкция "ДЛЯ ИЗМЕНЕНИЯ" (автоматический режим) препятствует чтению заблокированных данных любыми запросами в транзакции. Лично проверил.

СУБД MS SQL выше 2005: Режим совместимости 8.3 – всегда используется RCSI. Чтение вне транзакции 8.2 - используется Read UnCommited. Чтение в транзакции 8.2 используется Serializable/RepeatebleRead для автоматического режима и Read Commited для управляемого режима.

5. Архитектура хранения констант

Выполним запрос

ВЫБРАТЬ	Константы.ВалютаРегламентированногоУчета
ИЗ	Константы КАК Константы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ	Константы.ВалютаУправленческогоУчета
ИЗ	Константы КАК Константы

База ut_vasilev3 в режиме совместимости 8.2.13, обращение идет к единственной таблице dbo._Consts

Выполним тот же запрос, база ut_vasilev в режиме совместимости 8.2.16, обращение идет к разным таблицам. 

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

6. Ограничения прав на уровне записей (RLS)

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

7. Долой объектное чтение !

При получении данных через точку обрабатываются все реквизиты объекта. Если присутствуют табличные части, они тоже считываются, причем запросы чтения реквизитов и табличных частей объединяются одной транзакцией.  Подробнее - второе издание Настольная книга 1С:Эксперта, страницы 106-107. Функция ПолучитьОбъект() отправляет на сервер СУБД такие-же запросы.

Посмотрим как-бы безобидный код ПрочитатьОдинРеквизит = ДокументСсылка.Дата;

Начинается транзакция (1), читается версия объекта (2), читаются реквизиты объекта (3), читается первая табличная часть объекта(4)... Текст запроса показывает чтение всех реквизитов документа. Их слишком много, использовал пропуск.

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

Чтобы читать данные быстрее и аккуратнее, используйте функции: ЗначениеРеквизитаОбъектов, ЗначенияРеквизитовОбъектов, ЗначениеРеквизитаОбъекта, ЗначенияРеквизитовОбъекта. Кстати, с помощью этих функций можно выполнять запросы с соединениями через точку, например ИНН контрагента из накладной:

СтруктураЗапроса = Новый Структура("ИННКонтрагента","Контрагент.ИНН");

ЗначенияРеквизитов = ЗначенияРеквизитовОбъекта(РеализацияТоваровУслуг, СтруктураЗапроса);

8. Избранные события.

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

 

Locks

 
 

Deadlock Graph

XML-описание взаимоблокировки

 

Lock:Acquired

получена блокировка для ресурса. В случае, если блокировка не получена - происходят другие события )

 

Lock:Cancel

получение блокировки ресурса было отменено

 

Lock:Escalation

возникает при укрупнении уровня блокировки

 

Lock:Released

блокировка ресурса была снята

 

Lock:Timeout

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

 

Performance

 
 

Showplan All

План до выполнения запроса. Используют например, если запрос не выполняется из-за ошибки. Любопытно, что стоимость операторов (cost) в актуальном плане может не пересчитываться.

 

Showplan All for Query Compile

План в момент компиляции. Если используется план из кэша - событие не происходит. Кэш хранит планы в зависимости от текста запроса. Поэтому булево включают в текст. Смотри также parameter sniffing.

 

Showplan Statistics Profile

План в текстовом формате. Используется при анализе.

 
  • BinaryData

Обязательный столбец

 

Showplan XML Statistics Profile

В формате XML. Используется при анализе.

 
  • TextData

План запроса

 

Stored Procedures

 
 

RPC:Completed

например, завершение запроса

 
  • Duration

Количество занятого событием времени. В микросекундах, начиная с SQL Server 2008 R2. В миллисекундах в более ранних версиях.

 
  • EndTime

Время окончания

 
  • Reads

Число операций чтения страниц

 
  • RowCounts

Число строк

 
  • StartTime

Время начала

 
  • TextData

Текст запроса

 

TSQL

 
 

SQL:BatchCompleted

Выполнение команды. Например, начата/завершена транзакция.

 
  • Duration

Количество занятого событием времени. В микросекундах, начиная с SQL Server 2008 R2. В миллисекундах в более ранних версиях.

 

SQL:BatchStarted

выполнение начато

 

SQL Server Profiler Подготовка Профессионал по технологическим вопросам.

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2356    spyke    25    

38

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7351    149    ZAOSTG    66    

95

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5761    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8577    ivanov660    6    

75

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    4999    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15956    skovpin_sa    14    

98

Как эффективно настроить autovacuum в Postgres для 1С

HighLoad оптимизация Администрирование СУБД Платформа 1С v8.3 Бесплатно (free)

Кто не любит убирать мусор? Думаю, практически все, а вот в Postgres это обязательный ритуал для эффективной работы. Как эффективно настроить уборку за 1С в Postgres, можно прочитать в этой статье и еще раз задуматься о бесплатности Postgres.

05.08.2023    4972    1CUnlimited    5    

51
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. пользователь 21.08.17 16:05
Сообщение было скрыто модератором.
...
3. пользователь 21.08.17 17:13
Сообщение было скрыто модератором.
...
2. пользователь 21.08.17 16:34
Сообщение было скрыто модератором.
...
4. пользователь 21.08.17 17:20
Сообщение было скрыто модератором.
...
5. пользователь 24.08.17 10:56
Сообщение было скрыто модератором.
...
6. пользователь 28.08.17 15:45
Сообщение было скрыто модератором.
...
7. пользователь 28.08.17 16:27
Сообщение было скрыто модератором.
...
8. noizu 2 10.04.19 07:02 Сейчас в теме
Просьба исправить:

В СУБД MS SQL конструкция "ДЛЯ ИЗМЕНЕНИЯ" препятствует чтению в транзакции данных не любыми запросами в транзакции, а только теми, которые тоже используют конструкцию "ДЛЯ ИЗМЕНЕНИЯ".


Блокировки обновления (U) совместимы только с совмещаемыми блокировками (S).

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


https://docs.microsoft.com/ru-ru/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014
9. vasilev2015 2677 19.04.19 09:10 Сейчас в теме
(8) Здравствуйте, уважаемый коллега !

я имел ввиду, если в 1С (автоматический режим блокировок) делать запрос "Выбрать...Для Изменения" в транзакции, то он предотвратит чтение данных другими запросами 1С, только если они будут в транзакции и иметь опцию "Для изменения". То есть речь о запросах 1С, а не о всех блокировках СУБД MS SQL, все корректно.

Спасибо за ваш комментарий.
10. AKunitsyn 25.11.23 10:46 Сейчас в теме
Материал составлен с оглядкой на статью Андрея Бурмистрова.

Спасибо за Шпаргалку). Актуализируйте битую ссылку пожалуйста.
11. vasilev2015 2677 27.11.23 19:02 Сейчас в теме
(10) Андрей Б. убрал статью с infostart, на сторонние ресурсы ссылки не одобряются.
Ссылку переделал на текст.
Оставьте свое сообщение