Extended Events Gets a New Home

Comments: 2 Comments
Published on: May 18, 2020

It should be no secret that I am a huge advocate of the Extended Events (XEvents) feature in SQL Server. A good example of this advocacy can be found just by looking through my collection of articles – here.

Over the years I have been working towards improving my collection of articles in a number of ways. I have been trying to add more information all the time. I eventually created a landing page with a compilation of all of the articles. That led to the creation of a shortened link because it was just easier to remember. Then, I created a subdomain, because I wanted to launch a brand new site for XEvents, and redirected it to the landing page in hopes of trying to get the site up sooner rather than later. Truth be told, the subdomain ended up being a lot easier to remember for many than the bitly link. So despite the delays there, it did serve a fair purpose.

Out with the Old

Well, after much pain, blood, sweat, tears and beating up on the hosting companies, XEvents has a new home! The old methods (bitly and subdomain) will still be valid to point to the old landing page – for a time. I have finally completed the launch of sqlxevents.com with a whole new look and hopefully with better organization of articles.

This new site is intended to not just be a home for articles written by myself, but for various other community leaders on the topic as well. I am currently still migrating some articles and adding to the descriptions and so forth, so be not dismayed if the content seems a little lacking for the near future (it’s a lot of work). Rest assured that the content will be growing and should be easier to find help on topics most important to you!

I will be accepting requests to add content as well as requests to syndicate valuable XEvents content. How? Shortly, I intend to add appropriate forms for each. Worst case scenario, leave me a comment or send me an email or DM. I would personally love to see people like Grant Frtichey (b|t), Erin Stellato (b|t), Erik Darling (b|t), and Jonathan Kehayias (b|t) syndicate a couple of articles to this new resource.

The Wrap

One of the most versatile and awesome power tools given to SQL Server now has a new home! This new home will serve as a fabulous repository of extensive resources and articles on the XEvents feature.

Through the power of XEvents, we can accomplish a great many things and reach extensive insights into our database footprint. Until I have migrated my articles to the aforementioned home, you may continue to read from the library on my site  – here. With the new repository (that is also live, as of this writing, at sqlxevents.com ), you now have a much more powerful tool at your disposal to help you learn about the power of XEvents!

Come and check out the new repository at sqlxevents.com as you put your learning into hyper-speed on your path to becoming an expert with this power tool!

Audit Logons with Extended Events

Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.

Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.

Auditing Logons

The Events to capture SQL Server logon/logoff activities were not a part of the original release of XEvents in 2008. The requisite events did not become available until SQL Server 2012. Why mention SQL Server 2008 at all given it has reached its End of Life you may ask? Well, as luck would have it, there are still plenty of 2008/R2 instances out there in the world that are yet to be upgraded still. So, it is useful to continually point it out. This session, and this information, does NOT apply to anything prior to SQL Server 2012.

The events in XEvents that we need happen to be called: sqlserver.login and sqlserver.logout. Here is a little more info on those events along with a query that can retrieve the details being shown here.

As shown in the preceding image, there are two events of interest that are needed for auditing logon events – sort of. These events are only useful to capture the successful connection (or connection pooling reuse connections) and the logoff events. To take this a step further and capture the failed logins, we can also use an Event that was available in 2008, error_reported. The use of the logout event would be an optional event in this case but could be of use during the troubleshooting under certain circumstances.

Focusing on the login and error_reported events, I have two very useful XEvent Sessions I like to use for my client servers. First part of the audit logon solution is the audit of successful logins with this XEvent Session.

Next, I like to use a separate session for the failed logins. This makes it easy to keep the audit files separate and only share the necessary files to others (e.g. only the failed logins instead of giving them both successful and failed which may be way too much information). In addition, I like this method because it makes searching the audit files easier and more focused.

In this second session, I have the predicate defined such that I will only get the failed login events. Funny thing about this, I can often catch 3rd party vendors trying to login to client systems in very odd ways (including putting the password into the user name box which in turn causes the password to be stored in clear text in the error log).

Could these sessions be combined into a single all-purpose session? Sure! That is up to your discretion. I prefer the separate sessions for ease of management and review.

The Extra Mile

Let’s say we wanted to go ahead and add the logout event to our session, we can then proceed with a slight modification to the successful login session so that it would look something like the following.

