Автоматизация учета

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 из первой строчки:

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

💖 Love is... когда обучение — не обязанность, а удовольствие!

🔥 Главбух на УСН
Освойте УСН с любовью и минимизируйте риски. Эксклюзивная программа с учётом налоговой реформы-2025! 💰 Рассрочка: 3 725 ₽ × 4 месяца

🔥 Бухгалтер с нуля: учет, налоги, 1С
Начните с нуля и обретите профессию с открытым сердцем. Всё, что нужно для успешного старта, за 4 месяца! 💰 Рассрочка: 2 725 ₽ × 4 месяца

🔥 Бухгалтер на ОСНО
Освойте отчетность на ОСНО с заботой о каждой детали. Избегайте ошибок и штрафов с помощью уникальной практики от экспертов! 💰 Рассрочка: 1 725 ₽ × 4 месяца

✨ Вкладывайте в знания с любовью — ваш успех начинается здесь!

Посмотреть все курсы

Начать дискуссию


Похожие материалы

Медицина

Минздрав отменил виагру. Как поднимать страну?

Об исчезновении известного препарата с российского рынка.

Зарплата

Сбер: в России выравнивается рост зарплат

В 2024 году доходы граждан страны в среднем увеличились на 17,7%.

Кабмин направил 2,5 млрд рублей на помощь аграриям Курской области

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

Курсы повышения
квалификации

22
Официальное удостоверение с занесением в госреестр Рособрнадзора
ЭДО

Налоговое требование теперь считается полученным на 6-й день после отправки по ТКС

С 5 февраля 2025 года вступили в силу поправки в НК, которые освобождают налогоплательщиков от обязанности направлять квитанции о получении электронных документов от ИФНС.

Инвестиции

🔥Позвонил так позвонил. Итоги 13 февраля на Московской бирже

Основное геополитическое событие прошло вчера после закрытия основной торговой сессии — это долгожданный телефонный звонок Трампа нашему президенту.

Блогеру Блиновской сократили исковые требования на 300 млн рублей

Налоговые доначисления Елене Блиновской снизили на 300 млн. Теперь блогер должна вернуть в бюджет больше 580 млн рублей.

mos-mo
Перевод

перевод денег ИП себе на карту

Подскажите, пожалуйста, насколько корректно переводить деньги ИП себе на карту с кодом заработная плата?
В том банке, в котором у нас открыл зарплатный проект - нет...

Читать полностью

Эксперт:

Надежда Камышева

Надежда Камышева
Эксперт

Жалобы ни к чему не приведут. Банки сами устанавливают правила ПОД/ТФ и этот банк считает ИП подозрительным. Потому что деньги проходят чисто...

Читать полностью

Суд при­знал банк «Га­рант-Ин­вест» банк­ро­том

Арбитражный суд города Москвы признал 12 февраля 2025 года КБ «Гарант-Инвест» (АО) (г. Москва) несостоятельным (банкротом).

К 2033 году прогнозируется удвоение рынка биотехнологий

Об этом сообщил член комитета Совета Федерации по экономической политике Иван Евстифеев при обсуждении перспектив развития промышленных производств на основе биотехнологий.

Если не сообщить в военкомат о переезде, смогут оштрафовать на 20 тысяч рублей

В Госдуме хотят с 10 до 20 тысяч рублей увеличить размер штрафа за нарушения сфере воинского учета.

ФНС заподозрит неладное, если после договоров ГПХ самозанятых приняли в штат. 👤«Ночной бухгалтер» № 1870

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

ФНС заподозрит неладное, если после договоров ГПХ самозанятых приняли в штат. 👤«Ночной бухгалтер» № 1870

Как в 2025 году признавать доходы по разным договорам, в том числе прошлых лет

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

Как в 2025 году признавать доходы по разным договорам, в том числе прошлых лет

⚡️ Итоги дня: брокеры отменяют заявки на покупку акций, DeepSeek регистрирует бренд в РФ, в Крыму национализируют еще 700 объектов

Подготовили обзор главных событий дня — 13 февраля 2025 года. Все самое интересное, что писали и обсуждали в сети, в одной подборке.

Налоговые каникулы ИП в 2025 году

Регионам предоставлено право освободить от налогов предпринимателей на патенте и на «упрощенке», при соблюдении ряда условий. В 2024 году был принят закон о продлении налоговых каникул до конца 2026 года, так что у ИП есть время воспользоваться льготой. Разберем, какие условия нужно соблюсти.

Иллюстрация: Вера Ревина/Клерк.ру
НДФЛ

Нужно ли уведомлять налоговую, что доход для НДФЛ стал больше 5 млн. ФНС ответила

Физлицо может трудиться на нескольких работах, где-то по трудовому договору, а где-то договору ГПХ. И в совокупности доход за год может превысить 5 млн рублей.

📞 Работодатель не может обязать сотрудника использовать личный телефон в рабочих целях

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

УСН

Проценты по займу облагаются налогом по УСН, даже если заем – из целевых средств

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

Если компания решилась на переоценку ОС, это надо делать регулярно

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

Обучение для бухгалтеров

Какие отчеты сдаем за 1 квартал 2025 года

Сделали для вас шпаргалку по отчетности в налоговую и СФР за 1 квартал 2025 года. Приложили новые формы деклараций с примерами заполнения, а также полезные материалы для подготовки.

Иллюстрация: Вера Ревина/Клерк.ру

👔 Госдума изменит правила работы совета директоров

При наступлении определенных обстоятельств члены совета директоров могут считаться выбывшими. Их голоса перестанут учитывать.

Интересные материалы

Спорные ситуации для ставок 5, 7, 10 и 20% по НДС

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

Спорные ситуации для ставок 5, 7, 10 и 20% по НДС
5