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.

Ghosts in your Database

Yes Virginia, there are ghosts in your database.  More specifically, there are ghosts in your SQL Server database.  They are not there to haunt you.  They are not there just for this holiday season (speaking of Halloween Month).

How can there be ghosts in the database?

Why would there be ghosts in the database?

Do they happen because somebody issued a KILL statement?

Let’s address each of those in turn. ¬†¬†A database ghost record is (in a very basic form) one that’s just been deleted in an index on a table¬†. Delete operations don’t actually¬†physically remove records from pages ‚Äď they¬†only mark them as having been deleted¬†(ghosted). Now why is it done this way? ¬†The answer here is largely¬†performance based. ¬†This is a performance optimization that allows delete operations to complete more quickly. Additionally, it¬†allows the¬†rollback of delete operations to process more quickly. ¬†The rollback processes faster because all that needs to happen is to “flip the flag”¬†for the records as being deleted/ghosted, instead of having to reinsert the deleted records. ¬†That may be a bit over-generalized, but I hope you get the gist. ¬†In short, records are marked as “ghosted” when a delete operation is performed; and to rollback, you simply undo that mark.

Now, what about this KILL statement thing?  The kill statement is pure Halloween fun and does not create ghost records.

Ghost Hunting

Now that we have established the purpose of Ghosts in the database, how do you verify the existence of Ghosts? ¬†In other words, what can we do to prove there really are spectral things in the database? ¬†This is where the fun really begins. ¬†First, we need to get out the equipment and tools (as any good ghost hunter would do) so we can capture these phantasms. ¬†Let’s call the first tool the “trap”. ¬†Here is what you will need for it.

This trap, err database, can be a bit large. ¬†As currently configured, we will need about 16GB of disk space to support it. ¬†If that is too much, I recommend removing the last column – “TheBlob”. ¬†As you can see, we are setting a rather large trap. ¬†The table we create (Halloween.Ghosts) will receive One Million records. ¬†This is most probably overkill to catch these ghosts, so you can also cut back on the number of records to be affected.

Now, to make sure we have some data and that we can use the table, let’s just run a little test query.

Excellent, we have a good sample of data.

database ghost records

At this point, it is important to note that we have done nothing that will cause database ghost records. ¬†All that has been done is to set the framework so we can see the ghosts. ¬†With the framework in place, let’s try to catch some ghosts. ¬†To do so, we need to try to delete something. ¬†Since we just happen to have had a clerical error in our database, we have 666 prime candidates to try and fix. ¬†We happen to have several records that were supposed to be given a Slimer date of Halloween. ¬†The clerk, being absent minded, thought that Halloween was supposed to be on Oct. 30. ¬†Our business model dictates that the invalid records must be deleted first and then we can try to enter the replacement records. ¬†So, let’s go ahead and try to remove those records.

Before we remove the records though, we need to discuss one important requirement for us to be able to see the ghosts. ¬†Let’s call it spectral vision goggles. ¬†In the database realm, we call it a trace flag. ¬†In order to see the the ghosts on the pages, we need to enable TF 661. ¬†We can do that with the following statement. ¬†There is a serious side effect to this method too – it alters the behavior of the Ecto Containment Unit or automatic ghost cleanup process. ¬†If you enable this, you will need to disable it later and/or manually run a ghost cleanup.

Now that we have the last piece of equipment in place, let’s go ahead and try to delete some records.

With all of those records deleted (all 666 of them), let’s see what we might have captured. ¬†First, let’s take a look at some index stats.

If we look at the output of this query, we will see that we did indeed attempt to delete 666 records.  Those records will now display in the ghost_record_count column.  We will also see that, since we had two indexes on the table, there are 666 ghost records marked on each index.


Very cool! ¬†We are definitely on the track to capturing those ghosts. ¬†We have a trail that they exist in the database. ¬†Let’s keep going and see where we can see them. ¬†You should note that there is an additional column in our result set that looks like it might be related to ghost records. ¬†We are going to leave the discovery of version_ghost_record_count as a homework experiment for you to perform. ¬†It is beyond the current scope of this article.

Now this is getting exciting.  We have stronger evidence in the log showing that these ghosts are hanging around in the database.  Not only are they hanging around in the database, we can see which pages in the database on which they are trying to hide.


This is really solid information!  fn_dblog is giving us just about everything we need in order to get those ghosts.  It took a little bit of work since the log reports the page number in hex.  Converting that to an integer page number is essential for us to look at the page (besides integer values are easier to interpret for most people).  Now I can take that PageID and pass that number, for any of the records reported by fn_dblog, and pass it into yet another undocumented procedure known as DBCC Page.

When looking to use DBCC page, we can either look at the PFS Page and see more pages that have ghost record counts. ¬†Or we can take the results seen from the fn_dblog output ¬†and then look at the contents of the page and catch those ghosts. ¬†We will take a quick look at the PFS page first. ¬†Then we will take a look at an index page next. ¬†In this database that we have created, the PFS page will show several other pages that have ghost records on them. ¬†Due to the size (over 2 million pages), we only see index pages with ghost records in that result. ¬†If our database were smaller, we would quite possibly see data pages in our first PFS page of the database. ¬†Let’s see a sample from the first PFS in this database.


