Upgrading From SQL Server Profiler

I just shared an article describing how to use Extended Events to perform your SQL Server Profiler duties. If not, you can read all about it here.

In that article, I showed one common use-case for SQL Server Profiler and how to achieve the same result via Extended Events. What I didn’t show in that article was how to correlate all of your favorite trace/profiler options into the shiny new XE equivalents. Is there even a way to do that?

As luck would have it, there is a means to correlate trace/profiler events to Extended Event events (yeah that sounds weird).  With the release of SQL Server 2012, Microsoft introduced a couple of catalog views to help with this correlation effort. Those views have a pretty straightforward naming convention. For example, one of the views is named sys.trace_xe_event_map while the other is sys.trace_xe_action_map. For this article, I will be focusing on the former.

Making the Upgrade

When looking to finally make the upgrade away from trace/profiler, a big key is to figure out if your favorite trace events are even an option at the next level. I have talked about finding events in XE in previous articles by trying to search for a specific topic that might apply to your current condition.

But if you are already familiar with specific trace events, you may just want/need to know what the new name is in XE. This is where those catalog views come into play. And in support of that, here is a query that can help in that upgrade effort:

Looking through the results, one would notice that not every trace event maps to an Extended Event event. The events that don’t map, in general, deal with audits (which is actually driven by Extended Events).

This is a really good start to getting you on your way to that much needed upgrade away from profiler/trace. What if there are several traces that are already in use or scripted for various reasons in the environment?

Decision Time

If you happen to have traces that are already deployed in the environment or in script form, it can be a bit of a tedious pain to convert those to Extended Events. Do you manually recreate the traces as XE sessions? Do you abandon the upgrade due to the effort and annoyance it will create? Or do you find some automated means of performing the conversion?

Of those three options, only two are valid options. Those options involve performing the conversion of the traces to XE sessions. There are pros and cons for each. You may opt to take your time and learn more about Extended Events by performing the manual upgrade, or you may choose to save time by using an automated routine.

Should you decide to try the automated routine, there is one already out and available to help you on your way. Jonathan Kehayias wrote the script and you can download it here.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in another article demonstrating the power of XE over Profiler? Check this one out!

This has been the second article in the 2018 “12 Days of Christmas” series. For a full listing of the articles, visit this page.

How To: XEvents as Profiler

A common excuse for not delving into extended events seems to be the jump from Profiler to extended events.  There appears to be an inherent fear with how to use extended events.  In addition to that fear, is the fear that extended events does not do what Profiler can do so easily.

Today, I would like to share a short tutorial on how to use extended events as if it were a profiling session.  I am only going to show one of the many possible means to profile via extended events.  Just understand that this means you have flexibility and a way to do more with this simple example than what I am going to share.

Requirement

You need to capture TSQL queries that are occurring against your instance in order to try and determine the code that is being passed to SQL Server from the application.

How-To

Rather than jump to Profiler, you are going to need to jump to a fresh, clean query window in SSMS.  Just like in Profiler, you will want to capture certain “statement” oriented events.  For this session, I want to start with sp_statement_starting and sql_statement_starting.  I am not going to use the _completed forms of those events because I want to capture as much as I can – without capturing too much noise.  Sometimes, there may be a _starting without a coordinated _completed and that could throw a wrench in the works.

With the basic information in hand, we are ready to implement some of the things learned in previous XEvents posts (here and here).  Let’s go ahead and start setting up the session that will capture the information we seek.

One caveat to consider is in the sp_statement_starting event, I have specified a specific set option be used.

This particular option is disabled by default.  If you want to know the object_name of code that is being used (triggers, stored procedures, etc), then this option must be enabled.

I have specified the same actions to be used for each of the events I am trapping, but this is purely up to you.  If you do not wish to capture these actions, then you can remove them or add different actions as necessary.  I have also specified a different predicate for each of the events.  This is something that works so much better in XEvents than Profiler – the filtering that can be applied is much more flexible.

Once the session is started, then it is merely a matter of figuring out how to look at the data.  I generally use TSQL to parse the data from the file, but you could also fiddle with the GUI as well.  You can access that by right-clicking the event session in management studio and then selecting “Watch Live Data”.  If everything is configured properly, then you will start to see data after the first event is triggered.  From within the GUI, you can pick and choose which columns to display in your view.  You can pause the display of the session data, or you can even “erase” the data from the current view (just like in Profiler).  You can even filter, aggregate or group the data in the view from the GUI.

