Finding File Growth

Andy Galbraith shows how to use extended events to track file growth events:

…but what does this have to do with Extended Events?

As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.

The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session…

Fortunately, you can create your own XEvent and Andy gives us the details.

Starting Extended Events Is Just As Fast

Erin Stellato shows she can create an Extended Event as fast as a Profiler trace:

I haven’t gotten a ton of comments, but I did get a few (thank you to those have responded!), and I decided to take one of them and create a Trace and create an Extended Events session and see how long it took for each.  Jonathan has mentioned before that he can create an XE session as fast as a Trace, and I’ve been thinking that I can as well so I thought I’d test it.  It’s a straight-forward Trace versus Extended Events test.  Want to see what’s faster?  Watch the video here.

I love the “I would pop up the timer on the screen but I don’t know how to do that” bit; very Friday afternoonish.

How Do You Trace?

Erin Stellato wants to know how you use Profiler and server-side traces:

Back in April I wrote a post asking why people tend to avoid Extended Events.  Many of you provided feedback, which I greatly appreciated.  A common theme in the responses was time.  Many people found that creating an event session in Extended Events that was comparable to one they would create in Trace took longer.  Ok, I get that…so as a follow up I am interested in knowing how you typically use Profiler and Trace.  If you would be willing to share that information, I would be extremely grateful.

Head over there and let her know.  For science!

Faster Extended Events Reader

The CSS SQL Server Engineers note that with SQL Server 2016, we’ll get faster Extended Events readers:

SQL Server 2016 improves the XEvent Linq reader scalability and performance.    The XEvent UI in SQL Server Management Studio uses the XEvent Linq reader to process the events for display.   Careful study of the XEvent Linq reader revealed opportunities for scalability and performance improvements.

I don’t know if this will push anyone in the direction of using Extended Events who isn’t already doing so, but I like the performance improvement here.

New Columnstore Extended Events

Niko Neugebauer talks about extended events relating to columnstore indexes in SQL Server 2016:

In SQL Server 2014 we have had 18 Extended Events and with Service Pack 1 we have received 1 more to be a total of 19 Extended Events for studying the Columnstore Indexes and the Batch Mode processing. In SQL Server 2016 that number has been greatly increased – there are whooping 61 Extended Events, that will give us an important insight into the Columnstore Indexes.

Even more important, Sunil & his team have given an own category inside the Extended Events – a category that is named Columnstore, which will ease the search for the basic columnstore events. Be aware though not all Extended Events related to Columnstore Indexes are included in that category – even including all channels will give you 41 Extended Events, while hiding the other 20 Extended Events, which are sometimes not categorised at all and at other times are stored under different categories, such as Execution or Error, for example. I believe the reason behind not changing the old Extended Events category is quite simple – Microsoft always looks for avoiding breaking existing applications.

There’s a lot here to digest, so read the whole thing.

Extended Events In Azure SQL Database

Julie Koesmarno walks through Extended Events in Azure SQL Database (currently in preview):

Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.

It’s nice to see Extended Events making their way into Azure SQL Database.

Transitioning To Extended Events

Dave Mason shares his story of moving from Profiler traces to Extended Events:

Thinking back, my initial reaction was probably something like “I don’t get it.”. XEvents just seemed like a new dog performing an old trick. Even though I concluded most of the SQL pros had already made the transition to Extended Events (XEvents), and that I’d be living in the past if I didn’t do the same, I continued to use Profiler. It’s too bad–my current employer at the time had SQL 2012 across the board. I missed a great opportunity there. Before I left that job, I stumbled upon this Paul Randal post. It helped me trouble-shoot a performance issue with a stored proc. “Ok, so there’s one thing XEvents can do that Profiler can’t do” I thought to myself. (Note: I don’t know if that’s a true sentiment or not–I never investigated further.) I also recall reading at least one of Jonathon Kehayias’ (b|t) posts about XEvents, but I don’t remember which one. Profiler would remain my go-to tool for the remainder of my time with that employer.

Read this in conjunction with Erin’s request.

Why Do You Avoid Extended Events?

Erin Stellato wants to know why you’re still using Profiler over Extended Events:

Extended Events *is* the replacement for Profiler/Trace; it’s not going away.  I really want people to be prepared for the time when Profiler and Trace are removed from the product.  And I want to provide feedback to the SQL Server product team to address limitations that people find in Extended Events.  If the feature is lacking something, we need to work together to create change.

Thanks in advance for your help, and if you haven’t tried XE, or are looking for a refresher, then please attend my webinar next Tuesday, April 5th at 12PM EDT: Kicking and Screaming: Replacing Profiler with Extended Events.  I’d love to see you there and can help get you started with XE!

Leave a comment on Erin’s post and let her know.

Autostart XE With Powershell

Rob Sewell shows us how to set a particular extended event to start automatically when SQL Server starts up:

Very quick and simple and hopefully of use to people, this could easily be turned into a function. The full script is below and also available here on the Powershell gallery or by running  Save-Script -Name Set-ExtendedEventsSessionstoAutoStart -Path <path>

This is indeed a quick and easy script, and quite useful when checking across a large number of instances.

Memory Pressure

Thomas Rushton walks us through determining if there’s memory pressure on an instance:

If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.

This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?

More importantly, he shows us how bad the situation is:  is this something that happened for a couple of seconds, or is it persistent?  This is a great walkthrough.

Categories

August 2017
MTWTFSS
« Jul  
 123456
78910111213
14151617181920
21222324252627
28293031