План выполнения sql запроса. О, эти планы запросов

История стара как мир. Две таблицы:

Cities – 100 уникальных городов.
People – 10 млн. людей. У некоторых людей город может быть не указан.

Распределение людей по городам – равномерное.
Индексы на поля Cites.Id, Cites.Name, People .CityId – в наличии.

Нужно выбрать первых 100 записей People, отсортированных по Cites.

Засучив рукава, бодро пишем:

Select top 100 p.Name, c.Name as City from People p
order by c.Name

При этом мы получим что-то вроде:

За… 6 секунд. (MS SQL 2008 R2, i5 / 4Gb)

Но как же так! Откуда 6 секунд?! Мы ведь знаем, что в первых 100 записях будет исключительно Алматы! Ведь записей – 10 миллионов, и значит на город приходится по 100 тыс. Даже если это и не так, мы ведь можем выбрать первый город в списке, и проверить, наберется ли у него хотя бы 100 жителей.

Почему SQL сервер, обладая статистикой, не делает так:

Select * from People p
left join Cities c on c.Id=p.CityId
where p.CityId
in (select top 1 id from Cities order by Name)
order by c.

Данный запрос возвращает примерно 100 тыс. записей менее чем за секунду! Убедились, что есть искомые 100 записей и отдали их очень-очень быстро.

Однако MSSQL делает все по плану. А план у него, «чистый термояд» (с).

Вопрос к знатокам:
каким образом необходимо исправить SQL запрос или сделать какие-то действия над сервером, чтобы получить по первому запросу результат в 10 раз быстрее?

