1с как посмотреть план запроса
Перейти к содержимому

1с как посмотреть план запроса

  • автор:

Анализ работы MS SQL Server, для тех кто видит его впервые (часть 2)

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

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

Задачи анализа действий пользователей условно поделим на группы и рассмотрим каждую отдельно:

Анализ производительности требует глубокого понимания устройства и принципов работы сервера БД и ОС. Поэтому, чтение только этих статей не сделает из вас эксперта.

Рассматриваемые критерии и счетчики в реальных системах находятся в сложной зависимости друг от друга. Например высокая нагрузка HDD, часто связана с проблемой не самого HDD, а с нехваткой оперативной памяти. Даже если вы проведете некоторые из замеров — этого недостаточно для взвешенной оценки проблем.

Цель статей — познакомить с базовыми вещами на простых примерах. Рекомендации не стоит рассматривать как «руководство к действию», рассматривайте их как учебные задачи (упрощенно отражающие действительность) и как варианты «на подумать», призванные пояснить ход мысли.
Надеюсь, по итогу статей вы научитесь аргументировать цифрами свои заключения о работе сервера. И вместо слов «сервер тормозит» будете приводить конкретные величины конкретных показателей.

Анализируем конкретный запрос

Первый пункт довольно прост, остановимся на нем кратко. Рассмотрим только некоторые малоочевидные вещи.

  • Практически все знают, что план запроса получается кнопками «Display Estimated Execution Plan» (оценочный план) и «Include Actual Execution Plan» (фактический план). Отличаются они тем, что оценочный план строится без выполнения запроса. Соответственно, информация о количестве обработанных строк в нем будет только оценочная. В фактическом плане будут как оценочные данные, так и фактические. Сильные расхождения этих величин говорят о неактуальности статистики. Впрочем, анализ плана — тема для отдельной большой статьи — пока не будем углубляться.
  • Менее известный факт — можно получать замеры затрат процессора и дисковых операций сервера. Для этого необходимо включить SET опции либо в диалоге через меню «Query» / «Query options. »

либо напрямую командами SET в запросе, например

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

Время синтаксического анализа и компиляции SQL Server:
время ЦП = 16 мс, истекшее время = 89 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 0 мс.

(32 row(s) affected)
Таблица «ProductProductPhoto». Число просмотров 32, логических чтений 96, физических чтений 5, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица «Product». Число просмотров 0, логических чтений 64, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица «ProductDocument». Число просмотров 1, логических чтений 3, физических чтений 1, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 15 мс, затраченное время = 35 мс.

  • Затраты процессора смотрим используя SET STATISTICS TIME ON.
  • Дисковые операции: SET STATISTICS IO ON. Не забываем, что «логическое чтение» — это операция чтения, завершившаяся в кэше диска без физического обращения к дисковой системе. «Физическое чтение» требует значительно больше времени.
  • Объем сетевого трафика оцениваем с помощью «Include Client Statistics».
  • Детально алгоритм выполнения запроса анализируем по «плану выполнения» с помощью «Include Actual Execution Plan» и «Include Live Query Statistics».
Анализируем нагрузку от приложения

Чуть более сложный путь — к выбранному шаблону добавить (или убавить) фильтров или событий. Данные опции на второй закладке диалога. Чтобы увидеть полный набор возможных событий и колонок для выбора — отметьте пункты «Show All Events» и «Show All Columns».

Из событий нам потребуются (лишние лучше не включать — чтобы создавать меньше трафика):

  • Stored Procedures \ RPC:Completed
  • TSQL \ SQL:BatchCompleted
  • Stored Procedures \ RPC:Starting
  • TSQL \ SQL:BatchStarting

Еще полезные события, которые мы пока не будем включать:

  • Stored Procedures \ SP:Starting (*Completed) — фиксирует внутренний вызов хранимой процедуры (не с клиента, а внутри текущего запроса или другой процедуры).
  • Stored Procedures \ SP:StmtStarting (*Completed) — фиксирует старт каждого выражения внутри хранимой процедуры. Если в процедуре цикл — будет столько событий для команд внутри цикла, сколько итераций было в цикле.
  • TSQL \ SQL:StmtStarting (*Completed) — фиксирует старт каждого выражения внутри SQL-batch. Если ваш запрос содержит несколько команд — будет по событию на каждую. Т.е. аналогично предыдущему, только действует не для команд внутри процедур, а для команд внутри запроса.

