I’ve been asked questions about these two features a number of times in the last year where the specific concepts for them have been severely confused.  Since I have used the same information each time I’ve answered these questions I figured it would be worth blogging about here as a reference as well.

Event Notifications were introduced in SQL Server 2005 and offer the ability to collect a very specific subset of SQL Trace (http://msdn.microsoft.com/en-us/library/ms190655.aspx) and DDL Events (http://msdn.microsoft.com/en-us/library/bb522542.aspx) through a Service Broker service and queue.  The use of Service Broker allows you to write stored procedures that can process the events asynchronously when they are placed on the queue through a functionality of Service Broker known as queue activation.  You can also make use of remote servicing that allows the event data to be sent from one SQL Server to another for processing which is very useful in larger environments where you want to centralize all of the events for reporting.

Extended Events were introduced in SQL Server 2008 and use entirely different architecture inside of the database engine for providing events.  Extended Events contain a number of events that are not available by any other means in SQL Server, but there are a number of events in SQL Trace that are not available in Extended Events in SQL Server 2008; for example the blocked process report is not available by Extended Events.  In SQL Server 2012 RTM, all of the events from SQL Trace have been implemented in Extended Events, and Extended Events will replace SQL Trace entirely in a future release of SQL Server since SQL Trace is a newly deprecated feature in 2012 (deprecated = slated for removal in 2-3 product life cycles, it does not mean removed entirely). 

The key difference between the two features is that Event Notifications allow automated processing of the events asynchronously through Service Broker.  There is no similar mechanism for Extended Events currently; even in SQL Server 2012 where Event Notifications still piggy back SQL Trace for event generation.  If you are interested in building an alerting infrastructure I would still use Event Notifications depending on the information that you are interested in gathering.  To build an alerting solution on top of Extended Events would require consistently polling the event session targets and tracking which events have already been seen by the solution in SQL Server 2008, and in SQL Server 2012 it would require the same kind of polling solution unless you wrote a .NET application to leverage the streaming API for Extended Events to harvest the events in real time.  Depending on the event frequency and target being used either of these solutions could lead to missed alerts or an expensive polling operation that reads the same information repeatedly.

Either feature can safely be used in a highly transactional system if the appropriate considerations are made in the implementation, and either feature can lead to performance impacts as well.  It all depends on the events being collected and how frequently they get fired.  The events that are available in Event Notifications are a generally safe subset of the events from SQL Trace and generally won't cause performance problems, but your mileage may very based on the frequency of the events firing.  As always you should test the event selection and monitor for impact after implementing a change to the system.

I hope this helps anyone interested in these two features.

The sqlserver.sql_text action in Extended Events is one of the worst named objects in Extended Events.  The name suggests that you are going to get back the sql_text that triggered the event being collected to fire, but in reality this is not the case.  I pressed internally with Microsoft to have this action renamed to sqlserver.inputbuffer in SQL Server 2012, which actually reflects what is being returned by the action.  However, there were concerns about this breaking existing scripts (a point I didn’t necessarily buy into since other actions, targets, and even events were renamed in SQL Server 2012, but the timing of recommending this change may have had a lot to do with this as well).  I made this recommendation after seeing a post on the MSDN Forums where the poster didn’t agree with the way this action behaved in their environment.

To demonstrate this, let’s take a look at an Event Session in SQL Server 2012 that captures the sqlserver.sql_batch_completed and sqlserver.sql_statement_completed events along with the sqlserver.sql_text action for both of the events:

-- If the Event Session exists Drop it
IF EXISTS (SELECT 1
            FROM sys.server_event_sessions
            WHERE name = 'SQLskills_sql_text_Action')
    DROP EVENT SESSION [SQLskills_sql_text_Action] ON SERVER;

-- Create Event Session to find the database with most SP Recompile Events
CREATE EVENT SESSION [SQLskills_sql_text_Action]
ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
    ACTION (sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION (sqlserver.sql_text))
ADD TARGET package0.ring_buffer
WITH (TRACK_CAUSALITY = ON);
GO

ALTER EVENT SESSION [SQLskills_sql_text_Action]
ON SERVER
STATE=START;
GO

With this event session created, we can then run a couple of different test scenarios in the environment to show how this action is not the sql_text, but is instead the input_buffer for the event that is being fired:

-- Perform some Tests
SELECT PASSWORD = 'bar12345!!';
SELECT 'password' as Secret;
CREATE LOGIN foo WITH PASSWORD = 'bar12345!!';
SELECT 'reallylongstringwithpasswordincludedintext' AS Funny;
EXEC('SELECT ''reallylongstringwithpasswordincludedintext'' AS Funny');
GO

-- Perform some additional Tests
SELECT PASSWORD = 'bar12345!!';
GO
SELECT 'password' as Secret;
GO
CREATE LOGIN foo WITH PASSWORD = 'bar12345!!';
GO
SELECT 'reallylongstringwithpasswordincludedintext' AS Funny;
GO
EXEC('SELECT ''reallylongstringwithpasswordincludedintext'' AS Funny');

Once these statements have been executed, we can then drop the events from the event session to allow the ring_buffer target to be queried to show the differences between the statement and batch_text columns from the events, which consequently don’t exist in SQL Server 2008 or SQL Server 2008 R2, and the sql_text action output.

ALTER EVENT SESSION [SQLskills_sql_text_Action]
ON SERVER
DROP EVENT sqlserver.sql_batch_completed,
DROP EVENT sqlserver.sql_statement_completed;
GO

With the events removed, we can now query the target data from the ring_buffer to look at what was actually captured.

-- Query the XML to get the Target Data
SELECT
    event.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    DATEADD(hh,
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
            event.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
    ISNULL(event.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)'),
            event.value('(event/data[@name="batch_text"]/value)[1]', 'nvarchar(max)')) AS [stmt/btch_txt],
    event.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as [sql_text]
FROM
(   SELECT n.query('.') as event
    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 s.address = t.event_session_address
        WHERE s.name = 'SQLskills_sql_text_Action'
          AND t.target_name = 'ring_buffer'
    ) AS sub
    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n)
) AS tab

Looking at the output, we can see that there is a different behavior and output from the sql_text action between the two different sets of tests.

The first set of tests, which were run in a single batch, have all of the sql_text output masked because of the inclusion of the work password in the input buffer text.  This is not new behavior in SQL Server, it actually existed in SQL Trace prior to SQL Server 2005 SP2, where the code path used to capture TextData was changed to output the post query parsing text, which only strips the text out for DDL operations that would actually contain password information, like the CREATE LOGIN event in the second set of tests highlighted in the green box above.  The code path used by the sql_text action is the same as the raw input buffer for the session which masks off the entire text if the work password appears anywhere in the text, which is why the last SELECT statement and dynamic string execution of a SELECT don’t return sql_text information in the first set of tests highlighted in red above.  Contrast this with the batch_text and statement column outputs from the events, and you can easily see that sql_text isn’t quite what you’d expect.

So how do you work around/with this information?  If you are on SQL Server 2008, my typical recommendation is to capture the tsql_stack action instead, which gives you the sql_handle and offset information to be able to parse the statements from cache (a topic for another blog post that I’ll write).  If you need the actual statement information or RPC information from a specific execution, the best thing to do in SQL Server 2012 is to use TRACK_CAUSALITY and and the appropriate event, rpc_starting/sql_statement_starting/completed to be able to correlate the activity_id back to the statement that actually triggered the event.

With the growing popularity of Extended Events in SQL Server 2012 with the UI enhancements that I’ve blogged about on a number of posts (SQL Server 2012 Extended Events Update - 1- Introducing the SSMS User Interface, SQL Server 2012 Extended Events Update - 2 - The SSMS UI Part 2, SQL Server 2012 Extended Events Update - 3 - Viewing Target Data).  To make use of Extended Events and the performance benefits of collecting data from SQL Server using Extended Events instead of SQL Trace, I’ve written a comprehensive converter to migrate existing trace definitions from SQL Trace to Extended Events for SQL Server 2012 RTM.  In the past the Extended Events team at Microsoft has blogged about how to perform this conversion through the use of SQLCLR in their blog post Migrating SQL Trace to Extended Events, however, due to changes in the catalog views for mapping Trace Events to Extended Events, as well as a lack of a comprehensive mapping of SQL Trace columns to their respective Extended Events event columns and actions, this method falls short of being a solution to converting from SQL Trace to Extended Events.

I personally spent a number of hours manually mapping the SQL Trace Events column mappings to the related Extended Events event column mappings to build a cross reference table of events between the two environments and the result is a comprehensive set of scripts that can migrate SQL Trace definitions into Extended Events with full comments of what columns have been mapped as Extended Events columns or Actions, and the columns and events that no longer match up based on the Extended Events event definitions.

To this effort, below is a TSQL Script that will convert SQL Trace definitions to Extended Events within your own environment.  The output of this script for the default trace in SQL Server 2012 is as follows:

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'XE_Default_Trace')
 DROP EVENT SESSION [XE_Default_Trace] ON SERVER;
