Why I hate the ring_buffer target in Extended Events

I’ve had so many questions about the same problem with the ring_buffer target in Extended Events lately that I figured I would write a blog post that explains all the information I teach about the ring_buffer target and the problems associated with it.  Since the release of SQL Server 2012, and the new UI for […]

Looking at read_microsec in sys.dm_os_buffer_descriptors in SQL Server 2012

Last week someone sent Kimberly an email asking what the read_microsec column in the sys.dm_os_buffer_descriptors DMV in SQL Server 2012 showed. The email was passed around our team and to be honest it was the first time I’d even heard of the column existing. The questioner also wanted to know if/how the read_microsec column be […]

Mapping wait types in dm_os_wait_stats to Extended Events

A few months back I received an email from a member of the community that was trying to filter the sqlos.wait_info event for some of the wait types that are filtered out by Glenn’s diagnostic queries, and to their dismay wasn’t able to find the specific wait types in the wait_types map in sys.dm_xe_map_values.  This […]

Logging Extended Events changes to the ERRORLOG

A question came up in class today about the difference between SQL Trace and Extended Events for logging information to the ERRORLOG file. Joe and I have both written about the observer overhead of Trace and Extended Events in the past (Observer Overhead and Wait Type Symptoms and Measuring “Observer Overhead” of SQL Trace vs. […]

SQL Server 2012 Extended Events Add-in Updates

Back in July I blogged about the SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances. Unfortunately, despite having multiple beta testers using the add-in before the release, I started getting reports of the add-in crashing Management Studio. A new version of the add-in is available for download (http://www.sqlskills.com/free-tools/sql-server-2012-extended-events-add-in/) that addresses this and one […]

Using ‘dbghelp.dll’ version ’4.0.5′ error fixed in SQL Server 2012 SP1 CU6

If you use Extended Events you may have noticed that the ERRORLOG file gets bloated with messages like: Using ‘dbghelp.dll’ version ’4.0.5′ every time you query sys.dm_xe_sessions or read a file using Transact-SQL and the sys.fn_xe_file_target_read_file() table-valued function. This issue can be especially problematic on SharePoint 2013 installations, where a timer job queries Extended Events […]

SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances

Extended Events are a powerful new way of troubleshooting problems with SQL Server, and the addition of UI support in SQL Server 2012 Management Studio has helped increase the awareness and usage of this feature. One short-coming is that the new UI for Extended Events only works for SQL Server 2012 instances leaving administrators that […]

Tracking SQL Server Database Usage

One of the challenges with inheriting an existing set of SQL Servers and databases when you start a new job can be determining which of the databases are actively being used or not, especially on development and testing systems where there may be multiple copies of the same database that were used by different projects […]

Graphically Viewing Extended Events Deadlock Graphs

I’ve previously blogged about the changes to the xml_deadlock_report in Extended Events to support multi-victim deadlock analysis.  One of the side effects of this change was that the XML document for the deadlock report had to be modified to be able to accommodate multiple victim processes in the graph.  As a result of this, the […]

New Article on SQLPerformance.com comparing “Observer Overhead” of Trace vs Extended Events

I have been so busy this week that I didn’t get a chance to blog about this yesterday when it happened, but I had a new article published on SQLPerformance.com that compares the performance impact or "observer overhead" of using SQL Trace and Extended Events.  I had a lot of fun running different benchmarks for […]

How useful are query_hash and query_plan_hash for troubleshooting?

After my last post, I was contacted by email about the usefulness of query_hash in actual troubleshooting scenarios since the query_hash value is based on the statement and not the database in a system.  It just happened that the example template from SQL Server 2012 that I showed had the query_hash included in it’s definition.  […]

Customizing Extended Events Templates in SQL Server 2012

One of the features of the Extended Events UI in SQL Server 2012 is the ability to export an event session definition out to a template for future reuse.  This can be accomplished through the right-click menu for an event session and then by selecting the Export Session menu item as shown in Figure 1. […]

Looking at Non-Updating Updates with Extended Events

For a few weeks now, there has been a long running thread on SQL Server Central about UPDATE operations that don't perform any changes because the statement sets a column to the same value as already exists in the row.  While I believe that one of the posters is incredibly snarky and outright rude to […]

Tracking Extended Events for a SQL Agent Job

This blog post is courtesy of a question I received from Chris Adkin (Twitter) by email.  Chris was trying to use Extended Events to track session level wait information, but he had a new twist that I’ve never been asked about before, he wanted to track all of the waits for a specific SQL Agent […]

New Article on Tracking Tempdb Contention in SQL Server 2012 with Extended Events

My latest article on Simple-Talk was published recently.  In this article I dig into how to track allocation bitmap contention in tempdb in SQL Server 2012 using Extended Events.  The method described in this article redefines how you troubleshoot this type of problem, leveraging the advanced diagnostic capabilities of Extended Events to track the problem. […]

Missing options in the SSMS Extended Events UI due to poor design

While I was writing about the new features of the Extended Events UI in SQL Server 2012, I realized that I hadn’t written about the TRACK_CAUSALITY session option in the New Session dialog.  I thought I was covering everything, but maybe I missed it so I went back and looked at my screenshots and then […]

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 […]

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 […]

Converting SQL Trace to Extended Events in SQL Server 2012

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 […]

Changes to Extended Events Permission Requirements in SQL Server 2012

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 […]

SQL Server 2012 Extended Events Update – 3 – Viewing Target Data

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 […]

Extended Events PerfObject Events

SQL Server 2008 R2 Service Pack 1 provides a new set of Events in Extended Events to collect performance counter data from the Windows OS that would be really useful to monitoring SQL Server.  The first place I can find that they were mentioned is on a blog post by Mark Weber, a PFE for […]

SQL Server 2012 Extended Events Update – 2 – The SSMS UI Part 2

In the first post in this series, SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface, we looked at how to use the New Session Wizard in SQL Server 2012 to define an Event Session.  In this post we’ll compare the Wizard to the standard New Event Session dialog that can […]

SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface

For the most part I have been relatively quiet about the coming changes in SQL Server 2012 with regards to Extended Events.  Primarily this has been to allow the new features of the product to become fully baked to ensure that the information would continue to be applicable as the product lifecycle progressed, and there […]

Does filing bugs on Microsoft Connect for SQL Server work?

In a word; YES! In a lot more words, not always in the way that we want it to, but there are plenty of cases where it actually works and changes are made to the product as a result. Now with that said, it doesn’t work all the time, and it helps to realize that […]

Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0

In SQL Server 2012 RC0 there are a number of event session templates provided that make creating a commonly used session easier using the Event Session Wizard in SQL Server Management Studio.  One of these has a bug in it’s definition XML file that was filed in the following connect item: https://connect.microsoft.com/SQLServer/feedback/details/705840/the-object-sqlserver-event-sequence-does-not-exist#tabs If you attempt […]

Tracking Problematic Pages Splits in SQL Server 2012 Extended Events – No Really This Time!

Just over a year ago I blogged about the enhancements that were made to the sqlserver.page_split Event in SQL Server 2012 to make it easier to identify what the splitting object was and the type of split that was being performed.  Sadly what I discovered writing that post was that even with the extra information […]

New AlwaysOn_health Extended Events Session in SQL Server 2012 RC0

While setting up my new Availability Group using SQL Server 2012 RC0 tonight, I noticed an interesting new addition to Extended Events associated with Availability Group configuration in the Release Candidate.  When you setup an Availability Group in RC0, another default Event Session is created on the servers that participate in the Availability Group to […]

Extended Events Changes in SQL Server 2012 – Event XML for XML data elements

While working on validating my demos for the 24 Hours of PASS and my PASS Summit 2011 Precon – Extended Events Deep Dive, I noticed a significant, and breaking change to the Event XML output for the raw event data in the ring_buffer and file_target in SQL Server Denali.  In SQL Server 2008 and 2008R2, […]

PASS Summit 2011 Sessions

Yesterday PASS announced the PASS Summit 2011 regular sessions and 4 new half-day sessions.  At the same time they also announced the top sessions from the SQLRally conference in Orlando, FL last month. I was thrilled to have been in the Top 5 for the conference overall. A few weeks ago PASS announced the Pre-conference […]

SQL Rally Presentation – Deadlocking for Mere Mortals

The first SQL Rally was held last week in Orlando, FL, and I had the honor of being selected for one of the spotlight sessions by the community in the DBA track.  SQL Rally was a different experience from the regular PASS Summit; it wasn’t anywhere as big as the normal summit, but it was […]

Looking at multiple data files and proportional fill with Extended Events

At SQL Connections, I presented a session titled “Learn SQL Server Internals with Extended Events” where I demonstrated a number ways to use Extended Events to learn about the internal workings of the database engine for SQL Server.  The morning of the session I was chatting with someone about a problem they had seen and […]

Does Index Fragmentation Matter with SSD’s?

Two weeks ago at SQL Connections in Orlando, FL, I got to participate in a session that Paul and Kimberly do at the end of the conference titled, “Follow the Rabbit.”  The premise of the session is that Paul and Kimberly throw a big list of topics up on the screen and anyone in the […]

What plan_handle is Extended Events sqlserver.plan_handle action returning?

The topic for today’s post comes from a forums question and a subsequent Connect feedback item where someone noted that the plan_handle being returned by Extended Events using the sqlserver.plan_handle action was not available in the plan cache even when queried immediately following completion of the event that should have produced the plan cache entry.  […]

Incorrect Timestamp on Events in Extended Events

Last week, Denny Cherry (Blog|Twitter) asked why the timestamp on the events he was collecting using Extended Events in SQL Server 2008 was incorrect.  I’ve seen this a couple of times on the MSDN Forums, and its come up a couple of times in discussions with other MVP’s about Extended Events.  According to a feedback […]

An XEvent a Day (31 of 31) – Event Session DDL Events

To close out this month’s series on Extended Events we’ll look at the DDL Events for the Event Session DDL operations, and how those can be used to track changes to Event Sessions and determine all of the possible outputs that could exist from an Extended Event Session.  One of my least favorite quirks about […]

An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits

While attending PASS Summit this year, I got the opportunity to hang out with Brent Ozar (Blog|Twitter) one afternoon while he did some work for Yanni Robel (Blog|Twitter).  After looking at the wait stats information, Brent pointed out some potential problem points, and based on that information I pulled up my code for my PASS […]

An XEvent a Day (29 of 31) – The Future – Looking at Database Startup in Denali

As I have said previously in this series, one of my favorite aspects of Extended Events is that it allows you to look at what is going on under the covers in SQL Server, at a level that has never previously been possible.  SQL Server Denali CTP1 includes a number of new Events that expand […]

An XEvent a Day (28 of 31) – Tracking Page Compression Operations

The Database Compression feature in SQL Server 2008 Enterprise Edition can provide some significant reductions in storage requirements for SQL Server databases, and in the right implementations and scenarios performance improvements as well.  There isn’t really a whole lot of information about the operations of database compression that is documented as being available in the […]

An XEvent a Day (27 of 31) – The Future – Tracking Page Splits in SQL Server Denali CTP1

Nearly two years ago Kalen Delaney blogged about Splitting a page into multiple pages, showing how page splits occur inside of SQL Server.  Following her blog post, Michael Zilberstein wrote a post, Monitoring Page Splits with Extended Events, that showed how to see the sqlserver.page_split Events using Extended Events.  Eladio Rincón also blogged about Using […]

An XEvent a Day (26 of 31) – Configuring Session Options

There are 7 Session level options that can be configured in Extended Events that affect the way an Event Session operates.  These options can impact performance and should be considered when configuring an Event Session.  I have made use of a few of these periodically throughout this months blog posts, and in today’s blog post […]

An XEvent a Day (25 of 31) – The Twelve Days of Christmas

In the spirit of today’s holiday, a couple of people have been posting SQL related renditions of holiday songs.  Tim Mitchell posted his 12 days of SQL Christmas, and Paul Randal and Kimberly Tripp went as far as to record themselves sing SQL Carols on their blog post Our Christmas Gift To You: Paul and […]

An XEvent a Day (24 of 31) – What is the callstack?

One of the actions inside of Extended Events is the package0.callstack and the only description provided by sys.dm_xe_objects for the object is 16-frame call stack.  If you look back at The system_health Session blog post, you’ll notice that the package0.callstack Action has been added to a number of the Events that the PSS team thought […]

An XEvent a Day (23 of 31) – How it Works – Multiple Transaction Log Files

While working on yesterday’s blog post The Future – fn_dblog() No More? Tracking Transaction Log Activity in Denali I did a quick Google search to find a specific blog post by Paul Randal to use it as a reference, and in the results returned another blog post titled, Investigating Multiple Transaction Log Files in SQL […]

An XEvent a Day (22 of 31) – The Future – fn_dblog() No More? Tracking Transaction Log Activity in Denali

I bet that made you look didn’t it?  Worry not, fn_dblog() still exists in SQL Server Denali, and I plan on using it to validate the information being returned by a new Event in SQL Server Denali CTP1, sqlerver.transaction_log, which brings with it the ability to correlate specific transaction log entries to the operations that […]

An XEvent a Day (20 of 31) – Mapping Extended Events to SQL Trace

One of the biggest problems that I had with getting into Extended Events was mapping the Events available in Extended Events to the Events that I knew from SQL Trace.  With so many Events to choose from in Extended Events, and a different organization of the Events, it is really easy to get lost when […]

An XEvent a Day (19 of 31) – Using Customizable Fields

Today’s post will be somewhat short, but we’ll look at Customizable Fields on Events in Extended Events and how they are used to collect additional information.  Customizable Fields generally represent information of potential interest that may be expensive to collect, and is therefore made available for collection if specified by the Event Session.  In SQL […]

An XEvent a Day (18 of 31) – A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 2)

In yesterday’s blog post A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1), we looked at what happens when we Backup a database in SQL Server.  Today, we are going to use the information we captured to perform some analysis of the Backup information in an attempt to find […]

