Failed to Create the Audit File

Categories: News, Professional, SSC
Comments: No Comments
Published on: December 31, 2017

One day while checking things for clients, I happened across a fun little error message – “SQL Server Audit failed to create the audit file“. It just so happens that the audit had been working and then suddenly stopped and started flooding the error logs with this message.

Why would it suddenly stop working? Well, it says in the error that the disk might be full or that there may be a permissions issue. So, at least there are some possibilities provided by the message. Granted – neither of these options is very settling for a DBA.

FAILED

While reading the first occurrence of this error message, you notice that the log is bombarded with 20 more messages of the same nature. Before you can even scroll to catch up with the error messages, another 30 have appeared. It looks like the server is starting to get busier with the business users starting to run through their daily routines. You need a fix and you need it quick. You copy and paste the error to another screen for reference and then close the log to remove that distraction. Here is a copy of that error message.

SQL Server Audit failed to create the audit file ‘C:\Database\XE\DBA_Server_Audit_906B13C3-8F3F-4CFC-A391-20C5F7CAD698.sqlaudit’. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file.

Let’s try the suggestions from the error message starting with security. The SQL Server Service account needs to have permissions to the directory where the audit is being stored. Looking in that directory, you can see that the audit was obviously successful at one point because there are audit files in the directory. In addition, the audit just barely stopped working and you are certain nothing has changed.

Regardless of that, you proceed to investigate the permissions settings on the directory. Looking at the directory permissions, you are able to fully confirm that the service account does indeed have adequate permissions to the folder.

So we can rule out the permissions having changed as being a viable contender for causing this problem. As you start to proceed to investigate the next option, you start to worry that users are being prevented from doing their jobs because of the flood of errors. For sanity sake, you run a few quick checks to verify things look normal on the server from an activity standpoint. You also check your ticket queues and find there is nothing alarming in there.

Phwew!

Still Trying

After checking the ticket queues and server activity, you bounce right back to your next check point – disk space. This is an easier check than the permissions. You have no mount points and you can verify the disk space with a quick glance in windows explorer. You look in windows explorer and can see that your C drive where the audits are being stored has 50{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170} free space (or roughly 200GB).

Well, that is obviously not the problem either. You know the audit was working as recently as 30 minutes before you started troubleshooting and the errors did not start until almost immediately before you started checking the problem. What could it be? Afterall, you have 35 audit files in the audit folder for the trace. Then, suddenly, it hits you. There are 35 files. The trace was configured for 35 files with no rollover.

In this case, the easy fix is to move a bunch of files to an archive folder. As soon as that is done, a new message will appear in the error logs:

Message
Audit: Server Audit: 65536, State changed from: TARGET_CREATION_FAILED to: STARTED

Now to go change the audit process to make it a little more robust.

When dealing with SQL Audit, max_files is an important setting. Here is what msdn has to say about the setting.

MAX_FILES =integer

Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

This article is just one of several audit related articles on this blog. You can read more about some of the different ways to audit along with different perspectives from this link.

Wrap

Auditing is a necessity but it doesn’t need to be alarming or scary. Sometimes, we can become a little bit alarmed when an error occurs. We just need to keep our cool and trust our skills and abilities to troubleshoot in the event an audit fails. This article will hopefully show some of that process and help to provide a cool demeanor.

An Introduction to Templates

Comments: No Comments
Published on: December 30, 2017

Templates are a powerful tool in so many trades and crafts. From decals and stickers all the way up to the largest cruise ships in the world, templates can be found everywhere in just about everything that do.

In SQL Server, templates are readily available for your use in so many different ways that I am sure we are unaware of most of them.

Templates

A good example of a template in SQL Server, that we clamor about frequently, is the unattended install. I am sure you have probably used that kind of template as you work to save yourself some hair and brain cells trying to remember the exact setup needed for your environment. With an unattended install, you build it once and then use the script to build all future installs.

Even though we may not call the unattended install script a template, it is. Think about the definition(s) of a template for a minute:

  1. something that serves as a model for others to copy.
  2. a preset format for a document or file, used so that the format does not have to be recreated each time it is used.

Looking at this definition, we can easily see that the unattended install routine fits perfectly as a “template”. This is just an example of the availability of templates within SQL Server. There are many different kinds of templates – many we probably don’t ever think about as being “templates” and many that we just flat out don’t bother to think about at all.

I have one specific kind of template in mind that I want to introduce and discuss. This template type comes with Extended Events. I actually introduced the concept very briefly in a recent post about the XE Profiler feature. That article is just one of many articles about Extended Events that I have written. You are welcome to catch up on the series of articles here.

XE

Extended Events has had templates available for quite some time. If you recall from the article about XE Profiler, templates were not introduced as a part of the XE Profiler feature – the templates were introduced quite some time prior to that.

There are really three distinct areas of focus for templates that I would like to discuss. I have them somewhat illustrated in this next screenshot.

In this image, you will see that I have three color coded regions. In green, I have the option to create a template from file. In blue, I have the XE specific templates that have no correlation to profiler. And then in red I have the templates that were created to simulate the behavior of the Profiler based templates while providing some sort of comfort zone for the profiler fanatics out there.

Today, I will focus on the templates that are readily available (red and blue zones). In a future article, I will cover the creation of a custom template (green zone). That said, this is not going to be a traditional article with the technical geekery showing the ins and outs of all of those templates. Oh no, this article will be an entirely different flavor of geekery. Today, I will be focusing on something that is more about fun. I will be exploring the available templates strictly via script (no XE GUI).

Exploratory Surgery

If you are like me, you sometimes just don’t care to pick up the mouse to browse the file system or even browse the SSMS hierarchy to find the exact screen that has some specific information that you want. For me, I ran into this when thinking about the templates in XE. What templates are there in XE again? Crap, now I have to open the GUI to figure that out. I wanted a quick way to be able to look at all of the templates – and not just the templates available to me in the version of SSMS that I am using, I want to see everything out there on the system.

The question is how to do this. The templates are not listed in any system table or view that I could find. The list of templates is specific to the version of SSMS that you are using and the templates are all xml files stored in a directory on the OS. A thought occurred to me while contemplating this dilemma – can I maybe use the registry?

The registry does give me some options so maybe, I can use xp_regread. I won’t be able to use xp_instance_regread since this is not a registry key that appears at the instance level, rather it is an SSMS related reg string. One problem that comes to mind is that I would need to find the SQLPath for each of the versions of SSMS installed (red arrows indicate each of the registry strings I might have to query). I start to think to myself after realizing this that I do not like the looks of this option – but it could be done.