P.S.
CREATE TABLE . (

uniqueidentifier
ON
GO

CREATE TABLE . (
uniqueidentifier NOT NULL,
nvarchar(50) NOT NULL,
ON
GO

P.P.S
Откуда растут ноги:
Задача вполне реальная. Есть таблица с основной сущностью, от нее по принципу «звезда» отходит множество измерений. Пользователю нужно ее отобразить в гриде, предоставив сортировку по полям.
Начиная с некоторого размера основной таблицы сортировка сводится к тому, что выбирается окно с одинаковыми (крайними) значениями, (вроде «Алматы») но при этом система начинает жутко тормозить.
Хочется иметь ОДИН параметризированный запрос, который будет эффективно работать как с малым размером таблицы People так и с большим.

P.P.P.S
Интересно, что если бы City были бы NotNull и использовался InnerJoin то запрос выполняется мгновенно.
Интересно, что ДАЖЕ ЕСЛИ поле City было бы NotNull но использовался LeftJoin – то запрос тормозит.

В комментах идея: Сперва выбрать все InnerJoin а потом Union по Null значениям. Завтра проверю эту и остальные безумные идеи)

P.P.P.P.S Попробовал. Сработало!

WITH Help AS
select top 100 p.Name, c.Name as City from People p
INNER join Cities c on c.Id=p.CityId
order by c.Name ASC
UNION
select top 100 p.Name, NULL as City from People p
WHERE p.CityId IS NULL
SELECT TOP 100 * FROM help

Дает 150 миллисекунд при тех же условиях! Спасибо

Существует несколько способов получения плана выполнения, который использовать будет зависеть от ваших обстоятельств. Обычно вы можете использовать SQL Server Management Studio для получения плана, однако, если по какой-то причине вы не можете запустить свой запрос в SQL Server Management Studio, вам может оказаться полезным получить план через SQL Server Profiler или путем проверки кэш плана.

Метод 1 - Использование SQL Server Management Studio

SQL Server поставляется с несколькими опрятными функциями, которые позволяют легко выполнить план выполнения, просто убедитесь, что пункт меню «Включить фактический исполняемый план» (найденный в меню «Запрос») отмечен галочкой и запускает ваш запрос как обычно,

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

Exec p_Example 42

Когда ваш запрос завершен, вы увидите дополнительную вкладку «План выполнения», которая появится в панели результатов. Если вы запустили много утверждений, вы можете увидеть много планов, отображаемых на этой вкладке.

Отсюда вы можете проверить план выполнения в SQL Server Management Studio или щелкнуть правой кнопкой мыши по плану и выбрать «Сохранить план выполнения как...», чтобы сохранить план в файл в формате XML.

Способ 2 - Использование опций SHOWPLAN

Этот метод очень похож на метод 1 (на самом деле это то, что делает SQL Server Management Studio внутренне), однако я включил его для полноты или если у вас нет доступной SQL Server Management Studio.

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

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS xml ON -- The is the recommended option to use

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

Как только вы закончите, вы можете отключить этот параметр со следующим утверждением:

SET < > OFF

Сравнение форматов плана выполнения

Если у вас нет сильных предпочтений, я рекомендую использовать параметр STATISTICS xml . Эта опция эквивалентна опции «Включить фактический план выполнения» в SQL Server Management Studio и предоставляет самую большую информацию в наиболее удобном формате.

SHOWPLAN_TEXT - Displays a basic text based estimated execution plan, without executing the query
SHOWPLAN_ALL - Displays a text based estimated execution plan with cost estimations, without executing the query
SHOWPLAN_XML - Displays an xml based estimated execution plan with cost estimations, without executing the query. This is equivalent to the "Display Estimated Execution Plan..." option in SQL Server Management Studio.
STATISTICS PROFILE - Executes the query and displays a text based actual execution plan.
STATISTICS XML - Executes the query and displays an xml based actual execution plan. This is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.

Способ 3 - Использование SQL Server Profiler

Если вы не можете запустить свой запрос напрямую (или ваш запрос не выполняется медленно при его непосредственном запуске - помните, что мы хотим, чтобы план запроса выполнялся плохо), вы можете сделать план с использованием трассировки Profiler SQL Server. Идея состоит в том, чтобы запустить ваш запрос, пока трассировка, которая захватывает один из событий «Showplan», запущена.

Обратите внимание, что в зависимости от нагрузки вы можете использовать этот метод в рабочей среде, однако вы должны, очевидно, проявлять осторожность. Механизмы профилирования SQL Server предназначены для минимизации влияния на базу данных, но это не означает, что влияние производительности any не будет. У вас может также возникнуть проблема с фильтрацией и определением правильного плана в вашей трассе, если ваша база данных находится под большим использованием. Вы должны, очевидно, проверить с вашим администратором базы данных, чтобы убедиться, что они довольны тем, что вы делаете это в своей драгоценной базе данных!

Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace.
Under the "Events Selection" tab check "Show all events", check the "Performance" -> "Showplan XML" row and run the trace.
While the trace is running, do whatever it is you need to do to get the slow running query to run.
Wait for the query to complete and stop the trace.
To save the trace right click on the plan xml in SQL Server Profiler and select "Extract event data..." to save the plan to file in xml format.

План, который вы получаете, эквивалентен опции «Включить фактический план выполнения» в SQL Server Management Studio.

Способ 4. Проверка кэша запросов.

Если вы не можете выполнить свой запрос напрямую, и вы также не можете захватить трассировку профилировщика, вы можете получить оценочный план, проверив кеш-план SQL-запросов.

Мы проверяем кеш плана, запрашивая SQL Server DMVs . Ниже приведен базовый запрос, в котором будут перечислены все кэшированные планы запросов (как xml) вместе с их текстом SQL. В большинстве баз данных вам также необходимо будет добавить дополнительные условия фильтрации, чтобы отфильтровать результаты вплоть до планов, которые вас интересуют.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Выполните этот запрос и щелкните на плане XML, чтобы открыть план в новом окне - щелкните правой кнопкой мыши и выберите «Сохранить план выполнения как...», чтобы сохранить план в файл в формате XML.

Заметки:

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

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

«фактические» и «оценочные» планы выполнения

План выполнения фактический - это тот, где SQL Server фактически выполняет запрос, тогда как план выполнения оцененный SQL Server выполняет то, что он делает без выполнения запрос. Хотя логически эквивалентный, фактический план выполнения намного полезнее, поскольку он содержит дополнительные данные и статистику о том, что на самом деле произошло при выполнении запроса. Это важно при диагностике проблем, когда оценки SQL-серверов отключены (например, когда статистика устарела).

Как интерпретировать план выполнения запроса?

Это тема, достойная достаточно для бесплатного в своем собственном праве.

SQL Server 2008 - использование хеш-запросов и хэш-планов запроса

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

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Пример StartCapture Определение

CREATE PROCEDURE StartCapture @Spid INT, @TraceID INT OUTPUT AS DECLARE @maxfilesize BIGINT = 5 DECLARE @filepath NVARCHAR(200) = N"C:\trace_" + LEFT(NEWID(),36) EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL exec sp_trace_setevent @TraceID, 122, 1, 1 exec sp_trace_setevent @TraceID, 122, 22, 1 exec sp_trace_setevent @TraceID, 122, 34, 1 exec sp_trace_setevent @TraceID, 122, 51, 1 exec sp_trace_setevent @TraceID, 122, 12, 1 -- filter for spid EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid -- start the trace EXEC sp_trace_setstatus @TraceID, 1

Пример StopCapture Определение

CREATE PROCEDURE StopCapture @TraceID INT AS WITH XMLNAMESPACES ("http://schemas.microsoft.com/sqlserver/2004/07/showplan" as sql), CTE as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData, ObjectID, ObjectName, EventSequence, /*costs accumulate up the tree so the MAX should be the root*/ MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM fn_trace_getinfo(@TraceID) fn CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1) CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x CROSS APPLY (SELECT T.relop.value("@EstimatedTotalSubtreeCost", "float") AS EstimatedTotalSubtreeCost FROM xPlan.nodes("//sql:RelOp") T(relop)) ca WHERE property = 2 AND TextData IS NOT NULL AND ObjectName not in ("StopCapture", "fn_trace_getinfo") GROUP BY CAST(TextData AS VARCHAR(MAX)), ObjectID, ObjectName, EventSequence) SELECT ObjectName, SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM CTE GROUP BY ObjectID, ObjectName -- Stop the trace EXEC sp_trace_setstatus @TraceID, 0 -- Close and delete the trace EXEC sp_trace_setstatus @TraceID, 2 GO

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