The use of the GUI is up to you.  I still prefer to use the script route.  With that route, here is a sample of what you may need to write in order to display the data from the session data file that has been captured.

It really is that simple.  Now you have a means to run a Profiler-like trace on your server without the impact of Profiler.  XEvents provides the means to run a more evolved Profiler session on your data to capture things like queries from an application.  XEvents does all of this without the severe penalty of Profiler and with many additional bonuses.  Try it out and enjoy!

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in another article demonstrating the power of XE over Profiler? Check this one out!

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

Profiler for Extended Events: Quick Settings

Not long ago, I wrote a rather long article about a new-ish feature within SQL Server Management Studio (SSMS) that impacted Extended Events. You can read that book – here! The XEvents Profiler feature is one of those things that you may or may not use. If you consider using the feature, I do believe it is important that you research it a bit and try to learn the pros and cons first.

With that there is a little more about the feature that the aforementioned book did not cover. In fact, this information has pretty much gone ignored and mostly stays hidden under the covers.

Settings

As of SSMS 17.4 we have been given the ability to control XEvents Profiler just a tiny bit more. For what it is worth, we as Database Professionals love to be able to control our database environment. So this teeny tiny bit of new control ability is potentially a huge win, right?

If you are the controlling type, or maybe just the curious type, you will be pleased to know that under “Options” from the Tools menu in SSMS, Microsoft has tucked some new control options to help you configure XEvents Profiler – to a degree. If you open options, you will see this new node.

If you expand the “XEvent Profiler” node (circled in red), you will discover the “options” node. If you click on this “options” node and do a quick comparison (in SSMS 17.4 and SSMS 17.5) you will also find that you don’t need t expand the “XEvent Profiler” node at all because the options are listed in the right hand pane for both nodes and they are exactly the same. So, choose one or the other and you will end up at the same place.

The options that you currently have are:

  • Stop Session on Viewer Closed
  • Toolbar commands stop and restart

You can either set these options to True or False. I recommend you play with them a bit to discover which you really prefer. That said, I do prefer to have the “Stop Session on Viewer Closed” set to true. There is “profiler” in the name of the feature afterall. And if you have read the “book” I wrote about this feature, you would know that the filtering offered by the default sessions of this feature basically turn on the fire hose effect and can have a negative impact on your server. Are you sure you want a profiler style fire hose running on your production server?

Conclusion

There surely will continue to be more development around this idea of an XE style profiler. More development generally means that the product will mature and get better over time. This article shows how there is more being added to the feature to try and give you better control over the tool. We love control so the addition of these options is actually a good thing. Is it enough to sway me away from using the already established, more mature, and high performing tools that have been there for several generations? Nope! I will continue to use TSQL and the GUI tools available for XE that predated the XEvent Profiler.

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.

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.

Profiler vs. Xe Smackdown

Categories: Presentations
Comments: No Comments
Published on: July 11, 2016

Abstract

Extended Events were introduced in SQL Server 2008. With SQL 2014, we have seen a significant upgrade to this feature. Join me for a little adventure into the realm of extended events. Not only will we explore extended events, we will also take a dive into profiler. During this session, we will compare and contrast the two trace utilities and how they can be of potential benefit to the attendee.

This session is largely demo and attendee driven. Attendees should come ready to discuss both extended events and profiler as we will explore the barriers to entry for extended events. Once explored, we will work to resolve those barriers in preparation to reduce the use of profiler.

Download

Extended Events – Better than Profiler

Categories: Presentations
Comments: No Comments
Published on: March 23, 2016

Abstract

Extended Events were introduced in SQL Server 2008. With SQL 2014, we have seen a significant upgrade to this feature. Join me for a little adventure into the realm of extended events. We will discuss how to use extended events to aid in performance tuning and in day to day administration. We will also explore some background and the architecture of extended events. This is a fast paced session and will be a lot of fun. Attendees will be able to take back several sample XE sessions to help troubleshoot different issues and get to more intimately know their data/environment.

Download

Activity Monitor and Profiler

Tags: ,
Comments: 2 Comments
Published on: July 28, 2011

Today I came across a link to a neat little script for SQL 2005 / 2008 to help derive the head blocker in a blocking chain.  That script can be found here.  I was looking at the script and thought it looked pretty cool and also wondered why it might look a little familiar.  Not too big of a concern since many admin type scripts can look familiar.