По колонкам

Какие выбирать, как правило, понятно из названия колонки. Нам будут нужны:

  • TextData, BinaryData — для описанных выше событий содержат сам текст запроса.
  • CPU, Reads, Writes, Duration — данные о затратах ресурсов.
  • StartTime, EndTime — время начала/окончания выполнения. Удобны для сортировки.

По кнопке «Column Filters. » можно вызвать диалог установки фильтров событий. Если интересует активность конкретного пользователя — задать фильтр по номеру сессии или имени пользователя. К сожалению, в случае подключения приложения через app-server c пулом коннектов — отследить конкретного пользователя сложнее.

Фильтры можно использовать, например, для отбора только «тяжелых» запросов (Duration>X). Или запросов которые вызывают интенсивную запись (Writes>Y). Да хоть просто по содержимому запроса.

Что же еще нам нужно от профайлера? Конечно же план выполнения!

Такая возможность имеется. Необходимо добавить в трассировку событие «Performance \ Showplan XML Statistics Profile». Выполняя наш запрос, мы получим примерно следующую картинку.

И это еще не всё

Трассу можно сохранять в файл или таблицу БД (а не только выводить на экран).
Настройки трассировки можно сохранить в виде личного template для быстрого запуска.
Запуск трассировки можно выполнять и без профайлера — с помощью t-sql кода, используя процедуры: sp_trace_create, sp_trace_setevent, sp_trace_setstatus, sp_trace_getdata. Пример как это сделать. Данный подход может пригодиться, например, для автоматического старта записи трассы в файл по расписанию. Как именно использовать эти команды, можно подсмотреть у самого профайлера. Достаточно запустить две трассировки и в одной отследить что происходит при старте второй. Обратите внимание на фильтр по колонке «ApplicationName» — проконтролируйте, что там отсутствует фильтр на сам профайлер.

Список событий фиксируемых профайлером очень обширен и не ограничивается только получением текстов запросов. Имеются события фиксирующие fullscan, рекомпиляции, autogrow, deadlock и многое другое.

Анализируем активность пользователей в целом по серверу

Жизненные ситуации бывают и такими, когда информация из разделов выше не помогает:
Какой-то запрос висит на «выполнении» очень долго и непонятно, закончится он когда-нибудь или нет. Проанализировать проблемный запрос отдельно — хотелось бы — но надо сначала определить что за запрос. Профайлером ловить бесполезно — starting событие мы уже пропустили, а completed неясно сколько ждать.

А может висит и не пользовательский запрос совсем, а может это сам сервер что-то активно делает…

Давайте разбираться