And, to show what might occur with this session, I can see data similar to this for the logout event.

See how easy that is!

The Wrap

This article has shown how to audit the logon events for SQL Server 2012 and beyond through the use of XEvents. It also happens to be an excellent follow up to another recent article – here. An essential function of any DBA is to have a clear understanding of the activity (logons) that is occurring on the servers. This audit session will help you do exactly that!

Through the power of XEvents, we can accomplish a great many things and reach extensive insights into our database footprint. This post is just one of many in the XE Series, of which you can read more – here.

In addition, this post is similar to many in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Collation Conflict with Extended Events

Have you ever run into an error like this?

Cannot resolve the collation conflict between “pick a collation” and “pick another collation” in the equal to operation.

This kind of error seems pretty straight forward and it is safe to say that it generally happens in a query. When you know what the query is and you get this error, it is pretty easy to spot and fix the problem. At least you can band-aid it well enough to get past the error with a little use of the collate clause in your query.

But what if the error you are seeing is popping up when you are trying to use Management Studio (SSMS)? The error is less than helpful and can look a little something like this.

And for a little context in how that error message popped up, here is a little better image.

As you can see here, the error message popped up just trying to get into the Extended Events Sessions folder in SSMS. Just trying to expand the folder to see a list of the sessions on the server throws this error. So what is really happening?

Background

First let’s cover a little background on this problem. This server was poorly configured. There was considerable set it and forget it action with this server – meaning all defaults and and a lot of bad choices. In an effort to update the instance to meet corporate standards, the client attempted to change the server collation to “Latin1_General_100_CI_AS_SC”.

Changing collations on a server is a rare occurrence in the grand scheme of things. If you do it at the right time, all will go well. Do it on a server that is poorly configured then there is a little more risk. In this case, the collation change had failed for the server. In order to figure out if the Server collation change had failed, we can run a few tests. First though, let’s see what that query looks like that was causing the error from within SSMS.

The query here is one time that profiler can actually come in handy (not that I would try to rely on it too much) to help retrieve given that there is a collation issue with Extended Events (XE). We can now take this a step further and start to show that the Server collation change failed. Let’s check the collations on each of those objects and then validate the server collation.

As we can see from the image, the collation for the master database indeed was changed (it is done before the user databases) but then the change for the server collation failed. As it turns out, due to the sequence of events, if there is a failure in changing the collation in a user database, then the collation change for the server fails but the master database will indeed change. We can further confirm this from the output of the attempted collation change. Here is a snippet from a failed change (sadly does not show the user database change failure due to extent of output).

So, how do we go about fixing it?

The Fix

Well, since we know where the failure occurs, the fix becomes pretty apparent. It may be a lot of work – but it isn’t too bad. The fix is to detach all user databases, attempt the collation change again, and then re-attach all databases. To help with user database detach and re-attach, I would recommend using a script that can generate the necessary detach and attach statements for all databases. It will save a bit of time.

Once, reattached, I can try to look at the XE Sessions from SSMS with much different results (shown here).

You see? It is as easy as that.

Final Thoughts

The default collation for SQL Server is a pretty bad idea. Sure, it works but so does SQL Server 7. When you have the opportunity to update to more current technologies, it is a good idea. Sometimes though, that upgrade can come with some pain. This article shows how to alleviate one such pain point by fixing problems related to collation conflicts and XE.

This is yet another tool in the ever popular and constantly growing library of Extended Events. Are you still stuck on Profiler? Try one of these articles to help remedy that problem (here and here)

The Extended Events library has just about something for everybody. Please take the time to explore it and become more familiar with this fabulous tool!

Window Shopping the Query Store with XEvents

Comments: 1 Comment
Published on: March 11, 2020

TSQL Tuesday

The weather is starting to warm and Spring is upon us. It feels like a great time to take a stroll outside in the warming sun and maybe do a little window shopping on our way to the March (2020) blog party called TSQLTuesday.

This party, that was started by Adam Machanic, has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

This month, the blog party is all about couponing and super shopping at the Query Store. Tracy Boggiano (b | t) insists that everybody could save a grundle of money by couponing and shopping at this ultra sleek new store. Ok, maybe you can’t actually shop there but you can pick and choose your execution plans from this store just like you could pick and choose your favorite brand of spaghetti noodles from a store shelf. You can find the invite from Tracy – here.