Then I noticed that the title contained the words Activity Monitor.  I wondered to myself, was this taken from activity monitor?  So, I decided to play with Profiler for a bit to see if I could generate the same query.  The process really isn’t too difficult.  Here is what I did.

  1. Open Activity Monitor.  Simple enough to do.  Right click the instance (in SSMS) you wish to monitor, select Activity Monitor from the context menu.
  2. Open Profiler.  Again from within SSMS, click the tools menu and then select SQL Server Profiler.
  3. Start a New trace.  When starting the trace ensure that you are connecting to the same instance as you started Activity Monitor.  Select the TSQL-Replay template (other tsql will probably work, this is the one that I used).  Goto the events selection tab and click on the Column Filters button.  Select “LoginName” and then expand “Like” on the right hand side.  In the new text box, type the login you are using to authenticate to the SQL Instance in question (domain\user or SQLLogin).
  4. Click run and browse through the results for a bit.  It shouldn’t take too long for that query to pop up.
  5. Once you find the query, make sure you stop the trace.
What is the point in this?  Well, as explained in that other blog, sometimes it is just better to run a query than to run Activity Monitor.  Furthermore, you can take this method and trap many of the queries that Microsoft built into that tool.  You can either use them for learning or troubleshooting.  This is just one more tool to have in your arsenal in order to become better at your job.

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!

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!

Life Support 2008 – Audit Logons

With SQL Server 2008 and 2008R2 limping along and becoming terminally ill (End of Life Support was July 9, 2019), it should come as no surprise that it is recommended to migrate/upgrade affected databases/servers to newer technology.

Planning to upgrade/migrate requires a fair amount of prep work. Some of that prep work involves auditing your server for any users that may still be using the instance.

Where does one even begin in order to audit those logon events in SQL 2008 or 2008R2? Some may say to use a SQL Server Audit but that feature is an Enterprise only feature for 2008 and R2. If we were on a newer version of SQL Server, Extended Events would be the easy choice. Unfortunately, XE was not adequately mature on 2008 or R2. XE just doesn’t have the appropriate events to use in these versions of SQL Server. What’s left?

How about a server side trace? Yes, that was a bit difficult to say. Server side trace does have just the right events for us to use to discover who is using the server and which principals can be eradicated. A big benefit here is that a server side trace does not come with a service outage as would be required for other methods.

Server Side Trace

How do we go about creating a trace to capture these events? It is common knowledge that using a script to create a server side trace is not very intuitive. It is also not very plausible to run a Profiler session and leave it running for weeks while you do your due diligence. There is a shortcut available that allows us to run a server side trace but it does require the use of Profiler – for just a tiny bit. You can start here to find how to do that.

Great, we have a method to create the script. What needs to go into this session? Let’s take a look at that. Ignoring the initial steps to start a profiler session (other than to use a blank template), let’s jump to the event selection tab. From there, we will make two selections (Logon and Login Failed in the Security Audit section) as shown in the next image.

Once the events are selected, go ahead and click run. From there you may stop the session and then continue with the instructions from the previous link on how to script a profiler session.

After scripting this session, I get a script that looks like the following.

I created this script from SSMS 18.0 and find it interesting that the script says “Created by: SQL Server 2019 CTP2.4 Profiler”. Despite the very recent version of SSMS used to create this script, this script will work perfectly fine on SQL Server 2008 or R2.

Once I start the trace on a server, I am ready to do just a little bit more. I want to verify what this script means. Afterall, it is a bunch of numeric values. Let’s look at that with this next query and then compare it to the initial script used to create the trace. The following query requires SQL 2012 or later.

This produces output similar to the following.

As you scroll through the list, you can see the Event Name along with the column name for all events/columns that have been added to the trace. I have highlighted the first event / column mappings to illustrate this relationship.

Cool! Now, I know the session does indeed contain the requisite data that I wanted so it is time to start checking to see what is happening on the server.

Now you are all set to go to start figuring out which logins (if any) are still connecting to the server and using databases on that server.

The Wrap

This article has shown how to audit the logon events for a SQL 2008/R2 instance. It also happens to be an excellent follow up to another recent article – here. As you begin to plan your migration off of the SQL 2008 dinosaur, it is essential to baseline the activity and use of the server. This audit session will help you do exactly that!

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

«page 1 of 6

Calendar
April 2020
M T W T F S S
« Mar    
 12345
6789101112
13141516171819
20212223242526
27282930  

Welcome , today is Sunday, April 5, 2020