Database Drops in SQL 2012

In the previous article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through the use of Extended Events.

What I forgot to mention is the method I shared was for SQL Server 2014. While the events do exist for SQL Server 2012, there is a minor modification that needs to be made in order to avoid the dreaded error message that says something like:

Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.

I am sure many of us would rather not have to deal with such a terrible thing as an error when we want to do something that should just work. Well, here is the fix for that error if you tried to deploy that XE Session to a previous version (such as 2012).

Do you see that change? Instead of using the map_value in 2012, one must use  the map_key. This was a good change in 2014 to allow us to use human friendly terms instead of needing to lookup the map_key for everything like in 2012.

In following the theme from the previous article, here is the rest of the setup for testing this XEvent session to see how it would trap that data and how to parse the data from the session.


Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.


In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.

SQL Cruise Mediterranean+

Comments: 2 Comments
Published on: June 24, 2015

This is a post that is looooong overdue. I have intended to write some of this at least since the first SQL Cruise I attended back in 2013 (in the Caribbean). Now with three Cruises under the belt and plenty of awesome experiences, it is high time to follow through on those intentions.

Official_SQLCruise_2015For those that have not heard, SQL Cruise is a mix of training and vacation. You get a little (or a lot) of SQL and then you get a little (or a lot) of vacation mixed with the opportunity to see places you may not have otherwise visited. Last but certainly not least is the opportunity afforded to all attendees to meet other people they may have never otherwise gotten a chance to meet. This last benefit may be the most important facet of SQL Cruise in that the networking done during the cruise will last a long time and can certainly open a few doors if/when necessary.

One great example of this networking occurred on the first cruise I attended. In the meetings outside of the scheduled training, one of the other cruisers (he is now an MCM and MVP) asked a pretty important question about an issue within his work environment. A performance monitoring package they ran for all of their clients was causing some serious problems. The problems affected about 30% of all of the servers which numbered over 1500. The application on the affected servers would stop responding and they would no longer receive metrics or alerts to conditions being raised.

This problem was significant enough that they engaged Microsoft and Microsoft engineers had been collecting metrics and logs for over six months (at the time). There was a series of try this and try that and all of it resulted in no change whatsoever. So this fellow cruiser brought the issue to the cruise with him. In chatting with the cruiser (oh, and he had to fly to the US from Europe in order to attend), I discovered that the symptoms he was enduring were quite similar to some things I had seen within SSIS packages for example. We discussed a quick fix which he took back to his employer.

Through a series of tests and deployments, this Cruiser and his employer rolled out the fix to all of the servers in the environment. This fix ended up saving them so much money in labor and other costs, that his employer sent him and his family (eight people in total) back to SQL Cruise in the Caribbean the following year. The ROI for this Cruiser and his employer was huge! Additionally, he and I have become friends thanks to the Cruise. This is the sort of stuff that defines SQL Cruise.

This year, we had the chance to repeat the Cruise by attending the Mediterranean version. This installment was a whirlwind of touring Europe. Some started in London before proceeding to Barcelona and then on to such places as Pompeii, Rome, Pisa, Cannes, Monaco, and Mallorica before returning to Barcelona to continue on to Berlin or London before returning home. For me, we chose to start in Paris and then taking the bullet train on to Barcelona.

Med Sunset

The tourism was fast and furious. The training was faster and more furious. And in the end, the European / Mediterranean trip was gone before we knew it. All who attended surely walked away with great memories and with having learned something (whether it be cultural, historic, or SQL in nature).

If you have the chance to attend a SQL Cruise, I would say do it. The training comes from the technical leads as well as the attendees in the rooms. Often, the tech lead will even defer to any of the other professionals in attendance. Especially in the case of Trace Flags as we learned this past week – in every session and in every office hours meeting. Just ask Grant Fritchey (blog | twitter) about it some time – he loves talking about Trace Flags.

Reading Extended Event File Session Data

Using Extended Events to trap/trace information allows the trapping of that information to various targets. One of the targets I will frequently tell people to use is the file target. The reasoning for this is the opportunity this affords to be able to review the output  at a later time whether the event session is running or not.

Along  with that recommendation, I also show some quick code to pull the data back from the session target (the file on disk). This code is written to dynamically pull the file information out of the XE Dynamic Management Views to make it a little easier on the user (and a bit less likely to have a typo). That code looks a bit like the following:

This works really well when the session is running. It pulls the desired file path and name back from the DMVs and one is able to parse the session data. But what if that session is stopped?

Well, if the session is stopped, we have a problem. With a stopped session, the session above will not produce any results. The short of this is that SQL Server removes the entries from the sys.dm_xe_session_targets and sys.dm_xe_sessions DMVs. Due to that, there is a bit of a tweak to be able to query those files for the sessions that have been stopped.

To query those files, one could rewrite the previous query to something like this:

Now, I bet you may be asking why stop an Extended Event session from running. Afterall, extended events is extremely light weight and has little impact on the server, right? True that may be, there is still a cost for traces. If you can just run a trace for a targeted time period, then why not limit the amount of tracing?