An XEvent a Day (17 of 31) – A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)

Today’s post is a continuation of yesterday’s post How Many Checkpoints are Issued During a Full Backup? and the investigation of Database Engine Internals with Extended Events.  In today’s post we’ll look at how Backup’s work inside of SQL Server and how to track the throughput of Backup and Restore operations.  This post is not […]

An XEvent a Day (16 of 31) – How Many Checkpoints are Issued During a Full Backup?

This wasn’t my intended blog post for today, but last night a question came across #SQLHelp on Twitter from Varun (Twitter). #sqlhelp how many checkpoints are issued during a full backup? The question was answered by Robert Davis (Blog|Twitter) as: Just 1, at the very start. RT @1sql: #sqlhelp how many checkpoints are issued during […]

An XEvent a Day (15 of 31) – Tracking Ghost Cleanup

If you don’t know anything about Ghost Cleanup, I recommend highly that you go read Paul Randal’s blog posts Inside the Storage Engine: Ghost cleanup in depth, Ghost cleanup redux, and Turning off the ghost cleanup task for a performance gain.  To my knowledge Paul’s posts are the only things that cover Ghost Cleanup at […]

An XEvent a Day (14 of 31) – A Closer Look at Predicates

When working with SQL Trace, one of my biggest frustrations has been the limitations that exist in filtering.  Using sp_trace_setfilter to establish the filter criteria is a non-trivial task, and it falls short of being able to deliver complex filtering that is sometimes needed to simplify analysis.  Filtering of trace data was performed globally and […]