We can follow that link from this point to page 126.  Page 126 happens to be an index page similar to the following.  There are a couple of indicators that this is an index page.  First being that when we run DBCC Page with a format of 3, we will see two result sets.  The second result set will show statistics and index information.  The second being in the image attached after the query.  We will leave it as an exercise to you to see other ways to demonstrate that this is an index page.


That is great, but we have more ghosts to find. ¬†Let’s look at a ghost on a data page. ¬†Randomly picking a PageID from that list that was output from fn_dblog, let’s see what DBCC Page will provide to us.



Well, isn’t¬†that just cool! ¬†We have trapped a bunch of ghosts and were even able to see them. ¬†This has been a fantastic deep dive into the crypts of the database. ¬†This is merely a scratch on the surface though. ¬†We hope this will encourage you to explore a bit and at least try one of the homework assignments we left behind in this article.

With all of that, we have a bit of cleanup to do. ¬†The cleanup comes in one of two methods. ¬†Method one involves manual labor. ¬†Method two involves our friendly little trace flag we already used. ¬†Since most DBAs prefer the automated mechanisms over manual, let’s just discuss method two for now. ¬†It is extremely effortless.

That will put the system back to the way it was when we started (and of course we trust that nobody did this on their prod box).

This has been one of a few articles about ghosts in the database. You can check out some of the others here and here.

SQL Server Haunt 2017

Comments: No Comments
Published on: November 1, 2017

Halloween is a great time of year. It is unfortunate that it is just one day of the year. That said, I do like to think of the phantasmripmonth of October as Halloween Month. I have several posts over the years that geek out over the cross-over between Halloween and SQL Server.

The undead of Halloween are now upon us. Among the hordes of data zombies roaming the streets in lab-coats and fishnet stockings, few of us are still scrambling to remove the hexes we have looming over our data.

As chance would have it, these hexidecimals, err hexes, have a more profound effect on us than we first thought. Many may have yet to even recognize the impact of the hexes placed along with the monsters that now lurk in the data after having been summoned via those hexes.

DB and Fun Related

Seeing as I am a really big fan of this holiday I have a few Halloween posts over the years. If you are interested in the previous Halloween posts, here is a list of a few of them:

All Halloween posts

That list is my Halloween treat this year. Now for a bit of a trick with a very strong warning. Because of this warning, I am not posting any code showing how to perform the trick.


The contents of this post are for the intent of HUMOR!

Freddy Kreuger


I can hear you clamoring from half a world away right now – “There is nothing called ‘Freddy Kreuger’ in SQL Server. What in the world are you talking about?”

You would be very accurate in your exclamation there. But this is not necessarily a strict exercise in feature names within SQL Server. I want you to think a little further outside the norms for a while.

Do you currently or have you ever needed to shred XML? XML shredding via TSQL can be a monstrously bloody killer to your database performance. As it turns out, Mr. Kreuger was also a monstrously bloody shredder.

Jason Voorheese

Yet another beast that is not truly in SQL Server, or is it? A not so new but new feature in SQL Server is called JSON. This feature does actually perform better than XML in some regards. That said, we do have a very common problem between the two of these features – blobs.

If you are not familiar with what that means -here you go. A blob is an overly large item being stored in the database. If you wish, you could correlate that to the other well known Halloween beast – “The Blob”.

Over time, this blob acts like sludge and just slows down your database queries. In addition, like the creature, the blob in your database tends to continue to grow in size and is seemingly never able to be put in check.


When I find skeletons, I have to be honest, I don’t find them terribly frightening. When talking about skeletons in your database, I am even less frightened.

Then again, when I run into the situation as described recently, in this post, I may get a bit of a startle and get just a wee bit concerned.

Overall though, I am rarely startled or frightened by any skeletons in the database. These are really just the supporting structures of a nice secure database and are called “schemas”. See, not really all that frightening here if we think about it just a bit.

This next one however, might be a little harder and should be nearly enough to cause some heart pain.


Surely there are no wizarding type of people in the database, right? Warlocks? I know for absolute certainty that there is no such feature or anything remotely close to a warlock, witch or wizard within the database. That is unless my database is about mystical creatures and people.

Alas, I urge you again to expand the box of perception a little bit and become just a tiny bit imaginative. This one, truth be told, does require a fair amount of explanation and imagination though.

The problem comes in part from some magical data issues that can occur due to this particular feature. In addition, this also comes from the wonderful grammatical errors from various blog posts and forums out there mis-spelling “which” as “witch”. Since “manwich” is really close to “man-witch”, I am calling it a warlock.

Now, since I am calling it a warlock, that leads us to the next strong hint about the feature. “Lock” in this case is the key. Now which magical, imaginative feature might there be that is related to “lock”? That would be the “nolock” directive and all of the data quality issues that it presents. Here is a really really good recap (by Aaron Bertrand) on this feature along with reference to it being “magic” – at this site.