I will not be delving into how to use the Query Store (QDS). Nor will I be exploring whether or not QDS is the right tool for you. What I am here to sell you on though is two methods to help you become better acquainted with the inner workings of QDS – via Extended Events (XE).

Some Basics

One of my favorite questions to ask during some of presentations on XE is “What was the first version of SQL Server to have Query Store?” You can imagine the wide array of answers but what is interesting is how often  the correct answer is always missed. I hear lots of answers for 2012, some answers for 2017 and somewhere in between for 2016. But never does the correct answer pop up.

Right now, I hope you are scratching your head at that last statement. You see, the question is somewhat of a trick question. The first version of SQL Server that has QDS is SQL Server 2014. However, the first version where you can actually use it is SQL Server 2016. This fun fact is visible when we start exploring SQL Server from the realm of XE. Let’s take a look.

The following is a screenshot showing some data that I have been tracking on XE across all editions of SQL Server ever since it was incorporated into SQL Server. Don’t trust my word for it, there is a query later that can help you see this for yourself on a SQL Server 2014 instance

The result of the query is far too large for this article, so I just grabbed a snippet of the result and marked each version of SQL Server where various events can be seen. Some events were deprecated after SQL Server 2014 (in blue) as noted in the image, and some that existed in SQL Server 2014 still exist in SQL Server 2019 (in green).

Here is a query (as promised) that can be executed on SQL Server to capture the events related to QDS.

If you are curious to see other sources showing that this feature was planned to be released in 2014, you can read it from Brent Ozar – here.

Looking Deeper

Referring back to the previous query, you may have noticed that I am filtering the events down to those that are provided by the “qds” package. The “qds” package is the provider for the Query Store (short name) or Query Data Store (long name). It’s not just events that are provided by that package, there are maps as well. Interestingly enough, there is more to this little package than just the QDS. Take a look at these results from a SQL Server 2019 instance (similar results can be found when querying SQL Server 2017 as well).

See those events in the blue box? Those are all related to the Automatic Tuning feature. Yes they are related to QDS and that is why these events are provided by the same package as QDS.

Sandbox

Let’s go ahead and enable QDS so we can take this to the next level.

Once we have QDS enabled (you are not restricted to the settings I chose to use – try something different if you want), let’s go ahead and also gather some additional information that will be helpful for creating some XE sessions.

That query will provide a list of tables that are related to QDS. It will also provide the proof of concept (basically) for creating an XE session. Knowing that the query will return just those objects for which I am interested, I am ready to go ahead and apply the same query pattern to my XE session.

But what is this XE session? Well, I am somebody who is curious and I want to know precisely what is being done when I do something like this.

That statement has the same effect as clicking the button from the Database properties page for the Query Store.

Since I really want to know what is happening when the QDS is cleared, I have this little XE Session to show me exactly that.

Never-mind the fact that I am creating a folder on disk despite the fact that I am using the ring_buffer in this session. I do that with most all of my sessions just in case I want to add an event_file target at some future point. A couple of key points for this session: 1) I have scoped the results to be pertinent just to the WhatsYourDBFlavorofTheDay database; and 2) the results are further filtered to just be for any objects that match “plan_persist” with some wildcards. If I have that session running, and I clear the QDS cache, I will see results similar to the following.

When the cache is cleared for QDS, it is a very quick process. Looking at the results of this session, we can see why that is – the underlying tables are all TRUNCATED. This may be a bit of a heavy handed approach given the truncate does reseed the tables. Just be aware of that fact. Personally, I am ok with the TRUNCATE and it is nice to know what is happening behind the scenes.

That is a bit of a fun look at a somewhat trivial piece of the QDS. Something more technical comes from trying to monitor the QDS for plan removal, cleanup processing, and similar tasks. How and when does some of that work/occur? Let’s get a more comprehensive XE session to peer into those kinds of internals.

That is a big session script! Here is basically what triggers all of those events in this session to fire.

And with that session running and after waiting a period of time, here is a sample of what some of that session data may look like.

Your results could vary quite substantially. That said, this session could help you to better understand how cleanup operations are occurring within QDS.

Wrapping it Up

This article has just shared multiple tools to help you become more acquainted with the Query Store! This acquaintance is coming via an extremely powerful tool called Extended Events. Through the use of these two sessions and two additional scripts, this article demonstrates how to become more familiar with the internals for QDS.

