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

  • An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target

    Yesterday’s post, Targets Week – pair_matching, looked at the pair_matching Target in Extended Events and how it could be used to find unmatched Events.  Today’s post will cover the etw_classic_sync_target Target, which can be used to track Events starting in SQL Server, out to the Windows Server OS Kernel, and then back to the Event completion in SQL Server.

    What is the etw_classic_sync_target Target?

    The etw_classic_sync_target Target is the target that hooks Extended Events in SQL Server into Event Tracing for Windows (ETW).  Event Tracing for Windows is a general purpose, high speed tracing mechanism provided by the Windows Server OS that allows for in-depth analysis and correlation of events from multiple applications, as well as the Windows Kernel.  ETW was first introduced in Windows Server 2000, was then expanded on in Windows Server 2003, and Windows Server 2008 and Windows Server 2008 R2 built significantly on the ETW tracing available in the OS.  For a background on ETW as a concept, I’d recommend that you read Event Tracing: Improve Debugging And Performance Tuning With ETW.

    It might surprise you to know that ETW integration with SQL Server wasn’t entirely new in Extended Events.  The first integration with ETW actually occurred in SQL Server 2005 and was talked about on the SQL Query Processing Team’s blog post Using ETW for SQL Server 2005 back in 2006.  The ETW integration in SQL Server 2005 was trace based integration, and is similar to as well as different from the ETW integration that exists through Extended Events.  There are two providers available currently in ETW in the Windows Server OS, a classic provider (Windows Server 2000 and newer), and a manifest based provider (Windows Vista and Server 2008 and newer) (http://msdn.microsoft.com/en-us/library/aa363668(VS.85).aspx#providers).  The etw_classic_sync_target uses the classic provider for buffering events to ETW to ensure that backwards compatibility is maintained for the supported Operating Systems that SQL Server can run on.

    Unlike the other targets available in Extended Events, the output of the etw_classic_sync_target is not available inside of SQL Server through a DMV or even by querying a DMF, since the events are buffered to ETW which is an OS based mechanism.  Currently, there can only be one ETW Session for Extended Events at a time, and that session is named XE_DEFAULT_ETW_SESSION.  The XE_DEFAULT_ETW_SESSION is created the first time a ETW Target is registered in an Event Session and is reused by subsequent Event Sessions that register an ETW Target in SQL Server.  If multiple Event Sessions utilize the etw_classic_sync_target on a server, even if they exist in multiple instances of SQL Server, the Events fired by the Event Sessions all use the XE_DEFAULT_ETW_SESSION session in ETW.  This makes isolation of Events to single instance impossible under the current design unless the Event Sessions are run independently instead of concurrently.

    Unlike the other Targets available in Extended Events, the ETW session created the first time that the etw_classic_sync_target is registered in an active Event Session is not removed when the etw_classic_sync_target is dropped from the Event Session, or when the Event Session is stopped.  The only way to remove the XE_DEFAULT_ETW_SESSION is with command line commands to one of the ETW consumers available in Window; either logman or xperf if installed.  Also in contrast to the other targets, the XE_DEFAULT_ETW_SESSION requires manual flushing to ensure that Events are processed before removing the XE_DEFAULT_ETW_SESSION ETW session in the OS.  

    Configuration Options

    There are five configuration options for the etw_classic_sync_target Target in Extended Events.  All of the configuration options are optional.  The default_etw_session_logfile_path can be used to specify the path to the log file created by the ETW Session for logging the Events.  Once this file path has been set, it can not be changed while the XE_DEFAULT_ETW_SESSION ETW session exists in Windows, the default file location is %TEMP%\XEEtw.etl.  If you are utilizing the etw_classic_sync_target Target in multiple Event Sessions or multiple Instance of SQL Server on the same OS, it is important to maintain consistency in the definition of this option.  The default_etw_session_buffer_size_kb specifies the default size of the in-memory buffers for the ETW session, the default buffer size is 128KB.  The default_etw_session_logfile_size_mb specifies the size of the file used to store the events sent to the ETW session, the default size is 20MB.  The retries option specifies the number of attempts that the Extended Event Engine will retry publishing the events to the ETW Session if the initial attempt to publish the events fails, the default value is 0 retry attempts, meaning that the Event will be dropped if they fail on the first attempt.  The default_xe_session_name specifies the name of the ETW session to create in the ETW subsystem for the Event Session, the default is XE_DEFAULT_ETW_SESSION.

    -- Target Configurable Fields
    SELECT 
       oc.name AS column_name,
       oc.column_id,
       oc.type_name,
       oc.capabilities_desc,
       oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o 
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc 
        ON o.name = oc.OBJECT_NAME 
       AND o.package_guid = oc.object_package_guid
    WHERE(p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND o.object_type = 'target'
      AND o.name = 'etw_classic_sync_target'

    image

    Understanding the Target Data Format

    Unlike the other Targets in Extended Events, that etw_classic_sync_target Target data format depends on a number of factors, specifically on which ETW consumer, and what options are specified for the consumer for exporting the information into a user consumable format.  To be perfectly honest as much as I have played with the etw_classic_sync_target, I have yet to figure out all of the possible options for consuming the ETW session data that can be generated.  There are a number of available tools for consuming ETW session data, including logman, tracerpt, and xperf. 

    A wide variety of output formats is possible including text, CSV, and XML, and when using Windows Vista or Windows Server 2008 as the system of analysis, xperfview can be be used to provide a graphical output of the ETW session data from from the .etl file.  For this reason I will not attempt to cover all of the formats available for consuming ETW session information, but will instead leave that up to the reader to investigate.

    Querying/Parsing the Target Data

    One of the topics not yet covered in this series is the fact that inside of Extended Events in SQL Server, every Event has an associated Channel and Keyword associated with it that maps to a Channel and Keyword in ETW.  Inside of ETW, the channel defines the intended audience for the Event, and the Keyword provides an application specific grouping of events.  This information can be queried from the sys.dm_xe_object_columns DMV by joining it to the sys.dm_xe_map_values DMV as follows:

    -- Event ETW Keyword/Channel pairings
    SELECT 
        package_name,
        object_name,
        CHANNEL as channel_name,
        KEYWORD as keyword_name
    FROM
    (
    SELECT 
        p.name AS package_name, 
        o.name AS object_name,
        oc.name AS column_name,
        mv1.map_value
    FROM sys.dm_xe_packages p
    JOIN sys.dm_xe_objects o
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns oc
        ON o.package_guid = oc.object_package_guid
            AND o.name = oc.object_name
    LEFT JOIN sys.dm_xe_map_values mv1 
        on oc.type_name = mv1.name 
            and oc.column_value = mv1.map_key
    WHERE oc.name IN ('CHANNEL', 'KEYWORD')
        -- Filter out private internal use only objects
      AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND (oc.capabilities IS NULL OR oc.capabilities & 1 = 0)
    ) AS tab
    PIVOT
    ( 
        MAX(map_value)
        FOR column_name IN ([CHANNEL], [KEYWORD])
    ) as pvt
    ORDER BY CHANNEL, KEYWORD, package_name, object_name
    

     

    When planning Event Sessions in general, using the Channels and Keywords of Events to identify events of interest can be very useful, especially when first learning Extended Events.  In relation to ETW, they provide the integration

     

    IF EXISTS(SELECT * 
             FROM sys.server_event_sessions 
             WHERE name='etw_test_session') 
        DROP EVENT SESSION [etw_test_session] ON SERVER; 
    CREATE EVENT SESSION [etw_test_session] 
    ON SERVER 
    ADD EVENT sqlserver.file_read( 
         ACTION (sqlserver.database_id, sqlserver.session_id)), 
    ADD EVENT sqlserver.file_read_completed( 
         ACTION (sqlserver.database_id, sqlserver.session_id)), 
    ADD EVENT sqlos.async_io_requested( 
         ACTION (sqlserver.database_id, sqlserver.session_id)), 
    ADD EVENT sqlos.async_io_completed( 
         ACTION (sqlserver.database_id, sqlserver.session_id)), 
    ADD EVENT sqlos.wait_info( 
         ACTION (sqlserver.database_id, sqlserver.session_id)), 
    ADD EVENT sqlserver.sql_statement_starting( 
         ACTION (sqlserver.database_id, sqlserver.plan_handle, 
                sqlserver.session_id, sqlserver.sql_text)), 
    ADD EVENT sqlserver.sql_statement_completed( 
         ACTION (sqlserver.database_id, sqlserver.plan_handle, 
                sqlserver.session_id, sqlserver.sql_text)) 
    -- ADD ETW target 
    ADD TARGET package0.etw_classic_sync_target (
           SET default_etw_session_logfile_path = N'C:\SQLBlog\sqletwtarget.etl')
    WITH (MAX_MEMORY = 4096KB, 
         EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
         MAX_DISPATCH_LATENCY = 5 SECONDS, 
         MAX_EVENT_SIZE = 4096KB, 
         MEMORY_PARTITION_MODE = PER_CPU, 
         TRACK_CAUSALITY = ON, 
         STARTUP_STATE = OFF) 
    GO

    This Event Session will capture SQL statements from start to complete as well as the file read operations performed by the database engine to satisfy the request.  To get the OS Kernel information using ETW we will need to start a Kernel

    logman start "NT Kernel Logger" /p "Windows Kernel Trace" (process,thread,disk) /o C:\SQLBlog\systemevents.etl /ets

    image

    With the NT Kernel Logger started and capturing kernel level process, thread, and disk events into the systemevents.etl file, we can now start our Extended Events Session in SQL Server, and run our test workload.  To ensure that we get physical reads from disk the following example will clear the Buffer Cache before starting the Event Session.

    USE [AdventureWorks2008] 
    GO 
    -- Clear the Buffer Cache to force reads from Disk 
    DBCC DROPCLEANBUFFERS 
    GO 
    
    -- Start the Event Session so we capture the Events caused by running the test 
    ALTER EVENT SESSION etw_test_session 
    ON SERVER 
    STATE=START 
    GO 

    If you run this on a SQL Server where you use a minimal privilege AD Account for the SQL Server Service Account, you will get an error similar to the following:

    Msg 25641, Level 16, State 0, Line 2
    For target, "CE79811F-1A80-40E1-8F5D-7445A3F375E7.package0.etw_classic_sync_target", the parameter "default_etw_session_logfile_path" passed is invalid.  The operating system returned error 5 (ACCESS_DENIED) while creating an ETW tracing session.  Ensure that the SQL Server startup account is a member of the 'Performance Log Users' group and then retry your command.

    If this occurs, the Service Account does not have sufficient privileges to use the ETW provider, and it will be necessary to add the SQL Service Account to the Performance Log Users group on the SQL Server and then restart the SQL Server Database Engine service for the permissions change to take effect.  (Yet another pre/post Installation Checklist item that needs to be performed!)  Once the Event Session is started, we can run a query to generate some Events and cause physical reads to occur from disk.

    -- Run the Simple SELECT against AdventureWorks 
    SELECT SUM(TotalDue), SalesPersonID 
    FROM Sales.SalesOrderHeader 
    GROUP BY SalesPersonID 
    GO 

    Once the query completes we can stop our Event SessionWith we can stop the NT Kernel Logger using the logman utility again:

    logman update "NT Kernel Logger" /fd /ets
    logman stop "NT Kernel Logger" /ets

    image

    With the Kernel Logger stopped, we can then stop our Event Session inside of SQL Server:

    ---- Start the Event Session so we capture the Events caused by running the test 
    ALTER EVENT SESSION etw_test_session 
    ON SERVER 
    STATE=STOP

    However, even though we stopped the Event Session in SQL Server, the XE_DEFAULT_ETW_SESSION still exists in the Windows OS.

    logman query -ets

    image

    Or if you are using Windows Server 2008/2008R2, the Performance Monitor can show you the Event Session:

    image

    In either case we need to flush the buffers for the XE_DEFAULT_ETW_SESSION and in this case stop it.

    logman update XE_DEFAULT_ETW_SESSION /fd /ets
    logman stop XE_DEFAULT_ETW_SESSION /ets

    image

    With the two ETW sessions stopped, we can now use tracerpt to merge the trace files together and output them to a CSV file. 

    (Note: The following commands are specific to Windows Server 2008/2008R2 if you are using Windows Server 2003, tracerpt does not have a –of option and will output the merged results in CSV format by default.  The default in Windows Server 2008/2008R2 is XML format.)

    tracerpt C:\SQLBlog\systemevents.etl C:\SQLBlog\sqletwtarget.etl -o C:\SQLBlog\ETW_Merged.csv -of CSV

    image

    With the results merged in a CSV there are a number of options available for how to work with the data.  If we just open up the CSV file and look at scroll down through the information we can see that the SQL Server async_io_requested Events that lead to file_read events, the setting of wait_info Event and the subsequent Kernel level DiskIO Read Event.

    image

    As cool as this seems like it is, if you have done any work with ETW in Windows, you know that the future is even brighter than this simple example begins to touch on.  The Windows Performance Analyzer and xperf offer a way to read ETW trace files and generate a graphical presentation of the information held within them.  For example if we use xperf to view the Kernel Logger file:

    image

    However, the Kernel Logger uses the newer Manifest based provider in Windows Server 2008 and 2008R2, and since SQL Server 2008 Extended Events uses the classic provider, xperf doesn’t recognize the Event names for the Events contained in the ETW trace file, and instead you get a bunch of Guid’s that require manual deciphering.

    image 

    If you happen to be running SQL Server 2008/2008R2 on Windows Server 2003, which I happen to be in most of my demonstration VM’s that I use for speaking (Windows Server 2003 takes significantly less disk space than 2008 and its at a premium on my laptops 120GB SSD), the sqletwtarget.etl and systemevents.etl files generated by this demo will have the same version and xperf can be used to open both files together and merge their respective Event Views:

    image

    The ProviderIds window is not expanded here, but it has the same Guids that the first example had.  Keep in mind that the above merged view came from a different system than the original two xperf views, but they used the same exact demo to generate.

    Considerations for Usage

    I have often commented in presentations that the etw_classic_sync_target Target is not something that the average DBA is going to make meaningful use of in troubleshooting problems with SQL Server.  At PASS this year, I had some eyebrows raised when I mentioned this in my presentation on Extended Events, but I stand by that statement, even after trying to brush up on ETW for this blog post, I’ve ran into numerous complications associated with actually consuming the ETW session information, that required that I perform further research to figure things out.  There is certainly meaningful information available through the use of the etw_classic_sync_target Target with Extended Events, when merged with Kernel level tracing as demonstrated later in this blog post.  However, when focusing on general troubleshooting, the etw_classic_sync_target should not be considered the target of choice.

    What’s next?

    Now that we have looked at all of the Targets currently available in Extended Events, in the next post we’ll look at how to utilize multiple targets in an Event Session to simplify the troubleshooting of problems in SQL Server.

  • An XEvent a Day (9 of 31) – Targets Week – pair_matching

    Yesterday’s post, Targets Week – synchronous_event_counter, looked at the counter Target in Extended Events and how it could be used to determine the number of Events a Event Session will generate without actually incurring the cost to collect and store the Events.  Today’s post is coming late, I know, but sometimes that’s just how the ball rolls.  My original planned demo’s for today’s post turned out to only work based on a fluke, though they were very consistent at working as expected, and as a result I had to rework a lot of this post this evening instead of letting it auto-post in the morning.  Today's post will cover the pair_matching Target, which can be used to find Events that didn’t have a corresponding matching Event based on the Targets configuration.

    What is the pair_matching Target?

    The pair_matching Target works by matching a Begin Event with an End Event based on the specified match Columns and Actions, and drops the matched pairs of Events from the Target so that only unmatched Events remain.   However, life would be grand if it was only that simple to use.  The Books Online example How to: Determine Which Queries Are Holding Locks, uses the pair_matching Target with the sqlserver.lock_aquired and sqlserver.lock_released events for matching, to try and show how to find queries that haven’t released their Locks.  The problem is, there is not a 1:1 relationship between lock_aquire and lock_released Events.  Lock escalation can kick in and multiple granular locks are acquired but the escalation to a less granular Lock only requires a single lock_released Event to fire. 

    In the Using SQL Server 2008 Extended Events whitepaper I wrote, I showed how to track down orphaned transactions using the sqlserver.database_transaction_begin and sqlserver.database_transaction_end Events and matching on the sqlserver.session_id Action.  The reason that this example works is that only one explicit transaction can be open for an session_id, even if you issue multiple BEGIN TRANSACTION commands, a single ROLLBACK undoes every operation performed since the first BEGIN TRANSACTION.  Yes this is a tangent, but fear not, I am coming back to why this matters to the pair_matching Target.  It matters to the pair_matching target because the begin and end Events must uniquely match each other in a manner that is 1:1 or the pair_matching Target is not going to work as you expect it to, as in the “Which Queries Are Holding Locks” example in the BOL.

    Like the ring_buffer, bucketizer, and the synchronous_event_counter, the pair_matching Target is a memory resident Target that holds event data in memory while the Event Session is active on the SQL Server.  When the Event Session is stopped, the memory buffers allocated to the synchronous_event_counter target are freed and any information contained in the target is lost.

    Configuration Options

    There are seven configuration options for the pair_matching Target, with two of them being mandatory.  The pair_matching Target requires that the begin_event and end_event for matching be specified for the Target.  In addition to specifying the events, it is also possible to restrict the match criteria by specifying an ordered comma separated list of column names for the begin_matching_columns and end_matching_columns configuration options.  If the matching requires the use of Actions, an ordered comma separated list of Action names in the format of <package_name.action_name> can be specified for the begin_matching_actions and end_matching_actions configuration options.  The final configuration option respond_to_memory_pressure determines whether or not the Target responds to memory pressure, and stops adding new orphaned events when there is memory pressure in the SQL Server.

    -- Target Configurable Fields
    SELECT 
       oc.name AS column_name,
       oc.column_id,
       oc.type_name,
       oc.capabilities_desc,
       oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o 
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc 
        ON o.name = oc.OBJECT_NAME 
       AND o.package_guid = oc.object_package_guid
    WHERE(p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND o.object_type = 'target'
      AND o.name = 'pair_matching'

    image

    Understanding the Target Data Format

    Like the other memory resident Targets, the pair_matching Target returns its information by querying the sys.dm_xe_session_targets DMV, and it returns the Target data in an XML format that is not schema bound, but that has a standardized format.  The pair_matching Target XML document, closely matches the output of the ring_buffer Target.  The root node of the XML is the <PairingTarget> node which has attributes for the number of truncated Events, the count of the current number of orphans held in the Target, the number of matched Event pairs that have been made by the Target, and the number of orphans that have been dropped due to memory pressure in SQL Server.  The <PairingTarget> node has child <event> nodes that match the XML document of the <event> nodes in the ring_buffer and asynchronous_file_target Targets.  A simplified representation of the XML output by the pair_matching Target is below:

    <PairingTarget truncated="" orphanCount="" matchedCount="" memoryPressureDroppedCount="">
      <event name="" package="" id="" version="" timestamp="">
        <data name="">
          <type name="" package="" />
          <value />
          <text />
        </data>
        <action name="" package="">
          <type name="" package="" />
          <value />
          <text />
        </action>
      </event>
    </PairingTarget>

    Querying/Parsing the Target Data

    Like the other memory resident Targets in Extended Events, the pair_matching Target data is only exposed by querying the sys.dm_xe_session_targets DMV.  Mike Wachal at Microsoft, traded emails with me, and dug into the source code for the pair_matching target yesterday trying to help me with some questions for this post, especially as they related to the legitimacy of the demo’s being planned for this post.  In the end Mike sent me a demo and permission to post it here in lieu of the questionable ones that I had been trading emails with him about.  It is with much appreciation to Mike and the Extended Events Development team for their assistance with this blog post and the consistent back and forth with emails that they provided yesterday.

    Mike provided an example that I will show in its entirety for this weeks wrap up post on Saturday, but I am going to show a shorter sample of the demo to show how to use the pair_matching Target and query the Target data from it.  When SQL Server executes a statements, generally the sqlserver.sql_statement_starting Event is fired when the statement begins executing and the sqlserver.sql_statement_completed Event is fired when the statement completes.  However, when the client sets an execution time, also known as a CommandTimeout in .NET, if the execution duration exceeds that timeout, the statement never completes inside of SQL Server.  I have run into problems with the default timeout of 30 seconds in .NET more times than I ever care to think about in my career. 

    To demonstrate a execution time out using SQL Server Management Studio, you can open a New Query window, and in the connection dialog click on the Connection Properties tab and change the Execution time-out option from 0 (zero) to a positive integer value.  For the purposes of this blog post example, I am going to use 5 seconds as the execution timeout for one query window that will generate the unmatched event.

    image

    The first thing we need to do is setup our Event Session to capture our Events and Actions, and configure our pair_matching Target.

    -- Create the Event Session
    CREATE EVENT SESSION FindAttentionEvents
    ON SERVER
    ADD EVENT sqlserver.sql_statement_starting
    (    ACTION(sqlserver.session_id, sqlserver.tsql_stack)
    ),
    ADD EVENT sqlserver.sql_statement_completed
    (    ACTION(sqlserver.session_id, sqlserver.tsql_stack)
    )
    ADD TARGET package0.pair_matching
    (    SET begin_event = 'sqlserver.sql_statement_starting',
            begin_matching_actions = 'sqlserver.session_id, sqlserver.tsql_stack',
            end_event = 'sqlserver.sql_statement_completed',
            end_matching_actions = 'sqlserver.session_id, sqlserver.tsql_stack',
            respond_to_memory_pressure = 0
    )
    WITH (MAX_DISPATCH_LATENCY=5 SECONDS, TRACK_CAUSALITY=ON)
    
    -- Start the Event Session
    ALTER EVENT SESSION FindAttentionEvents
    ON SERVER
    STATE=START
    GO

    Now in the New Query window that had the connection option for execution timeout set to 5 seconds, run the following commands:

    SELECT TOP 100 *
    FROM sys.objects
    GO
    
    SELECT TOP 100 *
    FROM sys.columns
    GO
    
    WAITFOR DELAY '00:00:10'
    GO
    

    In my test system the output for this is:

    image

    If we flip back to a normal Query window and query the Target data, we will see multiple matched Events and one orphaned Event, for the above failure.

    -- Create XML variable to hold Target Data
    DECLARE @target_data XML
    SELECT @target_data = 
        CAST(target_data AS XML)
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets AS t 
        ON t.event_session_address = s.address
    WHERE s.name = 'FindAttentionEvents'
      AND t.target_name = 'pair_matching'
    
    -- Query XML variable to get Target Execution information
    SELECT 
        @target_data.value('(PairingTarget/@orphanCount)[1]', 'int') AS orphanCount,
        @target_data.value('(PairingTarget/@matchedCount)[1]', 'int') AS matchedCount,
        @target_data.value('(PairingTarget/@memoryPressureDroppedCount)[1]', 'int') AS memoryPressureDroppedCount
    
    -- Query the XML variable to get the Target Data
    SELECT 
        n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
        n.value('(event/@id)[1]', 'int') AS id,
        n.value('(event/@version)[1]', 'int') AS version,
        DATEADD(hh, 
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
                n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(event/data[@name="source_database_id"]/value)[1]', 'int') as [source_database_id],
        n.value('(event/data[@name="object_id"]/value)[1]', 'int') as [object_id],
        n.value('(event/data[@name="object_type"]/value)[1]', 'varchar(60)') as [object_type],
        n.value('(event/data[@name="state"]/text)[1]', 'varchar(50)') as [state],
        n.value('(event/data[@name="offset"]/value)[1]', 'int') as [offset],
        n.value('(event/data[@name="offset_end"]/value)[1]', 'int') as [offset_end],
        n.value('(event/data[@name="nest_level"]/value)[1]', 'int') as [nest_level],
        n.value('(event/action[@name="session_id"]/value)[1]', 'int') as session_id,
        n.value('(event/action[@name="tsql_stack"]/value)[1]', 'varchar(max)') as tsql_stack,    
        n.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)') as activity_id    
    FROM
    (    SELECT td.query('.') as n
    FROM @target_data.nodes('PairingTarget/event') AS q(td)
    ) as tab
    ORDER BY session_id, activity_id
    GO

    If you pay close attention to the above XQuery of the Target data, you should catch that there is a difference between the above and previous examples.  I have a price for the first person that is not Adam Machanic (sorry dude, but you told me about it so that would be unfair) to comment with what that difference is and why it is important to take note of.  The output of the above query on my test system after running the demo is:

    image

    Now with this Demo, it is important that you reset the environment that you tested it in if you followed the instructions and changed the execution timeout in SQL Server Management Studio.  If you don’t change it back, you will try and run a query that takes longer than 5 seconds and it will timeout on you.  Also don’t forget to cleanup the Event Session by dropping it from the catalog.

    -- Cleanup from the demonstration
    DROP EVENT SESSION FindAttentionEvents 
    ON SERVER

    Considerations for Usage

    The pair_matching Target can be a very useful tool in finding unmatched Events, but as previously pointed out in this blog post, you have to be very careful what you are providing for match criteria, and the Events have to have a 1:1 correlation for the begin_event and end_event or the target will produce incorrect results.

    What’s next?

    Now that we have looked at the ring_buffer, asynchronous_file_target, bucketizers, synchronous_event_counter, and the pair_matching Targets, in the next post we’ll look at the last Target available for use in Extended Events, the etw_classic_sync_target, which can be used to track Events firing in SQL Server out into the Windows OS Kernel and back into SQL Server to see end to end what occurred inside the Server.

  • An XEvent a Day (8 of 31) – Targets Week – synchronous_event_counter

    Yesterday’s post, Targets Week - Bucketizers, looked at the bucketizer Targets in Extended Events and how they can be used to simplify analysis and perform more targeted analysis based on their output.  Today’s post will be fairly short, by comparison to the previous posts, while we look at the synchronous_event_counter target, which can be used to test the impact of an Event Session without actually incurring the cost of Event collection.

    What is the synchronous_event_counter?

    The synchronous_event_count simply put, is a Target that counts the number of Events that fire for a given Event Session.  It can be used to test whether or not the defined Predicates on Events in an Event Session perform the level of filtering expected, without having to actually perform full Event collection using one of the raw Event data targets like the ring_buffer or asynchronous_file_target.  The Target is synchronous however, due to the fact that it only counts the number of times each Event fires, its impact is minimized in comparison to the other synchronous targets available.  Like the ring_buffer and bucketizer, the synchronous_event_counter Target is a memory resident Target that holds event data in memory while the Event Session is active on the SQL Server.  When the Event Session is stopped, the memory buffers allocated to the synchronous_event_counter target are freed and any information contained in the target is lost.

    Configuration Options

    There are no configuration options for the synchronous_event_counter Target. (see I said this was going to be a short post comparatively).

     -- Target Configurable Fields
    SELECT 
       oc.name AS column_name,
       oc.column_id,
       oc.type_name,
       oc.capabilities_desc,
       oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o 
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc 
        ON o.name = oc.OBJECT_NAME 
       AND o.package_guid = oc.object_package_guid
    WHERE(p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND o.object_type = 'target'
      AND o.name = 'synchronous_event_counter'

     

    image

    Understanding the Target Data Format

    Like the ring_buffer and bucketizer Targets, the synchronous_event_counter Target returns its information by querying the sys.dm_xe_session_targets DMV, and it returns the Target data in an XML format that is not schema bound, but that has a standardized format.  The synchronous_event_counter Target has a very simple XML document, much like the bucketizer Targets.  The root node of the XML is the <CounterTarget> node which has a child node <Packages> which has a child <Package> node for each package that identifies the package using an @name attribute.  Each <Package> node will have one or more <Event> nodes based on the number of Events defined in the Event Session for that particular package.  The <Event> nodes each will contain two attributes, the name of the event and the count for its occurrence since the Event Session started.  A simplified representation of the XML output by the synchronous_event_counter Target is below:

    <CounterTarget truncated="">
      <Packages>
        <Package name="">
          <Event name="" count="" />
        </Package>
      </Packages>
    </CounterTarget>

    Querying/Parsing the Target Data

    Like the other memory resident Targets in Extended Events, the synchronous_event_counter Target data is only exposed by querying the sys.dm_xe_session_targets DMV.  The following example will demonstrate how the synchronous_event_counter can be used to test the number of Events that an Event Session will generate:

    -- Create an Event Session to Track Recompiles
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='CounterTargetDemo')
        DROP EVENT SESSION [CounterTargetDemo] ON SERVER;
    CREATE EVENT SESSION [CounterTargetDemo]
    ON SERVER
    ADD EVENT sqlserver.sql_statement_starting,
    ADD EVENT sqlos.wait_info
    (    WHERE (duration > 0))
    ADD TARGET package0.synchronous_event_counter
    GO
    
    -- Start the Event Session
    ALTER EVENT SESSION [CounterTargetDemo]
    ON SERVER
    STATE=STOP
    GO
    
    -- Wait for Events to generate and then Query Target
    
    -- Query the Target
    SELECT 
        n.value('../@name[1]', 'varchar(50)') as PackageName,
        n.value('@name[1]', 'varchar(50)') as EventName,
        n.value('@count[1]', 'int') as Occurence
    FROM
    (
    SELECT CAST(target_data AS XML) as target_data
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets AS t 
        ON t.event_session_address = s.address
    WHERE s.name = 'CounterTargetDemo'
      AND t.target_name = 'synchronous_event_counter'
    ) as tab
    CROSS APPLY target_data.nodes('CounterTarget/Packages/Package/Event') as q(n)
    
    -- Drop the Event Session
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='CounterTargetDemo')
        DROP EVENT SESSION [CounterTargetDemo] ON SERVER;

    This session on one of my test servers generated the following output while running for less than five seconds:

    image

    Based on this number of Events being fired, it may be determined that the predicates for the session need to provide further filtering of the Events, or if it is determined that the Predicates are filtering the Events as intended, this at least lets us know that the number of Events firing will require the use of the asynchronous_file_target, if the plan is to look at the Events raw data.

    Considerations for Usage

    The only real consideration associated with the synchronous_event_counter Target is that it is an synchronous Target.  However, since it is only counting the occurrences of the Events defined in the Event Session and is not actually buffering the data for dispatch its impact is not generally a concern.

    What’s next?

    Now that we have looked at the ring_buffer, asynchronous_file_target, bucketizers, and synchronous_event_counter Targets, in the next post we’ll look at the pair_matching Target, which can be used to match up related Events based on specific criteria to discard the matched pairs, leaving unmatched and potentially problematic Events for analysis. 

  • An XEvent a Day (7 of 31) – Targets Week – bucketizers

    Yesterday’s post, Targets Week - asynchronous_file_target, looked at the asynchronous_file_target Target in Extended Events and how it outputs the raw Event data in an XML document.  Continuing with Targets week today, we’ll look at the bucketizer targets in Extended Events which can be used to group Events based on the Event data that is being returned.

    What is the bucketizer?

    The bucketizer performs grouping of Events as they are processed by the target into buckets based on the Event data and the Targets configuration.  There are two bucketizer targets in Extended Events; a synchronous_bucketizer and an asynchronous_bucketizer.  The only difference between the two is the manner in which the Event data is processed; either synchronously on the connection that generated the Event, or asynchronously after being dispatched to the target based on the MAX_DISPATCH_LATENCY for the Event Session, or when the dispatch buffer becomes full.  Since the two bucketizers are identical in every way, except for their processing, this blog post will use the asynchronous_bucketizer for all further references.  The bucketizers are a memory resident target, similar to the ring_buffer and like the ring_buffer, only contain the grouped Event data when the Event Session is active.  When the Event Session is stopped, the memory buffers allocated to the bucketizer target are freed and all data contained in the target disappears.  The bucketizer targets can be used to simplify troubleshooting by identifying the events that are occurring the most, and then allowing more focused Event collection for further analysis.  Further analysis could include using either the ring_buffer or the asynchronous_file_target to look at the actual Event data being generated, or changing the bucketizer Targets configuration to group event occurrences based on a different criteria.

    Configuration Options

    The ring_buffer like most of the targets has configuration options that can be found in the sys.dm_xe_object_columns DMV.

    -- Target Configurable Fields
    SELECT 
        oc.name AS column_name,
        oc.column_id,
        oc.type_name,
        oc.capabilities_desc,
        oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o 
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc 
        ON o.name = oc.OBJECT_NAME 
        AND o.package_guid = oc.object_package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND o.object_type = 'target'
      AND o.name = 'asynchronous_bucketizer'
    

    In SQL Server 2008, 2008R2, and SQL Server Denali CTP1, there are four configuration options for the asynchronous_bucketizer Target.  The slots option sets the maximum number of buckets the target will collect.  Once this number of buckets is reached, new events that do not apply to an existing bucket are dropped by the target and not grouped.  The filtering_event_name option is used to set the name of the specific Event in the Event Session to filter on.  The source_type option is used to specify whether the source being used for bucketing is a part of the Event data or an Action that has been added to the Events contained in the Event Session.  The source option specifies the source that will be used to generate the buckets for grouping in the target.

    image

    As shown above the source is the only required option for the asynchronous_bucketizer Target.  However, when the source is an Action the source_type option is also required to specify that the source is an Action.  When using one of the Event Data elements as the source, only the Data element (also known as a column name) needs to provided to the source.  When using an Action for the source, the Package name must be specified along with the Action name in the format of packagename.actionname.  Likewise when specifying a filtering_event_name, the Package name must also be provided in the format of packagename.eventname.

    Understanding the Target Data Format

    The bucketizer Targets like the other Targets already output the data in XML format, and the XML is not schema bound, but has a predictable format.  Inside the Extended Events Engine, the bucketing data is maintained in a binary format that minimizes the amount of memory necessary for the Targets memory buffers.  The bucketing data is materialized into an XML document when the Target information is queried using the sys.dm_xe_session_targets DMV, allowing it to be used for analysis.  The asynchronous_bucketizer XML document contains a parent XML <BucketizerTarget> Node that contains attributes about the Targets operation since the Event Session was started including the number of truncated Events and the maximum number of buckets contained in the Target.  The bucket groups are contained in <Slot> nodes that have two attributes; the count is the number of events that have occurred and the trunc is the number of bytes that have been truncated.  The <Slot> node contains a <value> node that contains the source that the bucket belongs to.  A simplified representation of the XML document for the asynchronous_bucketizer target is:

    <BucketizerTarget truncated="" buckets="">
      <Slot count="" trunc="">
        <value></value>
      </Slot>
    </BucketizerTarget>

    Querying/Parsing the Target Data

    The asynchronous_bucketizer targets simplistic XML output makes querying it relatively simple compared to the targets that we’ve already looked at this week.  However, unlike the other ring_buffer and asychronous_file_target, the asychronous_bucketizer can not be parsed using Adam Machanic’s Extended Events Code Generator.  The simplicity of the XML and its standard output doesn’t really require specialized code to generate a easily usable table output for this.  The Extended Events SSMS Addin for SQL Server 2008  TargetDataViewer will shred the XML but its not even worth using for this particular target, since the XQuery is very simple, and you can do a lot more with the TSQL depending on the Event Session that your create.  To demonstrate the usage of the asynchronous_bucketizer, we’ll look at a couple of examples.  The first example will show how to track recompiles by database_id to find the databases that have the most recompiles occurring.

    -- Create an Event Session to Track Recompiles
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BucketizerTargetDemoRecompiles')
        DROP EVENT SESSION [BucketizerTargetDemoRecompiles] ON SERVER;
    CREATE EVENT SESSION [BucketizerTargetDemoRecompiles]
    ON SERVER
    ADD EVENT sqlserver.sql_statement_starting
    (    ACTION (sqlserver.database_id) -- database_id to bucket on
         WHERE (state=1) -- recompile state from dm_xe_map_values
    ),
    ADD EVENT sqlserver.sp_statement_starting
    (    ACTION (sqlserver.database_id) -- database_id to bucket on
         WHERE (state=1) -- recompile state from dm_xe_map_values
    )
    ADD TARGET package0.asynchronous_bucketizer
    (     SET source_type=1, -- specifies bucketing on Action 
             source='sqlserver.database_id' -- Action to bucket on
    )
    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
    GO
    ALTER EVENT SESSION [BucketizerTargetDemoRecompiles]
    ON SERVER
    STATE=START

    The above session collects the sql_statement_starting and sp_statement_starting Events, adds the database_id Action to the Event so that we can bucket on it, and then filters the Events to only fire if the state for the Event matches the map_key in sys.dm_xe_map_values for Recompile.  If the server being tested on doesn’t have a high recompile rate, an easy way to trigger Recompiles is to update the statistics on the tables inside of a database.

    EXECUTE sp_MSforeachtable 'UPDATE STATISTICS ?'

    To view the bucketized data from the target, we query sys.dm_xe_session_targets for our session and target using CAST to convert the target_data to XML in a derived table, and then using a CROSS APPLY of the .node() method to split on the <Slot> nodes.

    SELECT 
        DB_NAME(n.value('(value)[1]', 'int')) AS DatabaseName,
        n.value('(@count)[1]', 'int') AS EventCount,
        n.value('(@trunc)[1]', 'int') AS EventsTrunc
    FROM
    (SELECT CAST(target_data as XML) target_data
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets t
        ON s.address = t.event_session_address
    WHERE s.name = 'BucketizerTargetDemoRecompiles'
      AND t.target_name = 'asynchronous_bucketizer') as tab
    CROSS APPLY target_data.nodes('BucketizerTarget/Slot') as q(n)

    With the <Slot> nodes split, pulling the <value> node and attributes is very simple, and since we bucketed on database_id, we can use the DB_NAME() function in SQL to return the database name associated with the database_id in the <value> node.

    Considerations for Usage

    The bucketizer targets are great for simplifying analysis of Event data to determine who to best proceed with further troubleshooting.  However, in SQL Server 2008, and 2008R2 a bug exists that causes incorrect output from the bucketizers when used to bucket on the wait_info event wait_type Data element.  This was fixed in SQL Server 2008 Service Pack 2 (http://support.microsoft.com/kb/2285068), and is not a problem in SQL Server Denali CTP1, but as of this writing has yet to be corrected in SQL Server 2008 R2 (at least the CU’s I have tested, there may be a newer one that I have missed, but I didn’t find one in a search).  To demonstrate this problem the following Event Session can be used:

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BucketizerTargetDemoWaits')
        DROP EVENT SESSION [BucketizerTargetDemoWaits] ON SERVER;
    CREATE EVENT SESSION [BucketizerTargetDemoWaits]
    ON SERVER
    ADD EVENT sqlos.wait_info
    (    ACTION (sqlserver.database_id)
        WHERE (duration > 0)) 
    ADD TARGET package0.asynchronous_bucketizer(
         SET filtering_event_name='sqlos.wait_info', source_type=0, source='wait_type')
    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
    GO
    ALTER EVENT SESSION [BucketizerTargetDemoWaits]
    ON SERVER
    STATE=START

    The above Event Session will return valid map_key values for the wait_types Map in sys.dm_xe_map_values on SQL Server 2008 Service Pack 2 and SQL Server Denali CTP1, but will have erroneous information in the <value> node on SQL Server 2008 RTM and SP1 and SQL Server 2008 R2.  To query the bucketed waits from the target, use the following query:

    SELECT 
        mv.map_value AS WaitType,
        n.value('(@count)[1]', 'int') AS EventCount,
        n.value('(@trunc)[1]', 'int') AS EventsTrunc,
        n.value('(value)[1]', 'int') AS MapKey
    FROM
    (SELECT CAST(target_data as XML) target_data
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets t
        ON s.address = t.event_session_address
    WHERE s.name = 'BucketizerTargetDemoWaits'
      AND t.target_name = 'asynchronous_bucketizer') as tab
    CROSS APPLY target_data.nodes('BucketizerTarget/Slot') as q(n)
    JOIN sys.dm_xe_map_values as mv
        ON mv.map_key = n.value('(value)[1]', 'int')
    WHERE mv.name = 'wait_types'

    What’s next?

    Well, at this point we are nearly half way through the Targets in Extended Events, and tomorrow we’ll continue our investigation by looking at the synchronous_event_counter, which can be used to help determine the impact an Event Session may have without having to perform full Event collection.

  • An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target

    Yesterday’s post, Targets Week - ring_buffer, looked at the ring_buffer Target in Extended Events and how it outputs the raw Event data in an XML document.  Today I’m going to go over the details of the other Target in Extended Events that captures raw Event data, the asynchronous_file_target.

    What is the asynchronous_file_target?

    The asynchronous_file_target holds the raw format Event data in a proprietary binary file format that persists beyond server restarts and can be provided to another person via ftp or email for remote disconnected analysis of the events.  The asynchronous_file_target has two types of files that are associated with it, the log files which contain the Event data, and the metadata file which contains information about the Events contained in the log files, allowing correct parsing of the log files and the Events and associated Actions contained within them.  Depending on the options configured for the asynchronous_file_target, there may be multiple log files associated with a started Event Session, but there will only be one metadata file created for the duration of that Event Sessions collection.  Subsequent collections by the same Event Session, for example, stopping it and starting it again at a later time, will create a new metadata file associated with that collection by the Event Session.  These files exist as a set and must be maintained together for the log files to be read.

    Configuration Options

    The asynchronous_file_target like the ring_buffer, has configuration options that can be found in the sys.dm_xe_object_columns DMV.

    -- Target Configurable Fields
    SELECT 
        oc.name AS column_name,
        oc.column_id,
        oc.type_name,
        oc.capabilities_desc,
        oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o 
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc 
        ON o.name = oc.OBJECT_NAME 
        AND o.package_guid = oc.object_package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND o.object_type = 'target'
      AND o.name = 'asynchronous_file_target'
    

    In SQL Server 2008, 2008R2, and SQL Server Denali CTP1, there are five configuration options for the asynchronous_file_target.  The filename specifies the path and name of the log files and is a required to add the asynchronous_file_target to an Event Session.  The max_file_size option functions the same as SQL Trace maxfilesize option, limiting the size of each file before rollover occurs.  The max_rollover_files option functions the same as the SQL Trace maxrolloverfiles option, specifying the number of rollover files to maintain in the file system, and can be used in conjunction with the max_file_size option to prevent the SQL Server from running out of disk space during Event collection.  The increment option is similar to the AutoGrowth settings for a database in SQL Server, and specifies the size in megabytes that the log files grow, allowing the files to grow incrementally and reducing the number of times a log file has to grow while Events are being dispatched and buffered to the Target.  The metadatafile option specifies the path and name of the metadata file for the target.

     

    image

    Notice that the only mandatory option for the file target is the filename for the log files.  When the asynchronous_file_target is used in an Event Session, if the metadatafile option is not explicitly set, the asynchronous_file_target will use the same path and filename specified in the filename option with a .xem extension for the metadata file automatically. 

    Understanding the Target Data Format

    Like the ring_buffer, the asynchronous_file_target stores Event data in its raw format.  Inside the log files, the Event data is maintained in a binary format that minimizes the amount of space necessary to store the Events, maximizing the number of Events that can be stored inside the log files.  Unlike the ring_buffer target however, the asynchronous_file_target is queried not through the sys.dm_xe_session_targets DMV, but through the sys.fn_xe_file_target_read_file() DMF.  The sys.fn_xe_file_target_read_file() DMF requires four input parameters; @path which is the path, filename, and extension mask to the log files, @mdpath which is the path, filename, and extension mask to metadata file, @initial_file_name which is the exact path and filename of a file to start reading from and when specified requires the final parameter @initial_offset which is the offset inside that file from which to begin reading the events.

    The sys.fn_xe_file_target_read_file() DMF returns a single row for each instance of Event data that is contained inside of the log files being read.  The Event data is materialized into an XML document in the event_data column output by the DMF.when the Target information is queried using the sys.fn_xe_file_target_read_file() DMF, allowing it to be used for Event analysis.  Like the ring_buffer Target, the Event data returned by the sys.fn_xe_file_target_read_file() DMF is not schema bound, but it has exactly the same XML format as an individual <event> node in the ring_buffer Targets output making it very easy to parse the Events contained in either target with very similar XQuery’s.

    Querying/Parsing the Target Data

    Since the asynchronous_file_target returns the Event data as XML, we have to do the same type of XQuery work to retrieve the Event data from it as we did with the ring_buffer target from yesterday.  After reading yesterday’s post, Adam Machanic (Blog|Twitter) pointed out in a comment on Twitter that the slow XML parsing is an optimizer bug that is handled with a derived table in his Extended Events Code Generator.  Adam is absolutely correct, and if you attended my session on Extended Events this year at PASS you’d recall that I didn’t have performance issues in my demo’s for querying the Target data generated by my demo’s.  I used a very different parsing method in my PASS demo’s than I showed yesterday, and I plan to cover that method in a later post in this series already.  However, if you want to see the gist of how to work around the performance issue take a look at the code output by Adam’s code generator.

    I am going to reuse yesterday’s demo as a basis for looking at the asynchronous_file_target for simplicity as well as to show the similarity of the XQuery used for querying the Event data.  The basic Event Session captures the error_reported Event and to trigger an error performs a SELECT against a non-existent table.

    -- Create an Event Session to capture Errors Reported
    CREATE EVENT SESSION DemoPersistedEvents
    ON SERVER
    ADD EVENT sqlserver.error_reported
    ADD TARGET package0.ring_buffer,
    ADD TARGET package0.asynchronous_file_target(
         SET filename='D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\DemoPersistedEvents.xel')
    WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
    GO
    -- Alter the Event Session and Start it.
    ALTER EVENT SESSION DemoPersistedEvents
    ON SERVER
    STATE=START
    GO
    -- SELECT from a non-existent table to create Event
    SELECT *
    FROM master.schema_doesnt_exist.table_doesnt_exist
    GO
    -- Drop the Event to halt Event collection
    ALTER EVENT SESSION DemoPersistedEvents
    ON SERVER
    DROP EVENT sqlserver.error_reported
    GO

    The first thing we need to know to query our asynchronous_file_target is the filename and metafilename for the files that we want to query from.  If the event session is active and running, we can get this information by querying the Active Session DMV’s.

    SELECT 
        soc.column_name,
        soc.column_value
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_object_columns soc
        ON s.address = soc.event_session_address
    WHERE s.name = 'DemoPersistedEvents'
      AND soc.object_name = 'asynchronous_file_target'

    image

    Notice that the metatdatafile option is NULL, meaning that we were lazy and didn’t explicitly define the metadata file information in our Event Session so now we have to figure it out in order to query the target data from the log files.  One way to find the information would be to open up the path on the server to the log file that was specified:

    image

    Notice that the Extended Events Engine automatically created a metadata file with the same name as the log file, but a different extension, .xem.  Also notice that the file names for both the log file and the metadata file have changed from what was actually defined in the Event Session.  The Engine adds a _0_ and a long integer value that represents the number of milliseconds between January 1, 1600 and the date and time that the file was generated by the Extended Events Engine.  Subsequent files will have a different long integer value that is larger in value allowing you to easily sort the log files from oldest to newest or vice versa.  To query the data contained in the log files, you have two options.  First you can explicitly provide the filenames as shown above, or you can use wildcards in the names and the engine will find the correct matching files and begin reading them.

    DECLARE @path nvarchar(260), @mdpath nvarchar(260)
    
    -- Get the log file name and substitute * wildcard in
    SELECT 
        @path = LEFT(column_value, LEN(column_value)-CHARINDEX('.', REVERSE(column_value))) 
            + '*' 
            + RIGHT(column_value, CHARINDEX('.', REVERSE(column_value))-1)
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_object_columns soc
        ON s.address = soc.event_session_address
    WHERE s.name = 'DemoPersistedEvents'
      AND soc.object_name = 'asynchronous_file_target'
      AND soc.column_name = 'filename'
    
    -- Get the metadata file name and substitute * wildcard in 
    SELECT 
        @mdpath = LEFT(column_value, LEN(column_value)-CHARINDEX('.', REVERSE(column_value))) 
            + '*' 
            + RIGHT(column_value, CHARINDEX('.', REVERSE(column_value))-1)
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_object_columns soc
        ON s.address = soc.event_session_address
    WHERE s.name = 'DemoPersistedEvents'
      AND soc.object_name = 'asynchronous_file_target'
      AND soc.column_name = ' metadatafile'
    
    -- Set the metadata filename if it is NULL to the log file name with xem extension
    SELECT @mdpath = ISNULL(@mdpath, 
                            LEFT(@path, LEN(@path)-CHARINDEX('*', REVERSE(@path))) 
                            + '*xem')
    
    -- Query the Event data from the Target.
    SELECT
        module_guid,
        package_guid,
        object_name,
        event_data,
        file_name,
        file_offset
    FROM sys.fn_xe_file_target_read_file(@path, @mdpath, null, null)
    

    image

    The DMF outputs the module_guid, package_guid, and object_name associated with the Event, the event_data as a XML document, but in string format requiring that it be CAST/CONVERT’d to XML for parsing, the file_name of the log file that the Event data was read from and the file_offset inside the file for the event.  Using a CAST to XML and performing a CROSS APPLY of the <event> nodes and the same XQuery’s as in yesterday’s post we can query the Event data from the asynchronous_file_target.

    -- Query the Event data from the Target.
    SELECT 
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh, 
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="error"]/value)[1]', 'int') as error,
        n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
        n.value('(data[@name="duration"]/value)[1]', 'int') as state,
        n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
        n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
    FROM 
    (SELECT
        CAST(event_data AS XML) AS event_data
     FROM sys.fn_xe_file_target_read_file(@path, @mdpath, null, null)
    ) as tab
    CROSS APPLY event_data.nodes('event') as q(n)

    Like the ring_buffer Target, the asynchronous_file_target also has an entry in sys.dm_xe_session_targets, but instead of returning the Event data, it returns information about the targets operation.

    select 
        target_data.value('(FileTarget/@truncated)[1]', 'int') as truncated,
        target_data.value('(FileTarget/Buffers/@logged)[1]', 'int') as logged,
        target_data.value('(FileTarget/Buffers/@dropped)[1]', 'int') as dropped
    FROM
    (SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets AS t 
        ON t.event_session_address = s.address
    WHERE s.name = 'DemoPersistedEvents'
      AND t.target_name = 'asynchronous_file_target'
    ) as tab

    The file_name and file_offset information in the the sys.fn_xe_file_target_read_file output can be used to perform differential reads from the asynchronous_file_target.  To demonstrate this we can create an Event Session that will capture a lot of Events in a short period of time.

    (Note: I wouldn’t create an unfiltered Event Session on the starting and completed events like this on a production server without first evaluating its potential impact.  While this should be safe, if it causes you a problem, its your server not mine.)

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FileTargetDemo')
        DROP EVENT SESSION [FileTargetDemo] ON SERVER;
    CREATE EVENT SESSION [FileTargetDemo]
    ON SERVER
    ADD EVENT sqlserver.sql_statement_starting,
    ADD EVENT sqlserver.sql_statement_completed,
    ADD EVENT sqlserver.sp_statement_starting,
    ADD EVENT sqlserver.sp_statement_completed,
    ADD EVENT sqlserver.rpc_starting,
    ADD EVENT sqlserver.rpc_completed,
    ADD EVENT sqlserver.module_start,
    ADD EVENT sqlserver.module_end
    ADD TARGET package0.asynchronous_file_target(
         SET filename='D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo.xel', 
             metadatafile='D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo.xem',
             max_file_size = 5,
             max_rollover_files = 5)
    WITH(MAX_DISPATCH_LATENCY = 5SECONDS)
    GO
    
    -- Start the Event Collection
    ALTER EVENT SESSION [FileTargetDemo]
    ON SERVER
    STATE=START
    GO
    
    -- Take a pause and allow events to be generated
    
    
    -- Query the target data from the files.
    SELECT 
        object_name,
        CAST(event_data as xml) as event_data,
        file_name, 
        file_offset
    FROM sys.fn_xe_file_target_read_file('D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo*xel', 
                'D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo*xem', 
                null,
                null)

    If you scroll through the output to where the file_offset changes, you can grab the file_name and file_offset for the last event in the first file_offset.

    image

    Then requery the target passing that file_name and file_offset into the @initial_file_name and @intitial_offset parameters of the sys.fn_xe_file_target_read_file DMF to have the DMF begin reading from the last entry of the provided offset forward.

    -- Query the target data from the files.
    SELECT 
        object_name,
        CAST(event_data as xml) as event_data,
        file_name, 
        file_offset
    FROM sys.fn_xe_file_target_read_file('D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo*xel', 
                'D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo*xem',             
                'D:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Log\FileTargetDemo_0_129360796797990000.xel',
                0)

    image

    If you’ve run the demo’s in this blog post to this point, don’t forget to cleanup the system.

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FileTargetDemo')
        DROP EVENT SESSION [FileTargetDemo] ON SERVER;
    GO
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='DemoPersistedEvents')
        DROP EVENT SESSION [DemoPersistedEvents] ON SERVER;
    GO

    Considerations for Usage

    The asynchronous_file_target will probably be the preferred target for most people interested in performing long term analysis of Events collected, or performing short term analysis using an Event Session that is expected to generate a large number of events and event loss due to the FIFO nature of the ring_buffer is not acceptable.  However, there are a couple of considerations associated with this target.  The first is that the log files and metadata file are a set, and have to be maintained together.  If you send someone a log file without the metadata file, they won’t be able to read the information contained in the log file.  The second consideration associated with this target is that the only way to read the information contained inside of the log files, as of the date of this blog post being published, is to copy them to a system that is running SQL Server 2008 or 2008R2 and query the files using the there is no way to retrieve the information contained inside of the log files, without querying the sys.fn_xe_file_target_read_file() DMF using TSQL. 

    What’s next?

    Now that we have looked at the asynchronous_file_target Target, in the next post we’ll look at the bucketizer Targets which can be used to group occurrences of Events based on the Event data being returned.

  • An XEvent a Day (5 of 31) - Targets Week – ring_buffer

    Yesterday’s post, Querying the Session Definition and Active Session DMV’s, showed how to find information about the Event Sessions that exist inside a SQL Server and how to find information about the Active Event Sessions that are running inside a SQL Server using the Session Definition and Active Session DMV’s.  With the background information now out of the way, and since this post falls on the start of a new week I’ve decided to make this Targets Week, where each day we’ll look at a different target in Extended Events starting with the ring_buffer today.

    What is the ring_buffer?

    The ring_buffer is one of two targets available in Extended Events that captures event data in its raw format.  The ring_buffer is a memory resident target that holds event data in a single XML document while the Event Session is active on the SQL Server.  When the Event Session is stopped, the memory buffers allocated to the ring_buffer target are freed and all data contained in the target disappears.  The ring_buffer collects events in a First In First Out (FIFO) manner that can be configured to be strict, where the oldest event is removed when the memory allocated to the target become full and new events arrive, or per event, allowing you to specify the maximum number of occurrences that will be retained for each event defined in the event session.  The default configuration for event flushing is strict FIFO.

    Configuration Options

    The ring_buffer like most of the targets has configuration options that can be found in the sys.dm_xe_object_columns DMV.

    -- Target Configurable Fields
    SELECT 
        oc.name AS column_name,
        oc.column_id,
        oc.type_name,
        oc.capabilities_desc,
        oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o 
        ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc 
        ON o.name = oc.OBJECT_NAME 
        AND o.package_guid = oc.object_package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND o.object_type = 'target'
      AND o.name = 'ring_buffer'
    

    In SQL Server 2008, 2008R2, and SQL Server Denali CTP1, the configuration options for the ring buffer are the max_memory option, which sets the maximum amount of memory to be used by the target buffers to hold Event data, and the occurence_number, which is used to configure the FIFO operation of the target per Event, specifying the number of events by type that the target will retain.

    image 

    Both of the configurable options for the ring_buffer target are optional, and when they are not specified as a part of the Event Session definition, they take on their default values.  As previously stated, the default configuration for the ring_buffer is to use strict FIFO if no value is specified for the occurence_number in the Event Session.  The default max_memory value is 4MB for the ring_buffer, which is covered in slightly more detail in the Considerations for Usage section of this post.

    Understanding the Target Data Format

    As previously mentioned, the ring_buffer stores Event data in its raw format.  Inside the Extended Events Engine, the Event data is maintained in a binary format that minimizes the amount of memory necessary to store the Events, maximizing the number of Events that can be stored inside the Targets memory buffers.  The Event data is materialized into an XML document when the Target information is queried using the sys.dm_xe_session_targets DMV, allowing it to be used for Event analysis.  The ring_buffer XML document contains a parent XML <RingBufferTarget> Node that contains attributes about the Targets operation since the Event Session was started including the number of Events processed per second, the amount of time the Target has spent processing Events, the total number of Events that have been processed by the Target, the current number of Events in the target, the number of Events dropped due to full buffers, and the amount of memory used by the Target.

    <RingBufferTarget eventsPerSec="" processingTime="" totalEventsProcessed="" eventCount="" droppedCount="" memoryUsed="" />

    Inside of the <RingBufferTarget> parent node, are the Event data XML nodes which contain the information returned by the Events defined in the Event Session.  While the XML returned for the Event data does not conform to any published XML Schema, it does have a predictable format based on the Event Session definition.  The root <event> node contains attributes for the Event name, the Package that loaded the Event metadata and that fired the Event for the Event Session, an id associated with the Event, a version associated with the Event, and the timestamp for the date and time in GMT that the Event fired on the server.  Each <event> node will have one or more <data> nodes that contain the information for each of the Event Data Elements returned by the Events default payload.  If Actions have been defined for the Event in the Event Session the <event> node will have an <action> node for each of the Actions that were added to the Event.

    The <data> nodes and <action> nodes share a common XML schema, with one exception.  These <data> nodes contain a single attribute containing the name of the Data Element contained by that node, whereas the <action> nodes contain two attributes; one containing the name of the Action contained by that node, and the other the Package for the Action.  Each <data> or <action> node will have a <type> node that contains two attributes; the name of the Type for data type of the value being returned by the parent node, and the Package for the Type.  The <data> or <action> node will also have two additional nodes; a <value> node which contains the value for the data being returned in the Data Element, and a <text> node which will contain the Map lookup text for Data Elements that correspond to Maps in the Extended Events Metadata.  The basic XML definition of an <event> node would be:

    <event name="" package="" id="" version="" timestamp="">
      <data name="">
        <type name="" package="" />
        <value />
        <text />
      </data>
      <action name="" package="">
        <type name="" package="" />
        <value />
        <text />
      </action>
    </event>

    Below is an example Event from the default system_health Event Session that is running on every installation of SQL Server 2008, and SQL Server Denali CTP1 for the wait_info Event.

    <event name="wait_info" package="sqlos" id="48" version="1" timestamp="2010-12-03T15:29:00.578Z">
      <data name="wait_type">
        <type name="wait_types" package="sqlos" />
        <value>98</value>
        <text>ASYNC_IO_COMPLETION</text>
      </data>
      <data name="opcode">
        <type name="event_opcode" package="sqlos" />
        <value>1</value>
        <text>End</text>
      </data>
      <data name="duration">
        <type name="uint64" package="package0" />
        <value>44598</value>
        <text />
      </data>
      <data name="max_duration">
        <type name="uint64" package="package0" />
        <value>44598</value>
        <text />
      </data>
      <data name="total_duration">
        <type name="uint64" package="package0" />
        <value>44598</value>
        <text />
      </data>
      <data name="signal_duration">
        <type name="uint64" package="package0" />
        <value>0</value>
        <text />
      </data>
      <data name="completed_count">
        <type name="uint64" package="package0" />
        <value>1</value>
        <text />
      </data>
      <action name="callstack" package="package0">
        <type name="callstack" package="package0" />
        <value>0x0000000001829555
    0x0000000000CEA584
    0x000000000233FA28
    0x0000000002E2FA0C
    0x0000000002F10CB9
    0x0000000002F112D7
    0x0000000002F1B90B
    0x0000000002D8C59A
    0x0000000000B0F6D2
    0x000000000065C59B
    0x000000000065C25A
    0x000000000065BF35
    0x0000000000BE6410
    0x0000000000BE64E0
    0x0000000000BD87A0
    0x0000000000BE5F9F</value>
        <text />
      </action>
      <action name="session_id" package="sqlserver">
        <type name="uint16" package="package0" />
        <value>87</value>
        <text />
      </action>
      <action name="sql_text" package="sqlserver">
        <type name="unicode_string" package="package0" />
        <value>Unable to retrieve SQL text</value>
        <text />
      </action>
    </event>

    In this Event, the wait_type and opcode Data Elements correspond to the Maps wait_types and opcode respectively, and the text value for the Map that corresponds to the <value> node is in the <text> node.  It is possible to lookup the Maps in the DMV’s separately though not necessary in this example as follows:

    SELECT 
        p.name AS package_name,
        mv.name AS map_name,
        mv.map_key,
        mv.map_value
    FROM sys.dm_xe_packages p
    JOIN sys.dm_xe_map_values mv 
    ON p.guid = mv.object_package_guid
    WHERE (p.name = 'sqlos' AND mv.name = 'wait_types')
       OR (p.name = 'sqlos' AND mv.name = 'event_opcode') 

    Querying/Parsing the Target Data

    Since the ring_buffer target returns the Event data as XML, obviously we are going to have to do a little bit of work to shred the XML into actionable data using XQuery.  For those new to XQuery, the best I can recommend is to jump over to my good friend Jacob Sebastian’s blog and work your way through his series of XQuery Labs, which have been an amazing resource along my way to learning XQuery.  If you are not interested in learning XQuery, but still want to work with Extended Events, all is not lost.  Adam Machanic wrote the Extended Events Code Generator, which you can use to generate a TSQL statement that will parse out the target data for the ring_buffer, as well as for tomorrows topic, the asynchronous_file_target.  It also includes a SQLCLR helper TVF that optimizes shredding the XML by leveraging the power of .NET that you can optionally deploy in your environment.  Another option is to use the Extended Events SSMS Addin for SQL Server 2008 which includes a TargetDataViewer that shreds the XML for every target available in Extended Events and displays the Event data in a SQL Profiler like GridView inside of SQL Server Management Studio.  Beyond these two tools, you can also use the code available in this blog series.

    One of the nuances of working with XML inside of SQL Server, especially with Extended Events, is that sometimes it is better for performance to use a XML variable to hold the XML data for shredding, rather than attempting to shred the XML directly from the DMV’s.  I’ve never quite figured out why this is the case, but it tends to make a bigger impact on larger XML documents, specifically those in the 2MB+ size range.

    Since every server running SQL Server 2008, 2008R2, or Denali CTP1 has the system_health session running by default in it, I am going to use that event session to demonstrate how to query the information from the ring_buffer target.  To get the Target data into an XML variable, we’ll need to query the target_data column of the sys.dm_xe_session_targets DMV and CAST the value returned to the XML data type.

    DECLARE @target_data XML
    SELECT @target_data = CAST(target_data AS XML)
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets AS t 
        ON t.event_session_address = s.address
    WHERE s.name = 'system_health'

    With that variable, we can then parse out the Targets header information from the <RingBufferTarget> node attributes:

    SELECT 
        @target_data.value('(RingBufferTarget/@eventsPerSec)[1]', 'int') AS eventsPerSec,
        @target_data.value('(RingBufferTarget/@processingTime)[1]', 'int') AS processingTime,
        @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS totalEventsProcessed,
        @target_data.value('(RingBufferTarget/@eventCount)[1]', 'int') AS eventCount,
        @target_data.value('(RingBufferTarget/@droppedCount)[1]', 'int') AS droppedCount,
        @target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int') AS memoryUsed

    We can also parse out the individual <event> data nodes from the Target data by using .nodes() method and specifying the XPath to the event nodes and then using .query() method to materialize each node returned by .nodes() as a separate XML document for output.

    SELECT 
        n.query('.') AS event_data
    FROM @target_data.nodes('RingBufferTarget/event') AS q(n)

    We can also use an XPath filter in the .nodes() method to query specific Events only

    (Note: if your server hasn’t encountered waits that exceed the predicates for the system_health session, this query will not return results).

    SELECT 
        n.query('.') AS event_data
    FROM @target_data.nodes('RingBufferTarget/event[@name=''wait_info'']') AS q(n)

    Building on this, we can shred the <event> nodes for the wait_info Events based on the Event definition in the Event Session to turn the XML data into a tabular output that is easier to read.

    SELECT 
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh, 
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="wait_type"]/text)[1]', 'varchar(250)') as wait_type,
        n.value('(data[@name="opcode"]/text)[1]', 'varchar(250)') as opcode,
        n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
        n.value('(data[@name="max_duration"]/value)[1]', 'bigint') as max_duration,
        n.value('(data[@name="total_duration"]/value)[1]', 'bigint') as total_duration,
        n.value('(data[@name="completed_count"]/value)[1]', 'bigint') as completed_count,
        n.value('(action[@name="callstack"]/value)[1]', 'varchar(max)') as callstack,
        n.value('(action[@name="session_id"]/value)[1]', 'int') as session_id,
        n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
    FROM @target_data.nodes('RingBufferTarget/event[@name=''wait_info'']') AS q(n)

    Now we could do a lot more actionable work with this data by turning this query into a derived table, or changing it to be a SELECT INTO a temporary table that we query a number of other ways after shredding the XML information.  I’ll leave the possibilities for how to consume this data after shredding the XML up to your imagination, and for another post in this series.

    Considerations for Usage

    While the ring_buffer target may seem like the ideal Target for short term analysis, there are a number of considerations that must be made in determining whether or not it is the correct Target to use in an Event Session.  The first of these is the number of Events that the Target can actually hold based on the max_memory.  If the Event Session is expected to generate a large quantity of Events, the ring_buffer will probably not meet your needs, depending on the Events definitions in the Event Session.  In addition to this consideration, there is a known issue related to the ring_buffer Target associated with it returning XML through the sys.dm_xe_session_targets DMV as discussed by Bob Ward in his blog post: You may not see the data you expect in Extended Event Ring Buffer Targets….  Essentially, the DMV can only return 4MB of materialized XML, which becomes problematic for a Target that is defined to retain 4MB of Event data in binary form.  The binary representation of the Event data can easily exceed 4MB when materialized as XML for the DMV to output.  When this occurs the output from the DMV is a malformed XML document, as detailed in the Connect item referenced in Bob’s blog post.  According to the the Connect item, this problem has been addressed in SQL Server 2008 Service Pack 2.  In addition to this issue, Adam Machanic filed a slightly different Connect item regarding the failure of the ring_buffer target to return all of the Events captured, which according to the feedback comments is also fixed in SQL Server 2008 Service Pack 2 and SQL Server 2008 R2 Cumulative Update 1.

    One of the other considerations for using the ring_buffer Target is that the information captured by an Event Session is memory resident only.  This means that if you are capturing Events in the Event Session that are critical and require persistence in the event of that the SQL Server instance encounters a crash the information captured by the target will not be available when the SQL Server instance restarts.  However, when you are doing analysis of a specific workload while the server is online and available, the ring_buffer can still be useful for capturing Events specific to the workload being analyzed.  To accommodate this, and capture Events and maintain them in a static nature without performing a DROP SESSION on the Event Session, it is necessary to remove the Events from the Event Session by performing an ALTER EVENT SESSION in conjunction with the DROP EVENT DDL command.  To demonstrate this, we can create an Event Session that captures the error_reported Event.

    -- Create an Event Session to capture Errors Reported
    CREATE EVENT SESSION DemoPersistedEvents
    ON SERVER
    ADD EVENT sqlserver.error_reported
    ADD TARGET package0.ring_buffer
    WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
    GO
    -- Alter the Event Session and Start it.
    ALTER EVENT SESSION DemoPersistedEvents
    ON SERVER
    STATE=START
    GO
    -- SELECT from a non-existent table to create Event
    SELECT *
    FROM master.schema_doesnt_exist.table_doesnt_exist
    GO
    -- Drop the Event to halt Event collection
    ALTER EVENT SESSION DemoPersistedEvents
    ON SERVER
    DROP EVENT sqlserver.error_reported
    GO
    -- Wait for Event buffering to Target
    WAITFOR DELAY '00:00:01'
    GO
    -- Create XML variable to hold Target Data
    DECLARE @target_data XML
    SELECT @target_data = CAST(target_data AS XML)
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets AS t 
        ON t.event_session_address = s.address
    WHERE s.name = 'DemoPersistedEvents'
      AND t.target_name = 'ring_buffer'
    
    -- Query XML variable to get Event Data
    SELECT 
        @target_data.value('(RingBufferTarget/@eventsPerSec)[1]', 'int') AS eventsPerSec,
        @target_data.value('(RingBufferTarget/@processingTime)[1]', 'int') AS processingTime,
        @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS totalEventsProcessed,
        @target_data.value('(RingBufferTarget/@eventCount)[1]', 'int') AS eventCount,
        @target_data.value('(RingBufferTarget/@droppedCount)[1]', 'int') AS droppedCount,
        @target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int') AS memoryUsed
         
    SELECT 
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh, 
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="error"]/value)[1]', 'int') as error,
        n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
        n.value('(data[@name="duration"]/value)[1]', 'int') as state,
        n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
        n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
    FROM @target_data.nodes('RingBufferTarget/event') AS q(n)
    GO
    -- Drop the Event Session to cleanup Demo
    DROP EVENT SESSION DemoPersistedEvents
    ON SERVER

    If your specific requirements allow for the loss of Events due to the FIFO nature of the ring_buffer Target, and you have applied the necessary patches to your SQL Server instance to ensure that invalid XML is not returned by the sys.dm_xe_session_targets DMV, the ring_buffer Target may provide the required functionality for your specific implementation.  When using an Event Session in the short term, or an Event Session that is Predicated to minimize the number of Events that will actually be fired, the ring_buffer is a maintenance free method of collecting raw Event data for further analysis when the Events are dropped from the Event Session to ensure that unnecessary Events are not captured by the Event Session.

    What’s next?

    Now that we have looked at the ring_buffer Target, in the next post we’ll look at the asynchronous_file_target which can be used to capture raw Event data into a persistent file in the Operating System allowing for long term Event analysis and capturing Events in a persistent manner that exists beyond SQL Server Instance failures and service restarts and in a manner than can be provided for external analysis similar to SQL Trace files.

  • An XEvent a Day (4 of 31) – Querying the Session Definition and Active Session DMV’s

    Yesterdays post, Managing Event Sessions, showed how to manage Event Sessions in Extended Events Sessions inside the Extended Events framework in SQL Server. In today's post, we’ll take a look at how to find information about the defined Event Sessions that already exist inside a SQL Server using the Session Definition Catalog Views and how to find information about the Active Event Sessions that exist using the Active Session DMV’s.

    Session Definition Catalog Views

    (Note: The following section has been changed based on feedback provided by Mike Wachal, who works on the Extended Events team at Microsoft.  In the original posting, this section refered to the Session Definition DMV's, which Mike pointed out is not accurate.  Per Mike, "Session Definition DMV’s that you describe are not DMVs, they are part of the system catalog. The system catalog is persisted storage while DMVs are generated at runtime by querying the state of the server."  To keep the information in this series as technically accurate as possible I have updated all references to these being DMV's to instead be Catalog Views, views, or system views.  Many thanks to Mike for pointing out this discrepancy.)

    The Session Definition Catalog Views provide information about the Event Sessions that have been defined in the Extended Events Engine and may or may not be actively running against the SQL Server Instance.  Five system views provide information about the Event Sessions that exist inside of the Extended Events Engine; sys.server_event_sessions, sys.server_event_session_events, sys.server_event_session_actions, sys.server_event_session_targets, and sys.server_event_session_fields.

    sys.server_event_sessions

    The sys.server_event_sessions view provides information about the Event Sessions that exist inside of the Extended Events Engine.  The Session level options for the Event Session can be retrieved from this view, to determine how the Event Session is configured.

    -- Session level information for current Event Sessions
    SELECT
      
    s.name,
      
    s.max_memory,
      
    s.event_retention_mode_desc,
      
    s.max_dispatch_latency,
      
    s.max_event_size,
      
    s.memory_partition_mode_desc,
      
    s.track_causality,
      
    s.startup_state
    FROM sys.server_event_sessions s

    sys.server_event_session_events

    The sys.server_event_session_events view provides information about the specific Events that are defined in the Event Sessions maintained by the Extended Events Engine.  This view also returns the defined Predicates for the Events that are included for collection in Event Sessions on the server.  The event_session_id column can be used to join this view to sys.server_event_sessions as shown below.

    -- Get events in a session
    SELECT
      
    ses.name AS session_name,
      
    sese.package AS event_package,
      
    sese.name AS event_name,
      
    sese.predicate AS event_predicate
    FROM sys.server_event_sessions AS ses
    JOIN sys.server_event_session_events AS sese
       
    ON ses.event_session_id = sese.event_session_id

    sys.server_event_session_actions

    The sys.server_event_session_actions view contains one row for each of the Actions that have been added to an Event in an Event Session.  If the same Action was added to multiple Events, there would be a separate row per Event and Action pair in the Event Session.  The event_session_id and event_id columns are used to join this view to the sys.server_event_session_events view.

    -- Get actions 
    SELECT
      
    ses.name AS session_name,
      
    sese.package AS event_package,
      
    sese.name AS event_name,
      
    sese.predicate AS event_predicate,
      
    sesa.package AS action_package,
      
    sesa.name AS action_name
    FROM sys.server_event_sessions AS ses
    JOIN sys.server_event_session_events AS sese
       
    ON ses.event_session_id = sese.event_session_id
    JOIN sys.server_event_session_actions AS sesa
        
    ON ses.event_session_id = sesa.event_session_id
       
    AND sese.event_id = sesa.event_id

    sys.server_event_session_targets

    The sys.server_event_session_targets view contains one row for each of the configured Targets that are defined for an Event Session.  The event_session_id column is used to join this view to the sys.server_event_sessions view.

    -- Get target information
    SELECT
      
    ses.name AS session_name,
      
    sest.name AS target_name
    FROM sys.server_event_sessions AS ses
    JOIN sys.server_event_session_targets AS sest
            
    ON ses.event_session_id = sest.event_session_id

    sys.server_event_session_fields

    The sys.server_event_session_fields view contains one row for each of the configured options for each Target defined for an Event Session.  The event_session_id and target_id columns are used to join this view to the sys.server_event_session_targets view.

    -- Get target option information
    SELECT
      
    ses.name AS session_name,
      
    sest.name AS target_name,
      
    sesf.name AS option_name,
      
    sesf.value AS option_value
    FROM sys.server_event_sessions AS ses
    JOIN sys.server_event_session_targets AS sest
            
    ON ses.event_session_id = sest.event_session_id
    JOIN sys.server_event_session_fields AS sesf
        
    ON sest.event_session_id = sesf.event_session_id
       
    AND sest.target_id = sesf.OBJECT_ID

    Active Session DMV’s

    The Active Session DMV’s provide information about the Event Sessions that are currently in a started state on a SQL Server Instance.  Five DMV’s make up the group of Active Session DMV’s; sys.dm_xe_sessions, sys.dm_xe_session_events, sys.dm_xe_session_event_actions, sys.dm_xe_session_targets, and sys.dm_xe_session_object_columns.

    sys.dm_xe_sessions

    The sys.dm_xe_sessions DMV contains one row for each active Event Session (STATE=START) in the SQL Server Instance, and provides information about the configuration of the Session buffers.  Information about the size, and number of buffers is returned for the regular sized and large sized buffers associated with the Event Session.  An Event Session will have large sized buffers when the MAX_EVENT_SIZE configured is larger than the regular buffer size.  In general, most Events will be buffered to the regular buffers.  Information about event loss associated with the buffers being full and buffers that are full and pending dispatch is also contained in this DMV.

    -- Look at Active Session Information
    SELECT
      
    s.name,
      
    s.pending_buffers,
      
    s.total_regular_buffers,
      
    s.regular_buffer_size,
      
    s.total_large_buffers,
      
    s.large_buffer_size,
      
    s.total_buffer_size,
      
    s.buffer_policy_flags,
      
    s.buffer_policy_desc,
      
    s.flags,
      
    s.flag_desc,
      
    s.dropped_event_count,
      
    s.dropped_buffer_count,
      
    s.blocked_event_fire_time,
      
    s.create_time,
      
    s.largest_event_dropped_size
    FROM sys.dm_xe_sessions AS s

    sys.dm_xe_session_targets

    The sys.dm_xe_session_targets DMV will contain one row for each Target that exists for an active Event Session.  Information about the Target such as the Target name (ring_buffer, pair_matching, etc.) and Target execution statistics are returned by this DMV.  For memory resident Targets, the target_data columns will return an XML document containing the information about the Events that have been dispatched to the Target and are still available.  For persisted Targets, the target_data column still contains an XML document, but only statistics about the Target will be contained in the document.  More specific information about the target_data column will be provided in the next week as we look at each Target individually.  The event_session_address column is used to join this DMV to the address column in the sys.dm_xe_sessions DMV.

    -- Target information for a running session
    SELECT
      
    s.name AS session_name,
      
    t.target_name AS target_name,
      
    t.execution_count AS execution_count,
      
    t.execution_duration_ms AS execution_duration,
      
    CAST(t.target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
      
    ON s.address = t.event_session_address

    sys.dm_xe_session_events

    The sys.dm_xe_session_events DMV contains one row for each Event that is defined in an Active Event Session.  The predicate definition for each event, if defined, is included in the output of this DMV.  However, the predicate is not the same as returned by sys.server_event_session_events if standard logical operators were used in the Event definition.  Instead the Predicates are converted to use Predicate Comparators in text form, and for complex Predicates, the length can exceed the allowable output.  When this occurs, “Predicate too large for display” will be returned by the DMV.  The event_session_address column is used to join this DMV to the address column in the sys.dm_xe_sessions DMV.

    -- Event Information for a running session
    SELECT s.name AS session_name,
          
    e.event_name AS event_name,
          
    e.event_predicate AS event_predicate
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_events AS e
        
    ON s.address = e.event_session_address

    sys.dm_xe_session_event_actions

    The sys.dm_xe_session_event_actions DMV contains one row for each Action that is defined on an Event in an Active Event Session.  If the same Action is defined on multiple Events in the Event Session, one row will be returned for each Event/Action pair.  The event_session_address and event_name columns are used to join this DMV to the address column in the sys.dm_xe_session_events DMV.

    -- Event Information with Actions for a running session
    SELECT s.name AS session_name,
          
    e.event_name AS event_name,
          
    e.event_predicate AS event_predicate,
          
    ea.action_name AS action_name
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_events AS e
        
    ON s.address = e.event_session_address
    JOIN sys.dm_xe_session_event_actions AS ea
        
    ON e.event_session_address = ea.event_session_address
       
    AND e.event_name = ea.event_name

    sys.dm_xe_session_object_columns

    The sys.dm_xe_session_object_columns DMV contains one row for each of the configured options for a Target that is defined in an Active Event Session, as well as one row for each of the customizable Data Elements for a Event that is defined in an Active Event Session.  The event_session_address and event_name columns are used to join this DMV to the address column in the sys.dm_xe_session_events DMV.  The event_session_address and target_name columns are used to join this DMV to the address column in the sys.dm_xe_session_targets DMV.

    -- Configurable event and target column information
    SELECT DISTINCT s.name AS session_name,
          
    oc.OBJECT_NAME,
          
    oc.object_type,
          
    oc.column_name,
          
    oc.column_value
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
        
    ON s.address = t.event_session_address
    JOIN sys.dm_xe_session_events AS e
        
    ON s.address = e.event_session_address
    JOIN sys.dm_xe_session_object_columns AS oc
        
    ON s.address = oc.event_session_address
       
    AND ((oc.object_type = 'target' AND t.target_name = oc.OBJECT_NAME)
           OR (
    oc.object_type = 'event' AND e.event_name = oc.OBJECT_NAME))

    What’s next?

    Now that we understand how to query the Extended Events Metadata, how to manage Event Sessions, and how to determine what Event Sessions have been created in a SQL Server, the next week of this series will focus on the specific targets of Extended Events and how to query the data contained in them.  The next post will look at the ring_buffer target and the data that it exposes.

  • An XEvent a Day (3 of 31) – Managing Event Sessions

    Yesterdays post, Querying the Extended Events Metadata, showed how to discover the objects available for use in Extended Events.  In todays post, we’ll take a look at the DDL Commands that are used to create and manage Event Sessions based on the objects available in the system.  Like other objects inside of SQL Server, there are three DDL commands that are used with Extended Events; CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION.  The command names are self explanatory and their purposes should be clear to most SQL Server DBA’s.  The books online covers the syntax in detail so I won’t rehash all of that in this post, but will instead provide examples for each that cover specific areas of the commands.

    CREATE EVENT SESSION

    Creating an Event session adds the session definition to the Extended Events Engine making it available for event collection.  Creating an Event Session requires that at least one event be added to the session, but an Event Session does not need to have a target added to it to be created.  An individual event or target can only  be used once in an Event Session, and the complex predicates that are possible in Extended Events mitigates the need to have the same event multiple times in a session.  Session options are optional and if left unspecified the defaults documented in the books online will be used for the Event Session.  An example Event Session that shows all of the options used is:

    CREATE EVENT SESSION [TrackTempdbFileWrites] ON SERVER
    ADD EVENT sqlserver.file_write_completed(
      
    SET collect_path = 1
       ACTION
    (sqlserver.sql_text)
      
    WHERE database_id = 2),
    ADD EVENT sqlserver.file_written(
      
    WHERE database_id = 2)
    ADD TARGET package0.ring_buffer,
    ADD TARGET package0.asynchronous_bucketizer(
        
    SET filtering_event_name='sqlserver.file_write_completed', source_type=0, source='file_id')
    WITH (MAX_MEMORY=4096 KB,
        
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
        
    MAX_DISPATCH_LATENCY=30 SECONDS,
        
    MAX_EVENT_SIZE=0 KB,
        
    MEMORY_PARTITION_MODE=NONE,
        
    TRACK_CAUSALITY=OFF,
        
    STARTUP_STATE=OFF)
    GO

     

    This event session captures the sqlserver.file_write_completed event and specifies that the event should collect the path to the file, which is a customizable column on the event, to execute the sql_text action to collect the sql_text that caused the event to fire, if it is available, and to only fire the event for database_id = 2, which happens to be tempdb.  Notice that in the Event definition, only the ACTION specification is defined inside of a set of parenthesis, which is defined the the BOL as required in the <event_definition> specification:

    <event_definition>::=
    {
        ADD EVENT [event_module_guid].event_package_name.event_name
             [ ( {
                     [ SET { event_customizable_attribute = <value> [ ,...n] } ]
                     [ ACTION ( { [event_module_guid].event_package_name.action_name [ ,...n] } ) ]
                     [ WHERE <predicate_expression> ]
            } ) ]
    }

    Modifications to a specific event are enclosed in a set of parenthesis, and then only the Action is constrained to provide a set of parenthesis in its definition.  However, the <predicate_expression> can optionally include parenthetical notation to group predicate sets similar to what is possible in the TSQL WHERE clause.  The session also captures the file_written event when the database_id = 2.

    The session utilizes two targets, not because it necessarily provides additional meaning to the event consumption, but because I needed a valid example that shows how multiple targets are defined on a single event session at creation.  The ring_buffer target is defined and will capture the raw event data using the default configuration options for the target, and the asynchronous_bucketizer target will count the occurrences of the sqlserver.file_write_completed events based on their specific source data element (file_id), allowing for easy identification of the files that are being written to the most frequently.

    The session options provided in the above script correspond to the default session options that are defined if an Event Session is created without specifying the WITH() clause at all.  The maximum buffer space allocated for the Session is 4MB, and the session allows single events to be lost if the events are generated faster than they can be dispatched to the targets, the maximum amount of time that an event can be in the buffers before being dispatched to the targets is 30 seconds, the maximum event size is not set, no partitioning of buffers is defined, causality tracking is not turned on, and the session will not start automatically when the SQL Server service starts.

    ALTER EVENT SESSION

    Creating an Event Session does nothing more than that in Extended Events; it simply catalogs the Event Session definition inside of the Extended Events Engine, if it passes the syntax check, and makes the Event Session available for use.  To actually begin Event collection, the Event Session must be ALTERed to start the Event Collection.  This is accomplished with the ALTER EVENT SESSION DDL command.

    -- ALTER the Event Session to Start it
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    STATE
    =START
    GO

    Once a Event Session is set to STATE=START, it becomes active inside of the Extended Events Engine and begins collecting Events that have been defined in the Session Definition.  An active Event Session can be modified to ADD EVENTs, DROP EVENTs, ADD TARGETs, and DROP TARGETs while the Event Session remains active inside the Extended Events Engine.  When an Event Session only has in memory targets defined on it, dropping all of the defined Events from the Event Session stops the Event Session from capturing further Events to allow for captured Event analysis while preserving the captured Event information for analysis.  If we run a relevant workload against the tempdb database based on the Event Session created above this can be demonstrated.

    USE [tempdb]
    GO
    IF OBJECT_ID('Test') IS NOT NULL
      
    DROP TABLE Test
    CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY, exampledata VARCHAR(4000))
    GO
    INSERT INTO Test(exampledata) VALUES (REPLICATE('abcd', 1000))
    GO 100

    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    DROP EVENT sqlserver.file_write_completed,
    DROP EVENT sqlserver.file_written
    GO

    SELECT CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
    WHERE s.name = 'TrackTempdbFileWrites'
    GO

    After dropping the events from the session, no further events are captured by the Event Session, which can be shown by running the example workload again and re-querying the targets.

    USE [tempdb]
    GO
    IF OBJECT_ID('Test') IS NOT NULL
      
    DROP TABLE Test
    CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY, exampledata VARCHAR(4000))
    GO
    INSERT INTO Test(exampledata) VALUES (REPLICATE('abcd', 1000))
    GO 100

    SELECT CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
    WHERE s.name = 'TrackTempdbFileWrites'
    GO

    While dropping events from a session that utilizes in memory targets allows for captured data evaluation, ALTERing the Session to change the event definition can be useful to react to the event information that has been captured, to provide more specific predicates, minimizing the number of events captured. To change the definition of a specific event already defined on an Event Session requires that the Event Session be ALTERed with a DROP EVENT definition for the specific Event and then that the Event Session be ALTERed with an ADD EVENT clause to add the Event back to the Event Session with the new Predicate Definition. For the purposes of the demo presented in this blog post, since we have already dropped the sqlserver.file_write_completed event, all we have to do is issue an ALTER EVENT SESSION command with an ADD EVENT specification that adds the new Event specification back to the Event Session.

    -- Add the sqlserver.file_write_completed back with new predicates
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    ADD EVENT sqlserver.file_write_completed(
      
    SET collect_path = 1
       ACTION
    (sqlserver.sql_text)
      
    WHERE database_id = 2 AND FILE_ID = 1)

    Rerunning the workload against the new event session will show that the Event Session now only captures the sqlserver.file_write_completed Events based on the new criteria.

    USE [tempdb]
    GO
    IF OBJECT_ID('Test') IS NOT NULL
      
    DROP TABLE Test
    CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY, exampledata VARCHAR(4000))
    GO
    INSERT INTO Test(exampledata) VALUES (REPLICATE('abcd', 1000))
    GO 100

    SELECT CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
    WHERE s.name = 'TrackTempdbFileWrites'
    GO

    If the Event Session definition proves to be to much for the ring_buffer target to maintain the data in a useable fashion, it may be preferable to instead capture the events to the asynchronous_file_target, which isn't constrained by the memory configuration of the ring_buffer target. At add the asynchronous_file_target, an ALTER EVENT SESSION statement can be used with the ADD TARGET command to add the new target to the Event Session, and a subsequent ALTER EVENT SESSION statement can be used with the DROP TARGET command to remove the ring_buffer target once its captured data has been consumed from the ring_buffer target.

    -- Add a file target
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    ADD TARGET package0.asynchronous_file_target
    ( SET filename = 'C:\SQLBlog\TrackTempdbFileWrites.xel',
        
    metadatafile = 'C:\SQLBlog\TrackTempdbFileWrites.mta')
    GO

    -- Drop a ring_buffer target  
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    DROP TARGET package0.ring_buffer
    GO

    The only part of an Event Session that can not be modified without restarting the Event Session is the Session Options. To change a Session Option, for example the EVENT_RETENTION_MODE from ALLOW_SINGLE_EVENT_LOSS to NO_EVENT_LOSS, requires that the Event Session first be stopped and then restarted. An attempt to change a session level option for an active Event Session results in the following Error:

    Msg 25707, Level 16, State 1, Line 2 Event session option "event_retention_mode" cannot be changed while the session is running. Stop the event session before changing this session option.

    To change an Event Session level option, requires that the Event Session first be stopped, the option be changed with ALTER EVENT SESSION, and then the Event Session be started again.

    -- Stop the Event Seession first
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    STATE
    =STOP
    GO

    -- Change Event Retention Mode
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS)
    GO

    -- Start the Event Seession after the change
    ALTER EVENT SESSION [TrackTempdbFileWrites]
    ON SERVER
    STATE
    =START
    GO

    DROP EVENT SESSION

    To DROP a Event Session from the Extended Events Engine to prevents its future use, the DROP EVENT SESSION DDL command can be used. The DROP command can be used against an Active Event Session as well as against a Sessions that are simply cataloged in the Engine and not active.

    DROP EVENT SESSION [TrackTempdbFileWrites] ON SERVER

    What’s next?

    Now that we have how to manage Event Session in Extended Events, in the next post we’ll look at how to find information about the Event Sessions that have been defined in a SQL Server Instance.

  • An XEvent a Day (2 of 31) – Querying the Extended Events Metadata

    In yesterdays post, An Overview of Extended Events, I provided some of the necessary background for Extended Events that you need to understand to begin working with Extended Events in SQL Server.  After receiving some feedback by email (thanks Aaron I appreciate it), I have changed the post naming convention associated with the post to reflect “2 of 31” instead of 2/31, which apparently caused some confusion in Paul Randal’s and Glenn Berry’s series which were mentioned in the round up post for this series.

    In today’s post we’ll look at the DMV’s that contain the Extended Events Metadata, and how to query those to find out information about the objects that can be used in Extended Events.

    What No UI Support?

    One of the first things you’ll notice is that there is no native UI support in Extended Events.  This was a problem very early on and makes working with Extended Events difficult for most people.  If you are the type of person that doesn’t want to write code, you can download the Extended Events SSMS Addin I wrote from Codeplex, and it will go a long way in simplifying working with Extended Events.  However, to really exploit the power of Extended Events, you are going to have to write some things manually.  I generally use the Addin to create my basic Event Session definition and script that to a SSMS editor window, where I then go back and manipulate the DDL as needed.  I’ll cover the Addin in detail in a later post, but one of the items included in it is a Metadata Viewer that allows you to browse the package metadata in a TreeView to see all of the objects available in the Engine.

    image 

    The Metadata DMV’s

    Since there is no UI support, all of the metadata for Extended Events is exposed through a set of DMV’s that I refer to as the Metadata DMV’s.  There are other sets of DMV’s associated with Extended Events that will be covered in later posts as well and I have similar reference names for them as logical groups.  There are four DMV’s that contain metadata information about Extended Events in SQL Server; sys.dm_xe_packages, sys.dm_xe_objects, sys.dm_xe_object_columns, and sys.dm_xe_map_values.  Not all of the objects that are returned by the DMV’s are useable in Extended Events.  To identify the objects that can not be used, the first three DMV’s include a bitwise integer column named capabilities that can be checked with a bitwise logical AND against the number one to determine if the object is for internal usage only.

    sys.dm_xe_packages

    The sys.dm_xe_packages DMV contains a single entry for each of the packages that has been registered in the Extended Events Engine.  The packages each have a unique guid associated with them that is used to identify the objects that belong to that package.  As mentioned yesterday, there are four packages in SQL Server 2008, but one of those, the SecAudit package is for internal usage only by Server Audits.  When we filter out the internal packages using the previously mentioned bitwise logical AND against the capabilities column, only the useable packages remain.

    -- Extended Event Packages
    SELECT
      
    name,
      
    guid,
      
    description
    FROM sys.dm_xe_packages p
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)

    In yesterday's post I talked about the changes between SQL Server 2008 and SQL Server Denali CTP1 with regards to the number of available packages, and the modules that load them. The sys.dm_xe_packages DMV includes the module_address column that maps to the base_address column in the sys.dm_os_loaded_modules DMV, which can be used to see the specific modules name that loaded the package.

    SELECT 
      
    p.name,
      
    p.description,
      
    lm.name
    FROM sys.dm_xe_packages p
    JOIN sys.dm_os_loaded_modules lm
      
    ON p.module_address = lm.base_address
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)

    The output for SQL Server 2008 is:

    image

    The output for SQL Server Denali CTP1 is:

    image 

    sys.dm_xe_objects

    The sys.dm_xe_objects DMV contains information about all of the objects (events, actions, predicates, targets, types and maps) available in the packages registered in the Extended Events Engine.  It is joined to the sys.dm_xe_packages DMV by the package_guid column to map specific objects back to the package that loaded them.  The object_type column determines the type of object and can be used to filter on to only find specific object types.  The following examples show how to use this DMV to find the specific objects by type.

    Events

    -- Event objects
    SELECT p.name AS package_name,
          
    o.name AS event_name,
          
    o.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o
        
    ON p.guid = o.package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'event'

    Actions

    -- Actions
    SELECT p.name AS package_name,
          
    o.name AS action_name,
          
    o.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o
        
    ON p.guid = o.package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'action'

    Targets

    -- Targets
    SELECT p.name AS package_name,
          
    o.name AS target_name,
          
    o.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o ON p.guid = o.package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'target'

    Predicates

    Predicates are a special type of object in Extended Events because there are two different types of predicate objects in the Metadata; source objects which provide the global state data elements for filtering on and comparators which provide the textual comparisons that can be performed between a data element and the specified value.

    Predicate Sources
    -- State Data Predicates
    SELECT p.name AS package_name,
          
    o.name AS source_name,
          
    o.description
    FROM sys.dm_xe_objects AS o
    JOIN sys.dm_xe_packages AS p
        
    ON o.package_guid = p.guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'pred_source'
    Predicate Comparators
    -- Comparison Predicates
    SELECT p.name AS package_name,
          
    o.name AS source_name,
          
    o.description
    FROM sys.dm_xe_objects AS o
    JOIN sys.dm_xe_packages AS p
        
    ON o.package_guid = p.guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'pred_compare'

    Maps

    -- Maps
    SELECT p.name AS package_name,
          
    o.name AS source_name,
          
    o.description
    FROM sys.dm_xe_objects AS o
    JOIN sys.dm_xe_packages AS p
        
    ON o.package_guid = p.guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'map'

    Types

    -- Types
    SELECT p.name AS package_name,
          
    o.name AS source_name,
          
    o.description
    FROM sys.dm_xe_objects AS o
    JOIN sys.dm_xe_packages AS p
        
    ON o.package_guid = p.guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'Type'

    sys.dm_xe_object_columns

    The sys.dm_xe_object_columns DMV provides the information about the columns, or data elements, that exist for a specific object.  There are three types of columns that are returned from this DMV; readonly, data, and customizable.  Readonly columns are additional system metadata about an event, that allows the integration with Event Tracing for Windows.  Data columns are the data elements that are returned by default when the event fires.  Customizable columns have different usages depending on the object type and are covered in detail below.

    Event Data Elements

    To know if an event is going to be useful or not often requires knowing the data elements returned when the event fires. This can then be used with the requirements to determine what actions to add to a specific Event during Session creation.

    -- Event Columns
    SELECT oc.name AS column_name,
          
    oc.column_type AS column_type,
          
    oc.column_value AS column_value,
          
    oc.description AS column_description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o
        
    ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc
        
    ON o.name = oc.OBJECT_NAME
       
    AND o.package_guid = oc.object_package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND (
    oc.capabilities IS NULL OR oc.capabilities & 1 = 0)
      AND
    o.object_type = 'event'
     
    AND o.name = 'wait_info'

    Configurable Event Elements

    Part of the flexibility behind Extended Events, and one of the design features that keeps it highly performant is the inclusion of Event Data Elements that can collect additional data in the base payload for an event that may be more expensive than acceptable for general uses, but may be useful at times, and acceptable to expend the additional impact on. When these types of information exist, the Event will have a customizable data element that has to be turned on in the Event Session definition to collect the additional information. An example of this is the collect_path element for the file_write_completed Event. By default this Event won't collect the file path, but if this information is needed or considered to be important, it can be turned on and will be collected when the event fires.

    -- Configurable Event Columns
    SELECT oc.name AS column_name,
          
    oc.column_type AS column_type,
          
    oc.column_value AS column_value,
          
    oc.description AS column_description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o
        
    ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc
        
    ON o.name = oc.OBJECT_NAME
       
    AND o.package_guid = oc.object_package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND (
    oc.capabilities IS NULL OR oc.capabilities & 1 = 0)
      AND
    o.object_type = 'event'
     
    AND o.name = 'file_write_completed'
     
    AND oc.column_type = 'customizable'

    Target Configurable Options

    Some of the targets available in Extended Events have configurable elements that are required to use the target.  An example of this would be the asynchronous_file_target, which requires that you provide a filename for the file.  Other options are optional like the max_file_size and max_rollover_files options for the asynchronous_file_target.

    -- Target Configurable Fields
    SELECT oc.name AS column_name,
          
    oc.column_id,
          
    oc.type_name,
          
    oc.capabilities_desc,
          
    oc.description
    FROM sys.dm_xe_packages AS p
    JOIN sys.dm_xe_objects AS o
        
    ON p.guid = o.package_guid
    JOIN sys.dm_xe_object_columns AS oc
        
    ON o.name = oc.OBJECT_NAME
       
    AND o.package_guid = oc.object_package_guid
    WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
      AND (
    o.capabilities IS NULL OR o.capabilities & 1 = 0)
      AND
    o.object_type = 'target'
     
    AND o.name = 'asynchronous_file_target'

    sys.dm_xe_map_values

    The sys.dm_xe_map_values DMV provides the key/value pairs for each of the Maps defined in the system.  Maps are linked by their object_package_guid to the specific package that created them.

    -- Map Values
    SELECT name, map_key, map_value
    FROM sys.dm_xe_map_values
    WHERE name = 'wait_types'

     

    In SQL Server Denali CTP1, it may seem like there are duplicate Maps for sqlserver, but remember that there are two sqlserver packages, and they are loaded from different modules, so each module has to load its own Maps into the Engine.  It would be theoretically possible for the modules to have different Map definitions for the same Map name in the Engine, if there was a change in one module that didn’t occur in the other module.  Hopefully we don’t actually find that to be true one day.

    What’s next?

    Now that we have information about the objects available in Extended Events, in the next post we’ll look at the DDL commands used to create and manage Extended Events Sessions.

  • An XEvent a Day (1 of 31) – An Overview of Extended Events

    First introduced in SQL Server 2008, Extended Events provided a new mechanism for capturing information about events inside the Database Engine that was both highly performant and highly configurable.  Designed from the ground up with performance as a primary focus, Extended Events may seem a bit odd at first look, especially when you compare it to SQL Trace.  However, as you begin to work with Extended Events, you will most likely change how you think about tracing problems, and will find the power in the design of Extended Events.  This series of blog posts should help you get a jumpstart to using Extended Events if you haven’t already.  This post will review Extended Events from a high level, providing the basic background upon which the rest of the series will build on.

    The Extended Events Engine

    The Extended Events engine is the central service that manages the objects and resources for Extended Events.  The engine manages a pool of workers known as dispatchers that are responsible for processing event information from the memory buffers to the targets based on the session options.  The engine also maintains the information for Event Sessions that have been created in the instance, and the metadata about the packages that have been registered by modules inside the engine.  The engine itself contains no object metadata.  The engine essentially provides the platform for which packages can be registered and event sessions can be created, and event information can be consumed.

    Packages

    Packages are registered by modules in the Extended Events Engine and contain the information about what Extended Events objects the module contains.  Packages are the top level containers of metadata for the other objects that exist inside of Extended Events.  Packages can contain a combination of events, actions, targets, maps, predicates and types.  In SQL Server 2008, there are only four packages; sqlserver, sqlos, package0, and a private internal use only package SecAudit, which is used by Server Audits.  All of these packages are loaded by the sqlservr.exe module.  In Denali CTP1, there are three additional packages, one more for sqlserver but loaded from different module, a new sqlclr package that is loaded from sqlservr.exe,  and a new ucs (unified communications stack) package that is loaded from sqlservr.exe.  In addition to these changes the package0 and sqlos modules are no longer loaded by sqlservr.exe and instead are loaded by sqldk.dll.

    Events

    Events provide information about the execution of the module that loaded the package containing the event.  An event corresponds to a specific point in code where something of interest occurs inside the Database Engine.  In SQL Server 2008, there are 253 events that can be used to capture information and as previously mentioned, while there are a lot of events, not all of the common events from SQL Trace were implemented in Extended Events.  In SQL Server Denali CTP1, the number of events has expanded to 446, and now includes a corresponding event for all of the events from SQL Trace.  Each event returns information specific to the point in code corresponding to the event, which I refer to as is basic payload of information.  The amount of information returned in the base payload is set by the events definition in the package metadata, but can be added to through the use of Actions.

    Actions

    Actions are bound to individual events in the definition of the Event Session in the engine, and perform a specified task when the event fires.  Actions generally do two different types of tasks, they either collect additional information that is added to the firing events base payload, generally global state information like session_id, sql_text, etc. and they can perform tasks inside the engine like inserting a debug_break, or performing a memory dump for analysis.  In SQL Server 2008 there are 35 actions that can be used.  In SQL Server Denali CTP 1 there are 43 actions that can be used and include new ones that map to Trace Columns like context_info, database_name, and event_sequence that were unavailable in SQL Server 2008.

    Targets

    Targets are the event destinations for Extended Events.  Most of the targets are memory resident, meaning that they exist in memory, and only while an event session is active in a started state on the instance.  These can be useful for short term troubleshooting, when a long term persisted version of the events is not necessary.  A file target also exists which functions similar to the Trace File in SQL Trace and collects the event information in files on the file system that can then be transferred for analysis by another person at a later point in time.  Two types of targets exist in Extended Events, synchronous targets which have the events buffered to them by the executing thread, and asynchronous targets that have the events dispatched to them by the dispatchers in Engine when the buffers fill up, or the maximum duration for dispatch defined by the session is exceeded for an event.  A more detailed coverage of the specific targets will be done a little bit later in this series.

    Maps

    Maps provide lookup information for the information that is available inside Extended Events.  Maps provide a key/value pairing for specific types of information that can be used in defining event sessions, and correlating the information captured by event sessions.  An example of a map would be the wait_types, which maps the engine key to logical name for all wait_types that can be fired inside of SQL Server.

    Predicates

    Predicates provide the filtering mechanism inside of Extended Events and are defined on individual events and control the circumstances under which the event actually fires.  Predicates in Extended Events offer short-circuiting, where the first false evaluation in the predicate string terminates the evaluation and prevents the event from firing, making evaluation of properly defined predicates highly performant.  Predicates can be defined on any of the data returned by the events base payload, as well as on global state information that is available inside of the engine.  Predicates can be defined two different ways, using common filtering clause criteria such as <,>, >=, <=, =, <>, LIKE, NOT LIKE, etc. but can also be defined using textual comparators that are included in package metadata.  Inside the Engine when a session is started, any predicate using common filtering clause gets converted to its corresponding textual comparator.

    Types

    Types define the data type for the information elements inside of Extended Events.  There are 29 different types in SQL Server 2008, but only 28 inside of SQL Server Denali CTP1; the database_context type was removed.  In addition to the package defined types, some event data elements will define the data type as a map name, which provides information about the type of information that will be returned from the data element.

    Event Sessions

    Event Sessions are a collection of events, their corresponding actions and predicates, and the targets that will be the destinations for the events being collected.  Defined Event Sessions exist in the Extended Events Engine until they are explicitly dropped allowing the session to be started and stopped as needed without having to recreate the session, even beyond service restarts.  Event Sessions have a number of configuration options that control how the session functions, including the ability to start automatically when the service starts up, the maximum duration of time between an event firing and being dispatched to the targets, the amount of memory available to the event session for buffering, how that memory is partitioned, and when/if/how event loss can occur when the buffer space fills up faster than the dispatchers can deliver the events.  and have a number.  An event session requires at least one event at creation, but does not require a target, allowing side acting actions like a memory dump to occur without having to actually dispatch the event for consumption.

     

     

     

     

    What’s next?

    With the basics out of the way, in the next post we’ll look at the Metadata DMV’s and how to get the information about the objects available in Extended Events.  For more information about Extended Events in the mean time take a look at the Using SQL Server 2008 Extended Events whitepaper I wrote on MSDN.

  • An XEvent A Day: 31 days of Extended Events

    Back in April, Paul Randal (Blog|Twitter) did a 30 day series titled A SQL Server Myth a Day, where he covered a different myth about SQL Server every day of the month.  At the same time Glenn Alan Berry (Blog|Twitter) did a 30 day series titled A DMV a Day, where he blogged about a different DMV every day of the month.  Being so inspired by these two guys, I have decided to attempt a month long series on Extended Events that I am going to call A XEvent a Day.  I originally wanted to do this series during the month of November, but with school requirements and preparations for PASS Summit, I just couldn’t make it work out.  Instead I am going to end 2010 with a bang and at the same time double my blog post count for they year by doing it in December.

    This post will be the master post and will have a link to each of the posts throughout the month as I post them.

    An XEvent a Day (1 of 31) – An Overview of Extended Events
    An XEvent a Day (2 of 31) – Querying the Extended Events Metadata
    An XEvent a Day (3 of 31) – Managing Event Sessions
    An XEvent a Day (4 of 31) – Querying the Session Definition and Active Session DMV’s
    An XEvent a Day (5 of 31) – Targets Week – ring_buffer
    An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target
    An XEvent a Day (7 of 31) – Targets Week – bucketizers
    An XEvent a Day (8 of 31) – Targets Week – synchronous_event_counter
    An XEvent a Day (9 of 31) – Targets Week – pair_matching
    An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target
    An XEvent a Day (11 of 31) – Targets Week – Using multiple targets to simplify analysis
    An XEvent a Day (12 of 31) – Using the Extended Events SSMS Addin
    An XEvent a Day (13 of 31) – The system_health Session
    An XEvent a Day (14 of 31) – A Closer Look at Predicates
    An XEvent a Day (15 of 31) – Tracking Ghost Cleanup
    An XEvent a Day (16 of 31) – How Many Checkpoints are Issued During a Full Backup?
    An XEvent a Day (17 of 31) – A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)
    An XEvent a Day (18 of 31) – A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 2)
    An XEvent a Day (19 of 31) – Using Customizable Fields
    An XEvent a Day (20 of 31) – Mapping Extended Events to SQL Trace
    An XEvent a Day (21 of 31) – The Future – Tracking Blocking in Denali
    An XEvent a Day (22 of 31) – The Future – fn_dblog() No More? Tracking Transaction Log Activity in Denali
    An XEvent a Day (23 of 31) – How it Works – Multiple Transaction Log Files
    An XEvent a Day (24 of 31) – What is the package0.callstack Action?
    An XEvent a Day (25 of 31) – The Twelve Days of Christmas
    An XEvent a Day (26 of 31) – Configuring Session Options
    An XEvent a Day (27 of 31) – The Future - Tracking Page Splits in SQL Server Denali CTP1
    An XEvent a Day (28 of 31) – Tracking Page Compression Operations
    An XEvent a Day (29 of 31) – The Future – Looking at Database Startup in Denali
    An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits
    An XEvent a Day (31 of 31) – Event Session DDL Events

  • Some Thoughts on Clustering SQL Server Virtual Machines

    On my blog post Virtualizing SQL on VMware Reference List, Oscar Zamora (Blog | Twitter) asked the following question in a comment:

    As a virtualized instance has the benefit of "failing over" to another physical box, would you consider clustering a virtualized instance?

    The answer to this question more than I want to write up in a comment, so I decided instead to blog my response.  Since at least ESX 3, VMware has provided a number of high availability features in their enterprise server virtualization product.  A detailed listing of the HA features available in VSphere 4 can be found in the vSphere Availability Guide.
    In this post I’ll talk about the most popular ones and the ones that VMware marketing uses to try to convince people that VMware HA solves all of their High Availability needs.

    High Availability and DRS Clusters

    ESX hosts can be clustered together providing high availability from the hardware failure of a host for the guests running inside of the cluster.  If a host fails, the VM’s that were running on that host fail as well, but the cluster detects this and powers the VM’s up on other hosts inside of the cluster automatically, bringing the VM’s back online and restoring application serviceability.  Other features such as Server vMotion and Storage vMotion allow movement of the VM’s and storage dynamically to allow for hardware maintenance and upgrades with little to no downtime to the applications running on the VM’s. In addition to this the VMware Tools installed inside of the guests can provide monitoring of the guest to detect Operating System failures and lock ups inside of the VM and automatically restart the VM.

    VMware High Availability: Easily Deliver High Availability for All of Your Virtual Machines
    VMware High Availability: Concepts, Implementation, and Best Practices

    Fault Tolerance

    VSphere 4 introduced a new high availability feature for VM guests called Fault Tolerance.  Fault Tolerance creates a synchronized Secondary virtual machine on another host in the high availability cluster that is lock stepped with the Primary VM.  In the event of a host failure, guests that have Fault Tolerance enabled immediately failover to their Secondary in a manner that is similar to vMotion preventing application downtime from occurring.  When this occurs a new Secondary is created on another host inside of the cluster and synchronized with the new primary maintaining the fault tolerance of the guest inside of the environment.

    VMware vSphere™ 4 Fault Tolerance: Architecture and Performance
    VMware® Fault Tolerance Recommendations and Considerations on VMware vSphere™ 4
    Protecting Mission-Critical Workloads with VMware Fault Tolerance

    What does this all mean to SQL Server?

    These features are really great features provided by virtualization, but that doesn’t make them the solution to all of your High Availability needs.  SQL Server is often considered and treated by server administrators as just another application server, especially when it comes to virtualization.  However, SQL Server is not just another application and SQL Server provides its own High Availability options, like clustering, that may be more appropriate based on your environmental requirements.  There are specific reasons that the above features may not be acceptable HA features for SQL Server. 

    Fault Tolerance is currently limited to single vCPU guest VM’s only, so unless your SQL Server VM’s are all single vCPU, that’s not going to help you out.  This leaves you with VMware HA and the potential for failure with automatic restart on another host.  However, what none of the VMware features provides is minimization of downtimes associated with planned Windows Updates of the guest VM’s or the application of SQL Server Service Packs.  If you have the ability to take periodically planned downtimes of one to two hours for patching the basic HA features of VMware will probably meet your needs.  This may be an acceptable configuration in your environment, and if it is, I won’t fault you for deciding that it meets your high availability needs.  I have VM’s in production that are protected first, by good database backups, and then by VMware HA. 

    However, I also have systems that have minimal downtime requirements, and because of this, relying on VMware HA as my primary HA solution doesn’t provide the level of availability required for those systems.  This leads to SQL Server clustering, which is supported in virtualization if it meets specific requirements as documented on the Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment and in Bob Wards blog post SQL Server Support Policy for Failover Clustering and Virtualization gets an update.  This brings us to the question that started this discussion: “Would I consider clustering a virtualized instance?”  Maybe, if the host environment had the resources to support it, but using VM’s for my cluster nodes wouldn’t be my first choice for clustering for a couple of reasons.

    First if a database has a minimal downtime requirement it probably also has a minimum performance requirement that is coupled with it.  While you can, and should, setup reservations for the resources allocated to a SQL Server VM, in NUMA enabled hosts like newer Nahalem systems, the maximum recommended size of a single VM is the resources available in a single NUMA node.  That means if you have a quad socket quad core server with 128GB RAM with 4 NUMA nodes, on per socket, each node would have 4 cores and 32GB RAM, making the largest VM 4 vCPU and 32GB RAM, unless you trade off the NUMA optimizations in ESX and memory locality.  In addition to this, you have to be careful where your SQL Server VM cluster nodes exist inside of the VMware host cluster to actually maintain the high availability of the SQL Server cluster.  If both of the SQL Server VM cluster nodes exist on the same physical host, and that host fails, both of the SQL Server VM cluster nodes are going to fail as well.  On top of these reasons, configuration of the VM guests for MSCS is not a trivial process as shown by the 36 page whitepaper Setup for Failover Clustering and Microsoft Cluster Service.

    In my current environment we have multiple clustered SQL Server instances, and none of them are virtualized and at the current time we are planning to build additional clustered SQL Server instances to migrate databases that have high availability and minimal downtime requirements to.  Despite having a dedicated VMware host cluster for our SQL VM environment comprised of very powerful hardware, there isn’t any added benefit to building the clusters inside of virtual machines.  Your environment may be different and clustering inside of virtual machines may make sense for your specific requirements, but in the end it is the database downtime requirements that should determine whether or not the database should be clustered, not that VMware provides built in High Availability, because that only covers one aspect of minimizing downtime.

  • Virtualizing SQL on VMware Reference List

    I’ve been managing SQL Server virtualized in production environments for nearly five years now, and in that time, I’ve had to do a significant amount of reading/learning about VMware as a hypervisor so that I could properly track down performance problems and in a lot of cases, prove to vendors that the problem wasn’t virtualization.  It wasn’t very long ago that nobody supported virtualization, including Microsoft, despite the fact that it generally wasn’t the cause of problems.  Lately I’ve seen more people virtualizing SQL Servers and asking questions about running SQL Server in a virtualized environment and in a lot of cases, when the platform selected is VMware, I can answer their questions by pointing them to one of the many whitepapers I’ve read over the last few years.  To make this easier, and to share what I consider to be important references for VMware I am going to list them in this blog post  by category.  I’ve made it a point to try and list the most recent papers but in some cases, the only paper I know of or could find searching is for a version or two back in the ESX version.

     

    CPU/Scheduling
    VMware vSphere™ 4: The CPU Scheduler in VMware® ESX™ 4
    VMware® vSphere™: TheCPU Scheduler in VMware ESX® 4.1


    Memory
    Understanding Memory Resource Management in VMware® ESX™ Server
    Understanding Memory Resource Management in VMware ESX 4.1
    Virtual Performance: Love Your Balloon Driver
    Large Page Performance


    DiskIO
    PVSCSI Storage Performance
    VMware vSphere 4 Performance with Extreme I/O Workloads
    Comparison of Storage Protocol Performance in VMware vSphere™ 4
    VMware® vStorage Virtual Machine File System Technical Overview and Best Practices
    Recommendations for Aligning VMFS Partitions

    SQL Server Specific
    Performance and Scalability of Microsoft® SQL Server® on VMware vSphere™ 4
    Microsoft SQL Server and VMware Virtual Infrastructure
    Availability Guide for Deploying SQL Server on VMware® vSphere
    Microsoft® SQL Server on VMware® Best Practices Guide

    Performance Best Practices General
    Performance Best Practices for VMware vSphere™ 4.1
    Performance Troubleshooting for VMware vSphere 4

  • PASS Summit 2010 – Session Presentations and Demos

    I know I am late on actually publishing these, but here are the presentation slide decks and demo’s from the two sessions I presented at PASS Summit 2010.  Attached to this post are two zip files that contain all the materials for each of the sessions.  You will probably notice that I haven’t published PowerPoint slides, and that all of the code files for the demo’s have a copyright and license agreement on them that didn’t exist in my PASS presentations.  This is because I have had issues in the past year with someone using my slides and demo’s without permission to present my content at other events, which is unacceptable.  If you are interested in reusing this material send me an email with your contact information, where and when you would like reuse the information and I will review your request on a case by case basis.

    SQL PASS 2010 Presentations.zip

  • T-SQL Tuesday #12 - Why are DBA skills necessary?

    This month’s TSQL Tuesday event is being hosted by Paul Randal (Blog|Twitter), and for November is a week early so that it doesn't clash with PASS.  For this month, Paul picked the topic  "why are DBA skills necessary?" which is quite an interesting question and one that I am sure will generate a wide variety of responses that I look forward to reading.  To keep from having an impact on my own response I intentionally avoided Twitter and my blog reader today so that I could respond to this topic without having any other responses affect the outcome. 

    My last two jobs have been with companies that didn’t have a skilled SQL Server DBA on staff prior to my arrival.  In both cases, I inherited an environment that had problems on many fronts.  Some systems were backed up using a third party tool like ComVault or Netbackup, others went to local disk using Maintenance Plans, some weren’t backed up at all.  The environments were both fairly large, and had a lot of SQL Server bloat since every application got its own SQL installation, often times on the same server as the application.  Local Administrator rights were granted to the servers to lists of users.  In some cases vendors that had support contracts for the software products had shared accounts that were Local Administrators and could VPN into the environment to fix problems, perform upgrades, or god only knows what else.  In both of these places, system outages, performance problems, and supportability issues lead to the creation of a new position for a true on staff SQL Server DBA.

    Why are DBA skills necessary?  A number of thoughts come to mind when i think about this question.  A few of those are to ensure protection of critical company data, to provide solutions to complex business problems that relate to the data inside of their environments, and to have authority in your environment when it relates to data.

    Protection of Critical Company Data

    There are plenty of shops out there that operate without an on staff DBA and many of them have competent server administrators that are backing up their company databases using third party tools and protect the data from loss.  In fact it has been my experience that some shops would have been better off if they didn’t have their onsite DBA and simply relied on their server administrators to protect their data.  At least then they might have had a weekly or nightly backup of the database in whatever enterprise wide backup product they relied on.  While it is certainly true that DBA’s should have the skills to backup the databases in their environment, my own experiences, sadly enough, have been that many DBA’s don’t understand the basics of the SQL Server recovery models, leading to backups that don’t support the business SLA, OLA, RTO, or RPO’s. 

    However, protection of critical data doesn’t just involve taking a backup, as Paul Randal says, “You don’t have a valid backup until you have tested restoring.”  Beyond validating your backups, there is much more to protecting critical company data.  Recently I had a vendor deliver two Desktop computers to our data center that were supposed to act as the servers for their application.  These were older single processor Pentium systems with 1GB RAM and a single SATA drive in them and both were supposed to have an instance of SQL Server on them.  These were for an additional test environment, but the testing is a critical part of a long term project that is ongoing at our hospital and to lose time because of a crash of either of these machines could prove detrimental to the project.  The lack of any redundancy here was a huge risk and after much debate, this was implemented on VM’s in our data center.  I wish I could say that this kind of thing was isolated or rare, but all over there are SQL Express and Desktop/Personal editions running on a workstation under someone’s desk that holds critical data and is just a misplaced foot or power spike away from total loss, and don’t get me started on all the business critical Excel workbooks, and Access databases that exist inside of most business environments.

    It is the job of the DBA to find this business critical data and protect it from loss.  If it is held in Excel or Access, it can be architected to use a backend SQL Database to secure and protect the data behind standardized backup processes.  Using tools like the Microsoft Assessment and Planning Toolkit with the server administrators a DBA can scan the entire environment to identify all of the SQL Server instances that exist and then begin to ensure that the databases are hosted on appropriate hardware with appropriate backups and security implemented.

    Providing Solutions to Complex Business Problems

    Any business that has data, eventually finds a need to use that data in ways that the original application and or database design just doesn’t support.  One of the most common of these is reporting, which can cause of performance issues in OLTP systems due to poorly written or complex reporting queries leading to blocking of data changing operations.  When this happens, it is the DBA that is called upon to troubleshoot the problems.  There are a number of different possible solutions to this kind of problem, for example a common one I’ve seen is to take a full backup of the production database and restoring to another server nightly and only running reports against this restored copy.  While this kind of solution might work for smaller databases, as database size increases the time take a full backup and then subsequently restore it to another server can take longer than feasible.

    One of the key skills of a DBA is gaining an understanding of the business that they support so that they can provide solutions to support those business needs.  For example, one of the systems I inherited at my current job uses a SQL database that is just under 250GB in size.  Some quick analysis of this database would show that 248GB of the data stored in this database exists in a single table in a image data typed column that is never used for reporting.  For a system like this, using replication to synchronize data to a reporting database makes much more sense than using backup/restore or other options like database mirroring to create a reporting database.

    Have Authority When it Relates to Data

    This is something that only became apparent to me when I started doing consulting work on the side, but actually having good DBA skills and being able to make the decisions that solve problems builds a level of authority with the people you work for and work with (well most of the time, there are those crappy environments where this just isn’t the case).  Having a strong set of DBA skills allows you to intelligently discuss problems with end users, as well as explain the benefits and ROI associated with changes to how things are designed, or configured.  In my current job I had to gain the trust of a majority of the IT staff as well as the business end users after accepting the job.  The problems in the past had not been addressed timely or correctly, and many of the groups had created their own workarounds and methods of managing SQL Server and were very cautious about letting someone new come in and change anything.  It only took resolving two or three major problems to start bringing the sentiment around that changes were necessary to properly stabilize the environment. 

This Blog

Syndication

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