This is probably the easiest of the day by far. For all the data loving geeks out there, SQL Server has this pirate flavored way for you to get your drool on. This feature is called “R”. Yup – just like what a pirate says matey.

R is a tool to be used by data scientists or data geeks in general to try and throw together many different flavors of statistical analysis about your data.

Split Brain

Finally, (at least for this Halloween) we have this condition that is real within SQL Server. While treatable with long hours and heavy medication, it is something to be feared.

This condition is something rare but it is very real. The split brain syndrome is pretty much a multiple identity personality disorder in your database. If you have multiple nodes in a cluster, mirror or availability group, it is possible for more than one of those nodes to believe it is the master node and then for different transactions to become hardened in each of those nodes.

When this happens, you will not be able to use bleach to clean up the mess. Instead, you will be required to spend a grundle of time with your database cuddling it and nursing it back to data consistency and good mental health.

Last but not least, HAPPY HALLOWEEN!

Seattle SQL Pro Workshop 2017

Categories: News, Professional, SSC
Comments: No Comments
Published on: October 19, 2017

Seattle SQL Pro Workshop 2017

October is a great time of year for the SQL Server and Data professional. There are several conferences but the biggest happens to be in the Emerald City РSeattle. db_resuscitate

Some friends and I have come together the past few years to put on an extra day of learning leading up to this massive conference. We call it the Seattle SQL Pro Workshop. I have created an Eventbrite page with all the gory details here.

That massive conference I have mentioned – you might have heard of it as well. It is called PASS Summit and you can find out a wealth of info from the website. Granted there are plenty of paid precon events sanctioned by PASS, we by no means are competing against them. We are trying to supplement the training and offer an extra avenue to any who could not attend the paid precons or who may be in town for only part of the day on Tuesday.

This year, we have a collision of sorts with this event. We are holding the event on Halloween – Oct 31, 2017. With it being Halloween, we welcome any who wish to attend the workshop in FULL costume.

So, what kinds of things will we cover at the event? I am glad you asked. Jimmy May will be there to talk about IO. Gail Shaw will be talking about the Query Optimizer (QO). Louis (Dr. SQL) will be taking us deep into Hierarchies. Andy Leonard will be exploring BIML and Wayne Sheffield will be showing us some SQL Injection attacks.

That is the 35,000 foot view of the sessions. You can read more about them from the EventBrite listing – HERE. What I do not yet have up on the is what I will be discussing.

My topic for the workshop will be hopefully something as useful and informative as the cool stuff everybody else is putting together. I will be sharing some insights about a tool from our friends over at Red-Gate that can help to change the face of the landscape in your development environments. This tool as illustrated so nicely by my Trojan Sheep, is called SQL Clone.

I will demonstrate the use of this tool to reduce the storage footprint required in Dev, Test, Stage, QA, UAT, etc etc etc. Based on client case study involving a 2TB database, we will see how this tool can help shrink that footprint to just under 2% – give or take. I will share some discoveries I met along the way and I even hope to show some internals from the SQL Server perspective when using this technology (can somebody say Extended Events to the Rescue?).

Why Attend?

Beyond getting some first rate training from some really awesome community driven types of data professionals, this is a prime opportunity to network with the same top notch individuals. These people are more than MVPs. They are truly technical giants in the data community.

This event gives you an opportunity to learn great stuff while at the same time you will have the chance to network on a more personal level with many peers and professionals. You will also have the opportunity to possibly solve some of your toughest work or career related problems. Believe me, the day spent with this group will be well worth your time and money!

Did I mention that the event is Free (with an optional paid lunch)?

Tricks and Treats with XE

Halloween is a great time of year. It is unfortunate that it is just one day of the year. That said, I do like to think of the phantasmripmonth of October as Halloween Month. I have several posts over the years that geek out over the cross-over between Halloween and SQL Server.

With the proximity to Halloween, it’s not hard (knowing me) to figure out why I originally decided to terminate this series on the Eve of Halloween day. Then again, as it turns out, I decided to bring the series back to life on the other side of Halloween. In short, it was to be killed on Halloween Eve and then implicitly converted to some living dead creature. You pick whether it is a zombie (which is far better suited to SQL Server) or a vampire.

If you are interested in the previous Halloween posts, here is a list of a few of them:

XE Related

Ghosts in your Database I

Ghosts in your Database II

Ghosts – an eXtrasensory Experience

DB and Fun Related

All Halloween posts

That list is my Halloween treat this year. Now for the trick with a very strong warning. Because of this warning, I am not posting any code showing how to perform the trick.



I have debated seriously over even publishing this “trick” for Halloween because of how deliciously evil it is. I will try and paint the picture in broad strokes. I will leave it up to you to connect the dots.


A third party vendor has provided an application along with some¬†database requirements. Among these requirements is that the application account must use the ‘sa’ login. You have a strict policy that ‘sa’ must never be used for any applications or by the end-users. This is an extremely protected account by policy. The dilemma you have been presented is that the CEO insists that this application must be used (never happens right?) and the vendor insists the application will not function properly without the use of ‘sa’ (I can hear you chortle at that).