Все вы наверно видели «Activity Monitor». В старших студиях его функционал стал богаче. Чем он может нам помочь? В «Activity Monitor» много полезного и интересного, но третий раздел не о нем. Всё что нужно будем доставать напрямую из системных представлений и функций (а сам Монитор полезен тем, что на него можно натравить профайлер и посмотреть какие запросы он выполняет).

    — информация о сессиях. Отображает информацию по подключенным пользователям. Полезные поля (в рамках этой статьи) — идентифицирующие пользователя (login_name, login_time, host_name, program_name, . ) и поля с информацией о затраченных ресурсах (cpu_time, reads, writes, memory_usage, . ) — информация о запросах выполняющихся в данный момент. Полей тут тоже довольно много, рассмотрим только некоторые:
    • session_id — код сессии для связи с предыдущим представлением
    • start_time — время старта запроса
    • command — это поле, вопреки названию, содержит не запрос, а тип выполняемой команды. Для пользовательских запросов — обычно это что-то вроде select/update/delete/и т.п. (также, важные примечания ниже)
    • sql_handle, statement_start_offset, statement_end_offset — информация для получения текста запроса: хэндл, а также начальная и конечная позиция в тексте запроса — обозначающая часть выполняемую в данный момент (для случая когда ваш запрос содержит несколько команд).
    • plan_handle — хэндл сгенерированного плана.
    • blocking_session_id — при возникновении блокировок препятствующих выполнению запроса — указывает на номер сессии которая стала причиной блокировки
    • wait_type, wait_time, wait_resource — поля с информацией о причине и длительности ожидания. Для некоторых видов ожидания, например, блокировка данных — дополнительно указывается код заблокированного ресурса.
    • percent_complete — по названию понятно, что это процент выполнения. К сожалению, доступен только для команд у которых четко прогнозируемый прогресс выполнения (например, backup или restore).
    • cpu_time, reads, writes, logical_reads, granted_query_memory — затраты ресурсов.

    Приведенный перечень — лишь малая часть. Полный список всех системных представлений и функций описан в документации. Также, имеется схема связей основных объектов в виде красивой картинки — можно распечатать на А1 и повесить на стену.

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

    Поле «command» — для пользовательских запросов оно практически бессмысленно — ведь мы можем получить полный текст… Но не всё так просто. Это поле очень важно для получения информации о системных процессах. Как правило, они выполняют какие-то внутренние задачи и не имеют текста sql. Для таких процессов, информация о команде единственный намек на тип активности. В комментариях к предыдущей статье был вопрос про то, чем занят сервер, когда он, вроде бы, ничем не должен быть занят — возможно ответ будет в значении этого поля. На моей практике, поле «command» для активных системных процессов всегда выдавало что-то вполне понятное: autoshrink/autogrow/checkpoint/logwriter/и т.п.

    Как же это использовать

    Перейдем к практической части. Я приведу несколько примеров использования, но не стоит ограничивать вашу фантазию. Возможности сервера этим не исчерпываются — можете придумывать что-то своё.

    Пример 1: Какой процесс расходует cpu/reads/writes/memory

    Для начала, посмотрим какие сессии больше всего потребляют, например, CPU. Информация в sys.dm_exec_sessions. Но данные по CPU (а также reads, writes) — накопительные. Т.е цифра в поле содержит «итого» за все время подключения. Понятно, что больше всего будет у того кто подключился месяц назад, да так и не отключался ни разу. Это вовсе не означает, что он прямо сейчас грузит систему.

    Немного кода решает проблему, алгоритм примерно такой:

  1. сначала сделаем выборку и сохраним во временную таблицу
  2. затем подождем немного
  3. делаем выборку второй раз
  4. сравниваем результаты первой и второй выборки — разница, как раз и будет затратами возникшими на п.2
  5. для удобства, разницу можем поделить на длительность п.2, чтобы получить усредненные «затраты в секунду».

В коде я использую две таблицы: #tmp — для первой выборки, #tmp1 — для второй. При первом запуске, скрипт создает и заполняет #tmp и #tmp1 с интервалом в одну секунду, и делает остальную часть. При последующих запусках, скрипт использует результаты предыдущего выполнения в качестве базы для сравнения. Соответственно, длительность п.2 при последующих запусках будет равна длительности вашего ожидания между запусками скрипта. Пробуйте выполнять, можно сразу на рабочем сервере — скрипт создает только «временные таблицы» (доступны только внутри текущей сессии и самоуничтожаются при отключении) и не несёт в себе опасности.

Те, кто не любят выполнять запрос в студии — могут его завернуть в приложение написанное на своём любимом языке программирования. Я покажу как это сделать в MS Excel без единой строки кода.

В меню «Данные» подключаемся к серверу. Если будет требовать выбрать таблицу — выбираем произвольную — потом поменяем это. Как всегда, жмем «Next» и «Finish» пока не увидим диалог «Импорт данных» — в нем нужно нажать «Свойства. ». В свойствах необходимо сменить «тип команды» на значение «SQL» и в поле «текст команды» вставить немного измененный наш запрос.

Запрос придется немного поменять:

  • добавим «SET NOCOUNT ON» — т.к. Excel не любит отсечки количества строк;
  • «временные таблицы» заменим на «таблицы переменные»;
  • задержка всегда будет 1сек — поля с усредненными значениями не нужны



Когда данные будут в Excel-е, можете их сортировать как вам нужно. Для актуализации информации — жмите «Обновить». В настройках книги, для удобства, можете поставить «автообновление» через заданный период времени и «обновление при открытии». Файл можете сохранить и передать коллегам. Таким образом, мы из навоза и веточек подручных средств собрали ЫнтерпрайзМониторингТул удобный и простой инструмент.

Пример 2: На что сессия расходует ресурсы