Feel free to explore some of the other TSQL Tuesday posts I have written.

If you are in need of a little tune-up for your XE skills, I recommend reading a bit on Extended Events to get up to date. For some “back to basics” related articles, feel free to read here.

Trace Query in Extended Events

Tracing a query is a common task for a DBA. The methods employed are pretty varied. I seem to encounter a new method here and there throughout my journeys and projects.

One method is surprisingly easy and I don’t recall ever paying it any attention until recently. Upon discovering this method, I was appalled that there is still no equivalent method within Extended Events (XE). In this article, I am going to share that method and a viable equivalent for XE until an appropriate GUI integration is created for SSMS.

Query Tracing

First things first, how do we find this supremely easy tracing method? When do we want to use it? Let’s answer that second question first. This is a method we will want to use whenever we have a query that we have just been handed and we want/need to trace the query to figure out things such as resource utilization. To get to this method, we simply right click in the query pane and select “Trace Query in SQL Server Profiler” from the context menu. The following image illustrates that menu option.

After selecting that menu option to trace the query, the next step is amazingly simple too – just execute the query. That is fabulous – if you want to use a tool as inefficient and outdated as Profiler. How do we do this in XEvents? First, we need to capture a bit more detail from this Profiler style trace.

Let’s delve into the properties for that trace session we just started (from the previous image).

Once we have the properties open, the next step is to click the “Column Filters…” button as shown in the following image.

After clicking the “Column Filters…” button, a new window will open, revealing any filters that were defined for that query we wanted to trace. In this case, the SPID for the query window is transferred to the query trace. Thus, in theory, this Profiler trace will only capture data related to the SPID in question. With the filter in hand, and also noting the events being trapped from the properties window, we have adequate information to create an XEvent session to perform the same functionality.

We can easily setup a session in XE through the GUI using the Standard template shown here:

And then modify it to include the missing events as shown here:

Or, one could use the easy button and take advantage of a script. The script option provides a much more robust option while also being far less repetitive than the GUI.

This session is ready to roll (without a GUI access point obviously) simply by entering the SPID #, into the @SessionId variable, for the query window in question. Once the spid value is entered, and the script is executed, we can easily watch the live data for the spid in question (if we wish).

So, what happens when I need to query a different spid? That’s easy! I just change the value of the @SessionId variable to the spid in question and then run the script again. The script will drop and recreate the session with all of appropriate filters in place and pointing to the correct SPID.

Final Thoughts

The ability to quickly and easily trace a query is important to database professionals. This script provides one useful alternative to trace a specific spid similar to the method of using the context menu to create the trace within SSMS and Profiler.

This is yet another tool in the ever popular and constantly growing library of Extended Events. Are you still stuck on Profiler? Try one of these articles to help remedy that problem (here and here)

The Extended Events library has just about something for everybody. Please take the time to explore it and become more familiar with this fabulous tool!

PowerShell to Backup XE Session

Quite some time ago, I shared a few articles that peeled back the top layer of how to use PowerShell (PoSh) with Extended Events (XEvents). Among those articles, I showed how to retrieve the metadata, predicates and actions, and targets (to mention a few). Those are prime examples of articles showing some of the basics, which means there is plenty of room for some deeper dive articles involving both PoSh and XEvents. One topic that can help us bridge to the deeper end of the XEvents pool is how to generate scripts for our XEvent Sessions.

In this article, I will venture to show how to generate good backup scripts of our sessions using PoSh. That said, there are some caveats to using PoSh to generate these scripts and I will share those as well.

Wading to the Deeper End!

PoSh provides many methods and classes that allow us to more easily do various things. One of my favorite uses for PoSh is to automate tasks across the enterprise database server footprint. Nothing like creating a script that can effectively hit all of the servers one manages in one fell swoop to help minimize effort and time spent touching all of the servers. Afterall, a prime directive of the effective DBA is to be lazy.

So, when I look at creating a script in PoSh, I also look at how can I make this script function for a single server just the same as it would work for hundreds of servers. That will be one of the “nice to have” add-ons that you will see in this script. But, how did we get there in the first place?