Now you, as the DBA, are stuck between a rock and an even harder place. Being smart (and somewhat smart and sadistic), you insist that the use of the ‘sa’ login should not be performed because it will break SQL Server (in reality we know the login does not break SQL Server, but something inevitably will break due to a mis-step by the person using it a) when they shouldn’t, and b) because they lack proper training). Inside you are grinning from ear to ear because you have some devilish ideas, some of which you learned by attending a session by Rob Volk (twitter) where he shows some devilish methods to deter the use of ‘sa’.

For the sake of the scenario, let’s just say you have a policy preventing the implementation of logon triggers (as suggested by Rob) due to a couple of mishaps a few months back. Somebody implemented a logon trigger that wasn’t properly configured and it happened to prevent all users from accessing the server (including the dba’s). Ruh roh!

And then…

Later in the week, after reading about the power of Extended Events, you decide to play around and do a little discovery. You come across this blog post that shows you how to find all of the possible events within the XEvent Engine. So you run the script that you found and shown here:

And while looking through the events and descriptions you discover that there is an event to log all of the successful logins. Not thinking anything of the third party vendor (because it just makes your blood boil) you begin to dream of the potential for auditing all successful logins (established connections) for all users and documenting who may be doing what and when.

After taking a few notes about the potential for the login event and getting access and logins under better control, you continue along your journey through Extended Events by reading this article about Actions. Feeling comfortable enough from the article, you do what any good data professional, trying to learn a new topic, would do Рyou start exploring using the provided query:

While looking through the available actions, you see one in particular that causes you to mangledraise an eyebrow (not both, just one). There is an action called “debug_break”. You squirm and ponder for quite a bit at the name and definition of this particular object. You wonder out loud “why would anybody ever want that in there?”

Your co-worker interrupts with “Do what?”

To which you reply “Oh nothing important. It was just something I read.” After which you dismiss it, realize the time of day, pack up and head home for the evening. Hours later after the work day has long since been finished, you awaken in a cold sweat with an “Aha!” that startles your slumbering¬†neighbors dog.¬†Immediately you pull out your laptop, connect to your local sandbox instance and get to work with a wry smile and devious giggle.

Upon returning to work the next day, you call the vendor and work out a solution to build them a sandbox server to provide a proof of concept. You grant them the use of sa to use for their application login. Sitting down together and connected to the new sandbox, the vendor attempts to login and the following happens:


Slack-jawed and speechless the vendor pales in complexion. Deep down inside you giggle like an elementary grade school girl – content with yourself. BATTLE WON!

After the vendor departs with a promise to rework the security requirements, you restart the service on the sandbox and go about the rest of your work week with a silly grin from ear to ear and nobody the wiser.

That concludes the trick. In the tradition of Halloween, you knocked on my virtual door of Extended Events and I, in accordance with the traditions of Halloween, gave you a trick and a treat.

I hope this helps you to understand the power of Extended Events. Thinking through a problem and reaching a solution is what XEvents can help to provide. I hope this Halloween XE article was enjoyable to you. Stay tuned as the XE 60 Day series goes full Zombie starting next week.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


Learning Extended Events in 60 Days

This post will serve as the landing page for a series I am calling 60 Days of Extended Events. The purpose of these posts will be to help somebody progress through the Extended XEisFutureEvents Concepts and become more proficient at implementing and using them.

The 60 days of XE will be a two month series running from September 1, 2015 through October 30, 2015. The posts will go live only on weekdays. So while it is 60 calendar days, it will end up being somewhere in the neighborhood of 45 articles.

These are the  types of articles designed to take 5 to 15 minutes to learn a concept and move on with the work day. The articles are designed to build on previous concepts.

As the posts go live, I will return and update this page with the article title and link.