I then thought of another option before going too far down the registry route. When I install an application, that application may modify my “Path” environment variable for the OS. This sounds a little promising. I still have some of the drawback with multiple versions of SSMS installed – but I think I can manage that more easily from the “Path” environment variable.

Let’s take a crack at a script.

Ouch, my eyes hurt already! What have I done here? I have written multiple loops. Never fear! Loops are not all evil. Sometimes, it makes sense to use a looping mechanism. This is one of those times where it makes some sense. This script is a limited use script for the eyes of the DBA only.

I can hear the moans about the use of xp_cmdshell and xp_dirtree and so forth. I am not listening to those grumbles. Again, this script is for fun and on my machine. The discussion on securing the server and xp_cmdshell is a discussion for another time.

In this script, I use the path variable to determine all of the flavors of SSMS that have been installed. Since the path for SSMS is all the way to the BINN folder, I need to do a little replace to get the correct paths for the xe templates. I also added a bit of a union in there because I had to duplicate the results – sorta. You see there are templates for AzureDB that, depending on your Azure Subscription or lack thereof, you may never see. I wanted to include those in my result set!

Looking at these results on a machine with SSMS 14 and SSMS 17, I would see something like the following.

I highlighted a few different sections of the results to help quickly show some of the templates available between different SSMS versions as well as those for Azure DB versus a local instance of SQL Server. Things are a tad different if we have SSMS 16 installed. Here is a sample result from a machine with SSMS 16 installed.

The main point of interest here (at least for me) is the removal of a template from SSMS 16 to SSMS 17. This is probably a template you don’t necessarily want to run on a production system and that is likely why it is gone from the available templates that get installed.

The Wrap

I am sure you can probably find an alternate routine to query these templates via TSQL that may be more efficient. I would be interested to hear about it.

What’s next now that I can get these templates? Well, a thought occurred to me to use this type of routine to create a means to use TSQL to read one of those template files to create the XE session from pure TSQL statements and eliminate the need to use the GUI. I think that would be a lot of TSQL/XE Geekery based fun.

Audit Domain Group and User Permissions

Comments: 3 Comments
Published on: December 29, 2017

No matter how simple the task or how versed we are with doing a security audit, it seems like we can always stand to learn just a little bit more. No matter how many times we hand an audit report over to the auditor, there is always “just one” more report we have to provide.

I know it seems like I am almost always running some sort of audit report. Whether it is to audit which user may have changed something or what access a user may have overall to the system, there is always a need for more audit.

One of the aspects of an audit that I have not yet written about is with regards to Domain Groups. It is a very common practice to grant access to SQL Server via Domain Group membership. We can easily see which groups have which access, but how often do we try to figure out who is in the group?

When you do try to figure out who is in a Domain Group, do you do like so many DBAs that I know and yell across a cubicle wall to the AD guys and ask them to look it up? Maybe you fire off an email to the AD Admins and ask them to do the grunt work for you. Nobody will shame you for doing either of those two methods. I mean, most people probably toss it over the fence in one way or another.

Fence

 

It is not a difficult stretch to imagine asking somebody who is an expert at a technology to see if they could get a quick answer to your question for you. That said, it is also really nice when you can service those types of questions by yourself and thus save everybody a little time and effort. Luckily, there is at least one easy way for the SQL Server professional to try and answer questions about group membership.

Microsoft has included a method for the DBA to try and capture information about AD related accounts. This method comes in the form of an extended procedure called xp_logininfo. This procedure can tell me the members of domain groups, the access level the account has, and also validate if the account is still active in AD.

Suppose you needed to determine all of the accounts that may have access to SQL Server, via direct Login or via group membership. In addition, you must find out which of the accounts are no longer valid in AD. I have a script that will do just that by first perusing the groups and then cycling through each of the User accounts produced from the group perusal.

Let’s take a look at that script.

You will see here that I have a couple of cursors built into this script. This is helping me cycle through first the groups and then the users. As I cycle through each user, I am able to determine the validity of the account. Not only will I know if an AD account is dead and needs to be removed from SQL Server, but I will also know if a Domain Group no longer exists.

This little script has proven to be a major time saver with some clients. Not only has it saved me some time, but it has also helped to figure out how a developer was able to perform sysadmin functions on a prod box even though his account did not have sysadmin access granted explicitly.

Correlate Trace and XE Events

Comments: No Comments
Published on: December 28, 2017

Several years back, when Extended Events was brand new, it was downright painful to try and convert the classic Profiler or Server Side trace to something meaningful and useful within Extended Events.

That was years ago and really is ancient history now. Microsoft has provided a couple of maps to help us translate the petroglyphs of Profiler into the high tech, smooth running, efficient engine and language of XEvents. This article is going to demonstrate a quick correlation between events in these two tools.

Maps

If you have browsed XEvents to any extent you should probably be familiar with at least one map object that Microsoft has given us. That particular object is sys.dm_xe_map_values and I wrote a bit about it here. That object presents some good information about various data values within XEvents that are delivered with the Event payload as well as some hidden gems that can be extra useful for exploring SQL Server internals. The point is, maps is not necessarily a new concept.

While the concept of a map is not new within XEvents, the implementation in this case is a tad different. You see, the dm_xe_map_values object is more like an EAV object while the map I will introduce today is more of an ordinary lookup table. Let’s take a look.

The map I want to concentrate on for now is the sys.trace_xe_event_map table. You read that correctly – it is a table. We don’t have very many “tables” when dealing with XEvents, but this one happens to be. As Microsoft Docs describes it, the table “contains one row for each Extended Events event that is mapped to a SQL Trace event class.

And to show that it is indeed a table, you can run a check like the following.

So this table contains a mapping between Extended Event events and SQL Trace events. If we are curious to see that mapping, we can do a simple query and see the trace_event_id and the XE event name along with the XE package that is related to that specific event. That said, most of us do not have the trace_event_id memorized to human friendly terms so we need to do a little bit more. That is fine because it is possible for us to also see the relationship between those trace ids and the trace event names. Let’s get a little more creative with the query.

Before doing that though, I do want to lay out some  baseline info. There are 180 trace events and of those there are 139 mappings in the map table between SQL trace and XE. This tells us immediately that there is not a one-to-one mapping. What this does not divulge just yet is that there may be a many-to-one mapping (ok, there is a single many-to-one mapping). We will look a bit at the differences in mappings.

And the results will look something like the following.

In the previous image, I highlighted three areas in different colors. First, note the red box surrounding the row count output in my results. Recall that I said there were only 180 events and only 139 rows in the map table. Clearly, we have a map that has a many-to-one relationship.

