Event Notifications vs Extended Events

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.

Providing attribution when blogging is an easy way to avoid plagiarism

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.

Understanding the sql_text Action in Extended Events

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.

image

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.