XE Table of Contents

  1. Shredding the Actions attached to an Extended Event Р01 September 2015
  2. How to View the Metadata for Deployed Extended Event Sessions Р02 September 2015
  3. How to View the Metadata for Running Extended Event Sessions Р03 September 2015
  4. Some Supporting Objects of Extended Events Р04 September 2015
  5. Packages as they Relate to Extended Events Р07 September 2015
  6. What is an Object in Extended Events? Р08 September 2015
  7. Categorization within Extended Events Р09 September 2015
  8. Introduction to Events within Extended Events Р10 September 2015
  9. The Anatomy of an Event Р11 September 2015
  10. Extended Events and Fixed Schemas Р14 September 2015
  11. Actions in Extended Events Р15 September 2015
  12. Storing/Consuming Event Payloads Р16 September 2015
  13. Data Types for your Event Payloads Р17 September 2015
  14. Custom Data / Maps / Lookup Tables for your Event Payloads¬†–¬†18 September 2015
  15. Predicates and Event Data Р21 September 2015
  16. Comparison Predicates Р22 September 2015
  17. Predicate Order is Critical – 23 September 2015
  18. Putting it all together into a Session Р24 September 2015
  19. Extended Events GUI Р25 September 2015
  20. Deployed Session Settings – Metadata – 28 September 2015
  21. Deployed Session Metadata for Events Р29 September 2015
  22. Deployed Session Metadata for Actions Р30 September 2015
  23. Deployed Session Metadata for Targets Р01 October 2015
  24. Set Operations and Metadata Р02 October 2015
  25. Backup Deployed Sessions Р05 October 2015
  26. Extended Event Management Power Tools Р06 October 2015
  27. XEvent Metadata via PoSH Р07 October 2015
  28. Action and Predicate Metadata with PowerShell Р08 October 2015
  29. Target Metadata with PowerShell Р09 October 2015
  30. Simplified Session Backups Р12 October 2015
  31. Intro to Reading Event_file Data Р13 October 2015
  32. Dynamically Read event_file Data Р14 October 2015
  33. Better Practices for Reading Event_file Targets   Р15 October 2015
  34. Jumping into the ring_buffer Р16 October 2015
  35. Know before you Go РTarget Settings Р19 October 2015
  36. Matching Events into Pairs – 20 October 2015
  37. Parsing Matched Events – 21 October 2015
  38. Using and Reading the Histogram Р22 October 2015
  39. Bean Counting and Events Р23 October 2015
  40. Seeing Short-Circuiting at Work Р26 October 2015
  41. Intro to Internals Via XEvents Р27 October 2015
  42. Azure SQL DB and XE Р28 October 2015
  43. Default Sessions Р29 October 2015
  44. Tricks and Treats with XE Р30 October 2015 (Happy Halloween)

At the end of this two month series, there is still just too much to cover. Since I have plenty more in my queue, I have decided to extend the series into overtime. I have no guarantees on how many more posts I will add to the series from here, but I will continue to add more into the series until the queue is depleted.

  1. Log Files from Different Sources Р30 December 2015
  2. Customize The XEvent Log Display Р31 December 2015
  3. Filtering Logged Data Р01 January 2016
  4. Hidden GUI Gems – 02 January 2016
  5. A Day in the Stream Р04 January 2016
  6. Waiting, is it a Bad Thing? Р04 January 2016
  7. Alter Event Session – 07 January 2016
  8. Extended Event Help Queries¬†–¬†19 July 2016
  9. Query to Find the Right Event –¬†21 July 2016
  10. Better Method to Parse Session XML Data¬†–¬†01 August 2016
  11. Using XEvents to Audit Queries¬†–¬†08 August 2016
  12. Finding the Right Path – 24 August 2016
  13. Deprecated Features via XEvents – 30 August 2016
  14. Index Maintenance Operations – 20 December 2017
  15. Feature: XE Profiler – 22 December 2017
  16. XE Permissions – 25 December 2017
  17. XE System Messages – 27 December 2017
  18. Correlate Trace and XE Events – 28 December 2017
  19. An Introduction to Templates – 30 December 2017
  20. Enterprise Thinking with Default Sessions – 9 September 2015
  21. Correlate SQL Trace and Actions – 1 January 2018
  22. Dynamics AX Event Session – 2 January 2018
  23. Sharepoint Diagnostics and XE – 3 January 2018
  24. Checking the Health of your CLR – 16 January 2018
  25. Profiler for Extended Events: Quick Settings – 5 March 2018
  26. Extended Events File Initialization Failure – 9 March 2018
  27. Monitor Database Offline Events – 15 November 2018
  28. How to Translate Event Duration – 20 November 2018
  29. Azure Data Studio and XEvents – 21 November 2018
  30. Ghost Cleanup in SQL – 28 October 2014
  31. Audit Database File Size Changes (v1) – 26 November 2014
  32. Database File Size Changes – 27 November 2018
  33. Where did the Database Go? – 7 April 2015
  34. Track Trace Flag Changes – 6 December 2018
  35. Missing Backup Files (a story) – 10 December 2018
  36. Using XE to Solve a Synonym Issue – 12 April 2016
  37. Quick and Easy XE for Azure DB – 24 December 2018
  38. How to: XEvents as Profiler – 25 December 2018
  39. Upgrading From SQL Server Profiler – 26 December 2018
  40. How to: File Target use in Extended Events – 27 December 2018
  41. SQL Servers Black Box Recorder – Def Trace – 28 December 2018
  42. SQL Servers Black Box Recorder – system_health – 29 December 2018
  43. SQLs Black Box Recorder – sp_server_diagnostics – 30 December 2018
  44. Finding Installed Event Sessions – 31 December 2018
  45. Finding Application Session Settings – 1 January 2019
  46. Checking Your Memory with XE – 2 January 2019
  47. Event Tracing for Windows Target – 3 January 2019
  48. Automatic Tuning Monitoring and Diagnostics – 4 January 2019
  49. Short Circuiting Your Session – 5 January 2019
  50. Audit SQL Agent Jobs – 17 January 2019
  51. Execution Plans in Extended Events – 17 June 2015
  52. XEvents and the Data Collector – 21 December 2012
  53. Retention of XE Session Data in a Table – 3 Jan 2014
  54. Reading Extended Event File Session Data – 21 June 2015
  55. Trapping Online Index Operations – 29 Jan 2015
  56. Audit who Dropped the Database Р7 April 2015
  57. Extended Events and Data Types Р14 April 2015
  58. Energy Savings and Extended Events – 10 June 2015
  59. Database Drops in SQL 2012 – 1 July 2015
  60. New Extended Events for 2016 – 6 July 2015
  61. Database Settings Changes – 8 July 2015
  62. Extended Events Removed from 2016 – 22 July 2015
  63. Shredding XML in XEvents – 26 August 2015
  64. An Experiment with Deadlocks – 25 March 2019
  65. Database Recovery Monitoring with XE – 10 April 2019
  66. Event Log File Paths – 20 May 2019
  67. Mass Backup All Sessions – 22 May 2019
  68. Implicit Conversion Insights with XE – 05 June 2019
  69. Top 5 Methods to Easily Open Event Log Files – 07 June 2019
  70. Get the Source of PREEMPTIVE_OS_PIPEOPS Waits – 13 June 2019
  71. Audit DB File Sizes (Reboot) – 25 June 2019
  72. Database In Use – 3 July 2019
  73. Event Files on Linux – 9 July 2019
  74. Server 2008 ‚Äď Audit Logons – 17 July 2019
  75. Creative Extended Event Sessions – 3 January 2020
  76. Creative XE Sessions – Funky Data – 4 January 2020
  77. Easy Audit Index Changes  Р27 December 2019
  78. PowerShell to Backup XE Session – 10 Jan 2020
  79. Window Shopping the Query Store with XEvents – 11 Mar 2020
  80. Collation Conflict with Extended Events – 12 Mar 2020
  81. Audit Logons with Extended Events – 20 April 2020
  82. TBA