Next up is the blue box. The only thing we can see here is  that there is a trace event with no mapping to an Extended Event. We expected this result given there were only 139 maps. Of interest here is that most of the unmapped events are Audit related. While SQL Audit relies heavily on the XE Engine, the events from Trace do not map directly to events in XEvents.

The last call-out is the green box. This is there to show the clear mapping between trace events and XEvents events. For the most part, this has not changed clear through SQL Server 2017. If you will notice, I have included the product version, dll file name and the dll version info. That version info helps to underscore what has changed with SQL Trace which is practically nothing.

This brings us to the question on everybody’s mind: What is the many-to-one map? That is an easy answer but the explanation is not quite as easy.

Running that query, I will find that event_id 165 has more than one mapping in the map table. Let’s see what that translates to in human terms.

And this is what it looks like:

So, “Performance Statistics” happens to be the event that maps to multiple different XEvent events. That said, you may wonder why “query_pre_execution_showplan” doesn’t map to something like “Showplan XML” or maybe “Showplan All” which produce the execution plan in similar fashion. I will leave that mystery to you as your deep dive homework assignment.

The Wrap

If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

Message in a Bottle of XE

Comments: 1 Comment
Published on: December 27, 2017

One of the age old features that most probably take for granted in SQL Server happens to be the error messages. What? How can a message be a feature, right?

Well, I concede. Maybe calling it a feature may be too much. Then again, consider it for just a touch longer. Traditionally, SQL Server stores all of the messages pertinent to SQL Server and you can see all of them view the sys.messages catalog view. In addition to that, you can also add custom messages for your specific liking and environment. That last piece is a somewhat important concept in regards to calling “messages” a feature.

So, considering this “feature” for everything that is SQL Server related, does this also mean that all Extended Events related messages are accessible in sys.messages too? Afterall, Extended Events is a SQL Server feature too, right? And, we do occasionally see errors and warning messages in relation to Extended Events.

Messages

Let’s go ahead and try to test the theory that XE related messages are accessible to view inside of SQL Server. The first step will be to try and find those messages in the traditional location – sys.messages.

Let’s start with a sample session that will throw an error due to an invalid disk path (I don’t have a Z drive).

If I try to execute that script, I will receive the following message.

Msg 25641, Level 16, State 0, Line 8
For target, “package0.event_file”, the parameter “filename” passed is invalid. Target parameter at index 0 is invalid

Let’s take note of the message number – 25641. I will use that for exploration in the next step. With the message text and ID in hand, I can try and query the sys.messages catalog view to try and determine if this XE related message can be found there.

Running the preceding query will yield the following result.

Obviously that message text is different than what we saw in SSMS when trying to create the invalid event session. The difference in the text can be partly explained away due to the parameters. That said, there is an extra part of the error message that is not showing in sys.messages. Is this the correct message? Did XE do something to maybe append an additional message to this one from sys.messages? Let’s find out.

XE Messages

If I run this next query, I discover something that may be a bit unexpected.

I have discussed most of the other types in my 60 day series, but the message type has been neglected by me so far. That is a neglect I am looking to rectify right here. Let’s see what this message type might actually hold for us. Let’s get a little fancier with this next query to try and discover what we have in the message “type” in XE.

And a sample output from that:

As luck would have it, there are 82 messages for XE that are registered as “objects” within XE in SQL Server 2014 (and 84 in SQL Server 2017). That said, none of them have a message id tied to the message within the XE views or DMVs. So, if I need to try and correlate messages between the two sources, it becomes a little hairier. Now seems like a good time to try this hairy approach.

Scary Hair

Ok, so the query is not actually all that scary. That said, it is not likely to yield very many results due to the issues I mentioned already – the messages don’t entirely match up. So, what can I do instead to try and find these messages? Well, let’s change things up a bit and work off the presumption that the XE engine has added a message to the message 25641 and what we have is two different messages instead of just one. We already have the message for 25641. If we take the difference in that message with what was written to the screen in SSMS, we can do a bit of a search for that term.

Let’s try the following now.

This query is actually two queries. There is one to check the sys.messages view as well as a query to check the messages available via Extended Events. Instead of checking for the full text of the message, I changed it to just a few key words. As it turns out, there are few results that match real well the messages I received. Here are my results.

There we have both of the messages that comprise that single error message received when trying to create that event session with an incorrect file path.

Wrapping up

There we have it, Extended Events will throw a message that does contain messages from sys.messages as well as some additional custom messages in the XE metadata. Unfortunately, there is no way that I have been able to find to create custom XE messages. Quite frankly, I really don’t see a very good reason to create a custom XE message either.

There is a world of depth and knowledge within Extended Events. There is plenty to learn and plenty of ability to monitor and investigate the SQL Servers under our purview. I recommend you take a moment to dive deeper into this great feature of Extended Events.

12 Days Of Christmas and SQL

Categories: News, Professional, SSC
Comments: No Comments
Published on: December 26, 2017

One of my all-time favorite times of the year happens to be the Christmas Season. I enjoy the season because it is supposed to remind us to try and be better people. And for me, it does help. In all honesty, it should be a better effort year round, but this is a good time of year to try and get back on track and to try and focus more on other more important things.

For me, one of the more important things is to try and help others. Focusing on other people and their needs helps them but also helps one’s self. It is because of the focus on others that I enjoy, not just Christmas Day, but also the 12 Days of Christmas.

The 12 Days of Christmas is about giving for 12 Days. Though, in this day and age, most view it as a span of 12 Days in which they are entitled to receive gifts. If we are giving for a mere 12 Days and not focusing on receiving, then wouldn’t we all be just a little bit happier? I know that when I focus more on the giving I am certainly happier.

Giving

In the spirit of the 12 Days of Christmas and Giving, I have a 12 Day series that I generally try to do each Holiday Season. The series will generally begin on Christmas day to align with the actual 12 Days of Christmas (rather than the adopted tradition of ending on Christmas). This also means that the series will generally end on the celebration of “Twelfth Night” which is January 5th.

Each annual series will include several articles about SQL Server and have a higher goal of trying to learn something more about SQL Server. Some articles may be deep technical dives, while others may prove to be more utilitarian with a script or some functionality that can be quickly put to use and frequently used. Other articles may just be for fun. In all, there will be several articles which I hope will bring some level of use for those that read while they strive to become better at this thing called SQL Server.

This page will serve as a landing page for each of the annual series and will be updated as new articles are added.

