It’s the end of the world as we know it. And as the song goes…I feel fine! But hey, we didn’t start the fire.
Those are a couple of songs that pop into my head every time somebody starts talking doomsday and doomsday prophecies.
If you are reading this, I dare say that the world is still turning. And that is a good thing because that gives us a chance to talk about the 12 days of pre-Christmas.
Today we will be talking about a tool that can be at the DBAs disposal to help in tracking performance as well as problems.
First there are a couple of items of housekeeping. First item is that I only realized with this post that the post on the first day was wrong. I had miscalculated my twelve days to end on Christmas day. That is wrong! Counting down from that first post on the 13th means the 12th day will end up on December 24th. Maybe I will throw in a bonus 13th day post, or delay a post for a day, or just go with it. It will be a mystery!
Second item is naturally the daily recap of the 12 days to date.
-
- SSRS Schedules – 8th Day
- Runaway Jobs – 7th Day
- Maintenance Plan Gravage – 6th Day
- Table Compression – 5th Day
- Exercise for msdb – 4th Day
- Backup, Job and Mail History Cleanup – 3rd Day
- Service Broker Out of Control – 2nd Day
- Maint Plan Logs – 1st Day
On the Ninth Day of pre-Christmas…
My DBA gave to me a Data Collection!
If only my DBA had told me that I would need to have Management Data Warehouse (MDW) preconfigured. Well, that is not a problem. We can handle that too. For a tutorial on how to setup MDW, I am going to provide a link to one that has been very well written by Kalen Delaney.
The article written by Kalen covers the topic of MDW very well all the way from setting up the MDW, to setting up the Data Collectors, and all the way down to the default reports you can run for MDW. The MDW and canned Data Collectors can provide some really useful information for your environment.
What I want to share though is a means to add custom data collections to your MDW. To create a custom collection, we need to rely on two stored procedures provided to us by Microsoft. Those stored procedures are: sp_syscollector_create_collection_item and sp_syscollector_create_collection_set. Both of these stored procedures are found in the, you guessed it, msdb database.
Each of these stored procedures has a number of parameters to help in the creation of an appropriate data collection. When creating a data collection, you will first create the collection set, and then you add collection items to that set.
There are a few notable parameters for each stored procedure that I will cover. Otherwise, you can refer back to the links for each of the stored procedures to see more information about the parameters.
Starting with the sp_syscollector_create_collection_set stored procedure, I want to point out the @schedule_name, @name, and @collection_mode parameters. The name is pretty obvious – make sure you have a distinguishable name that is descriptive (my opinion) or at least have good documentation. The collection mode has two values. As noted in the documentation, you will want to choose one value over the other depending on the intended use of this data collector. If running continuously, just remember to run in cached mode. And lastly is the schedule name. This will help determine how frequently the job runs.
Unfortunately, the schedule names are not found in the documentation, but rather you are directed to query the sysschedules tables. To help you find those schedules, here is a quick query.
|
1 2 3 |
SELECT schedule_id,name FROM dbo.sysschedules WHERE name LIKE '%collector%'; |
Now on to the sp_syscollector_create_collection_item stored procedure. There are three parameters that I want to lightly touch on. For the rest, you can refer back to the documentation. The parameters of interest here are @parameters, @frequency and @collector_type_uid. Starting with the frequency parameter, this tells the collector how often to upload the data to the MDW if running in cached mode. Be careful here to select an appropriate interval. Next is the parameters parameter which is really the workhorse of the collection item. In the case of the custom data collector that I will show in a bit, this is where the tsql query will go.
Last parameter to discuss is the collector type uid. Like the schedule for the previous proc, the documentation for this one essentially refers you to a system view – syscollector_collector_types. Here is a quick query to see the different collector types.
|
1 2 |
SELECT name FROM syscollector_collector_types; |
The collector type that I will be using for this example is Generic T-SQL Query Collector Type. A discussion on the four types of collectors can be reserved for another time.
Let’s move on to the example now. This custom data collector is designed to help troubleshoot deadlock problems. The means I want to accomplish this is by querying the system_health extended event session.
I can query for deadlock information direct to the system_health session using a query like the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT CAST( REPLACE( REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '', ''), '','') AS varchar(4000)) 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'; |
You may notice that I have converted to varchar(4000) from XML. This is in large part to make sure the results will play nicely with the data collector. Now to convert that to a query that can be used in the @parameters parameter, we get the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value> SELECT CAST( REPLACE( REPLACE(XEventData.XEvent.value(''(data/value)[1]'', ''varchar(max)''), '''', ''''), '''','''') AS varchar(4000)) 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>systemhealthdeadlock</OutputTable></Query></ns:TSQLQueryCollector> |
With this query, we are loading the necessary schema nodes that correlate to the Data Collector Type that we chose. Since this parameter is XML, the schema must match or you will get an error. We are now ready to generate a script that can create a deadlock data collector.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
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'systemhealthdeadlock' , @collection_mode=1 , @description=N'systemhealthdeadlock' , @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'systemhealthdeadlock' , @parameters=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value> SELECT CAST( REPLACE( REPLACE(XEventData.XEvent.value(''(data/value)[1]'', ''varchar(max)''), '''', ''''), '''','''') AS varchar(4000)) 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>systemhealthdeadlock</OutputTable></Query></ns:TSQLQueryCollector>' , @collection_item_id=@collection_item_id_4 OUTPUT , @frequency=30 , @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; GO |
Upon creation, this will create a SQL Agent job with the defined schedule. Since this is a non-cached data collector set, the Agent job will adhere to the schedule specified and upload data on that interval.
Now all we need to do is generate a deadlock to see if it is working. It is also a good idea to introduce you to the table that will be created due to this data collector. Once we create this collector set, a new table will be created in the MDW database. In the case of this collector set, we have a table with the schema and name of custom_snapshots.systemhealthdeadlock.
This new table will have three columns. One column represents the DeadlockGraph as we retrieved from the query we provided to the @parameters parameter. The remaining columns are data collector columns for the collection date and the snapshot id.
Now that we have covered all of that, your favorite deadlock query has had enough time to finally fall victim to a deadlock. We should also have some information recorded in the custom_snapshots.systemhealthdeadlock table relevant to the deadlock information (if not, it will be there once the agent job runs, or you can run a snapshot from SSMS of the data collector). With a quick query, we can start looking into the deadlock problem.
|
1 2 |
SELECT collection_time,CAST(DeadlockGraph AS XML) AS DeadlockGraph, snapshot_id FROM mdw.custom_snapshots.systemhealthdeadlock; |
This query will give me a few entries (since I went overkill and created a bunch of deadlocks). If I click the DeadlockGraph cell in the result sets, I can then view the XML of the DeadlockGraph, as in the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |