As you have most certainly heard elsewhere, SQL Server 2012 finally offers a version of Extended Events that is a viable alternative to SQL Trace, in terms of both better performance and event parity. There are other enhancements such as a usable UI in Management Studio – previously your only hope for this was Jonathan Kehayias' Extended Events Manager. There is also a great change related to permissions: in SQL Server 2012 you only need
ALTER ANY EVENT SESSION to create and manage Extended Event sessions (previously you needed
I came across a more subtle behavior change recently that made it look like my event session was dropping events. The change itself is not a secret, and in fact even after reading or hearing about this change multiple times (Jonathan reminded me that he told me about this change too), I still missed it in my initial troubleshooting since, at the time, it wasn't a change that I thought would affect me. Lo and behold…
In SQL Server 2012, your event session will only capture 1,000 events by default if it uses the
ring_buffer target (and 10,000 for
pair_matching). This is a change from 2008 / 2008 R2, where it was limited only by memory. (The change is mentioned almost in a footnote here, back in July 2011.) To override the default, you can use the
MAX_EVENTS_LIMIT setting – but note that this setting will not be recognized by SQL Server 2008 / 2008 R2, so if you have code that needs to work against multiple versions, you'll need to use a conditional.
The scenario I was working through was more complex than this, but to demonstrate this issue, let's assume a very simple use case for Extended Events: tracking who is modifying objects. There is a handy facility for this:
object_altered. We can see the description for this event from the following query:
SELECT description FROM sys.dm_xe_objects WHERE name = 'object_altered';
So, if an object is modified, say, 20 times, I would expect to pull 40 events. And this is exactly what happens in SQL Server 2008, 2008 R2 and 2012. The challenge comes when more than 500 modifications happen (leading to more than 1,000 events). In SQL Server 2008 and 2008 R2, we still capture all events. But SQL Server 2012 will drop some due to a change in the
ring_buffer target. To demonstrate, let's build a quick, sample event session that trades performance for prevention of losing events (note that this is not the set of options I would prescribe for any production system):
USE master; GO CREATE EVENT SESSION [XE_Alter] ON SERVER ADD EVENT sqlserver.object_altered ( ACTION (sqlserver.server_principal_name) WHERE (sqlserver.session_id = 78) -- change 78 to your current spid ) ADD TARGET package0.ring_buffer (SET MAX_MEMORY = 4096) WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS); ALTER EVENT SESSION [XE_Alter] ON SERVER STATE = START; GO
With the session started, in the same window, run the following script, which creates two procedures, and alters them in a loop.
CREATE PROCEDURE dbo.foo_x AS SELECT 1; GO CREATE PROCEDURE dbo.foo_y AS SELECT 1; GO ALTER PROCEDURE dbo.foo_x AS SELECT 2; GO 275 ALTER PROCEDURE dbo.foo_y AS SELECT 2; GO 275 DROP PROCEDURE dbo.foo_x, dbo.foo_y; GO
Now, let's pull the object name, and how many times each object was modified from the target, and drop the event session (be patient; on my system, this consistently takes about 40 seconds):
;WITH raw_data(t) AS ( SELECT CONVERT(XML, target_data) FROM sys.dm_xe_sessions AS s INNER JOIN sys.dm_xe_session_targets AS st ON s.[address] = st.event_session_address WHERE s.name = 'XE_Alter' AND st.target_name = 'ring_buffer' ), xml_data (ed) AS ( SELECT e.query('.') FROM raw_data CROSS APPLY t.nodes('RingBufferTarget/event') AS x(e) ) SELECT [object_name] = obj, event_count = COUNT(*) FROM ( SELECT --[login] = ed.value('(event/action[@name="server_principal_name"]/value)', 'nvarchar(128)'), obj = ed.value('(event/data[@name="object_name"]/value)', 'nvarchar(128)'), phase = ed.value('(event/data[@name="ddl_phase"]/text)', 'nvarchar(128)') FROM xml_data ) AS x WHERE phase = 'Commit' GROUP BY obj; GO DROP EVENT SESSION [XE_Alter] ON SERVER; GO
Results (which ignore exactly half of the 1,000 captured events, focusing on
Commit events only):
This shows that 50 commit events (100 events total) were dropped for
foo_x, and exactly 1,000 total events have been collected ((225 + 275) * 2)). SQL Server seems to arbitrarily decide which events to drop – in theory, if it were collecting 1,000 events and then stopping, I should have 275 events for
foo_x, and 225 for
foo_y, since I altered
foo_x first, and I shouldn't have hit the cap until after that loop was completed. But obviously there are some other mechanics at play here in how XEvents decides which events to keep and which events to throw away.
In any case, you can get around this by specifying a different value for
MAX_EVENTS_LIMIT in the
ADD TARGET portion of the code:
-- ... ADD TARGET package0.ring_buffer (SET MAX_MEMORY = 4096, MAX_EVENTS_LIMIT = 0) ------------------------------------------------------^^^^^^^^^^^^^^^^^^^^^^ -- ...
Note that 0 = unlimited, but you can specify any integer value. When we run our test above with the new setting, we see more accurate results, since no events were dropped:
As mentioned above, if you attempt to use this property when creating an event session against SQL Server 2008 / 2008 R2, you will get this error:
For target, "package0.ring_buffer", the customizable attribute, "MAX_EVENTS_LIMIT", does not exist.
So if you are doing any kind of code generation and want consistent behavior across versions, you'll have to check the version first, and only include the attribute for 2012 and above.
If you are upgrading from SQL Server 2008 / 2008 R2 to 2012, or have written Extended Events code that targets multiple versions, you should be aware of this behavior change and code accordingly. Otherwise you risk dropping events, even in situations where you would assume – and where previous behavior would imply – that dropped events were not possible. This isn't something tools like the Upgrade Advisor or Best Practices Analyzer are going to point out for you.
The underlying mechanics surrounding this problem are described in detail in this bug report and this blog post.
4 thoughts on “An important change to Extended Events in SQL Server 2012”
Thanks for the post. This is likely something I (and many others) would have missed…or at least, until we didn't capture what we expected to. :-)
Quick suggestion: perhaps the Intro or TL;DR section should explicitly state that only the ring_buffer target has a 1,000 event limit? Not all readers may click-through the XE blog link or read the BOL Targets DDL page, and "In SQL Server 2012, your event session will only capture 1,000 events by default." implies all target types.
Thanks Aaron. Great observation; I've updated the post for clarity.
Excellent post and something well worth knowing as Extended Events are going to be more and more used as time goes on. Just one thing to note; on my 2008 servers I'm not seeing any event named 'object_altered'. I'm not sure if it came with 2008 R2 or 2012?
Thanks Jonathan, completely guilty. In my excitement to get the information out there, I translated the event at the last possible step, after testing my original event session on both 2008 & 2012.
object_alteredwas indeed added in 2012, but you could demonstrate this change using any event session that uses
ring_bufferand actually works in both versions. Very sorry for any confusion.
Comments are closed.