2017

  1. XE Permissions – 25 December 2017
  2. Best New(ish) SSMS Feature – 26 December 2017
  3. XE System Messages – 27 December 2017
  4. Correlate Trace and XE Events – 28 December 2017
  5. Audit Domain Group and User Permissions – 29 December 2017
  6. An Introduction to Templates – 30 December 2017
  7. Failed to Create the Audit File – 31 December 2017
  8. Correlate SQL Trace and Actions – 1 January 2018
  9. Dynamics AX Event Session – 2 January 2018
  10. Sharepoint Diagnostics and XE – 3 January 2018
  11. Change Default Logs Directory – 4 January 2018
  12. Common Tempdb Trace Flags – Back to Basics (Day of Feast) – 5 January 2018

2015

  1. Failed – 25 December 2015
  2. Failed – 26 December 2015
  3. Failed – 27 December 2015
  4. Failed – 28 December 2015
  5. Failed – 29 December 2015
  6. Log Files from Different Source – 30 December 2015
  7. Customize XEvent Log Display – 31 December 2015
  8. Filtering Logged Data – 1 January 2016
  9. Hidden GUI Gems – 2 January 2016
  10. Failed – 3 January 2016
  11. Failed – 4 January 2016
  12. A Day in the Stream – 5 January 2016

2013

  1. Las Vegas Invite – 25 December 2013
  2. SAN Outage – 26 December 2013
  3. Peer to Peer Replication – 27 December 2013
  4. Broken Broker – 28 December 2013
  5. Peer Identity – 29 December 2013
  6. Lost in Space – 30 December 2013
  7. Command N Conquer – 31 December 2013
  8. Ring in the New Year – 1 January 2014
  9. Queries Going Boom – 2 January 2014
  10. Retention of XE Session Data in a Table – 3 January 2014
  11. Purging syspolicy – 4 January 2014
  12. High CPU and Bloat in Distribution – 5 January 2014

2012 (pre-Christmas)

  1. Maint Plan Logs – 13 December 2012
  2. Service Broker Out of Control – 14 December 2012
  3. Backup, Job and Mail History Cleanup – 15 December 2012
  4. Exercise for msdb – 16 December 2012
  5. Table Compression – 17 December 2012
  6. Maintenance Plan Gravage – 18 December 2012
  7. Runaway Jobs – 19 December 2012
  8. SSRS Schedules – 20 December 2012
  9. Death and Destruction, err Deadlocks – 21 December 2012
  10. Virtual Storage – 22 December 2012
  11. Domain Setup – 23 December 2012
  12. SQL Cluster on Virtual Box – 24 December 2012

Best New(ish) SSMS Feature

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: December 26, 2017

We all probably use SQL Server Management Studio (SSMS) on a very frequent basis. Just for giggles, lets just say that means we use it at least once a day. I think it is safe to say, we have all been wanting to see something cool released for SSMS for a very long time.

That is probably one of the big reasons for various other third party offerings and people bouncing from IDE to IDE or Management Tool to Management Tool. They just weren’t getting what they really wanted out of SSMS.

A couple of months ago, while sitting in a session about Service Broker, I saw the weirdest thing in the SSMS interface and was awe struck. Quickly I turned to my trusty magic blue box to google what it was that I was seeing. It turns out I had been living under a rock apparently because this was not a new feature. Rather, it had been out for multiple releases (at that time) of SSMS.

What was that gob smacking feature that I saw? That feature was (and still is) the scrollbar map. What is the scrollbar map, you ask? I will share that with you!

Scrollbar Map

The scrollbar map is this neat little replacement for the traditional scrollbar. The map gives a miniature code map in place of the scrollbar so you can quickly see the rough gist of your code in the scrollbar. Here is what it looks like.

In the preceding image, I have a segment of code active on my screen in SSMS. On the right of that, in place of the traditional scrollbar, I see a representation of the entire script in super tiny print. The active portion of my script is “highlighted” by a box on the map. Just like any scrollbar, I can slide it to any point in the script easily – or I can click on a spot in the map and be moved to that spot in the script.

So, when did this feature come about? After-all, It seemed brand new to me back in the beginning of November and many have said it was a part of SSMS 17.3. The scrollbar map has actually been around since SSMS 16.

Accessing and setting up the map is pretty easy. You can either right click the scrollbar and select “scrollbar options” or you can navigate the tools menu to options and follow the tree like shown in the preceding image. In the preceding image, I am showing my current configuration for the map. These are my preferences and I do recommend that both be enabled. That said, the choice is yours.

The second option: “Show Preview Tooltip” will look something like this.

In this image, I have my mouse hovering over a segment of code in the map. As I hover, a little preview box pops up to the left of the map (outlined in a red box in my image).

Enabling the scrollbar map has saved me oodles of time – even without the preview option enabled. I can much more quickly hop to different segments of code with this feature than I could if trying to scroll up and down trying to find that one little section I need.

Try it out. Play with the map. I think if you spend a lot of time bouncing around in TSQL scripts, you will find that this feature can and will save you time too.

Extended Events Permissions

Comments: 1 Comment
Published on: December 25, 2017

A common question that I am asked about Extended Events relates to what kind of permissions are required in order to use this awesome feature. In this article, I am going to compare and contrast the permissions required for Profiler as well as Extended Events.

Extended Events changed a lot with how to monitor your server. One of those changes is a slightly different set of permissions to be able to perform various different tasks. Before I dive into what the security landscape is with XEvents, let’s take a peek at what we have to do in order to be able to use Profiler.

Profiler

If you try to launch a profiler session as a standard login with what should be a typical user type account with restricted access, you will encounter something like the following.

Yes, according to the message you need to be a sysadmin (nobody reads past that first part so they always miss the alternative). So, let’s test things out. I will create a login called TraceUser and attempt to do tasks related to running a Profiler or Server Side type of trace. I will also use this same login when testing the permissions for Extended Events.

I included a quick test script after creating the login to confirm that the user does indeed have restricted access. The test script will produce the following message.

Msg 8189, Level 14, State 6, Line 8
You do not have permission to run ‘SYS.TRACES’.

This is the same net result as if I were to attempt to run a Profiler session – it’s just faster to get to the result. As we can see, the TraceUser login does not have the requisite permission to do anything with traces (profiler or server side).

Now, I will take this just a step further and try to add permissions to validate the minimum permissions needed.

Running this test script now will yield a result of all traces that are currently setup on the server. So, in my case that would be just a result showing the Default Trace and nothing else since I have not put any other traces on the server. I can also confirm the exact permissions granted to TraceUser do not actually exceed what I just granted.

Or is it?

After running that in the master database I will see that granting the “Alter Trace” permission has an extra side effect of granting “Showplan” to the user.