Some rather advanced uses and topic concerning Extended Invents. These are a little more time consuming.

  2. Encryption РTBA
  3. TLS – TBA
  4. NUMA / Schedulers – TBA
  5. TBA – TBA

Ghosts – an eXtrasensory Experience

ghostrip_fireThis is the last article in a mini-series diving into the existence of ghosts and how to find them within your database.

So far this has been a fun and rewarding dive into Elysium to see and chat with these entities.  We have unearthed some means to be able to see these things manifesting themselves in the previous articles.  You can take a look at the previous articles here.

For this article, I had planned to discuss another undocumented method to look into the ghost records and their existence based on what was said on an msdn blog. ¬†But after a lot of research, testing and finally reaching out to Paul Randal, I determined that won’t work. ¬†So that idea was flushed all the way to Tartarus.

Let it be made very clear that DBTABLE does not offer a means to see the ghosts.  Paul and I agree that the other article that mentioned DBTABLE really should have been referring to DBCC Page instead.

Despite flushing the idea to Tartarus, it was not a fruitless dive.  It just was meaningless for the purpose of showing ghosts via that DBCC command.  I still gained value from the dive!!

All of that said, the remainder of the plan still applies and it should be fun.

Really, at this point what is there that hasn’t been done about the ghosts? ¬†Well, if you are well tuned to these apparitions, you may have received the urge to explore them with Extended Events – sometimes called XE for short.

As has been done in the past, before we board Charon’s boat to cross the River Styx to Hades to find these ghosts in Elysium, one really needs to run the setup outlined here.

With the framework in place, you are now ready to explore with XE.

Look at that! There are several possible events that could help us track these ghosts.  Or at the least we could get to know how these ghosts are handled deep down in the confines of Hades, err I mean the database engine.



From these possible events, I opted to work with ghost_cleanup and ghost_cleanup_task_process_pages_for_db_packet.  The sessions I defined to trap our ghost tracks are as follows.

You can see there are two sessions defined for this trip down the Styx. ¬†Each session aptly named for our journey. ¬†The first (GhostHunt) is defined to trap ghost_cleanup and sends that information to a histogram target. ¬†The second (SoulSearch) is defined to use the other event, and is configured to send to the ring_buffer. ¬†Since the second event has a “count” field defined as a part of the event, it will work fine to just send it to the ring buffer for later evaluation.

Once I have the traps, I mean event sessions defined, I can now resume the test harness from the delete step as was previously done in previous articles.  The following Delete is what I will use.

Prior to running that delete though, I checked the Event Session data to confirm a starting baseline.  Prior to the delete, I had the following in my histogram target.




After running the delete, and checking my histogram again, I see the following results.



You can see from this that in addition to the 25 pre-existing ghosts, we had another 672 ghosts (666 of which were from the delete).

This is how I was able to investigate the GhostHunt Extended Event Histogram.

But what about looking at the other event session?

Let’s look at how we can go and investigate that session first and then look at some of the output data.



