Press "Enter" to skip to content

Category: Extended Events

How Do You Trace?

Published 2016-06-03 by Kevin Feasel

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!

Comments closed

Faster Extended Events Reader

Published 2016-05-20 by Kevin Feasel

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.

Comments closed

New Columnstore Extended Events

Published 2016-04-19 by Kevin Feasel

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.

Comments closed

Extended Events In Azure SQL Database

Published 2016-04-12 by Kevin Feasel

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.

Comments closed

Transitioning To Extended Events

Published 2016-04-04 by Kevin Feasel

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.

Comments closed

Why Do You Avoid Extended Events?

Published 2016-04-04 by Kevin Feasel

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.

Comments closed

Autostart XE With Powershell

Published 2016-03-28 by Kevin Feasel

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.

Comments closed

Memory Pressure

Published 2016-03-28 by Kevin Feasel

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.

Comments closed

Index Cannot Be Reorganized

Published 2016-03-21 by Kevin Feasel

Jason Brimhall digs into an error where page-level locking is disabled:

You receive the error message similar to the following:

Msg 2552, Level 16, State 1, Line 1 The index “blah” (partition 1) on table “blah_blah_blah” cannot be reorganized because page level locking is disabled

Immediately, you start double-checking yourself and verifying that it worked the previous night. You even go so far as to confirm that the same index was previously reorganized. How is it possible that it is failing now on this index. What has changed? Has something changed?

There’s an interesting troubleshooting story, but the important message is about setting up a good set of Extended Events so that you can troubleshoot these types of problems.

Comments closed

Capturing Blocking Information

Published 2016-01-04 by Kevin Feasel

Erin Stellato shows us how to capture details when processes are blocked:

To view the output from extended events you can open the .xel file in Management Studio or query the data using the sys.fn_xe_file_target_read_file function. I typically prefer the UI, but there’s currently no great way to copy the blocking report text and view it in the format you’re used to.  But if you use the function to read and parse the XML from the file, you can…

If you can’t buy a tool which monitors long-term blocking, you can still build it yourself pretty easily.

Comments closed