XEvents

For Extended Events, we have something a little different and a little more (just a little) granular.

Wow! That is quite the permissions escalation, right? Well, it’s not significantly different in some regards to what we see with Profiler. Taking it a step further, XEvents is more in tune with a lot more internals for SQL Server than Profiler. Little things like looking at DMVs or getting the current status for things like Hekaton or Availability Groups, which tie deeper into the engine, will require more permissions. That said, view server state or even these permissions levels is not the end of the world. There are work arounds to the permissions issues. Before I give the work arounds, lets look at the permissions.

Using the TraceUser login I created earlier in this article, I am going to start testing XEvent permissions.

The first step I took in this script was to revoke the “Alter Trace” permission. Then I queried both sys.traces and sys.dm_xe_objects along with running my permissions checks. The attempt to query the two views produces the following messages.

Msg 8189, Level 14, State 6, Line 34
You do not have permission to run ‘SYS.TRACES’.
Msg 300, Level 14, State 1, Line 36
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 36
The user does not have permission to perform this action.

From the permissions checks, I can also confirm that this login has barely the ability to connect to SQL Server.

I will now grant permissions little by little to see what happens.

Here is the sum of the differences.

The “Alter ..Event Session” permissions should be expected since that is what I explicitly added. That said, the permission, while enough to create an event session does not yield enough permissions to do anything with viewing the session data or looking into the dmvs or catalog views. In fact, if you have a recent enough version of SSMS, the XE Profiler feature will cause SSMS to crash if you try to “launch” a session via the Profiler GUI with only the “Alter Any Event” permission.

That said, I can run the following script to create a new Event Session.

As we can see, the permissions are adequate to create a session via TSQL and it does not cause SSMS to crash. This would be another consideration if using the XE Profiler (and even if you are going to allow non DBAs to create XE Traces) in addition to what I noted recently in this article – here.  Truth be told, the traditional GUI for XE also will not allow users to access the Extended Events node if they have “Alter Any Event” or lower permissions – you must use TSQL to alter sessions. The caveat being that only the XE Profiler feature causes SSMS to crash (I tried it 6 times in a row with consistent results).

So, if “Alter Any Event” is only going to get us partially there, let’s go ahead and grant the view server state permission.

Due to the nature of sys.traces, I still do not have permissions to view that data. So that is a good thing. On the other hand, I can now query all of my XE related views (you can read more about those in my 60 day series). And I now can see that I have the following permissions.

And now I can do all sorts of fun stuff from the XE GUI.

That basically lays out the required permissions for both Profiler and Extended Events. In either case, I view both of these as tools for the trusted DBA. Outside of the DBA team, there are select other individuals who may think they need the access and then there are others who really could use the access to the tools but should not really be granted full sysadmin access. At times, you may not even want those individuals to have anywhere near “view server state” access because there is a chance that individual could see something they are not permitted to see. What do we do with that?

Work Arounds

This is when we figure out a way to circumvent the restrictions. Don’t view this as a hack to the system or a way to violate trust. Rather these are methods to help think outside the box and work together as a cohesive team.

Method 1 is a solid solution. That said, it does have a high management overhead. Aaron Bertrand has offered a solution and it is a secure solution. Aaron has found a way to work with Trustworthy databases, views, and logins to help provide a workable solution while still securing the event session data. You can read his article here.

Method 2 is also a secure solution and does require a bit more communication but less management and upkeep than method 1. I like to save the event data to file. Working with the person that would like to be able to review the data, I can create an appropriate XE Session that will be highly unlikely to capture anything they should not see. In addition, I do not need to grant that person any elevated permission on the production server. Once the trace is complete, I can copy the event files to a dev server where that person has elevated access. Then they can merge the XEL files (I show how to do that in the 60 day series) and review the data at their leisure.

Method 3 is similar to the previous method but it requires more work similarly to method 1. This last method, I can grant the user specific access to read data from a specific table in a specific database. I then setup an automated process to dump the data into that table or I can manually load it for the user. Again, the permissions here are very limited in scope and the person has nothing more than the ability to Select from a pre-defined table in a pre-defined database.

I don’t see the permissions for Extended Events as a limitation, rather they seem appropriate based on what XE does and the extensive nature of the tool. I recommend you work with XE for a bit and see what you can come up with to help improve your efficiency and the efficiency of our team.

 

 

An Identity Crisis: Is it Profiler or is it Extended Events?

I have been working on this article for far longer than one might think is necessary for something of this nature. Truth be told, I was reluctant because there are so many people that quickly jumped on the wagon to write about this feature as soon as it was released. With some coercing, here the article is finally!

What is this newfangled feature? Well, that is kind of the problem. The name has already changed in the second release – be it ever so slightly. The feature is XE Profiler or XEvents Profiler and in theory it is supposed to give us something new in management studio.

Fact or Fiction

This Extended Events Profiler is a brand spanking new feature (ok, so the bouncing baby is a few months old now and no longer a newborn). Despite it being new, it seems there are a handful of “facts” already published about it that may or may not be accurate. Due to that, I want to play a little game of fact or fiction with it in this article.

Here is the short list from which we will work:

  1. Sessions (XE Profiler Sessions) are Customizable
  2. The default standard session displays all Extended Events
  3. It works with instances of SQL 2012 or greater only
  4. XE Profiler Adds ability to quickly start/stop sessions
  5. XE Profiler provides a new live view of the Event Session
  6. XE Profiler provides a GUI for Extended Events
  7. Templates were not available in XE until the release of XE Profiler
  8. Similarly, XE Profiler also brought the Profiler templates with it as part of the feature release
  9. A DBA can now do something that was not previously possible to do (quickly start a session)

That is a rather wealthy list of “facts” to be checked for validity if you ask me. Let’s go ahead and start diving into each of these. I will not be following the provided list order. Rather, I will be looking at items that seem appropriate to validate in my own special order. Rest assured tho, we will get through the list.

GUI

I don’t know how this is still a misconception about Extended Events, but it is certainly still out there. Suddenly XE Profiler comes along and people suddenly think that this tool has provided the GUI that everybody has been clamoring about since Extended Events was released in 2008 (yes almost 10 yrs ago).

I am not going to waste much time on this because it is a complete and total piece of fiction. The GUI has been readily and easily available since SQL Server 2012. In addition to that, there was a GUI that could have been installed for the older versions that was created by Jonathan Kehayias.

From SSMS 2014, here is a sample screenshot of the GUI wizard followed by a standard GUI screen.

As you can see, there is clearly a GUI prior to the release of SSMS 17.3.

Result: Fiction