Cool!  Querying the SoulSearch session has produced some information for various ghosts in the database.  Unlike the histogram session that shows how many ghosts have been cleaned, this session shows us some page ids that could contain some ghosts Рin the present.  I can take page 1030111 for instance and examine the page with DBCC PAGE as follows.



pagealtLook at that page and result!! We have found yet another poltergeist.


Once again we have been able to journey to the depths of the Database engine and explore the ghosts that might be there.  This just happens to illustrate a possible means to investigate those ghosts.  That said, I would not necessarily run these types of event sessions on a persistent basis.  I would only run these sessions if there seems to be an issue with the Ghost cleanup or if you have a strong penchant to learn (on a sandbox server).

Some good information can be learned.  It can also give a little insight into how much data is being deleted on a routine basis from your database.  As a stretch, you could even possibly use something like this to get a handle on knowing the data you support.  Just be cautious with the configuration of the XE and understand that there could be a negative impact on a very busy server.  And certainly proceed at your own risk.

Database Ghosts

phantasmripRecently you may have seen my first article on this topic over at SQL Solutions Group.  If not, here is the link so you can read that article first before proceeding here.  This article is intended as a first of two part follow-up to that article.

Now that you have read that other article, we can recap it a little bit.

You will recall that in that article, I discussed the presence of Ghosts in your database.  I also discussed that those ghosts are a good thing.  They have some benefits to them, of which is a bit of a performance boost for some operations like the rollback of a delete.

In that article I discussed one method with which you could see these ghost records.  In this article, I would like to share an alternate method to be able to see these ghosts.

In order to explore this alternate means, let’s go ahead and follow the first few steps from the other article to get the setup complete so our second ghost hunting foray can begin.

Now with the setup complete, we should once again confirm that we have appropriate data available for the hunt.  Once again a simple query can suffice to show the data.

Upon execution of the check script, we should see something similar to the following data-set.



Great, the data is confirmed and we should have a wonderful chance to find some ghosts once again. ¬†In the previous attempt, we needed an additional plasma blaster¬†in the form of a trace flag. ¬†In this attempt we will hunt these ghosts without that tool and see what we can find. ¬†Why attempt it in this way? ¬†Well, it is simply because I’d rather not use a trace flag if it is not necessary. ¬†If you recall, that trace flag had a couple of noted effects. ¬†One of the effects was that it turned off the ghost cleanup process. ¬†If I can avoid it, I’d rather leave the containment unit in tact.

Now, due to that clerical error of putting Halloween on the wrong date, we need to follow the prescribed policy to delete records prior to creating the proper records.

Notice that we issued the delete in a transaction this time around.  We are going to leave that transaction open for a bit while we hunt those ghosts.

The first step is to verify that some ghosts might be present.  To do that we should run a query such as the following.

From that query we should see something like the following.


Once again we are on the right track. ¬†We can further confirm the existence of these ghosts through a little more investigation and monitoring. ¬†Let’s try the trick with the dblog function again.

With that query, we should see something like the following result set.



Very cool.  We once again can see that these phantasms are in the database.  We have enough information that we can proceed on to the next stage.  We can pass the PageID into DBCC PAGE in order to investigate the ghosts on the page.  If we use the PageID that is circled with the green in the preceding result set, we can get a better feel for these specters.

And we may see results such as the following.


Recall that the log says this page has ghost records on it.  When we check the page with DBCC PAGE we can definitely see that there are ghosts on the page.  This is very cool.  Now, had we tried to check for ghost records on the PFS page we would not be able to see the ghost count like we were able to see by enabling the TF.

Once again we have been able to display the existence of ghosts in the database.  In order to get these ghosts to move on from the database to their afterlife, we merely need to commit the transaction or roll back the transaction.

Stay tuned for the next article in this mini-series about ghosts.  Who knows, we may even have a bonus Halloween article since this is Halloween month.

Part III of the series can now be found here.

Scaring a Database Near you

Comments: No Comments
Published on: October 31, 2013


Something I have a hard time by-passing is a good R2 unit.  I have R2 units in so many different forms, including a standing R2 cake one year for my birthday.  So when I cam across this R2 unit, I just had to share it.

That is a pumpkin carved into the resemblance of R2-D2. ¬†I think it is a mighty fine job too. ¬†It’s amazing how many good Star Wars related pumpkin carvings there are out there. ¬†You probably wouldn’t have too difficult a time finding three or four hundred if you tried a little google-fu.

Each year I try to have something for the Halloween Holiday such as this one or this one.  I failed to provide something in 2012, and this is getting back on the right track.

Despite the ease to find haunting Halloween effects related to SQL Server, I am amazed at how few have even heard of “Halloween Protection” which stems from the “Halloween Problem.”

I am not going to dive into the problem or the protection of it.  I think that has been covered plenty and even quite masterfully by Paul White (blog | twitter).  I recommend that you read his four part series on the topic starting here.

With all of the COSPLAY going about here in the States, I find some of the scarier things to be about either stuff I have previously fixed or about which I have written or tweeted or all of the above.

Take for instance this article about the blatant disregard by some vendors and clients in regards to security. ¬†I still can’t figure out why the public role would ever need to be dbo for a database – at least not a legitimate reason.