A critical component of using PoSh to generate the scripts for each of the XEvent Sessions on the server is the GetScript(). GetScript() can be called for many different objects within PoSh. Another critical component is the XEvent SMO classes that were added as of SQL Server 2012. Take a mental note of that because it will come into play in a bit.

In addition to these critical components, I used some of the stuff that I mentioned in the previous articles as well as my article on XEvent management with PoSh. As I have said many times, these articles are building blocks and do rely upon many fundamentals divulged in previous articles.

Getting into the Script

Given this is a script that interacts with SQL Server, we need to ensure the SqlServer module is installed and loaded. It if is already installed, that is great. During my testing, I found that one of my servers, I ran this locally on, did not have it installed. With that discovery, I figured it is just easier to implement a check. If the module is not installed, then install it.

The next major component is how I fetch my list of servers. I prefer to have all of my enterprise SQL Servers listed within SQL Server. I keep a table with all of the servers and designate various attributes for the Servers (e.g. prod or dev, business unit, decommissioned etc).

Once, I know which servers need to be queried, I need to make sure I set my PoSh location properly.

With this script, I also have the ability to script out a specific XEvent session or to script every XEvent session deployed on the server. When scripting all sessions on the server, I opted to put all of the sessions into a single output script file. Since, this script can be run against multiple servers, I name each output according to the Server Name and the Instance on that server followed by the XEvent Session name (or all_sessions in the event all sessions are being returned).

Since I prefer to use a custom event_file path, I also wanted to ensure my script would produce a means to ensure the custom file path is created. I needed to validate that this only gets added to those sessions that had an event_file added to it.

There are a few more nuggets throughout that help bring this whole thing together. This is what the completed script looks like.

If you follow along on that script, you will see that I concatenate the sessions together and then force overwrite the output file. This ensures that I am not appending to an existing file and also ensures that I have the most current version of the XEvent session script. I configure this to output to the local server from where the script is executed.

Caveats

Using PoSh to generate scripts is extremely handy especially when working across numerous servers. That said, there are certain problems for which one must be aware. First is that the SMO classes for PoSh were introduced as of SQL Server 2012. These objects are not backwards compatible.

The second issue I have may be less of a concern to you than me, but it goes well with concerns I mentioned in the article I wrote about how to do this same task in TSQL. The script generated via PoSh adds the events in alphabetical order. I have also seen on some cases where the predicate is not in the exact order that was originally created. Predicate order is essential! Event order may not be an essential thing for anything other than Source Control or for the demanding OCD DBA. Your mileage may vary.

Final Thoughts

Bringing PoSh to the world of XEvents can open up your eyes to a world of better automation and DBA efficiency as you manage your enterprise. PoSh also can help us dive deeper into the world of XEvents as we will see in some upcoming articles.

Working with Extended Events will help you become a better DBA. Working with PoSh can also help you in many various tasks to become a better DBA. Combine the two and you just might have a super weapon.

Interested in exploring the vast world of Extended Events? Check these out! The library of articles is pretty large and continues to grow.

Creative XE Sessions – Funky Data

In the previous article, I explored the opportunity of using special characters (or emojis) within Extended Event Sessions. Creating a session with these characters in the name is not terribly complicated. However, it is a bit more of a pain on the data review side (as mentioned in the previous article).

Just because it gets to be a little more complicated is seldom a deterrent for somebody such as myself. In fact, many times it is more of a challenge. These challenges are fun. With this particular challenge, and as I promised, we are going to see how we can solve the problems posed by having emojis within the object names (which causes pseudo funky data to be trapped in the session data).

Get the Funk Out!

As was demonstrated in the last article, when we have special characters in the database names and that data is captured in an XE Session, we end up with some smelly funky cheese type data that is fairly unpleasant to our desires. We have to do something quickly with that funky data in order to make it useful.

Unlike funky cheese that may have turned, we are not going to discard this data as being unwanted or un-useful. We still have a use for it. We just need to transform it a little bit before it becomes a little more palatable.

Let’s go back to a query we used to parse the session data from that last article. We need to modify this query to make it more capable of handling this new data experience.

Knowing that we have certain patterns that need to be recognized and altered, we can take advantage of the replace function. The first two patterns to find are: “&#” and then “;“. The next step is a little less known. What do we replace those known patterns with? Well, as it turns out, and as is shown in the above query results window, we know that we are looking for two surrogate code points (High and Low) that represent the character in question. When we do a little TSQL trickery and use NCHAR with those surrogate values, we can render the character to display in the desired format. Let’s see that in query form (it will help that explanation quite a bit).