Итак, в предыдущем примере мы определили проблемные сессии. Теперь определим, что именно они делают. Используем sys.dm_exec_requests, а также функции получения текста и плана запроса.

Подставляйте в запрос номер сессии и выполняйте. После выполнения, на закладке «Results» будут планы (два: первый для всего запроса, второй для текущего шага — если шагов в запросе несколько), на закладке «Messages» — текст запроса. Для просмотра плана — необходимо кликнуть в строке на текст оформленный в виде url. План откроется в отдельной закладке. Иногда бывает что план открывается не в графическом виде, а в виде xml-текста. Это, скорее всего, связано с тем что версия студии ниже чем сервера. Попробуйте пересохранить полученный xml в файл с расширением sqlplan, предварительно удалив из первой строки упоминания «Version» и «Build», а затем отдельно открыть его. Если и это не помогает — напоминаю, что 2016 студия официально доступна бесплатно на сайте MS.

Очевидно, полученный план будет «оценочным», т.к. запрос еще выполняется. Но некоторую статистику по выполнению получить всё равно можно. Используем представление sys.dm_exec_query_stats с фильтром по нашим хэндлам.

Допишем в конец предыдущего запроса

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

Пример 3: А можно всех посмотреть

Давайте объединим рассмотренные системные представления и функции в одном запросе. Это может быть удобно для оценки ситуации в целом.

Запрос выводит список активных сессий и тексты их запросов. Для системных процессов, напоминаю, обычно запрос отсутствует, но заполнено поле «command». Видна информация о блокировках и ожиданиях. Можете попробовать скрестить этот запрос с примером 1, чтобы еще и отсортировать по нагрузке. Но будьте аккуратны — тексты запросов могут оказаться очень большими. Выбирать их массово может оказаться ресурсоемко. Да и трафик будет большим. В примере я ограничил получаемый запрос первыми 500 символами, а получение плана не стал делать.

Примеры запросов выложил на гитхаб.

Заключение

Было бы ещё неплохо, получить Live Query Statistics для произвольной сессии. По заявлению производителя, на текущий момент, постоянный сбор статистики требует значительных ресурсов и поэтому, по умолчанию, отключен. Включить не проблема, но дополнительные манипуляции усложняют процесс и уменьшают практическую пользу. Возможно, попробуем это сделать в отдельной статье.

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

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

Как ускорить 1С – Анализ запросов с помощью SQL Profiler

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

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

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

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

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

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

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

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

Зачастую 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) Установить фильтр по длительности (устанавливается в миллисекундах):

настройка фильтра SQLProfiler

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

image009

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

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

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

Управление трассировкой

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

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

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

Выполнение запросов в консоли запросов 1С

Запрос 1С в SQLProfiler

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

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-версия статьи для участников группы ВКонтакте

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

Статья по оптимизации 1С - в PDF-формате

Статья в PDF-формате

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

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

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

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

Комментарии / обсуждение (11):

Здравствуйте! Вы пишете: “По поведению трассировки видно, что запросов в итоге получилось несколько, и только один из них нам интересен. Остальные запросы – служебные.” А как узнать, какой из них наш основной запрос?

Татьяна, здравствуйте!
Наш запрос будет обращаться к таблице которая соответствует объекту метаданных, а так же это понятно по набору выбираемых полей и полям по которым идет условие.
Что бы узнать какая таблица СУБД какому объекту метаданных соответствует, можно использовать функцию ПолучитьСтруктуруХраненияБазыДанных.
Вот пример одной из многих обработок с помощью которых можно посмотреть структуру хранения.

