С ростом баз данных вопросы производительности рано или поздно возникают, тем более, что разработчики, зачастую, уделяют недостаточное внимание вопросам производительности, откладывая оптимизацию на потом. Кроме этого, во время разработки архитектуры базы, зачастую, неизвестно какие именно поля лучше проиндексировать. Построение избыточных индексов тоже снижает производительность.
Перед нами стоит задача выявить «тяжелые» или часто используемые запросы в продуктивной среде, проанализировать данные и выявить способ оптимизации.
По мотивам 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 из первой строчки:
В итоге мы имеем данные, с которыми можно дальше работать. В частности, на рисунке выше предлагается добавить индексы.
Начать дискуссию