SQL сервер: Сбор и анализ статистики по выполняемым процедурам на продуктивном сервере

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

По мотивам Finding the Causes of Poor Performance in SQL Server, Part 1 https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Получение данных трассировки

С этой целью мы могли бы воспользоваться SQL Server Profiler, однако в условиях продуктивной среды его использование не рекомендуется

Мы будем использовать трассировку на стороне сервера (server-side trace)

Создание скрипта трассировки

Для создания скрипта трассировки мы можем воспользоваться SQL server Profiler на тестовом сервере

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

После того как мы запустили и остановили трассировку, можно сгенерировать скрипт трассировки

После его открытия в SQL query мы получим сгенерированный скрипт:

/****************************************************/

/****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 22/09/2014  09:40:38         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 146, 1, @on
exec sp_trace_setevent @TraceID, 146, 3, @on
exec sp_trace_setevent @TraceID, 146, 12, @on
exec sp_trace_setevent @TraceID, 146, 14, @on
exec sp_trace_setevent @TraceID, 146, 51, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 51, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 51, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @bigintfilter = 1000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Перед запуском его необходимо доработать. Добавим:

--указываем путь и имя файла
SET @OutputFileName = 'E:MSSQL_Trace' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

--время окончания через 30 мин
SET @EndTime = DATEADD(mi,30,getdate())

SELECT *
FROM   sys.traces;

Окончательная версия скрипта:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint

DECLARE @OutputFileName NVARCHAR(256)
DECLARE @EndTime DATETIME

set @maxfilesize = 100

--указываем путь и имя файла
SET @OutputFileName = 'E:MSSQL_Trace' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

--время окончания через 30 мин
SET @EndTime = DATEADD(mi,30,getdate())

--трассировка прекратится при наступлении времени окончания
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 146, 1, @on
exec sp_trace_setevent @TraceID, 146, 3, @on
exec sp_trace_setevent @TraceID, 146, 12, @on
exec sp_trace_setevent @TraceID, 146, 14, @on
exec sp_trace_setevent @TraceID, 146, 51, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 51, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 51, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @bigintfilter = 1
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1


SELECT *
FROM   sys.traces;

Осталось запустить трассировку в пик нагрузки и подождать полчаса.

Расшифровка трассировки

Поместим данные расшифровки в таблицу SQL. Я использую отдельную вспомогательную базу.

USE [ExchangeDB]

CREATE TABLE TraceResults (
EventClass VARCHAR(100),
TextData VARCHAR(4000),
PlanXML XML,
Duration INT,
RowCounts INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100),
EventSequence INT
)
GO

DECLARE @FileName NVARCHAR(256)
SET @FileName = 'E:MSSQL_Trace20140922101559.trc'

INSERT INTO TraceResults
(EventClass, TextData, Duration, Reads, RowCounts, Writes, CPU, StartTime, EventSequence)
SELECT EventClass, Convert(VARCHAR(4000),TextData) as TextData,
Duration, RowCounts, Reads, Writes, CPU, StartTime, EventSequence
FROM fn_trace_gettable(@FileName,1)
WHERE EventClass 146

UPDATE TraceResults
SET ProcedureName =
Convert(VARCHAR(100),TextData)
WHERE ProcedureName is NULL

INSERT INTO TraceResults
(EventClass, PlanXML, EventSequence)
SELECT EventClass,
Convert(XML,TextData) as PlanXML,
EventSequence
FROM fn_trace_gettable(@FileName,1)
WHERE EventClass = 146

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

SELECT
TR1.EventSequence
,TR2.EventSequence
,TR1.TextData
,TR2.PlanXML
,TR1.Duration AS Duration
,TR1.Reads
,TR1.Writes
,TR1.CPU
,TR1.StartTime
FROM  dbo.TraceResults AS TR1
LEFT OUTER JOIN
dbo.TraceResults AS TR2
ON TR1.EventSequence-1 = TR2.EventSequence

WHERE (TR1.TextData IS NOT NULL
AND TR2.PlanXML IS NOT NULL)

ORDER BY Duration Desc

Второй группирует по полю ProcedureName и суммирует данные по показателям

USE [ExchangeDB]


SELECT
TR1.EventSequence
,TR2.EventSequence
,TR1.ProcedureName
,TR2.PlanXML
,TR1.DurationTot
,TR1.RowCountsTot
,TR1.ReadsTot
,TR1.WritesTot
,TR1.CPUTot
FROM
(   SELECT
ProcedureName
,MAX(EventSequence) AS EventSequence
,SUM(Duration) as DurationTot
,SUM(Reads) as ReadsTot
,SUM(RowCounts) as RowCountsTot
,SUM(Writes) as WritesTot
,SUM(CPU) as CPUTot
FROM TraceResults
GROUP BY ProcedureName) TR1
LEFT OUTER JOIN
dbo.TraceResults AS TR2
ON TR1.EventSequence-1 = TR2.EventSequence
WHERE (TR1.ProcedureName IS NOT NULL
AND TR2.PlanXML IS NOT NULL)

ORDER BY DurationTot DESC

В нашем случае, по итогам двух запросов, лидирует вставка во временную таблицу #tt14 PlanXML из первой строчки:

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

14 Comments

nixel 25.09.2014 at 01:31

Сжато и по делу. Спасибо.

Reply ↓
lustin 25.09.2014 at 23:49

(0) отлично. Но вынужден заметить.

1. Да действительно — MSSQL с 2005 года имеет функционал «Рекомендации индексов». Как читать данную рекомендацию — применительно к 1С системам: тема достаточно обширная и о неё сломано много копий, но и сделано достаточно много инструментов

а. Enterprise Integrator

б. Сервисы Гилева

в. etc

2. трассировка на стороне SQL тоже достаточно интересная тема, и Microsoft достаточно широко ее осветил (в том числе в виде докладов) — на самом деле в продуктиве не рекомендуется использовать трассировку В ПАМЯТИ, а менять флаг на трассировку в файл или в базу.

3. трассировать средствами SQL сейчас достаточно странно — данная функциональность включена в Технологический журнал — можно посмотреть как это сделано в:

а. Инструментах разработчика

б. Enterprise Integrator

в. сервисы Гилева

г. etc

Reply ↓
jan27 26.09.2014 at 00:25

(2) в технологическом журнале или в сервисе Гилева есть план выполнения запроса?

Reply ↓
lustin 26.09.2014 at 00:38

(3) ну да — пример как его используют Инструменты разработчика — http://devtool1c.ucoz.ru/index/analiz_tekhnozhurnala/0-16

Это достаточно старая уже функциональность — пару лет как.

Reply ↓
jan27 26.09.2014 at 00:41

(4) при всем уважении, стараюсь пользоваться первоисточниками, нежели сторонними «пришлепками»

Reply ↓
lustin 26.09.2014 at 00:50

(5) не совсем понял про пришлепки. Что-то как-то не совсем уважительно 😉

Получения плана запросов с помощью ТЖ — типовая функциональность платформы.

Однако на экзамене «Эксперт по производительности 1С» дают принудительно знание еще и SQL профайлера.

Что вы считаете первоисточником ?

Reply ↓
jan27 26.09.2014 at 00:58

(6) могу предположить, что первоисточник планов запросов и оптимизатор запросов все-таки SQL сервер, а не платформа 1С

Reply ↓
lustin 26.09.2014 at 01:19

(7) а …. вы в этом смысле. ну конечно — это так и есть.

правда про пришлепки все равно не понял. 1С использует стандартный доступ к плану запросов через ядро SQL, тот-же доступ использует компонент трассировки MSSQL.

Profiler то — это же не компонент ядра MSSQL

ТЖ позволяет вам объединить сам запрос и его инициатора в коде (строка/модуль) — что как-бы для расследования несколько удобней.

А также имена полей сразу преобразует к именам в терминах 1С — удобней же ?

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

Reply ↓
jan27 26.09.2014 at 01:27

(8) тогда уж нужно было упомянуть о ЦУП

Reply ↓
lustin 26.09.2014 at 01:33

(9) упаси меня господь ночью ЦУП поминать 😉

Reply ↓
jan27 26.09.2014 at 01:35

(10) звучит как-то неуважительно 🙂

Reply ↓
tormozit 28.09.2014 at 10:45

ЦУП при всем уважении «дырявое и очень долгое решето». В реальных задачах по моему опыту оказывается часто неэффективен. Хотя сама идея продукта покрывает реальную нишу, но видимо за счет неряшливой реализации или плохого тестирования часто не выявляет проблемы, когда она есть.

Reply ↓
Gilev.Vyacheslav 13.12.2014 at 12:57

(3) в наших сервисах есть план выполнения, но зачем же «читать книги», когда проще написать «нетленку» )))

(12) а что наши сервисы тоже решето?

Reply ↓
jan27 13.12.2014 at 17:53

(13) а в чем нетленка? в использовании трассировки? Вы хотите сказать, что не нужно пользоваться стандартными средствами SQL, а сразу обращаться к Вам?

Reply ↓

SQL сервер: Сбор и анализ статистики по выполняемым процедурам на продуктивном сервере

Получение данных трассировки

Создание скрипта трассировки

Расшифровка трассировки

Related Posts

14 Comments

Leave a Comment Отменить ответ