An XEvent a Day (13 of 31) – The system_health Session

Today’s post was originally planned for this coming weekend, but seems I’ve caught whatever bug my kids had over the weekend so I am changing up today’s blog post with one that is easier to cover and shorter.  If you’ve been running some of the queries from the posts in this series, you have no […]

An XEvent a Day (12 of 31) – Using the Extended Events SSMS Addin

The lack of SSMS support for Extended Events, coupled with the fact that a number of the existing Events in SQL Trace were not implemented in SQL Server 2008, has no doubt been a key factor in its slow adoption rate.  Since the release of SQL Server Denali CTP1, I have already seen a number […]

An XEvent a Day (11 of 31) – Targets Week – Using Multiple Targets to Debug Orphaned Transactions

Yesterday’s blog post Targets Week – etw_classic_sync_target covered the ETW integration that is built into Extended Events and how the etw_classic_sync_target can be used in conjunction with other ETW traces to provide troubleshooting at a level previously not possible with SQL Server.  In today’s post we’ll look at how to use multiple targets to simplify […]

An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target

Yesterday’s post, Targets Week – pair_matching, looked at the pair_matching Target in Extended Events and how it could be used to find unmatched Events.  Today’s post will cover the etw_classic_sync_target Target, which can be used to track Events starting in SQL Server, out to the Windows Server OS Kernel, and then back to the Event […]