Templates

This is probably the next easiest notion to prove or disprove. I have heard from more than a single source that templates are brand new with SSMS 17.3 (which is when XE Profiler was released).

I don’t really need to go very far or work very hard to prove this notion as a complete work of fiction. I have SSMS 2014, 2016, 17.0, 17.3, and 17.4 readily available to me. I am going to start with SSMS 2014.

If I  once again open the GUI for XEvents in SSMS 2014, I can easily see the available templates in that version.

It looks pretty obvious there that templates were introduced well before SSMS 17.3.

I can also see all of the templates available by browsing out to the file system.

And for good measure, here is one from a 2012 instance.

The same templates (minus possible changes within the template definitions) are available in SSMS 2012, 2014 and 2016. We will see those same templates available in SSMS 17 and above as well – in addition to a few that have been added.

I want to also add here that these are just the default templates. Just like in Profiler, you can create and save your own templates for future use.

Result: Fiction

Profiler Templates

We just saw how there are templates available prior to the release of SSMS 17.3. We can also see that the profiler templates are not available in those prior versions of SSMS. So does that also mean that the profiler templates were only released as a part of SSMS 17.3? We best be careful with that slippery slope of assumption. Let’s take a closer look at SSMS 17.0.

And just so we can confirm that this is indeed a version of SSMS that pre-dates 17.3…

As you can see there, this is RC3 of SSMS 17.0 so it is definitely a version that precedes 17.3

And similarly from a peek inside the folder on disk:

Seems to me we have ample evidence that the profiler templates were made available to the XE GUI well in advance of SSMS 17.3

Result: Fiction

Event Session Live View

One of the most widely used feature of profiler is the ability to watch the data as it streams through the session. According to many, this ability is missing in Extended Events and the XE Profiler feature has brought this ability to the forefront with XE now.

Let’s start by taking a look at an XE Session in SSMS 2014 connected to a 2014 database instance to check the validity of this concern.

All I need to do is browse the nodes in SSMS down to the session I want to view. Right click the session and then select the option to “Watch Live Data”.

After clicking watch live data, the event session live stream will appear on the right and start streaming the data to your SSMS workspace as shown in the following image.

For this session demo, I selected a session that should be readily available to everybody – the system_health default session. That session comes installed and running for you already.

Don’t like the layout of the screen? Well, you can customize the view to your liking and it can be customized differently for each and every different running session on your server.

As it turns out, the ability to watch livestream data has been available to you for Extended Events since 2012 when the GUI was made available. If you are curious about learning more about the GUI and customizing the view, you can read various articles I have written in my 60 day series you can find here.

Result: Fiction

That brings us to the halfway point. To recap we are now sitting at a score of Fact 0 and Fiction 4. Let’s see if we can turn the tide in these last 5.

Quick Start a Session

To be quite frank here, this has been a long time argument against using Extended Events. It just isn’t possible to start a session quickly and easily. I have two methods that I have shown in my training sessions concerning that concern. Let’s take a look at the first. I will demonstrate this from SSMS 2014.

The first thing that needs to be done is browse the SSMS tree until a session is found that you want to start. Here is an ancient cheat code – I leave many sessions on the server in the stopped state until I need to use them. Pro-tip: create your XE sessions on all of your servers and leave them stopped until you think you need to use it.

For this example, I have selected my AG_LeaseTimeout sessions which is in the stopped state (denoted by the little red “stop” icon next to the session name).

Then right click the session name and select “Start Session” from the context menu. To help clarify, I have circled the session name in green and the menu item in blue. This an extremely easy method to start a session. Once started, and if you really feel you must watch data stream onto the screen, then you can also follow the steps shown for watching the live stream data in the previous section.

The second method to quick start a session is only a touch more difficult. The steps are just the same as shown in the “template” section. You select a template that matches your desires and give the session a name. Here are some broad strokes shown in SSMS 17.0 (same steps apply for SSMS 2014 minus the ability to select a profiler template):

Right click the “Session” node and selection “New Session” from the context menu.

