On the Ninth Day…

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 – 8^th Day
- Runaway Jobs – 7^th Day
- Maintenance Plan Gravage – 6^th Day
- Table Compression – 5^th Day
- Exercise for msdb – 4^th Day
- Backup, Job and Mail History Cleanup – 3^rd Day
- Service Broker Out of Control – 2^nd Day
- Maint Plan Logs – 1^st 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.

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.

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.

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.

<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.

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.

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.

<deadlock>
  <victim-list>
    <victimProcess id="process5a4ebc8" />
  </victim-list>
  <process-list>
    <process id="process5a4ebc8" taskpriority="0" logused="0" waitresource="KEY: 26:72057594048020480 (b4903b2250cc)" waittime="609" ownerId="2803145" transactionname="user_transaction" lasttranstarted="2012-12-20T22:32:09.987" XDES="0x8008d950" lockMode="X" schedulerid="8" kpid="13656" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="4" lastbatchstarted="2012-12-20T22:38:50.020" lastbatchcompleted="2012-12-20T22:38:50.020" lastattention="2012-12-20T22:38:24.217" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SomeServer" hostpid="7604" loginname="SomeLogin" isolationlevel="serializable (4)" xactid="2803145" currentdb="26" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <executionStack>
        <frame procname="" line="10" stmtstart="40" sqlhandle="0x02000000eac1af36f412db4e21d9dcc86feb261fa6bcd230" />
        <frame procname="" line="10" stmtstart="356" stmtend="518" sqlhandle="0x0200000095b4ee32a25e9724dd73fd6894c60748af6c136b" />
      </executionStack>
      <inputbuf>
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1 
BEGIN 
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1492;
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1494;
COMMIT TRAN 
END
   </inputbuf>
    </process>
    <process id="process5a44bc8" taskpriority="0" logused="0" waitresource="KEY: 26:72057594048020480 (ade87e3a717c)" waittime="609" ownerId="2878446" transactionname="user_transaction" lasttranstarted="2012-12-20T22:38:50.020" XDES="0xa9abd950" lockMode="X" schedulerid="7" kpid="15008" status="suspended" spid="86" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-12-20T22:38:47.887" lastbatchcompleted="2012-12-20T22:38:47.887" lastattention="2012-12-20T22:36:21.247" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SomeServer" hostpid="7604" loginname="SomeLogin" isolationlevel="serializable (4)" xactid="2878446" currentdb="26" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <executionStack>
        <frame procname="" line="9" stmtstart="40" sqlhandle="0x02000000eac1af36f412db4e21d9dcc86feb261fa6bcd230" />
        <frame procname="" line="9" stmtstart="352" stmtend="510" sqlhandle="0x020000000c4b9412577ec884cbd51882e5310dd340216739" />
      </executionStack>
      <inputbuf>
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1 
BEGIN 
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1494;
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1492;
COMMIT TRAN 
END
   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594048020480" dbid="26" objectname="" indexname="" id="lock5aeec80" mode="X" associatedObjectId="72057594048020480">
      <owner-list>
        <owner id="process5a44bc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process5a4ebc8" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594048020480" dbid="26" objectname="" indexname="" id="lock6164d80" mode="X" associatedObjectId="72057594048020480">
      <owner-list>
        <owner id="process5a4ebc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process5a44bc8" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Code to generate deadlock courtesy of “SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach” by Jason Brimhall, Wayne Sheffield et al (Chapter 12, pages 267-268). If you examine the deadlock graph you will see the code that generated the deadlock.

Since this is being pulled from the RingBuffer target of the system_health, it can prove useful to store that data into a table such as I have done. The reason being that the Ringbuffer can be overwritten, and with good timing on the data collector, we can preserve this information for later retrieval and troubleshooting. Deadlocks don’t always happen at the most opportune time and even less likely to occur when we are staring at the screen waiting for them to happen.

As you read more about the stored procedures used to create a data collector, you will see that there is a retention parameter. This helps prevent the table from getting too large on us. We can also ensure that an appropriate retention is stored for these custom collectors.

Conclusion

Creating a custom data collector can be very handy for a DBA. Especially in times of troubleshooting. These collectors are also quite useful for trending and analysis. Consider this a tool in the chest for the poor man.

Enjoy and stay tuned!

All scripts and references were for SQL 2008 R2. The Deadlock script was pulled from the 2012 book, but the script runs the same for the 2008 version of the AdventureWorks database.

12 Days Of Christmas and SQL December 26, 2017
Defaults In msdb Database January 28, 2019
CRM Data Source Connection Error January 23, 2020
The Mystery of Missing Backup Files December 10, 2018
Change SQL Server Collation - Back to Basics April 12, 2018

On the Ninth Day of pre-Christmas…

Conclusion

Related Posts:

Rate

Share

Share

Rate