Вы можете установить и интегрировать план ApexSQL в SQL Server Management Studio, поэтому планы выполнения можно просматривать непосредственно из SSMS.

Просмотр расчетных планов выполнения в Плане ApexSQL

Нажмите кнопку Новый запрос в SSMS и вставьте текст запроса в текстовое окно запроса. Щелкните правой кнопкой мыши и выберите «Отображать примерный план выполнения» в контекстном меню.

На диаграмме плана выполнения будет показана вкладка Планирование выполнения в разделе результатов. Затем щелкните правой кнопкой мыши план выполнения и в контекстном меню выберите вариант «Открыть в ApexSQL Plan».

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

Просмотр фактических планов выполнения в Плане ApexSQL

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

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

Более подробную информацию о просмотре планов выполнения можно найти, следуя этой ссылке .

Мой любимый инструмент для получения и глубокого анализа планов выполнения запросов - SQL Sentry Plan Explorer . Это гораздо удобнее, удобнее и полно для детального анализа и визуализации планов выполнения, чем SSMS.

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

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

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

UPDATE: (Thanks to Martin Smith ) Plan Explorer now is free! See http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view for details.

Планы запросов можно получить в сеансе расширенных событий через событие query_post_execution_showplan . Вот пример сеанса XEvent:

/* Generated via "Query Detail Tracking" template. */ CREATE EVENT SESSION ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)), /* Remove any of the following events (or include additional events) as desired. */ ADD EVENT sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.sql_batch_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), ADD EVENT sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

После создания сеанса (в SSMS) перейдите в Обозреватель объектов и перейдите в раздел Управление | Расширенные события | Сессии. Щелкните правой кнопкой мыши сеанс «GetExecutionPlan» и запустите его. Щелкните его правой кнопкой мыши и выберите «Watch Live Data».

Затем откройте новое окно запроса и запустите один или несколько запросов. Вот для AdventureWorks:

USE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO

Через минуту или два вы увидите некоторые результаты на вкладке «GetExecutionPlan: Live Data». Выберите одно из событий query_post_execution_showplan в сетке, а затем щелкните вкладку «План запроса» под сеткой. Он должен выглядеть примерно так:

EDIT : The XEvent code and the screen shot were generated from SQL/SSMS 2012 w/ SP2. If you"re using SQL 2008/R2, you might be able to tweak the script to make it run. But that version doesn"t have a GUI, so you"d have to extract the showplan XML, save it as a *.sqlplan file and open it in SSMS. That"s cumbersome. XEvents didn"t exist in SQL 2005 or earlier. So, if you"re not on SQL 2012 or later, I"d strongly suggest one of the other answers posted here.

Начиная с SQL Server 2016+, для мониторинга производительности была представлена функция Query Store. Он дает представление о выборе плана выполнения и производительности. Это не полная замена трассировки или расширенных событий, но поскольку она развивается от версии к версии, мы можем получить полностью функциональный магазин запросов в будущих выпусках SQL Server. Основной поток Query Store

SQL Server existing components interact with query store by utilising Query Store Manager.
Query Store Manager determines which Store should be used and then passes execution to that store (Plan or Runtime Stats or Query Wait Stats)
- Plan Store - Persisting the execution plan information
- Runtime Stats Store - Persisting the execution statistics information
- Query Wait Stats Store - Persisting wait statistics information.
Plan, Runtime Stats and Wait store uses Query Store as an extension to SQL Server.

Enabling the Query Store : Query Store works at the database level on the server.

Query Store is not active for new databases by default.
You cannot enable the query store for the master or tempdb database.
Available DMV

