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.

Plagiarism online is unfortunately a very common problem.  Over the last few years, there have been numerous circumstances where a blog or website has done a verbatim word for word copy of blog posts written by other authors/bloggers online.  These are clear-cut cases where plagiarism has occurred.  However, plagiarism doesn’t have to be a verbatim copy of a blog post, it can simply be the reuse of ideas or solutions that are not common without attribution.  According to the Plagiarism.org website, and the Merriam Webster Online Dictionary, plagiarizing covers any of the following:

• to steal and pass off (the ideas or words of another) as one's own
• to use (another's production) without crediting the source
• to commit literary theft
• to present as new and original an idea or product derived from an existing source

My undergraduate degree is in History, and during my time in school there were numerous times where someone failed to appropriately cite sources and as a result was failed from a class and either placed on academic probation, or even dismissed from the school entirely.  As a result I make it a point to provide attribution when I blog and two examples of how I do this are in the opening paragraph of this blog post.  It is really easy to cite your sources and as a blogger it is your responsibility to do so.

This applies to paraphrasing/summarizing blog material as well, though I am not a big fan of taking another person's work and rewriting it so that the same concept is shown, just to keep from plagiarizing the content word for word. Earlier this week I read a blog post and left a comment with a link to a blog post I wrote that provided the correct solution to the problem.  I even traded DM’s with the blog author on Twitter, so the last thing I expected was that this person would rewrite the solution I provided in my post on their blog the next day without providing any form of attribution.  A link has since been added after a number of DM’s pointing out that this was still plagiarism.  I don’t necessarily agree with someone reposting an entire concept without adding anything meaningful to the content, but if you are going to do this, at a bare minimum you have to provide attribution to your original source.

If you are a blogger and you are unsure, provide a reference as it only takes a second or two to do so.  Not citing sources doesn’t make you appear any smarter, quite to the contrary, not citing sources and getting caught for it makes you appear dishonest. 

There is no valid excuse for plagiarizing content or ideas.

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.

I recently did some drive upgrades in my personal laptops and as a result I had a couple of SSD drives sitting around that I wanted to make use of.  A few weeks ago I purchased a new OCZ Agility 3 240GB SATA III SSD from Newegg when it was on sale and at the same time I also purchased two MassCool USB 3 external enclosures to make use of the two 120GB OCZ Vertex 2 SSD’s that I would have sitting around.  The cost for the MassCool enclosures was only $14.99 so I figured it would be a good way to reuse the SSD’s at the time.  When the package from Newegg arrived, I immediately installed the SSD into one of my personal machines and then later I went about testing the performance of the USB 3 external enclosures with the older SSD’s.  Initially the performance was impressive, and I posted a few tweets that attracted some attention and a few people requested that I blog my results.

To make this blog post something worth looking at, I ran a number of additional tests over the last few days using SQLIO and a varying configuration of drives with external enclosures.  Specifically I tested two different USB 3 2.5” SATA enclosures, a separate eSATA 2.5” SATA external enclosure, as well as two USB 3 HDD’s that I already owned to get some performance information from each of the setups.

The external USB 3.0 HDD’s being tested are as follows:

• Toshiba CANVIO 3.0 500 GB,External,5400 RPM (E05A050CAU3XW)
• Western Digital My Passport Essential 500GB USB 3

The external enclosures being tested in these tests are as follows:

• MassCool (UHB-2233) USB 3
• Hornettek (Panther) USB 3
• Vantec NexStar CX (NST-200SU-BK) eSATA

The SSD’s used with the external enclosures listed above are:

• OCZ Vertex 2 120GB SATA II
• OCZ Agility 3 240GB SATA III

At the time that I bought the 120GB drive I paid close to $2.42 per megabyte for it, and at the time this was a good deal.  When I purchased the 240GB SSD I paid right around $1.08 per megabyte, showing how much the prices have decreased nearly 3 years later.  I happen to own a number of OCZ drives at home and they are one of my favorite brands personally because of the overall reliability I’ve had with them the last three years as well as the performance to cost ratio I’ve experienced. Initially I was just testing the MassCool USB 3 enclosures that I purchased, and I was incredibly happy with the performance that I had from them.  However, after tweeting about the results, my friend Jose Chinchilla (Blog | Twitter) mentioned that I should also try out eSATA because it performed significantly better in his own tests.  Based on this tweet, I set out to my local MicroCenter computer store to purchase a eSATA enclosure and while I was buying it, one of the sales representatives, a guy named Chris, approached me and asked what I was planning to do with the enclosure because USB 3.0 should be faster for SSD’s. The numbers he quoted didn’t match my previous testing, so he made a recommendation for a specific USB 3.0 enclosure, and I figured, for $20 it was worth testing, so I bought an extra Hornettek Panther USB 3.0 device for comparison testing along with the Vantec NexStar CX eSATA enclosure I had already selected.

For the tests, I ran a short set of tests using SQLIO that I previously blogged about on my blog post about the Powershell parser for SQLIO output.  The results from the tests are below:

The two external USB 3.0 HDD drives had very similar performance characteristics, and they beat their USB 2.0 counterparts performance wise significantly.  I’ve been incredibly happy with my USB 3.0 HDDs overall for the last few years and I only purchase USB 3.0 HDD drives based on my performance tests a few years ago.  However, when compared with the USB 3.0 external enclosures with the SSD’s, the performance difference is quite significant.  One of the problems I’ve had lately is being able to fit all of the virtual machine hard drives, VHD’s for Hyper-V when using my dual boot Hyper-V host VHD, as well as my original VMware Workstation VMDK’s for classes, and even the VirtualBox virtual disk images (VDI) from my blog series on building a completely free playground.  Using the SSD’s with USB 3.0 definitely makes storing my virtual hard disk files on an external array much more feasible performance wise, and my only real limitation from testing is the size of the external device. 

However, the performance of the eSATA external enclosure is incredibly better, with the side trade off that I can only have 1 of them attached to my laptops at a time, and for my personal laptop, the fact that the eSATA enclosure requires not only the eSATA port for throughput, but also the additional USB 2.0 port for power really limits what I can do as far as having multiple disks connected to the laptop.  To be honest, this is something that I can live with given that I also have swapped out my CD/DVD ROM bay for a replacement New Mode US second HDD conversion. This means that I can have two 240GB OCZ SSD’s in my E5420, which is a similar configuration to the dual 256GB Dell SSD configuration in my Precision M6500 for work, while being able to move VM images between the two machines using my older 120GB SSD’s with fantastic performance.

If you are looking at really high performance external hard disk configurations for scalability, I would highly recommend looking at either eSATA with an extra SSD drive or if you need multiple devices and you have USB 3.0, consider going that route.  Either will outperform your existing options significantly.  For the time being, I am going to stick with one eSATA device, which is compatible with both of my laptops, and one USB 3.0 device, which only works with my M6500 at USB 3.0 speeds.  My E5420 only has USB 2.0 onboard, so it makes more sense for me to stick with eSATA for the main shared drive.