Select the “Standard” template from the dropdown box. This is a profiler equivalent (and also happens to be one of the two default sessions in “XE Profiler” so makes sense for a good comparison. Note that the window has a warning that the Session name cannot be empty, so let’s give the session a name.

With that warning cleared, we can now move on to the next configuration. Notice the two red arrows in the preceding image. Enabling these two options will start the session immediately and also give you that profiler warm and fuzzy by allowing you to immediately start watching the session without needing to lift another finger.

From here, click the OK button ( I know I normally teach people that they should script it, but in this case we are going for quick and easy). After clicking “ok”, be prepared for something like the following.

After just a few seconds on a busy server, one should expect thousands of events to fire in a very profiler type manner. I want to reiterate here, that this was the same session template that feeds one of the two available sessions in XE Profiler.

Once you have determined that you have enough data, you can easily stop the session by right clicking the session name and then selecting the “Stop Session” menu item.

And that is all. It is very easy to start a session in two different manners without XE Profiler. One of the options even allows you to create the exact same session as XE profiler but without that feature even being made available on the version of SSMS being used. So if you really want that profiler type of session, you can use a template to quickly create it even if your SSMS version pre-dates 17.3 and even if you don’t have the profiler templates by default (remember you can simply add the profiler templates should you choose).

In the end, I have to call this one a piece of fiction as well. The XE Profiler does not add any new ability to quickly start a session that wasn’t already a viable option. That said, the XE Profiler does add a third alternative to start a session – so long as it is one of the two default sessions.

Result: Fiction

SQL 2012 Required

This is one of those areas that I wish were fiction. However, this has actually been a consistent theme ever since the GUI was released for XE. The GUI components only work on 2012 instances and later. Let’s take a look at a connection to a 2014 Server and a 2008R2 Server in the same SSMS window from SSMS 17.0.

We can see here that the standard Extended Events GUI is just not available for the 2008R2 instance. The 2014 instance is denoted with a Green box and arrow to help distinguish between that and the red marks for the 2008R2 instance.

Now, let’s take a look at it with SSMS 17.3. With a similar setup and similar notation, we see the following in SSMS 17.3 when connected to both a 2014 instance and a 2008R2 instance.

Once again, I will admit this is something I really wish were not the case. It would definitely be helpful if the XE GUI were to be made available to SQL Server Instances that are 2008 and 2008R2. Especially given that the tool is an “SSMS feature”. That said, it really behooves everybody to get their SQL Servers updated to current technologies (2008 is nearly 10 years old afterall).

Result: Fact

The default “Standard” session displays all Extended Events

So far, we have been easing ourselves into this XE Profiler very gently. It is time to finally get into the deeper end and actually look at some very specific XE Profiler things. When I first heard this one, I was admittedly flabbergasted. One session displays all extended events? Really? Let’s actually establish some baselines about the default sessions for XE Profiler first.

  1. There are only two default sessions for XE Profiler
  2. The session names are “Standard” and “TSQL”
  3. The default sessions are based on templates (discussed previously)
  4. The template for the “Standard” session is the xe_Profiler_Standard xml template file
  5. The template for the “TSQL” session is xe_Profiler_TSQL

If I peruse the template file located in the directory on disk (C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Templates\sql\xevent) for the standard session, I will be able to see that it contains the following events:

Now, that is not the entire template file. I shorted it to only include the events and none of the other details like session configuration or the actions attached to each event. As you can see, there is a grand total of seven events in this particular session.

If I run the following statement against a SQL Server 2014 instance, I will get a few more events than just seven.

And the results:

And just for the sake of clarity, the results on a 2017 instance:

Obviously the math does not add up. There are multiple things wrong with the notion that a session from XE Profiler will contain all events. First, there is no sense at all in adding every event to a single event session. The second problem is that the session is supposed to mimic a profiler default template session. If it is supposed to mimic a profiler style session, you can only use 180 events max anyway. Profiler only had 180 events and XE is obviously way beyond that with so much more feature support than Profiler could even muster back in its glory days.

In case, you were wondering, here is how you access the XE Profiler. It is not under the Extended Events Node, it is a brand new node.

As you can see, there really are only the two options for XE Profiler and if you recall, both of those options are derived from the templates that were made available in the traditional XE GUI and are very easy to create a quick session via the same templates as these two options.

Whether using the XE Profiler or the previous method to create these sessions, there is just no way that these sessions could contain every event in Extended Events. It’s just a bunch of hot air with no foundation to the claim.

Result: Fiction

XE Profiler adds ability to start/stop session

Let’s start back with the last image from the previous section and then go from there. We need to access what is available to us via the XE Profiler node so we can test this theory. I will start by right clicking one of the sesisons (right clicking on the “XE Profiler object itself will not yield anything very promising fwiw).

As you can see here, we are given some pretty basic options to either “Stop Session” or to “Launch Session”. It also seems very obvious what they want you to do here because the option is bolded – “Launch Session”.

This menu is the same regardless of the session state. What I mean by that is even if you do not have the session created, this menu is the same as if you have the session created and running. None of the options dim and all are clickable on this menu. This holds true for SSMS 17.4 as well. Let’s take a look at a server where none of these “profiler” sessions are running.

 

I want to make it abundantly clear. This is the way it appears after a completely fresh install of SSMS 17.3. None of these sessions have ever run on this particular instance of SQL Server 2017 and this is even a completely fresh install of SQL Server 2017. That said, I have done nothing yet to break it – buahaha.

Knowing that no sessions have ever been “launched” for XE Profiler, it is clear that it is time to go ahead and “launch” a session. In this new feature “launch” is the new equivalent of “start”. Once you launch a session you will get the same net effect as what we saw earlier when I created a session from the profiler template and checked the box to watch live data and the box to start the session. When I click launch, I will see something like the following:

A new XE session was created under the Extended Events node and it is now called “QuickSessionStandard”. Once again, this is created from that profiler template I have mentioned a few times.

And then after a bit, I may want to try and stop the session.

You can see here that I was able to capture some quick data into the live stream view (which was a feature that was already there prior to 17.3). And after clicking the “stop session” menu item, I will see the following.

Note that the “QuickSessionStandard” event session now has a “Stop” icon instead of a “play” icon. So yay – I was able to start and stop a session quickly from XE Profiler. In addition, XE profiler launched a live stream view just like we saw earlier when creating the same session from the template in the XE GUI. Now for a pro-tip – start and stop an XE Session from the old GUI – “right click any event session and select start/stop from the context menu.”

Yes it is that easy. In addition, only the available option for that session will be enabled (e.g. you cannot stop a session that is already stopped – or not running). In fact you can even start/stop from TSQL. If you try to stop a session that is not running in TSQL, you will get a warning message that it is already stopped. Far more intuitive than XE Profiler. You will get no indication that the session is unable to be stopped if it is already stopped. Let’s take a look at the old way.

And after the session is started, I can also quickly stop it.

You can also see from each of those images, that I have the option to watch live data for the session. Another easy access feature from the old XE GUI.

While XE Profiler has added an additional means to be able to start/stop sessions quickly, the fact of the matter is that it is not a new feature brought to the table by XE Profiler. All it did was add a different way of doing it. I will also add that this new method can actually be somewhat confusing as well. You can only determine (visually) if the session is running or stopped by expanding the “Extended Events” node. Stopping a session from the XE Profiler node does not close the livestream viewer so you could easily presume it is still running. The XE Profiler node gives you no insight into the actual state of the session.

And for giggles, this is how easy it is to start or stop a session via TSQL.

If I run the statement to stop the session when it is already stopped, I will get the following:

Msg 25704, Level 16, State 1, Line 9
The event session has already been stopped.

I have previously covered this specific topic (start/stop sessions) in my 60 Day series which can be found here.

Result: Fiction

Customizable XE Profiler Sessions

Let’s take a gander at the previous section where I showed the context menu available in XE Profiler and not the extent of that menu. The profiler iteself does not offer any sort of customization. You have only two default sessions. You cannot change either session within that particular feature.

Since I cannot customize anything for these sessions via the XE Profiler, let’s use the XE GUI that is tried and true. I will effectively perform the following via the XE Session Properties window:

And after I am done, that session will look like the following:

And visually, from the GUI, it will look like this:

You can see that the aforementioned events are definitely gone from this session. From here, I can absolutely start that session from the XE GUI and it will run just fine. I can hit “watch live data” from the context menu and it will open up the previous view of the live data that was used (so the exact same configuration of that window because the XE GUI has always done that). If I double check my session after it is started, I will be able to confirm that the altered session is indeed still tact just the same as I customized it.

Let’s go ahead and stop that and then “launch” it from the XE Profiler now. After the “launch” I will re-script the session to verify that it is either the same or altered from what I had configured. In this case, since the session was already in place, I will find that the XE Profiler did not alter my session in any way and merely started the session. That is a win for XE Profiler in that it was able to determine that the session did indeed exist. However, I have to use the XE GUI and not XE Profiler in order to get a decent config on those default sessions.

Result: Fiction

Bonus time

XE Profiler only has SQL Profiler Events

This is actually an interesting statement. While the XE Profiler relies on templates based on SQL Profiler and the events in those templates are events that are available in SQL Profiler, they are not exclusive to SQL Profiler. These events can be used in Extended Events and have been a part of the XE Engine since inception. So, I would call this one fiction as well. I think an obvious litmus test for that is that the events are actually running through an XE Session so they must obviously be XE events as well.

Result: Fiction

Up to this point I have not addressed any changes in the Extended Events Profiler that were made for SSMS 17.4. Here is where it seems we compound a bit of confusion for the masses. Not only do we have this Extended Events tool that we have decided to call “Profiler” which has already had many people asking me about it because the name confused them (is it profiler or is it XE??), but it seems that the identity crisis for this feature is exacerbated slightly with a sudden name change.

Using yet another very clean install of SSMS on a different instance of SQL Server to ensure that none of the XE Profiler components had been used or any of the default sessions created and launched, I have the following:

Yes, the name change is very innocuous but it can lead to some confusion for various parties involved. If a set of step by step instructions says to do something and the learner cannot find that explicit node, they will become confused.

For anybody that has been working with XE for some time, they will know that XE is synonymous with XEvent and will quickly figure it out.

Conclusion

So, in wrapping up this very long article, I want to recap the score of the Fact v. Fiction items. The final tally is Fact 1 and Fiction 9 (9 original list items plus 1 bonus entry). This means that there is a whole lot of stuff going on around out there about this new feature that is just not accurate. While some may be able to derive some small use from the XE Profiler, it really does not add anything that you could not already do with either TSQL or the old XE GUI.

Some say this is a way of bridging the gap. In my opinion, that gap was already bridged with the GUI that has been available for several years. Some say that maybe this tool needs to integrate a way to shred XML faster. To that, I say there are methods already available for that such as Powershell, the live data viewer, the Target Data viewer, or even my tools I have provided in the 60 day series.

I would challenge those that are still unfamiliar with the XE GUI (out for nearly 6 years now) to go and read some of my articles or articles by Jonathan Kehayias about the power that is in XE as well as some of the power in the GUI.

Capture Index Maintenance Operations

Comments: 1 Comment
Published on: December 20, 2017

One good database maintenance practice is to keep the indexes in good working order. This is typically done via index defragmentation routines. Some people perform routine index maintenance, some people do not, and still others have done some sort of “set it and forget it” script provided by a vendor and may or may not be doing index maintenance while thinking they are.

When done properly, this is a good thing. Sadly, there are many cases where good practice is not adhered to (let alone best practice). Some of these less than ideal situations could be when an application vendor has something built into their code to perform some sort of index maintenance, unbeknownst to you. In my opinion this is a near-worst case scenario. Being diligent, a good DBA would try to establish routine maintenance for the database based on best practices (or at least good practices). Should that DBA do such a thing and there is index maintenance baked into compiled code somewhere that is not accessible to the DBA, you just doubled the work.

Corruption

Why is this a near-worst case scenario beside the point just made? Imagine being stuck, due to vendor requirements, on one of the versions of SQL Server affected by the online index rebuild bug (read more about it here by Aaron Bertrand), and for some reason you end up with a corrupted index every night. In fact, it is the same index that becomes corrupt on a nightly basis. Every day you drop and recreate the index, and lucky for you, it is not a unique index or a clustered index. But despite those efforts, the corruption continues to return. And since you know you are performing your index maintenance with maxdop of 1 or you have decided to do the rebuilds offline, you know for certain that your process is not the cause.

Take it a step further and you disable your maintenance routine entirely just to be 100{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170} certain your process is not causing it. Yet when you check the recent update date for the index you find that it was still somehow updated/rebuilt while your maintenance was disabled. This is indeed a near-worst case scenario. This means that you have some digging to do to find when and how the index maintenance is being run and who/what is doing it.

How in the world are you going to find what/when this is happening without causing too much increased load on the server? Lightbulb!

Being on SQL Server 2012 or later, it suddenly occurs to you that Extended Events is an awesome tool and could very well be of some use in solving this problem. Let’s pull out that handy dandy script that we have used time and again to try and find potential Extended Events that could help us with this problem.

Running that code produces two very interesting results that seem plausible in this exercise. Those events are index_build_extents_allocation and progress_report_online_index_operation. Even the descriptions seem exceedingly promising.

Especially promising is the event named progress_report_online_index_operation. Look at it, the name tells us that it is a report on online index operations. Querying the available fields seems viable, the description is good, so give it a go.

Well, I will save you the trouble. I tried to create an index with the online = on syntax, tried a rebuild with online option, tried a reorganize option, and tried every way I could think to rebuild an index online. I just wanted to get some sort of data and I could not get it to fire while using the asynchronous_file_target (I really wanted to use the file target for ease of investigation later). Though the event_file target doesn’t appear to work, I was able to get results by sending data to the ring_buffer or to the Livestream target.

What does that mean? If you decide to watch the live data from SSMS, then you invoke the Livestream target, and you can see the data as it streams. Of course that also means you have to sit and watch to try and find when this phantom online rebuild occurs. If you choose to use the ring_buffer, then you run the risk of events cycling out of the buffer. This may or may not be a bad thing since you basically need just an event showing the source of the rebuild and the statement used.

XEvents for the Save

Without further ado, let’s see what we can do to find these online index build operations so we can hopefully target the additional maintenance that is causing us grief.

Notice that in this session I have decided to target a very specific database. For me, this database happens to be my ReportServer database. And for testing purposes, this works wonderfully. All I need to do is something like the following rebuild operation.

This is a rather innocuous index for me. In addition to it being on my test instance, it is in a database that is used lightly and a table that is used even more lightly. If you are looking to test, I recommend you find a similar scenario and definitely test somewhere other than on a production box.

Having rebuilt that index online, I can then query the Event session with TSQL using a query similar to the following:

Now I have an abundance of data at my fingertips to determine how this index continues to get rebuilt. And since this is likely happening in the middle of the night, and that I have to rely on the ring_buffer, I might decide to set an agent job that will run this query every hour or two, to see if I can trap the necessary data about how my indexes are getting rebuilt without my knowledge.

This is just one more tool that can be thrown into your utility belt or tool shed to help find those little things that cause us pain from time to time. I hope it helps you, whether in the same scenario I just described or otherwise. This could even be of help when trying to determine timings of defrag operations when trying to plan for maintenance windows.

«page 1 of 2

Calendar
December 2017
M T W T F S S
« Nov   Jan »
 123
45678910
11121314151617
18192021222324
25262728293031

Welcome , today is Wednesday, April 25, 2018