Collect Information in the Query Store : We collect all the available information from the three stores using Query Store DMV (Data Management Views).
- Query Plan Store: Persisting the execution plan information and it is accountable for capturing all information that is related to query compilation.
  
  Runtime Stats Store: Persisting the execution statistics information and it is probably the most frequently updated store. These statistics represent query execution data.
  . У последнего также есть полезная бонусная функция выбора плана для конкретного оператора , а не всей партии. Вот как вы используете его для просмотра планов текущих запущенных операторов:
  SELECT p.query_plan FROM sys.dm_exec_requests AS r OUTER APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS p
  Текстовый столбец в результирующей таблице, однако, не очень удобен по сравнению с столбцом XML. Чтобы иметь возможность щелкнуть результат, который будет открыт на отдельной вкладке в виде диаграммы, без необходимости сохранять его содержимое в файл, вы можете использовать небольшой трюк (помните, что вы не можете просто использовать CAST (... AS XML)), хотя это будет работать только для одной строки:
  SELECT Tag = 1, Parent = NULL, = query_plan FROM sys.dm_exec_text_query_plan(-- set these variables or copy values -- from the results of the above query @plan_handle, @statement_start_offset, @statement_end_offset) FOR xml EXPLICIT

Александр Куклин написал отличную статью «Кэш планов и параметризация запросов. Часть 1. Анализ кэша планов «. Всем рекомендую к ознакомлению.

Вот небольшая вырезка из неё:

Процессор запросов (query processor), который и занимается выполнением SQL-запросов, поступивших на SQL-сервер, и выдачей их результатов клиенту, состоит из двух основных компонентов:

Оптимизатор запросов (Query Optimizer).

Исполнитель запросов (Relational Engine).

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

Синтаксический анализатор (Parser) просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы, а также производит нормализацию запроса.

Из синтаксического анализатора данные попадают на вход компонента Algebrizer, который выполняет семантический анализ текста. Algebrizer проверяет существование указанных в запросе объектов базы данных и их полей, корректность использования операторов и выражений запроса, и извлекает из кода запроса литералы, для обеспечения возможности использования автоматической параметризации.
Например, именно поэтому запрос, имеющий в секции SELECT поля, не содержащиеся ни в агрегатных функциях, ни в секции GROUP BY, пройдёт в SQL Server Management Studio (SSMS) проверку по Ctrl+F5 (синтаксический анализ), но свалится с ошибкой при попытке запуска по F5 (не пройдёт семантический анализ).

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

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

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

Всем привет! Столкнулся тут недавно я с проблемой долгого проведения документа.

Вводные данные: конфигурация «Управление производственным предприятием, редакция 1.3 (1.3.52.1)», документ «Платежное поручение входящее». Жалоба: проведение в рабочей базе длится 20-30 секунд, что интересно в копии базы этот же документ проводится 2-4 секунды. О расследованиях и причине такого поведения читайте ниже.

Итак, с помощью замера производительности , как его использовать думаю, все знают, было найдено место-виновник:

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

По данным замера производительности эта операция занимала 13 секунд, если посчитать среднее, то получается 0,5 секунды на регистр, вечность просто!

Как мы все знаем, запись мы оптимизировать не можем, но тут явно что-то не так.
Для дальнейшего анализа открываем SQL Server Profiler и . Для анализа использовал классы событий:

Showplan Statistics Profile
Showplan XML Statistics Profile
RPC Completed
SQL:BatchCompleted .

В настройках трассировки есть фильтр по SPID :

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

Отображается в том случае, если в данный момент соединение с базой данных захвачено сеансом: либо выполняется вызов СУБД, либо открыта транзакция, либо удерживается объект «МенеджерВременныхТаблиц», в котором создана хотя бы одна временная таблица.

Напишем обработку для удержания SPID, в ней будет одна процедура:

Важно, чтобы удерживаемый объект соединения, в нашем случае менеджер временных таблиц, был определен как переменная обработки. Открываем обработку, запускаем процедуру и до тех пор, пока она открыта, SPID будет зафиксирован. Открываем консоль администрирования серверов 1С:

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

Также в глаза бросилось количество чтений — 1872578 , но я сразу не придал этому значения и начал разбирать, что и с какой таблицей тут делается.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

Как видно по запросу SQL обрабатывается таблица «AccRg1465» это таблица регистра бухгалтерии Хозрасчетный. Текстовое представление плана выполнение запроса:

Как видно по плану выполнения запросу SQL ничего страшного не происходит, обрабатывается таблица «AccRg1465 », везде используется поиск по кластерному индексу.

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

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

Текст запроса в SQL:

EXEC sp_executesql N"SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND ((((T1._Period <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)" , 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9A95A0369F30F8DB11E46684B4F0A05F, "4018-05-31 23:59:59"

Графическое представление плана запроса:

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

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

DBCC FLUSHPROCINDB(< database_id >)

где < database_id > — это идентификатор базы. Чтобы узнать идентификатор базы нужно выполнить скрипт

select name, database_id from sys . databases

он вернет нам список баз и их идентификаторы.

Получаем опять трассировку:

Текстовое представление плана запроса: