This is the tenth installment in the 12 day series for SQL tidbits during this holiday season.
Previous articles in this mini-series on quick tidbits:
- SQL Sat LV announcement
- Burning Time
- Reviewing Peers
- Broken Broker
- Peer Identity
- Lost in Space
- Command ‘n Conquer
- Ring in The New
- Queries Going Boom
Gathering event information is a pretty good thing. It can do wonders for helping to troubleshoot. What do you do if you need or want to be able to review the captured information in 3 months or maybe 12 months from now?
Retaining the session data for later consumption is often a pretty essential piece of the puzzle. There is more than one way to accomplish that goal. I am going to share one method that may be more like a sledgehammer for some. It does require that Management Data Warehouse be enabled prior to implementing.
When using MDW to gather and retain the session data, you create a data collector pertinent to the data being collected and retained. In the following example, I have a data collector that is created to gather deadlock information from the system health session. In this particular case, I query the XML in the ring buffer to get the data that I want. Then I tell the collector to gather that info every 15 minutes. The collection interval is one of those things that needs to be adjusted for each environment. If you collect the info too often, you could end up with a duplication of data. Too seldom and you could miss some event data. It is important to understand the environment and adjust accordingly.
Here is that example.
BEGIN TRANSACTION
BEGIN Try
DECLARE @collection_set_id_1 INT
DECLARE @collection_set_uid_2 UNIQUEIDENTIFIER
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set] @name=N'system_health_deadlock', @collection_mode=1,
@description=N'system_health_deadlock', @logging_level=1, @days_until_expiration=14,
@schedule_name=N'CollectorSchedule_Every_15min', @collection_set_id=@collection_set_id_1 OUTPUT,
@collection_set_uid=@collection_set_uid_2 OUTPUT
SELECT @collection_set_id_1, @collection_set_uid_2
DECLARE @collector_type_uid_3 UNIQUEIDENTIFIER
SELECT @collector_type_uid_3 = collector_type_uid FROM [msdb].[dbo].[syscollector_collector_types]
WHERE name = N'Generic T-SQL Query Collector Type';
DECLARE @collection_item_id_4 INT
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
@name=N'system_health_deadlock'
, @PARAMETERS=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
SELECT CAST(
REPLACE(
REPLACE(XEventData.XEvent.value(''(data/value)[1]'', ''varchar(max)''),
'''', ''''),
'''','''')
AS XML) AS DeadlockGraph
FROM
(SELECT CAST(target_data AS XML) AS TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = ''system_health'') AS Data
CROSS APPLY TargetData.nodes (''//RingBufferTarget/event'') AS XEventData (XEvent)
where XEventData.XEvent.value(''@name'', ''varchar(4000)'') = ''xml_deadlock_report''
</Value><OutputTable>system_health_deadlock</OutputTable></Query></ns:TSQLQueryCollector>',
@collection_item_id=@collection_item_id_4 OUTPUT
, @frequency=900 --#seconds in collection interval
,@collection_set_id=@collection_set_id_1
, @collector_type_uid=@collector_type_uid_3
SELECT @collection_item_id_4
COMMIT TRANSACTION;
END Try
BEGIN Catch
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure,
@ErrorLine, @ErrorMessage);
END Catch;
GOLooking this over, there is quite a bit going on. The keys are the following paramaters: @parameters and @interval. The @parameters parameter stores the XML query to be used when querying the ring buffer (in this case). It is important to note that the XML query in this case needs to ensure that the values node is capped to a max of varchar(4000) like shown in the following.
WHERE XEventData.XEvent.VALUE(''@name'', ''VARCHAR(4000)'')With this data collector, I have trapped information and stored it for several months so I can compare notes at a later date.