An XEvent a Day (9 of 31) – Targets Week – pair_matching

Yesterday’s post, Targets Week – synchronous_event_counter, looked at the counter Target in Extended Events and how it could be used to determine the number of Events a Event Session will generate without actually incurring the cost to collect and store the Events.  Today’s post is coming late, I know, but sometimes that’s just how the […]

An XEvent a Day (8 of 31) – Targets Week – synchronous_event_counter

Yesterday’s post, Targets Week – Bucketizers, looked at the bucketizer Targets in Extended Events and how they can be used to simplify analysis and perform more targeted analysis based on their output.  Today’s post will be fairly short, by comparison to the previous posts, while we look at the synchronous_event_counter target, which can be used […]

An XEvent a Day (7 of 31) – Targets Week – bucketizers

Yesterday’s post, Targets Week – asynchronous_file_target, looked at the asynchronous_file_target Target in Extended Events and how it outputs the raw Event data in an XML document.  Continuing with Targets week today, we’ll look at the bucketizer targets in Extended Events which can be used to group Events based on the Event data that is being […]

An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target

Yesterday’s post, Targets Week – ring_buffer, looked at the ring_buffer Target in Extended Events and how it outputs the raw Event data in an XML document.  Today I’m going to go over the details of the other Target in Extended Events that captures raw Event data, the asynchronous_file_target. What is the asynchronous_file_target? The asynchronous_file_target holds […]