GO
CREATE EVENT SESSION [XE_Default_Trace]
ON SERVER
/* Audit Login Failed is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Database Scope GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Schema Object GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Addlogin Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Login GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Login Change Property Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Login to Server Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add DB User Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Member to DB Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Backup/Restore Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit DBCC Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Change Audit Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Change Database Owner is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Schema Object Take Ownership Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Server Alter Trace Event is not implemented in Extended Events it may be a Server Audit Event */
ADD EVENT sqlserver.database_file_size_change(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
 )
),
/* Log File Auto Grow is implemented as the sqlserver.database_file_size_change event in Extended Events */
/* Data File Auto Shrink is implemented as the sqlserver.database_file_size_change event in Extended Events */
/* Log File Auto Shrink is implemented as the sqlserver.database_file_size_change event in Extended Events */
ADD EVENT sqlserver.database_mirroring_state_change(
 ACTION
 (
     package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
 )
),
ADD EVENT sqlserver.errorlog_written(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , sqlserver.database_id -- DatabaseID from SQLTrace
   , sqlserver.database_name -- DatabaseName from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   -- Severity not implemented in XE for this event
   -- State not implemented in XE for this event
   -- Error not implemented in XE for this event
 )
),
ADD EVENT sqlserver.full_text_crawl_started(
 ACTION
 (
     package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   -- ServerName not implemented in XE for this event
 )
),
ADD EVENT sqlserver.full_text_crawl_stopped(
 ACTION
 (
     package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   -- ServerName not implemented in XE for this event
 )
),
ADD EVENT sqlserver.hash_warning(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , sqlserver.database_id -- DatabaseID from SQLTrace
   , sqlserver.database_name -- DatabaseName from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.missing_column_statistics(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , sqlserver.database_id -- DatabaseID from SQLTrace
   , sqlserver.database_name -- DatabaseName from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.missing_join_predicate(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , sqlserver.database_id -- DatabaseID from SQLTrace
   , sqlserver.database_name -- DatabaseName from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.object_altered(
 ACTION
 (
     package0.attach_activity_id -- IntegerData from SQLTrace
   , sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
   -- BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.object_created(
 ACTION
 (
     package0.attach_activity_id -- IntegerData from SQLTrace
   , sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
   -- BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.object_deleted(
 ACTION
 (
     package0.attach_activity_id -- IntegerData from SQLTrace
   , sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
   -- BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.plan_guide_unsuccessful(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , sqlserver.database_id -- DatabaseID from SQLTrace
   , sqlserver.database_name -- DatabaseName from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
   -- TextData not implemented in XE for this event
 )
),
ADD EVENT sqlserver.server_memory_change(
 ACTION
 (
     package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.server_start_stop(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
 )
),
ADD EVENT sqlserver.sort_warning(
 ACTION
 (
     sqlserver.client_app_name -- ApplicationName from SQLTrace
   , sqlserver.client_hostname -- HostName from SQLTrace
   , sqlserver.client_pid -- ClientProcessID from SQLTrace
   , sqlserver.database_id -- DatabaseID from SQLTrace
   , sqlserver.database_name -- DatabaseName from SQLTrace
   , package0.event_sequence -- EventSequence from SQLTrace
   , sqlserver.is_system -- IsSystem from SQLTrace
   , sqlserver.nt_username -- NTUserName from SQLTrace
   , sqlserver.nt_username -- NTDomainName from SQLTrace
   , sqlserver.request_id -- RequestID from SQLTrace
   , sqlserver.server_instance_name -- ServerName from SQLTrace
   , sqlserver.server_principal_name -- LoginName from SQLTrace
   , sqlserver.server_principal_sid -- LoginSid from SQLTrace
   , sqlserver.session_id -- SPID from SQLTrace
   , sqlserver.session_resource_group_id -- GroupID from SQLTrace
   , sqlserver.session_server_principal_name -- SessionLoginName from SQLTrace
   , sqlserver.transaction_id -- TransactionID from SQLTrace
   , sqlserver.transaction_sequence -- XactSequence from SQLTrace
 )
)
ADD TARGET package0.event_file
(
 SET filename = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XE_Default_Trace.xel',
  max_file_size = 20,
  max_rollover_files = 5
)

sp_SQLskills_ConvertTraceToExtendedEvents.sql (55.41 kb)

Traditionally questions about how much memory SQL Server needs were aimed at how to appropriately set the 'max server memory' sp_configure option in SQL Server, and in my book the recommendation that I make is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.  This has typically worked out well for servers that are dedicated to SQL Server.  You can also get much more technical with determining where to set 'max server memory' by working out the specific memory requirements for the OS, other applications, the SQL Server thread stack, and other multipage allocators.  Typically this would be  ((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4GB) – (memory for other applications) - (memory for multipage allocations; SQLCLR, linked servers, etc)), where the memory for thread stack = ((max worker threads) *(stack size)) and the stack size is 512KB for x86 systems, 2MB for x64 systems and 4MB for IA64 systems.  The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info.  However, the assumption with either of these methods is that you want SQL Server to use everything that is available on the machine, unless you've made reservations in the calculations for other applications.

As more shops move towards virtualizing SQL Servers in their environment this question is more and more geared towards determining what is the minimum amount of memory that a SQL Server will need to run as a VM.  Unfortunately there is no way to calculate out what the ideal amount of RAM for a given instance of SQL Server might actually be since SQL Server is designed to cache data in the buffer pool, and it will typically use as much memory as you can give it.  One of the things to keep in mind when you are looking at reducing the RAM allocated to a SQL Server instance is that you will eventually get to a point where the lower memory gets traded off for higher disk I/O access in the environments.

If you need to figure out the ideal configuration for SQL Server memory in an environment that has been over provisioned the best way to try to go about doing this is start off with a baseline of the environment and the current performance metrics.  Counters to begin monitoring would include:

• SQL Server:Buffer Manager\Page Life Expectancy
• SQL Server:Buffer Manager\Page reads/sec
• Physical Disk\Disk Reads/sec

Typically if the environment has excess memory for the buffer pool, the Page Life Expectancy value will continue to increase by a value of one every second and it won't typically drop off under the workload because all of the data pages end up being cached.  At the same time, the number of SQL Server:Buffer Manager\Page reads/sec will be low after the cache ramp up occurs which will also correspond to a low value for Physical Disk\Disk Reads/sec. 

Once you have your baseline for the environment, make a change to the sp_configure 'max server memory' option to reduce the size of the buffer pool by 1GB and then monitor the impact to the performance counters after things stabilize from the initial cache flushing that may typically occur when RECONFIGURE is run in the environment.  If the level of Page Life Expectancy remains acceptable for your environment (keeping in mind that a fixed target of >= 300 is ridiculous for servers with large amounts of RAM installed), and the number of SQL Server:Buffer Manager\Page reads/sec is within what the disk I/O subsystem can support without performance degradation, repeat the process of reducing the sp_configure value for 'max server memory' by 1GB and continuing to monitor the impact to the environment. 

This past week at the SQL Server Connections Conference in Las Vegas, I was asked about the permissions required for managing Extended Event Sessions in SQL Server.  In SQL Server 2008 and 2008R2, using Extended Events required the CONTROL SERVER permission for the instance of SQL Server (http://msdn.microsoft.com/en-us/library/bb677289(v=sql.105).aspx).  In SQL Server 2012, a much more granular permission is required, ALTER ANY EVENT SESSION (http://msdn.microsoft.com/en-us/library/bb677289.aspx) which reduces the level of access that you have to provide to end users that need to use an Extended Event Session.  What is even better is that you can attach this permission set to a Custom Server Role (http://msdn.microsoft.com/en-us/library/ee677610(v=sql.110).aspx), to apply it to end users in a much easier to implement method than having to manually add the ALTER ANY EVENT SESSION to the login when it is created.

While teaching last week at our Tampa Immersion Event, I mentioned the support for SMB as a storage in SQL Server 2012, which is documented in the following Books Online topics.

Install SQL Server with SMB fileshare as a storage option
Hardware and Software Requirements for Installing SQL Server 2012 : Storage Types for Data Files

Someone asked the question about whether SMB could be used for the shared storage for a failover cluster in SQL Server 2012, and I had to go do a little research, but I found that it was a supported configuration.

AlwaysOn Failover Cluster Instances (SQL Server)
Before Installing Failover Clustering

Last year, Kevin Farlee from the Storage Engine Team blogged about the changes in the SMB stack that make hosting SQL Server databases over SMB 2.2 a feasible prospect in his blog post SQL Databases on File Shares - It's time to reconsider the scenario.  Additionally the SQLCAT team, actually Kevin Cox specifically, blogged about the use of SMB for hosting database files in the Top 10 Hidden Gems in SQL Server 2008R2 post.  Now that this is a viable alternative to expensive SAN’s it is not surprising to see that it is also a viable storage option for SQL Server Failover Clustering in SQL Server 2012. 

(Note: SAN infrastructures provide a lot of additional benefits that a single server hosting a file share doesn’t, so before you run out and place a mission critical SQL Server database on a SMB file share, make sure that you understand the implications of doing so, and the trade offs that you are making in the process.)

Building the Failover Cluster in Windows

Building the Failover Cluster in Windows is essentially the same as building a standard Failover Cluster for SQL Server, with the exception that you have to skip the Disk checks in the Cluster Validation Tool, and then override the failed checks to actually form the cluster.  To do this, select the option to Run only tests I select on the Validate a Configuration Wizard.

Then collapse all of the parent nodes in the treeview and uncheck the Storage checks and run the cluster validation.

When the validation tests complete, review the report and make sure that you have a supported configuration for failover clustering.

Then click the No option on the Validation Warning page and create the cluster by providing a Cluster Name and IP Address.

Configuring the File Share for Failover Clustering Support

The first step in setting up a Failover Cluster instance for SQL Server that uses SMB for its shared storage is to configure the File Share server to support the clustered instance being able to connect to it with the appropriate permissions to manage the SQL Server databases.  Since there is no shared storage in the environment, there is an additional requirement, if you are using a 2 node Failover Cluster, that the File Share will need to be the witness in the quorum configuration using Node and File Share Majority.  The requirements for this are documented in the Exchange Books Online (I tried to find a SQL Server Books Online or Windows Server Failover Clustering Books Online entry that provided the correct details and couldn’t) http://technet.microsoft.com/en-us/library/bb676490(EXCHG.80).aspx.  Essentially, you need to have a separate File Share for the quorum, and a separate File Share for the SQL Server instance to use for data storage. 

Note: I didn’t initially set my environment up this way, and due to the time it took to create the screenshots for this blog post before filling in the text, I chose to not go back and correct this error, but as a best practice, you should have separate shares for the quorum and SQL Server instance installation if you choose to use SMB for a failover cluster.  This is documented in the Windows Server Failover Clustering Books Online. http://technet.microsoft.com/en-us/library/cc770620(WS.10).aspx

The first thing you want to do is Provision a new Share using the Provision Share context menu item from the Share and Storage Management node on the File Server.

Then you need to provide a path for the storage of the share.  If you are actually setting this up, perform all these steps twice to create two separate shares with the necessary permissions.

Then you want to manually edit the NTFS Permissions for the File Share being created.

For the Quorum Share, you need to provide the Failover Cluster Virtual Computer Object (VCO) account Full Control of the Share in NTFS.  The VCO is the ClusterName followed by a $ in Active Directory, and you will need to click the Objects button to add in the Computers object for the search to find the account.  For the installation share, the SQL Server Service Account will require Full Control permissions in NTFS.

The next step is to provide a name for the File Share(s) that you are creating which will be the path to the share for configuring your quorum and then your Failover Cluster installation.

I am skipping over the SMB Settings window and going to the SMB Permissions where you will need to customize the share permissions for the specific Users that need to access the share to minimize security issues in the environment.

Just like with NTFS, for the Quorum Share, you need to provide the Failover Cluster Virtual Computer Object (VCO) account Full Control of the Share.  For the installation share, the SQL Server Service Account will require Full Control permissions over the Share.

Then create the share and you are ready to configure the quorum settings for the failover cluster.

Configuring Quorum Settings

To configure the Node and File Share Majority quorum for the cluster, required only if you have an even number of voters in the configuration which is typical for 2 node clusters, click the Configure Cluster Quorum Settings menu item from the failover clusters context menu.

Pick the Node and File Share Majority radio button.

Then Browse for the shared folder and type in the name of the File Server that is hosting the SMB share for the quorum.

If all the permissions are configured correctly, you will have a successful configuration of the quorum in the environment.

Installing the Failover Cluster Nodes

For the interests of brevity, I am going to skip showing all of the standard Failover Cluster installation setup screens and only show the ones that matter for the configuration using SMB as the shared storage.  For the instance features in this blog post, the Database Engine, Client Connectivity, Client Connectivity Backwards Compatibility, and Management Tools have been selected for a minimum install in the environment.   Everything is exactly the same as it would be in a standard SQL Server Failover Clustered instance installation until you get to the Cluster Disk Selection page of the installation.  Here there will be nothing shown in the environment, and what is interesting in comparison to SQL Server 2008R2 and previous is that you still have the option to click Next.

The next page of interest is the the Database Engine Configuration page, where you will provide the UNC path to the SMB share for the Data root directory, and if you have multiple shares that target different physical disk arrays in the File Server for the instance you could also specify those UNC paths to achieve physical isolation of the I/O for logs, data files, tempdb and backups following best practices.

Notice the warnings about the file server being specified as the data directory.  If you double click on one of the warnings it will produce a dialog box similar to the following box.

If you don’t get the warnings, don’t worry, you will still get the above warning dialog box as soon as you click Next on the page.

After this last warning Setup will proceed as normal only it will install into the File Share everything that would have been on the shared disk in a standard configuration with a SAN.  Once the first node is setup, you can then proceed to run setup on the additional nodes and they will work just like a standard failover cluster configuration would against a SAN.

Summary

Once all of the setup completes, you can validate the location of the database files using the sys.master_files DMV in SQL Server.

The SMB storage option lets you get past the 25 instance limitation for SQL Server when using shared disks with drive letters assigned, since each instance requires a separate drive letter.  Using SMB up to 50 instances can be installed in a failover cluster, which provides a significant increase in the number of instances you can get over using a SAN.  The improvements in the SMB stack make this a much more viable solution to expensive shared storage implementations using SAN hardware, but as I pointed out earlier, there are still considerations that should be made about the level of redundancy and other benefits that are provided by SAN implementations before you determine that a SMB solution for shared storage in a failover cluster is appropriate for your specific environment.

Last week during the MVP Summit, Joe (Blog | Twitter) and I spent the days working at Microsoft teaching and recording SQL Server 2012 content for the Developer Platform Evangelism team.  Earlier today, the videos we recorded of the SQL Server 2012 content were posted on the SQL Server 2012 Early Adoption Cookbook site along with all of the other content needed to present topics related to SQL Server 2012.

The training content can be downloaded from the following link:

SQL Server 2012 Developer Training Kit Content

A comprehensive list of all the content contained in the kit, as well as instructions for how to build all of the VM’s required for performing the training, can be found in the following link:

SQL Server 2012 Developer Training Kit BOM

It was a ton of fun developing and teaching this content and I am sure that you will find it useful for learning about the new features of SQL Server 2012.

In SQL Server 2012 there are number of new ways to view target data generated by Extended Events Sessions, including a live streaming view as the events actually generate from the server, similar to the way SQL Server Profiler functions.  For this blog post, I am going to use the Query Detail Tracking template that ships by default with SQL Server 2012, and instead of using the default ring_buffer target, make use of the event_file target to show how to use the UI functionality to read and process through files that are generated by Extended Events.

The Event Session has been setup with a 10MB maximum file size and 5 rollover files.  The Event Session is also configured to start automatically when the Wizard closes, and the option to Watch live data on screen as it is captured is also selected.  When you close the dialog, the Live Data viewer will open and connect to the SQL Server instance to begin reading the event stream from the server.

Live Data viewer options

There are number of commands that exist for the Live Data viewer that are accessible through the Extended Events menu or through the toolbar that displays when the Live Data viewer is the active window inside of SQL Server Management Studio.  The buttons on the toolbar are in the exact same order as the menu items in the Extended Events menu.

If you don't have enough screen real estate for the toolbar to display completely, some of the buttons may not display but they will still be available through the drop down at the end of the toolbar as shown below.

The default view for any new Event Session in the Live Data viewer only shows two columns in the gridview, the event name and the timestamp for when the event was generated in the server.  The reason for this limited view initially is that there are to many columns available in Extended Events, and it is impossible to provide a globally useful initial display in the environment.  Instead for each event, all of the columns are provided in the Details pane below the main gridview.

Choosing Columns

Columns can be added to the table view a number of ways.  The Choose Columns menu item in the Extended Events menu or on the toolbar can be used, or you can also click on the column names on the gridview and select the Choose Columns menu item from the context menu.

Additionally you can add a single column from the Details pane by right clicking on the column and selecting the Show Column in Table menu item from the context menu.

The Choose Columns menu items will open up a column chooser dialog that allows you to add one or multiple columns to the gridview.  You can also change the column order in the gridview by selecting a column and clicking the up and down arrows to position the column appropriately in the list.

One functionality that you only get inside of the column chooser, is the ability to create a Merged Column for display.  This allows you to take columns with different names but similar meaning, for example the statement column produced by the sql_statement_completed event and the batch_text column produced by the sql_batch_completed event, and display them in a single column to maximize the available real estate on the screen and simplify analyzing the data.

After creating the merged column, and adding a number of additional columns to the gridview, we now have a much more recognizable view of the data being generated by our Event Session.

Saving the display for future use

If we close out the Live Data Viewer, Management Studio will remember the layout that we last used with this Event Session the next time we open it against this server.  However, if this Event Session is something that we are going to regularly use across multiple servers in our environment, or if we have multiple session definitions that leverage similar sets of events with different parameters configured the gridview layout won't default back to our settings in every scenario.  For this reason, you can save the display settings so that you don't have to customize the UI repeatedly.  This can be accomplished from the Extended Events menu or toolbars Display Settings item.

Filtering Live Data

While the Event Session is running, the UI can be used to filter the event data in the grid view on the client side, allowing you to limit the amount of information that is currently being displayed without actually having to change what is being collected by the Event Session in the targets.

These are just some of the more basic features of using the Live Data view inside of SQL Server 2012 for Extended Events.  In the next post we'll take a look at some of the additional features that exist for working with data stored in files or when the Live Data viewer is in a disconnected state.

SQL Server 2008 R2 Service Pack 1 provides a new set of Events in Extended Events to collect performance counter data from the Windows OS that would be really useful to monitoring SQL Server.  The first place I can find that they were mentioned is on a blog post by Mark Weber, a PFE for SQL and SAP at Microsoft.  However, a few weeks ago a question was asked about these counters one of the forums and the question was around how to use them.  I looked at the Events and found out that they aren’t really useable in their current implementation, something that is disappointing since being able to collect the data provided by these Events would really benefit most DBA’s out there. 

If you look at the Events and columns, these events collect information about the Logical Disk, Processor, Process for the SQL instance, and System at 15 second intervals and makes the data available through Extended Events.

SELECT name, description
FROM  sys.dm_xe_objects
WHERE name like 'perfobject_%'

If we look at the columns, we’ll see that the columns actually represent the individual counters under the categories exposed by the Event names. For example, the perfobject_logicaldisk event returns the following columns:

SELECT
    object_name, 
    name AS column_name, 
    description
FROM  sys.dm_xe_object_columns
WHERE object_name = 'perfobject_logicaldisk'
  AND column_type = 'data'
ORDER BY object_name, name

This all seems good, until we actually use the Events in an Event Session and take a look at the data being returned.

CREATE EVENT SESSION [XE_PerfCounters] 
ON SERVER 
ADD EVENT sqlserver.perfobject_logicaldisk 
ADD TARGET package0.ring_buffer;
GO

Unfortunately, the counters are returning Raw values for the Event and the necessary Base counters that are required to give these values any useful meaning have been left out of the Events data.  Looking at this in my test environment, it appears the counter values pulled for the perfobject_ Events are pulled directly from Win32_PerfRawData_PerfDisk_LogicalDisk, but if you look at the CookingType requirements for the counters in Win32_PerfFormattedData_PerfDisk_LogicalDisk the raw values have to be calculated by their base values for them to have meaning:

I’ve submitted Connect Item 725167 for this and I really hope that this one gets fixed in a future Cumulative Update or Service Pack.

In the first post in this series, SQL Server 2012 Extended Events Update - 1- Introducing the SSMS User Interface, we looked at how to use the New Session Wizard in SQL Server 2012 to define an Event Session.  In this post we’ll compare the Wizard to the standard New Event Session dialog that can also be used for creating and Event Session in SQL Server 2012.  The New Event Session dialog is the same dialog that is used for editing an existing Event Session on the server, and can be accessed from the Extended Events node in Object Explorer, just like the New Session Wizard can be.

Rather than opening up with an Introduction Page, the New Session dialog opens directly allowing you to begin configuring your session.  I like to think of the New Session dialog as the power user method of creating a new session, and as we’ll see in this post, it can actually takes less steps to configure an Event Session using the New Session dialog over the New Session Wizard.

The General page of the New Session dialog allows you to specify a name for the Event Session, as well as to select a template to create the Event Session from.  You also have the option to specify whether the Event Session should start automatically when SQL Server starts, whether to start the Event Session immediately after creating it with the dialog, and whether you want to immediate begin viewing the Event Session data in the Live Viewer.  If you compare this page to the first three, and final pages of the wizard you will see that we have a much more concise configuration using the dialog so far.  If you click on the Events page on the left hand side, the Events page will show in the dialog allowing you to customize the events being collected by the Event Session.

On first look, the Events page looks very similar to the Select Events To Capture page from the Wizard.  The Event search functionality, and the ability to filter the events by Category and Channel, as well as how you add and remove Events from the Event Session is identical.  However, in the New Session dialog, two additional buttons exist, a Configure button, (circled in red) that allows you to begin configuring the Events that have been added to the Event Session, and a Select button, (circled in green) that allows you to return to the Event selection screen from the configuration screen.  When the buttons are clicked, the screen will collapse/expand left and right.

The biggest difference between the functionality provided by the New Session Wizard versus the New Session dialog is the level of granularity that you have with assigning Actions or Global Fields, and Predicates or filtering, to the Events that have been added to the Event Session.  In the New Session Wizard, any Action or Predicate that is added to the Event Session, is added across the board to all of the Events in the session.  The same functionality can be achieved using the New Session dialog by using the multi-select functionality of the UI to select all of the Events, and then adding the appropriate Actions and Predicates.  However, typically we don’t actually need the Actions and Filters applied to every Event in the Event Session, and since Actions and global Predicates incur an overhead for data collection, even though it is incredibly small, as a performance best practice.  By selecting a single Event, new Actions can be added to the the Event, or as shown below, filtering can be applied at the individual Event level, which allows the filtering definition to be against any column on the Event, not just the shared subset of columns, or global files, across all Events.

If multiple events share the same columns, for example, the sqlserver.sql_batch_completed and sqlserver.sql_statement_completed Events in our session, you can also multi-select those Events and define filtering specific to both of those Events.

Complex Predicates can be defined through the use of the right-click context menu in the Filter table.  Keep in mind that Predicates in Extended Events allow short circuiting logic to occur, so the order of Predicates matters during evaluation time.  The context menu will allow you to insert a new Predicate above or below the currently selected Predicate in the UI, add or delete additional clauses to the existing Predicate list, group subsets of clauses together so that they evaluate as a complete set, ungroup previously grouped sets of clauses, and to toggle the Not operator which evaluates for the negation of the clause being configured.

A really good example of a complex predicate configuration can be seen by looking at the system_health Event Session and the Predicate on the sqlserver.error_reported event. 

The Event Fields tab, will allow you to turn on/off the collection of any customizable columns for the Event that is currently selected.  For example, the sql_batch_completed Event has a customizable column for the batch_text, which is turned on by default.  If you don’t need the batch text, for example, you may be collecting the tsql_frame action which is much smaller because you know that you will be able to get the batch information from the cache at the point you are analyzing the results, you can turn it off by unchecking the checkbox next to it.

The Data Storage page of the dialog, allows you to configure the targets for the Event Session.  The biggest difference here is that you get full use of all of the targets available in Extended Events, not just the event_file and ring_buffer targets provided by the Wizard, though these will typically be the targets that you will use the most.

The Advanced page, allows you to customize the Event Session Options to define how the session will be setup in the Extended Events Engine when it starts.

Once all of the configuration for an Event Session has been completed, you can script the Event Session DDL using the standard SSMS Script button at the top of the UI, or you can create the Event Session immediately by clicking OK.  If you need to change the Event Session definition after creating it, the Session Properties dialog can be opened from the right-click context menu in Object Explorer for the session.  The Session Properties dialog is exactly the same as the New Session dialog.

In the next post we’ll look at the target data viewer in SLQ Server 2012 and how to use it for analyzing the captured Events from an Event Session.

For the most part I have been relatively quiet about the coming changes in SQL Server 2012 with regards to Extended Events.  Primarily this has been to allow the new features of the product to become fully baked to ensure that the information would continue to be applicable as the product lifecycle progressed, and there have been a number of major changes that have made this decision a really good one.  With SQL Server 2012 in it’s RC0 phase, and based on the responses I have seen to a number of feedback items for bugs on Microsoft SQL Server Connect, like the recent one for the template issue I blogged about on my blog post, Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0, I’ve decided to go ahead and start a new series of posts that outline the new features of Extended Events in SQL Server 2012.  I can’t think of a better way to start off a series on the new features in SQL Server 2012 for Extended Events than the new SQL Server Management Studio User Interface for Extended Events in a couple of blog posts.

For this initial post, we’ll take a look at some of the features that replace existing SQL Profiler functionality that most DBA’s tend to use in their day to day operations.  To start off this topic, the first thing you need to know is that there is a new node for Extended Events under the Management folder in Object Explorer for SQL Server 2012.

If you happen to be connecting to a SQL Server 2008 server using SSMS from SQL Server 2012, this node will not exist.  This is due to the fact that UI for SSMS in SQL Server 2012 are not backwards compatible with SQL Server 2008, even though Extended Events exist in SQL Server 2008.  At some point when SQL Server 2012 actually releases to manufacturing (RTM), I will release an update to my SSMS Addin for Extended Events that back ports compatibility for SQL Server 2008 to Management Studio 2012, allowing full integration between SSMS 2012 and SQL Server 2008.

Within the scope of SQL Server 2012, the UI provides a lot of new functionality that should simplify the implementation and usage of Extended Events for most DBA’s.  One of the best enhancements is the ability to create an event session using the New Session Wizard to define an event session based with the least number of steps possible, possibly using an template for the event session, or manually defining a custom configuration that is applied to all of the events configured for the session.

By opening the New Session Wizard, immediately a Introductory page is presented that can be bypassed by selecting the option to Do not show this page again:

Since this page will typically slow down the creation of an event session for use, I would typically check this option before clicking on Next. The Set Session Properties page will allow you to specify a name for the session as well as whether or not the event session will startup automatically when SQL Server starts.  This can be very useful for troubleshooting an infrequent problem that does not predictably occur and you need to ensure that the session data is collected whenever the problem next occurs.  Some examples of where this might be applied will be shown in future posts in this series.

The New Session Wizard provides the ability to create the new event session based on a previously created template, or one of the templates provided by default with SQL Server 2012.  Keep in mind that in the RC0 build, and unfortunately the RTM release of SQL Server 2012, the Activity Tracking template has a bug in the XML definition that will cause this UI to error out.  This was documented by Mike Wachal, the PM for Extended Events at Microsoft on his blog post Activity Tracking event session template is broken, but a fix for the problem in the XML is available in my blog post Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0.  After replacing the broken template with the one attached to my previous blog post we can select it in the UI.

The alternative to using an existing template is to create a blank event session by selecting the option to Do not use a template.  If a template is selected, once you click Next the events from the template will be displayed in the Select Events To Capture page, which also displays the events available in Extended Events and information about the data returned by the events.  The event library can be searched a number of ways to simplify finding the correct events for the session.  The most common way to search, once you start learning the events that are available, would be to start typing the event name in the textbox (green circle below) and the results will dynamically begin filtering out the events that don’t match the search text.  If you click on a specific event in the table, the event description and information about the columns returned by the event will be displayed (purple box below).

However, if you don’t know the specific events that you want, but you know the general category that the events apply to you can make use of other search options in the UI as well.  Events in Extended Events are broken down by two attributes, a Category (Keyword in the DMVs) and a Channel, that make them compatible with Event Tracing for Windows (ETW).  The Category is similar to the trace category that the existing Trace events have and can be used for logically grouping events similar to the way SQL Profiler groups events in previous versions of SQL Server.  The Channel aligns with the channels you would see with ETW.  By default one of the Channels is excluded in the UI, the Debug Channel.  Debug events are focused towards internal debugging tasks and are not of general purpose use by most DBAs.  These events tend to be counting in nature, or can fire incredibly frequently. If you want to see the Debug events in the UI, you can click the drop down and check the checkbox for the Debug Channel and they will be available.

Additionally you can filter out specific categories by clicking the Category dropdown and unchecking specific Category names from the selection.  To add an Event to the session, you can double click on the event, or you can select multiple events using Ctrl or Shift + click on the event names and then clicking the arrow that points to the right.  Alternately, you can also remove events using a double click or by highlighting the event and clicking the arrow that points to the left.

After adding events to the session, the next page allows you to specify the Global Fields, known as actions in Extended Events, that you want added to each of the events in the session.  If you look at the columns being returned by the events in Extended Events, there are significantly fewer data elements being returned at the individual event level.  Many of the trace columns map to the Global Fields (actions) in Extended Events and can be added as needed to the events.  The goal was to minimize the size of the firing events allowing additional information to be added as needed to maximize the performance of Extended Events firing.

After selecting the global fields to add to the Event Session and clicking Next, the Set Session Event Filters page is displayed where you can define filtering (known as predicates) on the events in the session.  Any filters that were configured as a part of the template will be displayed in the upper table, while new filters that will be applied to all of the events in the session can be added to the bottom table.  New filters can only be created on the columns that are available for all of the events in the event session, which typically there won’t be any if using multiple events, or on the global fields available to Extended Events.

This is a very restrictive functionality of the New Session Wizard that was put in place to provide a parity for session creation to what most users would expect from SQL Server Profiler.  I’ll show more about how this is not the best thing when we look at the other parts of the new UI in SSMS in other posts.  Once the filters have been created, the next step is to define the event storage.  The New Session Wizard restricts you to the event_file and ring_buffer targets, which are going to be the most commonly used targets by most DBAs since they retain the full event data and do not apply additional processing to the events.  For an event session that is going to be collecting data for a long period of time, or data that generates at a fast rate, the event_file target should be used, and similar to SQL Trace you can setup the maximum file size and whether or not file rollover should occur.  If the event session is going to be collecting data for a short period of time or where the event predicates will restrict the session to only collecting a small amount of data, the ring_buffer target will generally be a good choice.

Once the data storage has been configured the session can be created by clicking Finish, or you can click Next to get to the Summary page which will allow you to review all the configured options for the Event Session and Script the session definition for further changes to the DDL if necessary.

Once the Event Session is created, the last page provides you the opportunity to start the event session and to open the Live Data Viewer for the event session which is similar to the SQL Server Profiler view from SQL Trace.

In the next blog post I’ll show the New Session dialog which is not a wizard based implementation and why it provides a much more robust method of creating an event session in SQL Server 2012.

In a word; YES! In a lot more words, not always in the way that we want it to, but there are plenty of cases where it actually works and changes are made to the product as a result.

Now with that said, it doesn’t work all the time, and it helps to realize that what is important to us as an individual user might not be important to the product as a whole.  Before you post comments, I am sure that there are plenty of cases out there where people can say that Microsoft Connect for SQL Server is broken.  I have personally been irritated to the point of posting negative comments on Twitter about the whole Connect process.  I feel that it is about time that I show the other side of the story as well and talk about some Connect successes that have occurred in the past year, and of course what better topic to do this with than Extended Events.  Over the next few weeks, I’ll post a couple of different examples of Connect actually working and bringing about changes to the product that are beneficial to the community, starting with this post.

Extended Events does not track insert statements

This Connect item is actually incorrectly titled and is based on some confusion about what the sqlserver.plan_handle action actually returns when executed in the engine.  I blogged about this with much more detail last year in my blog post; What plan_handle is Extended Events sqlserver.plan_handle action returning?

If we revisit the Connect item there is a note that the sql_statement_completed event in SQL Server 2012 now includes a parameterized_plan_handle customizable column that can be used to retrieve the parameterized plan handle for statements that are auto-parameterized by SQL Server during their execution.  Taking the same original demo code from my previous blog post, we can now see how this Connect item has improved the ability to find information about plan caching in SQL Server 2012:

-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name='SQLStmtEvents')
    DROP EVENT SESSION SQLStmtEvents 
    ON SERVER;
GO
CREATE EVENT SESSION SQLStmtEvents
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    SET collect_parameterized_plan_handle = 1
    ACTION (sqlserver.client_app_name,
            sqlserver.plan_handle,
            sqlserver.sql_text,
            sqlserver.tsql_stack,
            package0.callstack,
            sqlserver.request_id)
--Change this to match the AdventureWorks, 
--AdventureWorks2008 or AdventureWorks2008 SELECT DB_ID('AdventureWorks2008R2')
WHERE sqlserver.database_id=9
)
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)
GO
 
-- Start the Event Session
ALTER EVENT SESSION SQLStmtEvents 
ON SERVER 
STATE = START;
GO
 
-- Change database contexts and insert one row
USE AdventureWorks2008R2;
GO
INSERT INTO [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
VALUES(getdate(),SYSTEM_USER,-1,-1,-1,'ErrorProcedure',-1,'An error occurred')
GO 10
 
-- Drop the Event
ALTER EVENT SESSION SQLStmtEvents
ON SERVER
DROP EVENT sqlserver.sql_statement_completed;
GO

-- Retrieve the Event Data from the Event Session Target
SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('xs:hexBinary((event/data[@name="parameterized_plan_handle"]/value)[1])', 'varbinary(64)') as parameterized_plan_handle,
    event_data.value('xs:hexBinary((event/action[@name="plan_handle"]/value)[1])', 'varbinary(64)') as plan_handle,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
FROM(    SELECT evnt.query('.') AS event_data
        FROM
        (   SELECT CAST(target_data AS xml) AS TargetData
            FROM sys.dm_xe_sessions AS s
            JOIN sys.dm_xe_session_targets AS t
                ON s.address = t.event_session_address
            WHERE s.name = 'SQLStmtEvents'
              AND t.target_name = 'ring_buffer'
        ) AS tab
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS split(evnt) 
     ) AS evts(event_data)

If we look at the output of this, we will get the parameterized plan handle for each subsequent call of the statement after the initial call caches the parameterized plan into the cache.

If we plug one of the original plan_handle values from the sqlserver.plan_handle action into a query of sys.dm_exec_cached_plans() it will return nothing, but using the new parameterized_plan_handle value from the customizable column will give us the appropriate cached plan for the statement from cache:

-- Use the plan_handle from one of the Events action to get the query plan
DECLARE @plan_handle varbinary(64) = 0x06000900DFC9DD12608B18EE0100000001000000000000000000000000000000000000000000000000000000
SELECT * 
FROM sys.dm_exec_query_plan(@plan_handle)
GO

-- Use the parameterized_plan_handle from the same Events to get the query plan
DECLARE @plan_handle varbinary(64) = 0x06000900DD8D6D08601E70EE01000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
SELECT * 
FROM sys.dm_exec_query_plan(@plan_handle)
GO

Now, you might point out the different lengths of the plan handles in the above two queries.  If you look back at the source, the same code is being used to perform the xhexBinary conversion in the XML so the values are exactly the same as what was originally provided by the event and the action.  The non-parameterized plan is not cached because it is not likely to be reused, which is why we have the auto-parameterized plan in cache.

In SQL Server 2012 RC0 there are a number of event session templates provided that make creating a commonly used session easier using the Event Session Wizard in SQL Server Management Studio.  One of these has a bug in it’s definition XML file that was filed in the following connect item:

https://connect.microsoft.com/SQLServer/feedback/details/705840/the-object-sqlserver-event-sequence-does-not-exist#tabs

If you attempt to pick the Activity Tracking template you will get the following error:

The error is occurring because the event_sequence action is provided by package0 and not sqlserver. To work around this, you can edit the template file and replace the sqlserver package references with package0. The template is saved in the following location:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Templates\sql\xevent\xe_activity.xml

If you do a find for:

<action package="sqlserver" name="event_sequence" />

and replace it with:

<action package="package0" name="event_sequence" />

th template will work correctly once saved. This will at least let you play around with this while Microsoft works out the bug in the template XML.  A copy of the corrected file is attached to this blog post as well.

xe_activity.xml (18.28 kb)

Just over a year ago I blogged about the enhancements that were made to the sqlserver.page_split Event in SQL Server 2012 to make it easier to identify what the splitting object was and the type of split that was being performed.  Sadly what I discovered writing that post was that even with the extra information about the split type, the event didn’t give you enough information to really focus on the problematic splits that lead to fragmentation and page density issues in the database.  I didn’t do a whole lot with this again until recently when a question was posted by Ami Levin (Blog | Twitter) on the MVP email list that commented that the page_split event was broken in SQL Server 2012 based on a presentation he’d seen by Guy Glantser (Blog | Twitter).

Let me start off by saying, the event isn’t broken, it tracks page splits, but it doesn’t differentiate between an end page split that occurs for an ever increasing index, versus a mid-page split for a random index that leads to fragmentation and page density issues in the database.  Both of these are technically splits inside the storage engine, even if we as DBA’s don’t really care about the end-page split for a increasing key value like an IDENTITY column in the database.  I had Ami pass my information along to the presenter and we traded a few emails on the subject of tracking splits with the specific focus on trying to pull out the mid-page, fragmenting splits.  While going through things for the third time, it dawned on me that this is incredibly simple, based one of the demo’s that was sent to me.  Just over a year ago, I also blogged about tracking transaction log activity in SQL Server 2012 using the sqlserver.transaction_log event, which can be used to track mid-page splits in a database.

Last year when I wrote about the sqlserver.transaction_log event, there were 10 columns output by the event in CTP1, but as of RC0, the events output has changed and only 9 columns are output by the event.

SELECT 
    oc.name, 
    oc.type_name, 
    oc.description
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o
    ON p.guid = o.package_guid
INNER JOIN sys.dm_xe_object_columns AS oc
    ON oc.object_name = o.name
        AND oc.object_package_guid = o.package_guid
WHERE o.name = 'transaction_log'
  AND oc.column_type = 'data';

For the purposes of identifying the mid-page splits, we want to look at the operation column that is output by the event, which contains the specific operation being logged.  In the case of a mid-page split occurring, the operation will be a LOP_DELETE_SPLIT, which marks the delete of rows from a page as a result of the split.  To build our event session, we are going to need the map_key for the LOP_DELETE_SPLIT log_op map.  This can be obtained from the sys.dm_xe_map_values DMV:

SELECT *
FROM sys.dm_xe_map_values
WHERE name = 'log_op'
  AND map_value = 'LOP_DELETE_SPLIT';

With the map_key value, we have a couple of ways to collect the information with our targets.  We could collect everything into an event_file, but that doesn’t really make sense for this event.  Instead the best target for this type of information is the histogram target which will bucket our results based on how we configure the target and tell us how frequently the event fires based on our bucketing criteria.  If we don’t know anything about the server in question, we can start off with a very general event session that has a predicate on the operation only, and then aggregate the information in the histogram target based on the database_id to find the databases that have the most mid-page splits occurring in them in the instance.

-- If the Event Session exists DROP it
IF EXISTS (SELECT 1 
            FROM sys.server_event_sessions 
            WHERE name = 'SQLskills_TrackPageSplits')
    DROP EVENT SESSION [SQLskills_TrackPageSplits] ON SERVER

-- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server
CREATE EVENT SESSION [SQLskills_TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  -- LOP_DELETE_SPLIT 
)
ADD TARGET package0.histogram(
    SET filtering_event_name = 'sqlserver.transaction_log',
        source_type = 0, -- Event Column
        source = 'database_id');
GO
        
-- Start the Event Session
ALTER EVENT SESSION [SQLskills_TrackPageSplits]
ON SERVER
STATE=START;
GO

This event session will allow you to track the worst splitting database on the server, and the event data can be parsed out of the histogram target.  To demonstrate this, we can create a database that has tables and indexes prone to mid-page splits and run a default workload to test the event session:

USE [master];
GO
-- Drop the PageSplits database if it exists
IF DB_ID('PageSplits') IS NOT NULL
BEGIN
    ALTER DATABASE PageSplits SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PageSplits;
END
GO
-- Create the database
CREATE DATABASE PageSplits
GO
USE [PageSplits]
GO
-- Create a bad splitting clustered index table
CREATE TABLE BadSplitsPK
( ROWID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
  ColVal INT NOT NULL DEFAULT (RAND()*1000),
  ChangeDate DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP);
GO
--  This index should mid-split based on the DEFAULT column value
CREATE INDEX IX_BadSplitsPK_ColVal ON BadSplitsPK (ColVal);
GO
--  This index should end-split based on the DEFAULT column value
CREATE INDEX IX_BadSplitsPK_ChangeDate ON BadSplitsPK (ChangeDate);
GO
-- Create a table with an increasing clustered index
CREATE TABLE EndSplitsPK
( ROWID INT IDENTITY NOT NULL PRIMARY KEY,
  ColVal INT NOT NULL DEFAULT (RAND()*1000),
  ChangeDate DATETIME2 NOT NULL DEFAULT DATEADD(mi, RAND()*-1000, CURRENT_TIMESTAMP));
GO
--  This index should mid-split based on the DEFAULT column value
CREATE INDEX IX_EndSplitsPK_ChangeDate ON EndSplitsPK (ChangeDate);
GO
-- Insert the default values repeatedly into the tables
WHILE 1=1
BEGIN
    INSERT INTO dbo.BadSplitsPK DEFAULT VALUES;
    INSERT INTO dbo.EndSplitsPK DEFAULT VALUES;
    WAITFOR DELAY '00:00:00.005';
END
GO

If we startup this workload and allow it to run for a couple of minutes, we can then query the histogram target for our session to find the database that has the mid-page splits occurring.

-- Query the target data to identify the worst splitting database_id
SELECT 
    n.value('(value)[1]', 'bigint') AS database_id,
    DB_NAME(n.value('(value)[1]', 'bigint')) AS database_name,
    n.value('(@count)[1]', 'bigint') AS split_count
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 = 'SQLskills_TrackPageSplits'
  AND t.target_name = 'histogram' ) as tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)

With the database_id of the worst splitting database, we can then change our event session configuration to only look at this database, and then change our histogram target configuration to bucket on the alloc_unit_id so that we can then track down the worst splitting indexes in the database experiencing the worst mid-page splits.

-- Drop the Event Session so we can recreate it 
-- to focus on the highest splitting database
DROP EVENT SESSION [SQLskills_TrackPageSplits] 
ON SERVER

-- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server
CREATE EVENT SESSION [SQLskills_TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  -- LOP_DELETE_SPLIT 
      AND database_id = 8 -- CHANGE THIS BASED ON TOP SPLITTING DATABASE!
)
ADD TARGET package0.histogram(
    SET filtering_event_name = 'sqlserver.transaction_log',
        source_type = 0, -- Event Column
        source = 'alloc_unit_id');
GO

-- Start the Event Session Again
ALTER EVENT SESSION [SQLskills_TrackPageSplits]
ON SERVER
STATE=START;
GO

With the new event session definition, we can now rerun our problematic workload for a 2 minute period and look at the worst splitting indexes based on the alloc_unit_id’s that are in the histogram target:

 

-- Query Target Data to get the top splitting objects in the database:
SELECT
    o.name AS table_name,
    i.name AS index_name,
    tab.split_count,
    i.fill_factor
FROM (    SELECT 
            n.value('(value)[1]', 'bigint') AS alloc_unit_id,
            n.value('(@count)[1]', 'bigint') AS split_count
        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 = 'SQLskills_TrackPageSplits'
          AND t.target_name = 'histogram' ) as tab
        CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
) AS tab
JOIN sys.allocation_units AS au
    ON tab.alloc_unit_id = au.allocation_unit_id
JOIN sys.partitions AS p
    ON au.container_id = p.partition_id
JOIN sys.indexes AS i
    ON p.object_id = i.object_id
        AND p.index_id = i.index_id
JOIN sys.objects AS o
    ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0;

With this information we can now go back and change our FillFactor specifications and retest/monitor the impact to determine whether we’ve had the appropriate reduction in mid-page splits to accommodate the time between our index rebuild operations:

-- Change FillFactor based on split occurences
ALTER INDEX PK__BadSplit__97BD02EB726FCA55 ON BadSplitsPK REBUILD WITH (FILLFACTOR=70)
ALTER INDEX IX_BadSplitsPK_ColVal ON BadSplitsPK REBUILD WITH (FILLFACTOR=70)
ALTER INDEX IX_EndSplitsPK_ChangeDate ON EndSplitsPK REBUILD WITH (FILLFACTOR=80)
GO

-- Stop the Event Session to clear the target
ALTER EVENT SESSION [SQLskills_TrackPageSplits]
ON SERVER
STATE=STOP;
GO

-- Start the Event Session Again
ALTER EVENT SESSION [SQLskills_TrackPageSplits]
ON SERVER
STATE=START;
GO

With the reset performed we can again start up our workload generation and begin monitoring the effect of the FillFactor specifications on the indexes with our code.  After another 2 minute period, the following splits were noted.

With this information we can go back and again attempt to tune our FillFactor values for the worst splitting indexes and rinse/repeat until we determine the best FillFactor for each of the indexes to minimize splits.  This is an incredibly powerful tool for the DBA moving into SQL Server 2012, and will definitely change how we perform index fragmentation analysis and troubleshoot problems with excessive log generation in SQL Server 2012 onwards.

Cheers!

This afternoon, Orson Weston (Twitter), asked how to find the difference between two binary sets of schema options for replication on the #SQLHelp hash tag on twitter.  The valid values for the @schema_options parameter in replication are documented in the BOL Topic for sp_addarticle (http://msdn.microsoft.com/en-us/library/ms173857.aspx).  However, just having a table of values doesn’t really help you figure out what is different between two binary @schema_options values without doing some bitwise operations on the values.  To do this, we can create a table variable to hold the valid values and descriptions for the schema options and then use the & (Bitwise AND) operator to find which options are set for each of the binary values.

DECLARE @options TABLE
(Value VARBINARY(8), [Description] VARCHAR(1000))

INSERT INTO @options (Value, Description)
VALUES 
    (0x00, 'Disables scripting by the Snapshot Agent and uses creation_script.'),
    (0x01, 'Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.'),
    (0x02, 'Generates the stored procedures that propagate changes for the article, if defined.'),
    (0x04, 'Identity columns are scripted using the IDENTITY property.'),
    (0x08, 'Replicate timestamp columns. If not set, timestamp columns are replicated as binary.'),
    (0x10, 'Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.'),
    (0x20, 'Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.'),
    (0x40, 'Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.'),
    (0x80, 'Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.'),
    (0x100, 'Replicates user triggers on a table article, if defined. Not supported for Oracle Publishers.'),
    (0x200, 'Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table are not replicated. Not supported for Oracle Publishers.'),
    (0x400, 'Replicates check constraints. Not supported for Oracle Publishers.'),
    (0x800, 'Replicates defaults. Not supported for Oracle Publishers.'),
    (0x1000, 'Replicates column-level collation.'),
    (0x2000, 'Replicates extended properties associated with the published article source object. Not supported for Oracle Publishers.'),
    (0x4000, 'Replicates UNIQUE constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.'),
    (0x8000, 'This option is not valid for SQL Server 2005 Publishers.'),
    (0x10000, 'Replicates CHECK constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization.'),
    (0x20000, 'Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization.'),
    (0x40000, 'Replicates filegroups associated with a partitioned table or index.'),
    (0x80000, 'Replicates the partition scheme for a partitioned table.'),
    (0x100000, 'Replicates the partition scheme for a partitioned index.'),
    (0x200000, 'Replicates table statistics.'),
    (0x400000, 'Default Bindings'),
    (0x800000, 'Rule Bindings'),
    (0x1000000, 'Full-text index'),
    (0x2000000, 'XML schema collections bound to xml columns are not replicated.'),
    (0x4000000, 'Replicates indexes on xml columns.'),
    (0x8000000, 'Create any schemas not already present on the subscriber.'),
    (0x10000000, 'Converts xml columns to ntext on the Subscriber.'),
    (0x20000000, 'Converts large object data types (nvarchar(max), varchar(max), and varbinary(max)) introduced in SQL Server 2005 to data types that are supported on SQL Server 2000. For information about how these types are mapped, see the "Mapping New Data Types for Earlier Versions" section in Using Multiple Versions of SQL Server in a Replication Topology.'),
    (0x40000000, 'Replicate permissions.'),
    (0x80000000, 'Attempt to drop dependencies to any objects that are not part of the publication.'),
    (0x100000000, 'Use this option to replicate the FILESTREAM attribute if it is specified on varbinary(max) columns. Do not specify this option if you are replicating tables to SQL Server 2005 Subscribers. Replicating tables that have FILESTREAM columns to SQL Server 2000 Subscribers is not supported, regardless of how this schema option is set. '),
    (0x200000000, 'Converts date and time data types (date, time, datetimeoffset, and datetime2) introduced in SQL Server 2008 to data types that are supported on earlier versions of SQL Server. For information about how these types are mapped, see the "Mapping New Data Types for Earlier Versions" section in Using Multiple Versions of SQL Server in a Replication Topology.'),
    (0x400000000, 'Replicates the compression option for data and indexes. For more information, see Creating Compressed Tables and Indexes.'),
    (0x800000000, 'Set this option to store FILESTREAM data on its own filegroup at the Subscriber. If this option is not set, FILESTREAM data is stored on the default filegroup. Replication does not create filegroups, therefore, if you set this option, you must create the filegroup before you apply the snapshot at the Subscriber. For more information about how to create objects before you apply the snapshot, see Executing Scripts Before and After the Snapshot Is Applied.'),
    (0x1000000000, 'Converts common language runtime (CLR) user-defined types (UDTs) that are larger than 8000 bytes to varbinary(max) so that columns of type UDT can be replicated to Subscribers that are running SQL Server 2005.'),
    (0x2000000000, 'Converts the hierarchyid data type to varbinary(max) so that columns of type hierarchyid can be replicated to Subscribers that are running SQL Server 2005. For more information about how to use hierarchyid columns in replicated tables, see hierarchyid (Transact-SQL).'),
    (0x4000000000, 'Replicates any filtered indexes on the table. For more information about filtered indexes, see Filtered Index Design Guidelines.'),
    (0x8000000000, 'Converts the geography and geometry data types to varbinary(max) so that columns of these types can be replicated to Subscribers that are running SQL Server 2005.'),
    (0x10000000000, 'Replicates indexes on columns of type geography and geometry.'),
    (0x20000000000, 'Replicates the SPARSE attribute for columns. For more information about this attribute, see Using Sparse Columns.')

DECLARE @schema_option VARBINARY(8) = 0x000000000807509F;

SELECT CONVERT(INT, @schema_option, 1) & CONVERT(INT,value,1), *
FROM @options
WHERE CONVERT(INT, @schema_option, 1) & CONVERT(INT,value,1) <> 0;

SET @schema_option = 0x000000000803509F;

SELECT CONVERT(INT, @schema_option, 1) & CONVERT(INT,value,1), *
FROM @options
WHERE CONVERT(INT, @schema_option, 1) & CONVERT(INT,value,1) <> 0;

Using Orson’s two values we can see that the difference between the two is, the first value includes the “Replicates filegroups associated with a partitioned table or index.” option where the second value does not.

While setting up my new Availability Group using SQL Server 2012 RC0 tonight, I noticed an interesting new addition to Extended Events associated with Availability Group configuration in the Release Candidate.  When you setup an Availability Group in RC0, another default Event Session is created on the servers that participate in the Availability Group to provide monitoring of the health of the Availability Group overall.  The definition of the monitoring session is as follows:

CREATE EVENT SESSION [AlwaysOn_health] ON SERVER
ADD EVENT sqlserver.alwayson_ddl_executed,
ADD EVENT sqlserver.availability_group_lease_expired,
ADD EVENT sqlserver.availability_replica_automatic_failover_validation,
ADD EVENT sqlserver.availability_replica_manager_state_change,
ADD EVENT sqlserver.availability_replica_state_change,
ADD EVENT sqlserver.error_reported(
    WHERE ([error_number]=(9691) OR [error_number]=(35204) OR [error_number]=(9693) OR [error_number]=(26024) OR [error_number]=(28047) OR [error_number]=(26023) OR [error_number]=(9692) OR [error_number]=(28034) OR [error_number]=(28036) OR [error_number]=(28048) OR [error_number]=(28080) OR [error_number]=(28091) OR [error_number]=(26022) OR [error_number]=(9642) OR [error_number]=(35201) OR [error_number]=(35202) OR [error_number]=(35206) OR [error_number]=(35207) OR [error_number]=(26069) OR [error_number]=(26070) OR [error_number]>(41047) AND [error_number]<(41056) OR [error_number]=(41142) OR [error_number]=(41144) OR [error_number]=(1480) OR [error_number]=(823) OR [error_number]=(824) OR [error_number]=(829) OR [error_number]=(35264) OR [error_number]=(35265))),
ADD EVENT sqlserver.lock_redo_blocked
ADD TARGET package0.event_file(SET filename=N'AlwaysOn_health.xel',max_file_size=(5),max_rollover_files=(4))
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=ON)
GO

This Extended Event Session monitors a number of critical events in the system but one of the problems with figuring out what exactly this Event Session is monitoring is to figure out what all the predicate values on the sqlserver.error_reported event are actually firing on.  To that aspect of things, we can do a quick reuse of the predicate on this event be doing a replace SSMS on the [error_number] value with a replace for an alias to a query to sys.messages, on the message_id column from the DMV as follows:

SELECT message_id, severity, is_event_logged, text
FROM sys.messages AS m
WHERE m.language_id = SERVERPROPERTY('LCID')
  AND  (m.message_id=(9691)
        OR m.message_id=(35204)
        OR m.message_id=(9693)
        OR m.message_id=(26024)
        OR m.message_id=(28047)
        OR m.message_id=(26023)
        OR m.message_id=(9692)
        OR m.message_id=(28034)
        OR m.message_id=(28036)
        OR m.message_id=(28048)
        OR m.message_id=(28080)
        OR m.message_id=(28091)
        OR m.message_id=(26022)
        OR m.message_id=(9642)
        OR m.message_id=(35201)
        OR m.message_id=(35202)
        OR m.message_id=(35206)
        OR m.message_id=(35207)
        OR m.message_id=(26069)
        OR m.message_id=(26070)
        OR m.message_id>(41047)
        AND m.message_id<(41056)
        OR m.message_id=(41142)
        OR m.message_id=(41144)
        OR m.message_id=(1480)
        OR m.message_id=(823)
        OR m.message_id=(824)
        OR m.message_id=(829)
        OR m.message_id=(35264)
        OR m.message_id=(35265)
)

This will give us a list of the error messages that the Event Session will actually fire events for:

Based on this output, and the output of the following query:

SELECT name, description
FROM sys.dm_xe_objects
WHERE NAME IN (
'alwayson_ddl_executed',
'availability_group_lease_expired',
'availability_replica_automatic_failover_validation',
'availability_replica_manager_state_change',
'availability_replica_state_change',
'error_reported',
'lock_redo_blocked')

We can deduce the following about the Event Session:

CREATE EVENT SESSION [AlwaysOn_health] ON SERVER
--Occurs when AlwaysOn DDL is executed including CREATE, ALTER or DROP
ADD EVENT sqlserver.alwayson_ddl_executed,
--Occurs when there is a connectivity issue between the cluster and the Availability Group resulting
--in a failure to renew the lease
ADD EVENT sqlserver.availability_group_lease_expired,
--Occurs when the failover validates the readiness of replica as a primary. For instance, the failover
--validation will return false when not all databases are synchronized or not joined
ADD EVENT sqlserver.availability_replica_automatic_failover_validation,
--Occurs when the state of the Availability Replica Manager has changed.
ADD EVENT sqlserver.availability_replica_manager_state_change,
--Occurs when the state of the Availability Replica has changed.
ADD EVENT sqlserver.availability_replica_state_change,
--Occurs when an error is reported based on the previously listed table
ADD EVENT sqlserver.error_reported(
    WHERE ([error_number]=(9691) OR [error_number]=(35204) OR [error_number]=(9693) OR [error_number]=(26024) OR [error_number]=(28047) OR [error_number]=(26023) OR [error_number]=(9692) OR [error_number]=(28034) OR [error_number]=(28036) OR [error_number]=(28048) OR [error_number]=(28080) OR [error_number]=(28091) OR [error_number]=(26022) OR [error_number]=(9642) OR [error_number]=(35201) OR [error_number]=(35202) OR [error_number]=(35206) OR [error_number]=(35207) OR [error_number]=(26069) OR [error_number]=(26070) OR [error_number]>(41047) AND [error_number]<(41056) OR [error_number]=(41142) OR [error_number]=(41144) OR [error_number]=(1480) OR [error_number]=(823) OR [error_number]=(824) OR [error_number]=(829) OR [error_number]=(35264) OR [error_number]=(35265))),
--Occurs when the redo thread blocks when trying to acquire a lock.
ADD EVENT sqlserver.lock_redo_blocked
--Writes to the file target for persistence in the system beyond failovers and service restarts
ADD TARGET package0.event_file(SET filename=N'AlwaysOn_health.xel',max_file_size=(5),max_rollover_files=(4))
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=ON)
GO

What is really cool is that this Event Session is used by the Availability Groups Dashboard to provide an overall status of the health of the Availability Group in Management Studio.

In the first post in this blog series on using SQL Server 2012 Distributed Replay, Installing and Configuring SQL Server 2012 Distributed Replay, we looked at how to configure a Distributed Replay environment using multiple clients and a dedicated replay controller.  In this post we’ll actually make use of the previously configured servers to perform a distributed replay using a random workload that has been generated against the AdventureWorks2008R2 database installed on our Replay SQL Server.

Collecting the Replay Trace Data

For the purposes of generating a random workload against AdventureWorks2008R2, I created a workload generator that can be found on my blog post The AdventureWorks2008R2 Books Online Random Workload Generator.  I used this with 2 different PowerShell Windows from SQL2012-DRU1 and SQL2012-DRU2 to run a random workload across multiple sessions against the SQL2012-DB1 server.  To capture the trace data required for performing the replay, SQL Server Profiler was used along with the TSQL_Replay template to create the capture.

For production systems, the best way to go about capturing a Replay Trace is to script the trace definition to a file, and then create the trace as a server side trace that is writing to a trace file on local disks for the server.  This has a significantly lower impact that tracing directly from Profiler, which uses the rowset provider for Trace.  With the replay trace running, and the workload generating events I waited for the trace to collect around 80000 rows of data and then shutdown the trace so that I could access the trace file to copy it from the SQL2012-DB1 server to the SQL2012-DRU server where the Distributed Replay Controller is installed.

Preprocessing the Trace File(s)

At the point that I went to perform the preprocessing of the trace file for replay, I realized a difference in my environment using multiple servers to build this blog series versus my original setup using a single server for learning how to use Distributed Replay.  In order to preprocess the trace file for replay, you have to have the Management Tools Basic installed on the server that will be used for preprocessing the trace data.  If you have been following this blog series to learn how to use Distributed Replay, you will need to run Setup on the SQL2012-DRU server to add this feature before it can be used for pre-processing the trace file.  This is necessary to administer Distributed Replay.

Once the Management Tools Basic have been installed the server will have to be restarted and then it is possible to make use of the DReplay.Exe executable to administer the Distributed Replay components on the controller server. The DReplay executable has multiple options that can be discovered by using a –? from the command line as follows:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>dreplay -?
Info DReplay    Usage:
DReplay.exe {preprocess|replay|status|cancel} [options] [-?]}

Verbs:
preprocess Apply filters and prepare trace data for intermediate file on controller.
replay     Transfer the dispatch files to the clients, launch and synchronize replay.
status     Query and display the current status of the controller.
cancel     Cancel the current operation on the controller.
-?         Display the command syntax summary.

Options:
dreplay preprocess [-m controller] -i input_trace_file -d controller_working_dir [-c config_file] [-f status_interval]
dreplay replay [-m controller] -d controller_working_dir [-o] [-s target_server] -w clients [-c config_file] [-f status_interval]
dreplay status [-m controller] [-f status_interval]
dreplay cancel [-m controller] [-q]
Run dreplay <verb> -? for detailed help on each verb.

To perform the preprocessing, you will need to do a couple of different steps.  The first thing you need to do is edit any options that you want to set for the pre-processing by editing the DReplay.Exe.Preproces.config file in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn path on the server.  There are two configuration files for DReplay.Exe as highlighted below.  At this time make sure that you are only editing the Preprocess.config file.

The DReplay.Exe.Preproces.config file contains a schema defined XML document that controls the configuration of the preprocessing.  In general the options set for preprocessing should not need to be changed but if you want to include system sessions as a part of the replay, you can change the options in the XML, which is listed below.

<?xml version="1.0" encoding="utf-8"?>
<Options>
    <PreprocessModifiers>
        <IncSystemSession>No</IncSystemSession>
        <MaxIdleTime>-1</MaxIdleTime>
    </PreprocessModifiers>
</Options>

To preprocess the trace data, open a new command prompt window and change directories to the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn path.  The trace file has been copied onto the SQL2012-DRU server as C:\DReplay\SQL2012_ReplayTrace.trc.  To preprocess this file first start the “SQL Server Distributed Replay Controller” service by using NET START:

NET START "SQL Server Distributed Replay Controller"

Then execute the following command from within the Binn path to actually preprocess the trace file and output:

dreplay preprocess -i "C:\DReplay\SQL2012_ReplayTrace.trc" -d "C:\DReplay"

This will process the trace file and output the working files for performing the Distributed Replay to the C:\DReplay path.  Below is a screenshot of the full window for preprocessing the trace file.

Note: The dreplay executable can be called from any path within the server because the Binn path is a part of the Path Environmental variables.  However, the executable has to be called from within the Binn folder to access the necessary .config files and .xsd schema files for the configuration.  If you want to be able to run this executable from another location on the server, you will need to copy the .config and .xsd files out of the Binn folder to the folder that you want to be able to run dreplay within for it to work.

Performing the Replay

The first step in performing the replay is to start the “SQL Server Distributed Replay Client” service on each of the replay clients using NET START.

NET START "SQL Server Distributed Replay Client"

You will want to verify that each of the clients was able to successfully connect to the controller in the logs as shown in the previous post in this series.  Once this has been done, your environment is almost ready for replay.  For the purposes of this blog series, a SELECT only workload has been generated for replay against AdventureWorks2008R2.  However, in most environments you won’t have a SELECT only workload, so you will have to plan for and prepare your replay environment using a BACKUP/RESTORE of the production database from a point within the captured workload so that the database can be replayed against without having problems associated with Primary Key constraint violations during the replay.

If you want to change any of the parameters associated with the replay operation, you can edit the DReplay.Exe.Replay.config file in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn path.  The default contents of the configuration file are shown below:

<?xml version="1.0" encoding="utf-8"?>
<Options>
    <ReplayOptions>
        <Server></Server>
        <SequencingMode>stress</SequencingMode>
        <ConnectTimeScale>100</ConnectTimeScale>
        <ThinkTimeScale>100</ThinkTimeScale>
        <HealthmonInterval>60</HealthmonInterval>
        <QueryTimeout>3600</QueryTimeout>
        <ThreadsPerClient>255</ThreadsPerClient>
        <EnableConnectionPooling>No</EnableConnectionPooling>
        <StressScaleGranularity>SPID</StressScaleGranularity>
    </ReplayOptions>
    <OutputOptions>
        <ResultTrace>
            <RecordRowCount>Yes</RecordRowCount>
            <RecordResultSet>No</RecordResultSet>
        </ResultTrace>
    </OutputOptions>
</Options>

Before performing the actual replay, make sure that the account being used to run the SQL Server Distributed Replay Client service has been granted appropriate access to the target SQL Server and database to be able to perform the replay operations.  Once this has been done replay can be performed using the command line options for DReplay.Exe by providing the appropriate switches, or you can alternately provide the –c command line switch to specify the configuration file that should be used for performing the replay.  If you change any of the default values listed above in the DReplay.Exe.Replay.config file, you will need to specify the –c command line switch for those to take effect.  To perform a replay with the defaults, the following command line execution can be run:

dreplay replay -s "SQL2012-DB1" -d "C:\DReplay" -w "SQL2012-DRU1, SQL2012-DRU2"

Once this is executed, the Distributed Replay Controller will take read in the preprocessed replay file, and then synchronize the replay across all of the clients specified with the –w command line parameter.  While the replay operation occurs, the command window for the controller will output periodic updates about the current status of the replay process.

The frequency of the status updates can be controlled using the –f command line switch to specify the number of seconds between each of the updates.  Each of the status updates will provide information about each of the clients including the total number of events that have been replayed, the success rate of the replay operations per client, as well as an estimate for the total amount of time remaining to complete the replay operation.  When the replay completes the total elapsed time and pass rate for the events is output.

In the next and final post in this series, we’ll look at some of the common problems with using Distributed Replay and how to resolve them, including manually configuring the Controller and add additional Client Service accounts to the environment after Setup has been completed.

Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large workload script that I would run using multiple sqlcmd command line windows to fire off the workload, but one of the problems with this has been that it was incredibly predictable, and it didn’t scale the way I really wanted it to.

When I was working with Distributed Replay, this became somewhat problematic with generating a randomized workload to capture a Replay Trace off of, so I took a few hours and went back to the drawing board with my idea.  What I came up with was a large script file that contains all of the SELECT statement examples from the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms187731.aspx).  This script is divided into separate sections using a delimiter, and then I wrote a PowerShell script that reads the file and breaks it down into individual scripts that are randomly executed against the configured SQL Server using SMO.

The two files required to make use of this are attached to this blog post and can be used with minimal modifications against any SQL Server 2008+ system that has the AdventureWorks2008R2 database attached to it.  To make use of the PowerShell script, you will either have to sign it, or if you work like I do in my VMs, allow unsigned script execution with Set-ExecutionPolicy Unrestricted.

The PowerShell script is incredibly simple code wise.  It loads the SMO assembly, splits the file contents on the delimiter, then inside a infinite loop, it picks a random query and executes it against the SQL Server.

# Load the SMO assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

# Set the server to run the workload against
$ServerName = "SQL2012-DB1";

# Split the input on the delimeter
$Queries = Get-Content -Delimiter "------" -Path "AdventureWorks BOL Workload.sql"

WHILE(1 -eq 1)
{
    # Pick a Random Query from the input object
    $Query = Get-Random -InputObject $Queries;

    #Get a server object which corresponds to the default instance
    $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

    # Use the AdventureWorks2008R2 database
    $srv.ConnectionContext.set_DatabaseName("AdventureWorks2008R2")

    # Execute the query with ExecuteNonQuery
    $srv.ConnectionContext.ExecuteNonQuery($Query);

    # Disconnect from the server
    $srv.ConnectionContext.Disconnect();
   
    # Sleep for 100 miliseconds between loops
    Start-Sleep -Milliseconds 100
}

To generate random workloads, I generally fire up 3-5 copies of this script on a client concurrently and leave it running in the background to generate the load. 

AdventureWorks BOL Workload.zip (6.35 kb)

This blog post is the first in a series of posts that will cover how to install, configure, and use the new Distributed Replay Utility in SQL Server 2012.  The distributed replay utility can be used to assess the impact of changes and upgrades by replaying workload activity against a test environment based on a replay trace captured from the current production SQL Server environment.  Prior to SQL Server 2012, two features existed that provided replay capabilities for performance benchmarking and stress testing SQL Server based on a replay workload; SQL Server Profiler trace replay and RML Utilities.  Unlike these two tools, Distributed Replay is not limited to replaying events from a single computer, which allows you to drive higher loads against the test environment to better simulate mission-critical workloads by driving load from multiple clients concurrently.

This blog post will show how to configure a Distributed Replay Controller, using a dedicated server named SQL2012-DRU, and two Distributed Replay Clients using dedicated servers named SQL2012-DRU1 and SQL2012-DRU2.  For the purposes of performing an actual replay using Distributed Replay in another post, a separate server named SQL2012-DB1 with the Database Engine Services and the AdventureWorks2008R2 database installed on it will be used.

Create Replay Service Accounts in Active Directory

The first step in installing and configuring Distributed Replay is to setup the necessary Active Directory service accounts to run the Distributed Replay Client and Distributed Replay Controller under.  For the purposes of service isolation, the Distributed Replay Clients and Distributed Replay Controller will make use of separate Active Directory service Accounts.  For the purposes of this blog post, two different accounts will be created.  The Distributed Replay Client will use the SQLskillsDemos\DRUClientSvcAcct as shown in the below screenshots.

The Distributed Replay Controller will use the SQLskillsDemos\DRUCtrlSvcAcct as shown in the below screenshots:

Install the Replay Controller

With the service accounts setup in Active Directory, we can begin the installation of our Distributed Replay Controller.  To do this run SQL Server 2012 Setup on the controller and perform a SQL Server Feature Installation.  On the Feature Selection page select the Distributed Replay Client feature.

On the Server Configuration page set the Service Account credentials to the previously created Active Directory account for the client; in this case SQLSKILLSDEMOS\DRUCtrlSvcAcct.

On the Distributed Replay Controller page, click the Add button and search directory services for the client service account previous created in Active Directory; in this case SQLSKILLSDEMOS\DRUClientSvcAcct.

Finish the installation and then close SQL Server Setup.

Install the Replay Clients

With the service accounts setup in Active Directory, we can begin the installation of our Distributed Replay Client machines.  To do this run SQL Server 2012 Setup on each of the clients to be configured and perform a SQL Server Feature Installation.  On the Feature Selection page select the Distributed Replay Client feature.

On the Server Configuration page set the Service Account credentials to the previously created Active Directory account for the client.  In this case SQLSKILLSDEMOS\DRUClientSvcAcct.

On the Distributed Replay Client page, type in the name of the server that you previously installed the Distributed Replay Controller service on in the Controller Name box.

Finish the installation and then close SQL Server Setup.

Configure the Windows Firewall for the Services

In order for the Distributed Replay Clients (SQL2012-DRU1 and SQL2012-DRU2) to connect and register with the Distributed Replay Controller, in this case SQL2012-DRU, the firewall must be configured to allow inbound connections for the DReplayClient.exe application on each of the client machines, as well as for the DReplayController.exe application on the Distributed Replay Controller.  To do this, firewall rules need to be added through the use of the Windows Firewall with Advanced Security snapin, available through Start | Administrative Tools | Windows Firewall with Advanced Security, or through the use of NETSH command line statement executions (detailed later in this blog post). 

To add a new firewall rule for the DReplay Client using the Windows Firewall with Advanced Security snapin, Right-Click on Inbound Rules and click on the New Rule menu item.  Specify Program for the Rule Type and click Next.

On the Program page, click Browse and navigate to C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient and select the DReplayClient.exe executable.

On the Action page select the option Allow the connection and then click Next.

On the Profile page, select the appropriate network profiles/locations for the environment, generally Domain would be sufficient for most corporate domains, and then click Next.

Finally provide a Name for the rule and click Finish to make the firewall changes.

To setup the firewall rule for the Distributed Replay Controller, follow the same steps but instead of selecting the DReplayClient folder and DReplayClient.exe application, select the DReplayController folder and DReplayController.exe application

These rules can also be added using NETSH from the command line using the following commands:

NETSH advfirewall firewall add rule name="Allow DReplay Client" dir=in program="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.exe" action=allow

NETSH advfirewall firewall add rule name="Allow DReplay Controller" dir=in program="C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\DReplayController.exe" action=allow

Start the Services and Verify Client Registrations

The final step in the process of installing and configuring SQL Server 2012 Distributed Replay is to start the services and verify that the Distributed Replay Clients register with the Distributed Replay Controller correctly.  To do this start the “Distributed Replay Controller” service from the Services.msc snap-in or from the command line using NET START on the controller server.  Then start the “Distributed Replay Client” service on from the Services.msc snap-in or from the command line using NET START on each of the client machines.  As each of the client services starts, verify that the client was able to successfully register itself with the controller by reading the log file, which is located in the following path:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\Log

What you want to see in the DReplay Client Log file is:

2011-11-09 00:41:09:162 OPERATIONAL  [Client Service]      Microsoft SQL Server Distributed Replay Client - 11.0.1440.19.
2011-11-09 00:41:09:162 OPERATIONAL  [Client Service]      (c) Microsoft Corporation.
2011-11-09 00:41:09:162 OPERATIONAL  [Client Service]      All rights reserved.
2011-11-09 00:41:09:178 OPERATIONAL  [Client Service]      Current edition is: [Enterprise Evaluation Edition].
2011-11-09 00:41:09:178 OPERATIONAL  [Common]              Initializing dump support.
2011-11-09 00:41:09:178 OPERATIONAL  [Common]              Dump support is ready.
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Windows service "Microsoft SQL Server Distributed Replay Client" has started under service account "SQLSKILLSDEMOS\DRUClientSvcAcct". Process ID is 2928.
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Time Zone: Eastern Standard Time.
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Controller name is "SQL2012-DRU".
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Working directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir".
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Result directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir".
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Heartbeat Frequency(ms): 3000
2011-11-09 00:41:09:193 OPERATIONAL  [Client Service]      Heartbeats Before Timeout: 3
2011-11-09 00:41:09:367 OPERATIONAL  [Client Service]      Registered with controller "SQL2012-DRU".

What you don’t want to see in the DReplay Client Log file is:

2011-11-09 00:40:50:207 OPERATIONAL  [Client Service]      Microsoft SQL Server Distributed Replay Client - 11.0.1440.19.
2011-11-09 00:40:50:223 OPERATIONAL  [Client Service]      (c) Microsoft Corporation.
2011-11-09 00:40:50:223 OPERATIONAL  [Client Service]      All rights reserved.
2011-11-09 00:40:50:223 OPERATIONAL  [Client Service]      Current edition is: [Enterprise Evaluation Edition].
2011-11-09 00:40:50:223 OPERATIONAL  [Common]              Initializing dump support.
2011-11-09 00:40:50:239 OPERATIONAL  [Common]              Dump support is ready.
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Windows service "Microsoft SQL Server Distributed Replay Client" has started under service account "SQLSKILLSDEMOS\DRUClientSvcAcct". Process ID is 776.
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Time Zone: Eastern Standard Time.
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Controller name is "SQL2012-DRU".
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Working directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir".
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Result directory is "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir".
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Heartbeat Frequency(ms): 3000
2011-11-09 00:40:50:239 OPERATIONAL  [Client Service]      Heartbeats Before Timeout: 3
2011-11-09 00:40:50:476 CRITICAL     [Client Service]     [0xC8100013] Failed to register with controller SQL2012-DRU.

If the client fails to register, and the controller name is correct, you need to verify first that the firewall rules have been established for the client to allow the appropriate inbound connections from the controller service and that the firewall rules have been established for the controller to allow the appropriate inbound connections from the client services.  If this all checks out, you need to go troubleshoot the permissions and setup of the services in the environment, which I’ll cover in a later post.

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