The results of this query will look like the following.

Easy peasy right? Well, sort of easy. We can easily render that funky data we are seeing in the session details to something more human friendly. However, in order to display that data in human friendly format, we have to execute it in a sort. This means we have to do something a little more with the query – we have to convert some part of the query to a dynamic SQL statement. Here is how we can do that!

In this new version of the query, I have a few things going on. First, I am pattern matching and doing a replacement for those aforementioned terms. Next, I am dumping the results into a temp table and removing some extra data (unnecessary stuff due to the pattern matching and replacement). From the temp table, I then build a dynamic query to pull it all together and use that NCHAR trick to put the two surrogate values together to visually represent the character we expect. In the end, I have results that look like this.

Now, from a TSQL query, I have a visual representation of the Database Name that matches what I should be seeing within the SSMS object tree. This makes more logical sense to the person reviewing the data and is far easier to correlate to the correct database.

What about the histogram target that was demonstrated in that other article? Let’s look at the changes for that one as well.

The results are now similar to those we fetched for the event_file target (similar in display format). Again, this makes it easier to digest for us humans.

A little more effort on the preparation end with the TSQL queries, makes for an easier time going forward on the data review end of these XEvent Sessions.

Put a bow on it

Playing around with emojis in a database is a fun endeavor. Not only is it fun to play with for personal growth, but it does have some business advantages. We live in an era were these emojis are popping up everywhere and are even being stored long term in many databases. I view it as a great opportunity to improve your skill set and better your career for the future.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the eleventh article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Creative Extended Event Sessions

In the previous article, I showed how to take advantage of collations to add more unique and interesting names to databases. In that article, I alluded to the better collation to use but failed to be very explicit about it.

As Solomon Rutzky pleads in many articles, the best default collation to use is not any collation that starts with SQL, but rather the collation one should use is Latin1_General_100_CI_AS_SC. I agree. Let’s not use the old default collations anymore and start using more modern collations – even if Microsoft does not change the default, you should change your defaults in your builds to represent a better more modern collation!

Fun with Sessions

Let’s suppose I strongly suspect I am having an issue with the ghost cleanup process for a specific database called ‘👻’. I want to prove whether the suspicions are accurate or not so I resort to an old tried and true XE session to validate. Here is that session.

Now, let’s try to validate my suspicions.

The best part is yet to come. With the event session running, I need to execute the preceding query. The query may take several minutes to complete – don’t worry yet. After the query completes, it is time to check the event session data to try and confirm the suspicions.

This is where things start to get a little interesting! If I try to open the session data to view it from the GUI, I will see something like the following.

Well, that is not very useful! Let’s try it from a TSQL query instead!

Well, that seems to be a little better. At least I get some data back at this point. Sadly, the database name is poorly displayed. That said, the database name that is displayed is accurate – just not very useful. As it turns out, XE is lagging a bit behind in the ability to display the characters that require certain code points (seemingly any that require multiple code points or above a certain range). I have not yet tested the full limitation, just understand that there is a limitation.

What if I tried a different target such as an event_file target?

The event session will start successfully. In addition, I can confirm that the event file is created on disk.

Then I will rerun the experiment to test the ghost_cleanup process against the 💩 database. If I query the event session with TSQL, I will see something like this.

If I look at the event session from within the GUI, I will see something slightly more usable.

Peeking at the file data (instead of displaying it in tabular format) from tsql commands, I will see something like this.

Stay tuned to see how we can resolve this issue where the 💩 and 👻 are not being translated properly in these TSQL queries for these sessions.

Put a bow on it

A picture is worth a thousand words, right? Sometimes, a picture for an event session just may be able to say it better than 50-60 letters used to try and describe the session. Maybe you just want to try it out for fun. Either way, the use of these characters in an event session can be a little difficult when looking to review the data.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the tenth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Easy Audit Index Changes

Comments: No Comments
Published on: December 27, 2019

In the previous article, I showed a somewhat elaborate method that could be used should there be a need to audit changes to your indexes.

As I learned many years ago in school, the elaborate or difficult method may not always be the best method. That said, we often need to learn the more elaborate methods in order to better appreciate the simpler, more efficient methods available. This article will help demonstrate a much better method.