Looking at the code, you can see that I reverted to a less dynamic approach to read those event files. Instead of trying to get that info direct from the database, I am just telling SQL Server where to find the files on disk (note the filepath that is passed).

There you have it. A means to query these trace files whether the XE trace is running or stopped.

Execution Plans in Extended Events

Extended Events is a wonderful tool. Execution Plans are also some wonderful things – or are a wonderful tool as well. Both of these tools are fantastic for troubleshooting. Combined, they can potentially be even better.

Or, they could just cause some hair loss and be a fairly complicated pain in your butt. Hopefully the combination of the two will be more useful than painful for you. And today, I will discuss one of the pains that could be an issue when combining these two tools. I will also discuss some options for getting around this pain point between XE and Execution Plans.

Let’s start by taking a quick look at two Extended Events events that can expose execution plans for you. These two events are query_pre_execution_showplan and query_post_execution_showplan. Here are a couple sample sessions set to trap these events.


With these sample sessions ready, we need a quick description of what is happening. I have built the sessions (at least the TrapEstExecPlans session) to go to both a file and the ringbuffer targets. This was done to test the behavior on both targets to ensure consistency in behavior between the different targets.

Also, I added a bunch of actions that are available to query, but the sample queries shown later will not use the data for those actions. This is only due to the examples I have chosen to share.

With those sessions created, go ahead and start them, then run a query like the following:

After  having executed this query, I can start taking a look at the data captured. I will only query the TrapEstExecPlans session in this post because the behavior between the two sessions and events is consistent.

Keep in mind that I talked about having both a ringbuffer and a file target for this session. I am only going to share the query from the ringbuffer target because the behavior from the memory target and the filetarget is the same.

If I take a look at the execution plans shared from the results of that query, I might see something like this:

Plan from XE


If I look at the execution plan at runtime for that same query, I would see something like this:

Exec Plan

Between the two plans, I have color coded the various attributes of the plan to pair the matching attributes and make it easier to spot the differences. In this case, the differences are in the first two nodes. The plan from Extended Events does not have the connection properties, but is also missing a few other important things like the StatementType and StatementText. Looking at the plan from XE in the gui, you would see something like this:


Instead of this:


Those minor differences in the XML of the execution plans can lead to somewhat of a pain. Alas, there is a fix for that. And the fix comes down to doing one of a few things already available to us in the XE session data. We can either lookup the plan_handle that was trapped by the action, or we can lookup the QueryPlanHash that is available in the XML from the execution plan data that has been trapped. With that information, one can likely retrieve the stored execution plan and catch the rest of the missing components of that execution plan.

There is one other option and that is the action that traps the sql_text. The sql_text that is captured can lead us to understand what kind of plan (select, update, delete, etc) we are looking at from the XE session data.

So, while it is a bit of a nuisance that the two sources of execution plans does not produce the same plan, it is not the end of the world. There is still adequate information available from the XE session data to figure out the missing pieces of the execution plan.

Adventures with NOLOCK

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: June 15, 2015

Some of the beauty of being a database professional is the opportunity to deal with our friend NOLOCK.  For one reason or another this query directive (yes I am calling it a directive* and not a hint) is loved and idolized by vendors, applications, developers, and upper management alike.  The reasons for this vary from one place to the next, as I have found, but it always seems to boil down to the perception that it runs faster.

And yes, queries do sometimes run faster with this directive.  That is until they are found to be the head blocker or that they don’t run any faster because you can write good TSQL.  But we are not going to dive into those issues at this time.

A gem that I recently encountered with NOLOCK was rather welcome.  Not because of the inherent behavior or anomalies that can occur through the use of NOLOCK, but rather because of the discovery made while evaluating an execution plan.  Working with Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) , I came across something that I would rather see more consistently.  Let’s take a look at this example execution plan:



First is a look at the plan to see if you can see what I saw.

Read Uncommitted


And now, we can see it clear as day.  In this particular case, SQL Server decided to remind us that the use of this directive allows uncommitted reads to occur so it throws that directive into the query text of the execution plan as well.  This is awesome!  In short, it is a visual reminder that the use of the NOLOCK directive, while it may be fast at times, is a direct route to potentially bad results by telling the database engine to also read uncommitted data.

How cool is that?  Sadly, I could only reproduce it on this one version of SQL Server so far.  If you can reproduce that type of behavior, please share by posting to the comments which version and what you did.  For me, database settings and server settings had no effect on this behavior.  No trace flags were in use, so no effect there either.  One thing of note, in my testing, this did not happen when querying against a table direct but did only occur when querying against a view (complexity and settings produced no difference in effect for me).

* I would like to make it a goal for every database professional to call this a DIRECTIVE instead of a hint.  A hint implies that you may have a choice about the use of the option specified.  And while NOLOCK does not entirely eliminate locks in the queries, it is an ORDER to the optimizer to use it.  Therefor it is more of a directive than a mere suggestion.

«page 1 of 87

July 2015
« Jun    


Welcome , today is Thursday, July 2, 2015