gifts2017

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

Опубликовал Alexei Zhovner (jan27) в раздел Администрирование - Оптимизация БД (HighLoad)

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

По мотивам 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:\MyFolder\MyTrace. 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_Trace\20140922101559.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 из первой строчки:

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

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Никита Грызлов (nixel) 25.09.14 01:31
Сжато и по делу. Спасибо.
2. Алексей Лустин (lustin) 25.09.14 23:49
(0) отлично. Но вынужден заметить.

1. Да действительно - MSSQL с 2005 года имеет функционал "Рекомендации индексов". Как читать данную рекомендацию - применительно к 1С системам: тема достаточно обширная и о неё сломано много копий, но и сделано достаточно много инструментов
а. Enterprise Integrator
б. Сервисы Гилева
в. etc

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

3. трассировать средствами SQL сейчас достаточно странно - данная функциональность включена в Технологический журнал - можно посмотреть как это сделано в:
а. Инструментах разработчика
б. Enterprise Integrator
в. сервисы Гилева
г. etc
3. Alexei Zhovner (jan27) 26.09.14 00:25
(2) в технологическом журнале или в сервисе Гилева есть план выполнения запроса?
4. Алексей Лустин (lustin) 26.09.14 00:38
(3) ну да - пример как его используют Инструменты разработчика - http://devtool1c.ucoz.ru/index/analiz_tekhnozhurnala/0-16

Это достаточно старая уже функциональность - пару лет как.
5. Alexei Zhovner (jan27) 26.09.14 00:41
(4) при всем уважении, стараюсь пользоваться первоисточниками, нежели сторонними "пришлепками"
6. Алексей Лустин (lustin) 26.09.14 00:50
(5) jan27, не совсем понял про пришлепки. Что-то как-то не совсем уважительно ;-)

Получения плана запросов с помощью ТЖ - типовая функциональность платформы.
Однако на экзамене "Эксперт по производительности 1С" дают принудительно знание еще и SQL профайлера.

Что вы считаете первоисточником ?
7. Alexei Zhovner (jan27) 26.09.14 00:58
(6) могу предположить, что первоисточник планов запросов и оптимизатор запросов все-таки SQL сервер, а не платформа 1С
8. Алексей Лустин (lustin) 26.09.14 01:19
(7) jan27, а .... вы в этом смысле. ну конечно - это так и есть.

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

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

Я вообще отреагировал на Вашу фразу "С этими данными можно дальше работать" в конце статьи. Поэтому и сказал - если необходимо дальше работать, то использование чистых трассировок без просмотра кода и метаданных будет проблемных. Поэтому и указал на инструментарий для этого.
9. Alexei Zhovner (jan27) 26.09.14 01:27
(8) тогда уж нужно было упомянуть о ЦУП
10. Алексей Лустин (lustin) 26.09.14 01:33
(9) jan27, упаси меня господь ночью ЦУП поминать ;-)
11. Alexei Zhovner (jan27) 26.09.14 01:35
(10) звучит как-то неуважительно :)
12. Сергей Старых (tormozit) 28.09.14 10:45
ЦУП при всем уважении "дырявое и очень долгое решето". В реальных задачах по моему опыту оказывается часто неэффективен. Хотя сама идея продукта покрывает реальную нишу, но видимо за счет неряшливой реализации или плохого тестирования часто не выявляет проблемы, когда она есть.
13. Вячеслав Гилёв (Gilev.Vyacheslav) 13.12.14 12:57
(3) jan27, в наших сервисах есть план выполнения, но зачем же "читать книги", когда проще написать "нетленку" )))
(12) а что наши сервисы тоже решето?
14. Alexei Zhovner (jan27) 13.12.14 17:53
(13) а в чем нетленка? в использовании трассировки? Вы хотите сказать, что не нужно пользоваться стандартными средствами SQL, а сразу обращаться к Вам?
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа