Создать триггер для регистрации SQL, который повлиял на таблицу?
Я пытаюсь выяснить, что обновляет значение столбца, и я очень мало знаю о приложении. Я заметил, что около 90% бизнес-логики приложений обрабатывается в базе данных. Излишне говорить, что глубина SP, функций и триггеров сумасшедшая.
Я хотел бы создать триггер для рассматриваемой таблицы, который будет регистрировать SQL, который повлиял на таблицу. Какой SQL можно использовать для получения исполняемого SQL в контексте обновляемой таблицы?
Подробности: MS SQL Server 2008
Спасибо!!
5 ответов
Мое решение
Я добавил в таблицу триггер, который регистрировал информацию, которую я сузил с помощью временных меток sys.dm_exec_sql_text А ТАКЖЕ sys.dm_exec_query_stats, Это быстро прибило то, что я искал. Оказывается, было несколько триггеров, о которых я не знал, которые обновляли данные после ОБНОВЛЕНИЯ.
SELECT
qStats.last_execution_time AS [ExecutedAt],
qTxt.[text] AS [Query], qTxt.number
FROM
sys.dm_exec_query_stats AS qStats
CROSS APPLY
sys.dm_exec_sql_text(qStats.sql_handle) AS qTxt
WHERE
qTxt.[dbid] = @DbId
AND qTxt.[text] like '%UPDATE%'
AND qStats.last_execution_time between @StartExecutionSearchTime and @EndExecutionSearchTime
ORDER BY
qStats.last_execution_time DESC
Я понимаю, что эта проблема уже решена, но меня интересовало, как ее можно решить с помощью расширенных событий SQL Server 2008. Это моя первая игра с XEvents, так что я уверен, что есть что улучшить!
Скрипт для настройки тестовой базы данных
CREATE TABLE [dbo].[TableWithMysteryUpdate](
[Period] [int] NOT NULL,
[ColumnThatWillBeUpdated] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TableWithMysteryUpdate] ([Period], [ColumnThatWillBeUpdated]) VALUES (1, 20)
INSERT [dbo].[TableWithMysteryUpdate] ([Period], [ColumnThatWillBeUpdated]) VALUES (2, 23)
GO
CREATE TABLE [dbo].[TestTable](
[foo] [int] IDENTITY(1,1) NOT NULL,
[bar] [nchar](10) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[foo] ASC
)
)
GO
CREATE TRIGGER [dbo].[triggerCausingMysteryUpdate]
ON [dbo].[TestTable]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[TableWithMysteryUpdate]
SET [Period] = [Period]+1
END
GO
CREATE PROCEDURE [dbo].[Proc4]
AS
BEGIN
INSERT INTO [dbo].[TestTable]
(
[bar])
VALUES
('Test')
END
GO
CREATE PROCEDURE [dbo].[Proc3]
AS
BEGIN
EXEC dbo.Proc4
END
GO
CREATE PROCEDURE [dbo].[Proc2]
AS
BEGIN
EXEC dbo.Proc3
END
GO
CREATE PROCEDURE [dbo].[Proc1]
AS
BEGIN
EXEC dbo.Proc2
END
Таким образом, сценарий таков, что TableWithMysteryUpdate обновляется, но я не уверен, чем. Я добавлю триггер обновления, который ничего не делает, чтобы можно было фильтровать этот объект.
CREATE TRIGGER [dbo].[triggerAfterUpdate]
ON [dbo].[TableWithMysteryUpdate]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
END
Затем запустите сценарий для создания сеанса XEvents, запустите процедуру, которая в конечном итоге приведет к сбою стека вызовов, и произойдет обновление, а затем остановите сеанс.
USE TestDB
DECLARE @DynSql nvarchar(max)
SET @DynSql = '
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=''test_trace'')
DROP EVENT SESSION [test_trace] ON SERVER;
CREATE EVENT SESSION [test_trace]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)
WHERE (object_id = ' + cast(object_id('[dbo].[triggerAfterUpdate]') as varchar(10)) + ')
)
,
ADD EVENT sqlserver.sp_statement_completed(
ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)
WHERE (object_id = ' + cast(object_id('[dbo].[triggerAfterUpdate]') as varchar(10)) + ')
)
ADD TARGET package0.asynchronous_file_target
(set filename = ''c:\temp\test_trace.xel'' , metadatafile = ''c:\temp\test_trace.xem'')
ALTER EVENT SESSION [test_trace] ON SERVER STATE = START
'
EXEC sp_executesql @DynSql
GO
EXEC dbo.Proc1
GO
ALTER EVENT SESSION [test_trace] ON SERVER STATE = STOP
Данные трассировки могут быть прочитаны с
SELECT CONVERT (XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel', 'C:\Temp\test_trace*.xem', NULL, NULL)
Часть стека вызовов
<action name="tsql_stack" package="sqlserver">
<value><frame level='1' handle='0x03000800E8EA0D0612E4EB00A59D00000000000000000000' line='6' offsetStart='228' offsetEnd='264'/>
<frame level='2' handle='0x03000800921155002C81E700A59D00000000000000000000' line='8' offsetStart='258' offsetEnd='398'/>
<frame level='3' handle='0x03000800CB3549012F81E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='284'/>
<frame level='4' handle='0x03000800045A3D022F81E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='120'/>
<frame level='5' handle='0x030008003D7E31033081E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='120'/>
<frame level='6' handle='0x0300080076A225043081E700A59D00000100000000000000' line='5' offsetStart='90' offsetEnd='120'/>
<frame level='7' handle='0x010008002E775203603D9A0D000000000000000000000000' line='2' offsetStart='4' offsetEnd='-1'/></value>
<text />
</action>
Присоединение к DMV
WITH CapturedResults AS
( SELECT data.value ( '(/event/@timestamp)[1]', 'DATETIME') AS [TIME],
data.value ( '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)],
CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000 AS [Duration (s)],
data.value ( '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL STATEMENT],
CAST(data.value('(/event/action[@name="tsql_stack"]/value)[1]','varchar(MAX)') AS XML) AS [stack_xml]
FROM (SELECT CONVERT (XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel', 'C:\Temp\test_trace*.xem', NULL, NULL)
)
entries
)
,
StackData AS
( SELECT frame_xml.value('(./@level)', 'int') AS [frame_level],
frame_xml.value('(./@handle)', 'varchar(MAX)') AS [sql_handle],
frame_xml.value('(./@offsetStart)', 'int') AS [offset_start],
frame_xml.value('(./@offsetEnd)', 'int') AS [offset_end]
FROM CapturedResults CROSS APPLY stack_xml.nodes('//frame') N (frame_xml)
)
SELECT sd.frame_level,
object_name(st.objectid, st.dbid) AS ObjectName,
SUBSTRING(st.text, (sd.offset_start/2)+1, ((
CASE sd.offset_end
WHEN -1
THEN DATALENGTH(st.text)
ELSE sd.offset_end
END - sd.offset_start)/2) + 1) AS statement_text,
qp.query_plan,
qs2.creation_time,
qs2.last_execution_time,
qs2.execution_count
FROM StackData AS sd CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(MAX),sd.sql_handle,1)) AS st
LEFT OUTER JOIN sys.dm_exec_query_stats qs2
ON qs2.sql_handle = CONVERT(VARBINARY(MAX),sd.sql_handle,1) OUTER APPLY sys.dm_exec_query_plan(CONVERT(VARBINARY(MAX),qs2.plan_handle,1)) AS qp
дает результаты (показывает весь стек вызовов tsql)
Помимо использования триггеров, которые будут срабатывать при изменении данных, вы можете использовать трассировки SQL Server и анализировать их в SQL Profiler или стороннем инструменте аудита, который отслеживает выполненный код для изменений DML.
Используя ApexSQL Comply, вы можете сузить аудит только конкретного объекта базы данных (в данном случае таблицы) и типа события.
Отчеты показывают различную полезную информацию, включая выполненный оператор SQL, который инициировал изменение.
Отказ от ответственности: я работаю на ApexSQL в качестве инженера службы поддержки
Пожалуйста, проголосуйте за этот элемент Microsoft Connect, и посмотрите этот обходной путь стека вызовов.
SQL Server 2008 представил новую функцию, называемую "Сбор данных изменений" (CDC), вместо использования триггеров. Подробнее об этом читайте здесь.