Extended Events Series (5 of 31) – Targets Week – ring_buffer

Yesterday’s post, Querying the Session Definition and Active Session DMV’s, showed how to find information about the Event Sessions that exist inside a SQL Server and how to find information about the Active Event Sessions that are running inside a SQL Server using the Session Definition and Active Session DMV’s.  With the background information now […]

Extended Events Series (4 of 31) – Querying the Session Definition and Active Session DMV’s

Yesterdays post, Managing Event Sessions, showed how to manage Event Sessions inside the Extended Events framework in SQL Server. In today's post, we’ll take a look at how to find information about the defined Event Sessions that already exist inside a SQL Server using the Session Definition DMV’s and how to find information about the […]

Extended Events Series (3 of 31) – Managing Event Sessions

Yesterdays post, Querying the Extended Events Metadata, showed how to discover the objects available for use in Extended Events.  In today's post, we’ll take a look at the DDL Commands that are used to create and manage Event Sessions based on the objects available in the system.  Like other objects inside of SQL Server, there […]

Extended Events Series (2 of 31) – Querying the Extended Events Metadata

In yesterdays post, An Overview of Extended Events, I provided some of the necessary background for Extended Events that you need to understand to begin working with Extended Events in SQL Server.  After receiving some private feedback on the initial post, I have changed the post naming convention associated with the post to reflect “2 […]

Extended Events Series (1 of 31) – An Overview of Extended Events

SQL Server Extended Events were first introduced in SQL Server 2008, and provided a new mechanism for capturing information about events inside the Database Engine that was both highly performant and highly configurable.  Designed from the ground up with performance as a primary focus, Extended Events may seem a bit odd at first look, especially […]

An XEvent A Day: 31 days of Extended Events

Back in April, Paul Randal (Blog|Twitter) did a 30 day series titled A SQL Server Myth a Day, where he covered a different myth about SQL Server every day of the month.  At the same time Glenn Berry (Blog|Twitter) did a 30 day series titled A DMV a Day, where he blogged about a different […]

TSQL Tuesday #11 – Physical IO’s Don’t Always Accumulate Wait Times

It is time again for another TSQL Tuesday, this time hosted by my good friend and fellow MVP, Sankar Reddy(Blog|Twitter).   This month’s topic is Misconceptions about SQL Server, and as Sankar points out in this months, there are so many misconceptions out there that almost anyone can blog about this topic, in fact I might […]

Extended Events xml_deadlock_report and Multi-Victim Deadlocks

At the beginning of the year, I wrote an article titled Retrieving Deadlock Graphs with SQL Server 2008 Extended Events that detailed how to use the default system_health session to retrieve deadlock graphs from SQL Server 2008 without having to use SQL Profiler, SQL Trace or enabling a Trace flag on the SQL Server.  In […]

Changes to the Deadlock Monitor for the Extended Events xml_deadlock_report and Multi-Victim Deadlocks

At the beginning of the year, I wrote an article titled Retrieving Deadlock Graphs with SQL Server 2008 Extended Events that detailed how to use the default system_health session to retrieve deadlock graphs from SQL Server 2008 without having to use SQL Profiler, SQL Trace or enabling a Trace flag on the SQL Server.  In […]