Auditing

When looking for an easy method, one of the first technologies to try really should be Extended Events (xevents). Through the use of xevents, we have a large tool set at our disposal. Not only is the tool set rather large, it is also an extremely flexible multi-faceted tool set giving us greater potential to solve problems with simple tools.

Let’s look at how we can solve this problem through the use of xevents.

In the preceding xevent session, I am taking advantage of three events: object_created, object_altered, and object_deleted. These events will only trigger if an index definition is changed, added, or deleted. They will not trigger if the index is defragged (for instance) or the stats for the index happen to be updated (which is an issue for the stats_date method shown in this article). After deploying this session and then creating an index (shown in the following script), I can expect this session to capture a wealth of information.

In the preceding image, we can see that plenty of pertinent information has been captured. The image does not show the person who made the change, but that information is also captured with this session. Should I wish to focus this session to a specific database, that is definitely possible by removing the comment tags from the database_name predicate in the session script I have shared.

Put a bow on it

This article showed an easy method to audit for index changes. The weak link in a solution such as this really boils down to the requirement that the solution needs to be in place before the index change occurs. Otherwise, it would all be for naught.

Interested in more Extended Events articles? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the third article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

The Gift of the SPN

TSQL Tuesday

The second Tuesday of the month is upon us once again. That means it is time for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

This month, we are looking at the last opportunity for the year to contribute to this long-standing tradition. This month being right in the middle of the holiday season, we have an opportunity to explore how we have been blessed over the past year or so.

I personally like the topic. I have one comment concerning the topic before diving in too far. I feel it wise to reflect upon how you may have been blessed in various ways on a far more regular basis. Doing so just once a year is far too infrequent. Thanks to Mala (b | t) for bringing this topic up to remind us of how important it is.

In Malas words:

This is a time for material gift giving, for many of us. It might also be a time to consider the many gifts we have received through the year, and perhaps use this opportunity to appreciate people or situations that we were blessed with.

SPN

Service Principal Names (SPN) are used by Kerberos to help associate a service instance to a service logon account. SPNs are useful and powerful little things. That said, sometimes they can cause wonderful little issues with connectivity to SQL Server. One of the more common issues is an error message such as this:

Cannot Generate SSPI Context

As circumstances would have it, the cause of this issue is stated in a kb article as follows.

A Service Principal Name (SPN) for the SQL Server Database Engine may either be missing, misplaced, or is a duplicate to other SPNs configured in the Active Directory of the domain.

I have known many people run out of hair dealing with SPN issues and the dreaded SSPI context error. Gratefully, there are ways to resolve some of these issues and make life just a touch easier. One of the easier ways is a lesser known tool that you can download from Microsoft – here.

The tool is fairly simple to install and use. The one sticking point for using it is that you need to know where to find it so you can use it after it is installed. Here is the path to the executable.

%SystemDrive%:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server

The link mentioned provides the path and the install instructions along with more info on on how to use the tool from the command line. Personally, I use the tool to discover my SPNs on the server in question and then if I might be missing any.

Upon opening the tool, you are greeted with this functional screen.

From the welcome screen, click on the “Connect” menu option. From there a new screen will open that prompts for the server name, service account and password. However, if you are just connecting to the local server then none of that is required and you can just proceed. After a few minutes, and with a bit of magic, the SPNs are evaluated and a report is generated. Along with this report, there is even an option to “Fix” the missing SPNs if they exist.

This tool really takes some of the room for error out of the equation and makes the job fairly easy. I recommend it. I have shared this with some sysadmins and they found it to be very helpful and time saving. Check it out!

Wrapping it Up

Every once in a while there is an extremely valuable tool that comes along. While the footprint and use frequency of this tool may not be that big, the tool is essential to making the DBA job easier to do. I recommend getting this tool – especially if you have ever run into SSPI issues.

In this article I showed the Kerberos Configuration Manager Tool for SQL Server to help troubleshoot SSPI Context errors and wayward SPNs. Another exceptionally useful tool is Extended Events. If you are in need of a little tune-up for your XE skills, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

«page 1 of 14

Calendar
June 2020
M T W T F S S
« May    
1234567
891011121314
15161718192021
22232425262728
2930  

Welcome , today is Saturday, June 6, 2020