Добрый день. Подскажите, пожалуйста, почему в Profiler отображается не название используемого индекса (_Reference7_ByFieldFld109), а его номер (_Reference7_9)?
В примерах: Index Seek(OBJECT:([demo].[dbo].[_Reference7].[_Reference7_ByFieldFld109] AS [T1])
У меня: Index Seek(OBJECT:([demo].[dbo].[_Reference7].[_Reference7_9] AS [T1])
И только в обработке “Просмотр метаданных” я по номеру 9 нахожу этот индекс в списке и понимаю, что на самом деле поиск шел по индексу _Reference7_ByFieldFld109?

Здравствуйте!
Начиная с одной из версий платформы, индексы в СУБД стали именоваться по другому принципу “ИмяТаблицы_ПорядковыйНомер”, ранее нумерация была связана с названием и типом полей из которых индекс состоит. Изменение в платформе произошло относительно недавно, с этим и связано такое отличие.

Андрей, правильно я понимаю, что порядковый номер индекса совпадает с порядком в обработке “Структура хранения метаданных” и начинается не с 0, а с 1?

Имя индекса отображается одинаково и в MS SQL и в обработке структура хранения метаданных и начинается с 1.

История оптимизации одного большого запроса средствами MSSQL Profiler и 1С

Я думаю многие из Вас сталкивались с необходимостью оптимизации больших запросов, которые трудно поддаются рефакторингу. Иногда такие запросы бывают настолько объемны (несколько тысяч строк кода и десятки запросов в пакете), что просто не знаешь с какой стороны к ним подступиться. В статье я описал историю оптимизации заполнения документа «Начисление зарплаты сотрудникам» в ЗУП 2.5 на одном из проектов. В итоге получился некий «мини-гайд» как можно использовать SQL Profiler и План выполнения запроса для выявления неоптимальных/некорректных участков кода для больших запросов.

Добрый день, уважаемые коллеги.

Небольшая предыстория. Основной целью проекта являлась консолидация двух достаточно больших нетиповых баз ЗУП 2.5 (общее количество подразделений 140, сотрудников 4700). Базы ЗУП до нас дорабатывались и сопровождались другими подрядчиками, а нас привлекли именно на консолидацию. После консолидации пользователи сообщили о сильно возросшей длительности операции заполнения документа «Начисление зарплата сотрудникам». Собственно с этого момента я подключился к этому вопросу.

Часть I – Подготовительная:

Если вкратце, то методика APDEX является широко распространенным международным стандартом оценки производительности информационных систем и является:

  • объективной: оценка не зависит от субъективных факторов (эмоции, мнения и т.п.);
  • прикладной: оценка отражает реальную производительность прикладных операций, а не абстрактные технические показатели;
  • интегральной: оценка учитывает все аспекты работы системы, все требования бизнес-логики системы и удобство работы каждого пользователя;
  • количественной: оценка является численной для того, чтобы можно было сравнивать производительность, полученную при разных обстоятельствах (например, до и после оптимизации);
  • качественной: оценка интерпретируется в терминах «хорошо» — «плохо».

Оценка производительности

Время выполнения в максимуме иногда достигало 20 минут (см. ниже).

Замеры времени

Теперь понятно, ЧТО нужно оптимизировать, пока непонятно КАК. Идем дальше…

Часть II – Неудачная, но длинная

Простой перезапуск запроса через консоль в тестовой базе подтвердил достаточно длительное время выполнения запроса, но пытаться найти вручную один «плохой» запрос из пары десятков не очень хотелось. Было принято решение запустить запрос через SQL Profiler, т.к. через него я смогу сузить область поиска «плохого» запроса.

Запускаем Microsoft SQL Profiler (performance tools в Пуск). Как запустить SQL Profiler и подключиться к нужному серверу – выходит за рамки статьи – это более подробно описано в других местах (sql.ru / BOL).

Запускаем новый trace со следующим набором событий (Events) (см. ниже). Можно уменьшить выборку колонок, которые будут собираться, но для упрощения можно просто выделить все — пока не принципиально.

Trace

Не забываем установить фильтры событий, чтобы не собирать всякий «мусор» (т.е. события других баз, в т.ч. основной базы, напоминаю, что мы работаем в тестовой базе). Чтобы узнать ID базы можно выполнить запрос select DB_ID ( [ ‘database_name’ ] ) в SQL Server Management Studio

Filter

Стартуем трейс перед началом заполнения и останавливаем после завершения. Дальше выгружаем его в таблицу для удобства работы.

Выгрузка в таблицу

Для простоты можно выгрузить в tempdb с каким-нибудь простым именем, потом эту таблицу можно не задумываясь удалить. Находим длительные операции запросом

ORDER BY Duration DESC

//temp – название таблицы в которую выгрузили трейс из Profiler

Пример выполнения запроса на скриншоте ниже (немного другого :), но суть та же)

Результат

Видим две строчки с ОЧЕНЬ высоким Duration с ними и будем разбираться. В MS SQL Profiler находим по StartTime – EndTime (все строчки расположены по возрастанию EndTime). Проанализируем вторую строчку, как наибольшую. Находим строчку в замере Profiler’а.

Пример трейса

Смотрим план запроса (это строчка сразу НАД найденной строкой). Этим кстати и объясняются все танцы с бубном вокруг выгрузки трейса в таблицу, так как у планов выполнения запроса нет метрики Duration и найти план выполнения запроса без привязки к основному запросу крайне затруднительно. Не буду приводить полный ПВЗ, а покажу только его часть – в ПВЗ видим постоянные table scan с весом в 14% каждый с одной и той же таблицей #tt38.

ПВЗ

Смотрим таблицу #tt38 в ней обнаруживается 2 миллиона строк (см. ниже). Явная ошибка именно в этой таблице — в данных по зарплате по одному подразделению не может быть такого количества записей даже с самой маленькой детализацией (если только не предположить, что у сотрудников посекундная тарификация :))

tt38

Находим наш запрос в 1С. Вот он — куча левых соединений с таблицей ЗначенияПоказателей.

Запрос1С

Я думаю у многих возникает вопрос: «А как найти аналог запроса 1С по известному нам запросу SQL?». Для этого есть следующие «помощники»:

  • известные объекты в запросе SQL (т.е. когда SQL обращается не к временной, а к какой-то физической таблице). Соответствие между объектами 1С и объектами СУБД можно получить при помощи метода ПолучитьСтруктуруХраненияБазыДанных(), а дальше уже поиском находим нужный запрос
  • необычные агрегатные функции (максимум, среднее или количество различных)
  • структура запроса (например: два левых соединения и одно прямое с вложенным подзапросом) достаточно точно указывает на запрос 1С
  • условия соединений

ПВЗ№2

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

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

ПоследниеЗначенияПоказателей.Период КАК ПериодДействия,

ВЫБОР КОГДА ЗначенияПоказателей.Показатель.ВидПоказателя = Значение(Перечисление.ВидыПоказателейСхемМотивации.ПоДолжности) ТОГДА Истина ИНАЧЕ Ложь КОНЕЦ КАК ПоДолжности,

КОГДА ЗначенияПоказателей.Показатель.ВидПоказателя = ЗНАЧЕНИЕ(Перечисление.ВидыПоказателейСхемМотивации.ПоПодразделению)

ИЛИ ЗначенияПоказателей.Показатель.ВидПоказателя =ЗНАЧЕНИЕ(Перечисление.ВидыПоказателейСхемМотивации.ПоПодразделениюИСотруднику)

КОНЕЦ КАК ПоПодразделению,

ЕСТЬNULL(ЗначенияПоказателей.Подразделение, Значение(Справочник.Подразделения.ПустаяСсылка)) КАК Подразделение,

ЕСТЬNULL(ЗначенияПоказателей.Должность, Значение(Справочник.ДолжностиОрганизаций.ПустаяСсылка)) КАК Должность

ЗначенияПоказателейИсходныйПериодДействия КАК ЗначенияПоказателей

ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ

МАКСИМУМ(ЗначенияПоказателей.ПериодДействия) КАК ПериодДействия,

ПериодыФизлиц.Период КАК Период,

ЗначенияПоказателей.Физлицо КАК Физлицо,

ЗначенияПоказателей.Показатель КАК Показатель,

ВЫБОР КОГДА ЗначенияПоказателей.Показатель.ВидПоказателя = Значение(Перечисление.ВидыПоказателейСхемМотивации.ПоДолжности) ТОГДА Истина ИНАЧЕ Ложь КОНЕЦ КАК ПоДолжности,

ВЫБОР КОГДА ЗначенияПоказателей.Показатель.ВидПоказателя = Значение(Перечисление.ВидыПоказателейСхемМотивации.ПоПодразделению)ТОГДА Истина ИНАЧЕ Ложь КОНЕЦ КАК ПоПодразделению,

ЕСТЬNULL(ЗначенияПоказателей.Подразделение, Значение(Справочник.Подразделения.ПустаяСсылка)) КАК Подразделение,

ЕСТЬNULL(ЗначенияПоказателей.Должность, Значение(Справочник.ДолжностиОрганизаций.ПустаяСсылка)) КАК Должность

Добавить комментарий

Ваш адрес email не будет опубликован.