Or we can take on the recent time I tweeted about a cursor that I fixed.  I took that scary cursor down from a 30+ hour run time to a mere 50 seconds.  Here is a segment of the execution plan (plan is roughly 4mb in size to give a little scale) zoomed out to 1/5th.



The query was much uglier than that originally.  Imagine that beast looping through on your server for 30 hrs, and that is not even the entire thing.  It is little wonder why things started to drag on the server.

Another scary item I like is the effect of implicit conversions.  That is a topic that can be viewed pretty easily through the use of google-fu.  Here is a short demo on the implications of implicit conversions.

[codesyntax lang=”tsql”]


In this demo I have created three temp tables. ¬†Each is pretty simple in nature and each is to receive 10,000 records. ¬†The insert statement just inserts an integer into each field of each table through the while loop. ¬†Notice that I intentionally named a column in #T3 to be SomeReal but the datatype is an NVARCHAR. ¬†This is to underscore a pet peeve of mine that I have seen over and over again – naming the field in the table after the datatype and the datatype doesn’t even match.

When this query runs, I get the following timing results.



The thing that stands out to me is the huge difference in time between the implicit-free query and the query replete with an implicit conversion.  The implicit conversion query


was about 930 times slower than the query free of implicit conversions. ¬†Granted that query was against a cold cache, so let’s see what happens to an average of five runs each against a warm cache.

With a warm cache I see an average of 51ms for the implicit free query.  On the other hand, the implicit conversion query runs at an average of 84525ms.  That equates to about 1644 times slower.

Sure this was a contrived example. ¬†But keep in mind the table sizes, the datatypes and the number of records in each table. ¬†Had this been a more true to life example with larger tables and millions of records, we could be seeing a query that is far more devastating due to the implicit conversions. ¬†Let’s just call it the grim reaper of your database. (Grim Reaper from

With these horrifying things to haunt your database, I leave you with this new hope as you battle the dark side and grim that is in your database.


Haunting a Database Near You

Comments: 5 Comments
Published on: October 31, 2011

Today, we have a special Halloween edition.  For me, Halloween and computer geek go quite well together.  And thinking about it, I wanted to try to better understand if there was a correlation.  As a DBA, have you wondered the same thing?

Well, I have a short list of five things that may help you to correlate your affinity for Halloween with your love for Databases.


Did you know that a tombstone is a legitimate thing in SQL Server?

Tombstones are replica related. ¬†They are deleted items in the replica and are used to make sure the deleted item doesn’t get put back in the replica inadvertently.

You can read a lot more about tombstones from the msdn article here.

Tombstones are not unique to SQL Server.  These are commonplace in Active Directory as well.



Not all rows that are deleted move on to the afterlife quickly like they should.  Some like to hang around due to unfinished business.

The unfinished business in this case is the server running a cleanup thread.  This has to be done when the server is not too busy and has enough free resources to help these records move on to the afterlife.

You can see the evidence of these ghosts with specialized equipment.  By the use of a DMO, we can see the ghost record count on a per index basis.  The DMO is sys.dm_db_index_physical_stats.  Take a look at the ghost_record_count column in the returned record set.

With more specialized equipment, the engine takes care of the cleanup and removal of these ghosts. ¬†Here is an in-depth foray into the world of SQL ghost hunting. ¬†Whatever you do, don’t cross the streams.


It’s alive!!

No, I killed it!!

It can’t be…How is it still alive?

The transaction will not commit and may get rolled back. ¬†The zombie has reared its’ ugly head. ¬†A transaction that cannot commit but keeps going (or rolls back) due to an unrecoverable error is a zombie transaction.

From MSDN, here is a more specific definition of a zombie.

Rowsets can become zombies if the internal resource on which they depend goes away because a transaction aborts.


The spawn of SQL server is not so much like the Spawn character of the comics.  Nor is it much like the spawn shown to the right.

That is unless it is not managed very well.  SQL can spawn multiple threads if the optimizer deems it necessary for a query.  This is also known as parallelism.

Parallelism can be a good thing or it can be a bad thing. ¬†Understanding it can help keep it on the good side. ¬†You might want to check out some of Paul White’s articles on the topic.

Children of the Corn

Well, this one is not really something in SQL server.  That said, every time I think of orphaned users in SQL server Рchildren of the corn comes to mind.

An orphaned user is one in which the login SID does not match for one reason or another.  This makes it so that the user can no longer log in to SQL server.

If you don’t know about these kids, they can really make for a frustrating day. ¬†Read more here.


I have just covered five things in SQL server that correlate quite closely to Halloween. ¬†But this by no means is an exhaustive list. ¬†For instance, an obvious correlation is the “KILL” command. ¬†Another good one is the monster known as the Blob (read more about that monster here¬†and here).

With the opportunity to have Halloween every day, it’s no wonder I like being a DBA.

Happy Halloween

«page 1 of 2

June 2020
« May    

Welcome , today is Sunday, June 7, 2020