THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Using user_settable Event in Extended Events

A question came to me on Twitter today from Jason Hall regarding the user_settable Event in SQL Server 2008's Extended Events.  You can find the event in the the sys.dm_xe_objects DMV with the following query:

SELECT *
FROM sys.dm_xe_objects o
WHERE o.name = 'user_settable'
 
AND o.object_type = 'event'

What first struck me as odd is I couldn't find this event in the Extended Events Manager, and it is because the Event has no Keyword associated with it.  There are actually three Events with no Keyword and it is by design as the Keyword is optional and not required for an Event, something I didn't know until today.  You can get the column list for the Event with the following query:

SELECT * FROM sys.dm_xe_objects o
JOIN sys.dm_xe_object_columns c ON c.OBJECT_NAME = o.name
JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
WHERE o.name = 'user_settable'
 
AND o.object_type = 'event'

I will fix the bug in the application in the next release so that these events are useable.  The answer to the question is the user_settable event fires whenever one of the User Settable objects in SQL Server is changed by calling one of the sp_user_counter(n) stored procedures is called.  This functionality is documented in the Books Online:

http://msdn.microsoft.com/en-us/library/ms187480.aspx

To demonstrate this we can create an Event Session as follows:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='user_settable')
   
DROP EVENT SESSION [user_settable] ON SERVER;
CREATE EVENT SESSION [user_settable]
ON SERVER
ADD EVENT sqlserver.user_settable
ADD TARGET package0.asynchronous_file_target(
    
SET filename='c:\temp\xe_user_settable.xel', metadatafile='c:\temp\xe_user_settable.xem')
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

Since the Event Session doesn't start at creation then you need to set the STATE = START with ALTER EVENT SESSION

ALTER EVENT SESSION [user_settable]
ON SERVER
STATE
=START

Now we can play with this a bit. One thing to note is that this Event has the same data as the SQLServer:UserSettable\Query\User Counter(n) performance counters.

EXECUTE sp_user_counter1 @@SPID
EXECUTE sp_user_counter2 @@CONNECTIONS

I like to use the Extended Events Manager to look at target data because it only requires a couple of clicks, and UI removes the need to know XML parsing in TSQL. To get the event data from the target:

image

The target data view is a simple grid that pivots the data into a profiler like view that makes it easier to look at.

image

However there is a simplified way to get TSQL code to query the event targets by using a dynamic function in TSQL that Adam Machanic created called xe_code_generator that can be downloaded from the following link:

http://sqlblog.com/files/folders/beta/entry14604.aspx

To use the function to generate the query you call it passing the session_name and target_name to be queried as follows:

SELECT dbo.xe_code_generator('user_settable', 'asynchronous_file_target')

It will ouput a XML document that you can click on and it opens up a single node XML view that you can copy and paste the TSQL from to query the session target.  Very helpful indeed.

SELECT
  
pivoted_data.*
FROM
(
  
SELECT
      
MIN(event_name) AS event_name,
      
MIN(event_timestamp) AS event_timestamp,
      
unique_event_id,
      
CONVERT
      
(
          
DECIMAL(28,0),
          
MIN
          
(
              
CASE
                  
WHEN
                      
event_name = 'user_settable' AND
                      
d_name = 'count' AND
                      
d_package IS NULL
                          
THEN d_value
              
END
          
)
       )
AS [user_settable.count],
      
CONVERT
      
(
          
BIGINT,
          
MIN
          
(
              
CASE
                  
WHEN
                      
event_name = 'user_settable' AND
                      
d_name = 'increment' AND
                      
d_package IS NULL
                          
THEN d_value
              
END
          
)
       )
AS [user_settable.increment],
      
CONVERT
      
(
          
DECIMAL(28,0),
          
MIN
          
(
              
CASE
                  
WHEN
                      
event_name = 'user_settable' AND
                      
d_name = 'user_counter' AND
                      
d_package IS NULL
                          
THEN d_value
              
END
          
)
       )
AS [user_settable.user_counter]
  
FROM
  
(
      
SELECT
          
*,
          
CONVERT(VARCHAR(400), NULL) AS attach_activity_id
      
FROM
      
(
          
SELECT
              
the_xml.event_name,
              
the_xml.event_timestamp,
              
the_xml.unique_event_id,
              
n.value('(@name)[1]', 'VARCHAR(400)') AS d_name,
              
n.value('(@package)[1]', 'VARCHAR(400)') AS d_package,
              
n.value('((value)[1]/text())[1]', 'VARCHAR(8000)') AS d_value,
              
n.value('((text)[1]/text())[1]', 'VARCHAR(8000)') AS d_text
          
FROM
          
(
              
SELECT
                  
CONVERT(XML, event_data),
                  
CONVERT(XML, event_data).value('(event/@timestamp)[1]', 'DATETIME') AS event_timestamp,
                  
OBJECT_NAME,
                  
ROW_NUMBER() OVER (ORDER BY (SELECT 1))
              
FROM sys.fn_xe_file_target_read_file ('c:\temp\xe_user_settable*.xel', 'c:\temp\xe_user_settable*.xem', NULL, NULL)
           )
AS the_xml(event, event_timestamp, event_name, unique_event_id)
           CROSS
APPLY event.nodes('event/*') AS q (n)
       )
AS data_data
  
) AS activity_data
  
GROUP BY
      
unique_event_id
) AS pivoted_data;

Adam's script is in beta, but it is very useful and a great contribution. Running the code produced will output very similar results as the target data viewer in the Extended Events Manager but in SSMS:

image

Published Wednesday, June 24, 2009 6:42 PM by Jonathan Kehayias

Comments

 

Jason Hall said:

Thanks Jonathan. The extra information and demonstration is very helpful.

June 25, 2009 4:36 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement