One Easy Strategy for the Database Enterprise

Comments: 1 Comment
Published on: September 8, 2015



Welcome to the second Tuesday of the month. And in the database world of SQL Server and the SQL Server community, that means it is time for TSQL2SDAY. This month the host is Jen McCown (blog / twitter), half of the MidnightDBA team, and the topic that she wants us to write about is: “Strategies for managing an enterprise”. Specifically, she wants to know “How do you manage an enterprise? Grand strategies? Tips and tricks? Techno hacks? Do tell.”

For me, this month, I will be just doing a real quick entry. I have been more focused on my 60 Days of Extended Events series and was looking for something that might tie into both really well that won’t necessarily be covered in the series, but that might work well as an “Enterprise” worthy topic.

ussenterpriseSo, what I decided to land on was the system_health session.


Wait, isn’t the system_health session one of those things that is configured per Instance?

Yes it is!

The system_health session is a default Extended Events session that is running by default on every instance of SQL Server (keyword is default) since SQL Server 2008. Whether you want it to be running or not is an entirely different conversation. But by default it is running.

There is a small problem with that default though. That problem is in the 2008 and 2008 R2 flavors of SQL Server. The default behavior is that the session only dumps the events to the ring buffer. And if you are only dumping the events to the ring buffer, you can imagine this is not entirely that useful. Why? Well, the ring buffer is just a memory target and is considerably more volatile than to write the event session data out to a file. One need not try terribly hard to see why this can be frustrating (unless of course you didn’t even know it was there).

So what to do to help push this in a more enterprise friendly direction? The answer is to add a file target like was done in the 2012 (and up) flavors of SQL Server. Here is the entire system_health session as defined in u_tables.sql (the backup script of the session deployed to the install directory):

Now, with all of the session data going out to disk, you can also schedule a scraper to copy the files to a central log folder on the network. Unfortunately, placing the files directly on a UNC share (via mapped drive or via UNC naming) does not work in 2008 or R2. I have a few more configurations to run on that still, but it doesn’t look good.

At least by dumping the session data to an event file, you are closer to an enterprise worthy solution. Just remember to do it!

One last thing. After you alter the system_health session, make sure you start it again.


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. Database File Size Changes – 27 November 2018
  32. Where did the Database Go? – 7 April 2015
  33. Track Trace Flag Changes – 6 December 2018
  34. Missing Backup Files (a story) – 10 December 2018
  35. Using XE to Solve a Synonym Issue – 12 April 2016
  36. Quick and Easy XE for Azure DB – 24 December 2018
  37. How to: XEvents as Profiler – 25 December 2018
  38. Upgrading From SQL Server Profiler – 26 December 2018
  39. How to: File Target use in Extended Events – 27 December 2018
  40. SQL Servers Black Box Recorder – Def Trace – 28 December 2018
  41. SQL Servers Black Box Recorder – system_health – 29 December 2018
  42. SQLs Black Box Recorder – sp_server_diagnostics – 30 December 2018
  43. Finding Installed Event Sessions – 31 December 2018
  44. Finding Application Session Settings – 1 January 2019
  45. Checking Your Memory with XE – 2 January 2019
  46. Event Tracing for Windows Target – 3 January 2019
  47. Automatic Tuning Monitoring and Diagnostics – 4 January 2019
  48. Short Circuiting Your Session – 5 January 2019
  49. Audit SQL Agent Jobs – 17 January 2019
  50. Execution Plans in Extended Events – 17 June 2015
  51. XEvents and the Data Collector – 21 December 2012
  52. Retention of XE Session Data in a Table – 3 Jan 2014
  53. Reading Extended Event File Session Data – 21 June 2015
  54. Trapping Online Index Operations – 29 Jan 2015
  55. Audit who Dropped the Database – 7 April 2015
  56. Extended Events and Data Types – 14 April 2015
  57. Energy Savings and Extended Events – 10 June 2015
  58. Database Drops in SQL 2012 – 1 July 2015
  59. New Extended Events for 2016 – 6 July 2015
  60. Database Settings Changes – 8 July 2015
  61. Extended Events Removed from 2016 – 22 July 2015
  62. Shredding XML in XEvents – 26 August 2015
  63. An Experiment with Deadlocks – 25 March 2019
  64. Database Recovery Monitoring with XE – 10 April 2019
  65. Event Log File Paths – 20 May 2019
  66. Mass Backup All Sessions – 22 May 2019
  67. TBA
  68. TBA

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

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

Extended Events Objects

Comments: 3 Comments
Published on: September 8, 2015

db_buildingblocksSo far, I have talked about some of the metadata associated with Extended Events. But I have not yet started to dive into the individual components of that metadata.

Today I want to start diving into some of this data a little deeper as we progress through some of the core concepts surrounding Extended Events. At the base of these core concepts is the data that relates to objects. This is not your ordinary objects like we see in a database such as tables, views, procs and so forth. These are objects specific to Extended Events.


In Extended Events, all of the objects that help to build an extended event session are exposed through the DMV sys.dm_xe_objects. That should not be too much of a surprise after reading my introduction on the topic – here.

Querying that DMV, one will see there are numerous objects. With each release of SQL Server, this list grows larger and larger. With all of these objects, where does one really begin? Well, a good place to start is to figure out the types of objects that are available to use.

Unbelievably simple, right? The results of that query will yield results similar to this:

object_type Description
type Length and characteristics of the byte collection (the data)
event Some point of interest and the information about that point of interest
target Destination of the event. Can be called the event consumer.
pred_compare Compare specific data types and return a bool result
pred_source Retrieve values from event source for use in comparisons
action A means to append run-time data to an event.
map Numeric value to human friendly text translation. EAV model.
message Informational text regarding the Extended Event system

While I would not necessarily think of some of those as objects (e.g. message), it does make sense if thought of as in terms of “components” instead of objects. Each of these components contributes to the whole of the Extended Event System.

Beyond being able to find all of the objects/components that pertain to Extended Events, we can also see that various components have some specialized capabilities. This is shown to us through the capabilities and capabilities_desc columns within the DMV. According to BOL, the capabilities is a bitmap field. Let’s take a look at the various capabilities we can find in the DMV.


The bitmap, when viewed in this order, appears to have different bitmaps for each of the different “components”/objects. But where it seems to lose the whole bitmap feel is around value 512 within the target components. If you do the normal bitmap math, 1536 should be composed of the values for 512 and 1024. Within the target collection, 1024 is the “synchronous” capability, but there is no corresponding 512. It was simply skipped. At least it appears that way.

It would be nice to see the entire bitmap. Even what is documented falls a wee bit short of what is exposed through the DMVs.

If you are curious, here is a bit more showing how some of those capabilities relate to the different objects and types.


I wanted to look at it this way for one simple reason. Take a look back at the capabilities bitmap results from the previous query. Find the value for sign_extended. Which value did you find first? Was it 1280 or was it 256? I wouldn’t normally expect the same capability to be mapped to two different values – with no further explanation of it.

When I combine the type_name to the output, I see that 1280 is related to string data types while 256 is related to numeric data types.

This is mostly trivial information – at least it is on the part of the types. This is helpful when dealing with the other items such as targets. For instance, the singleton synchronize lets me know that target can only have one existence. If I look a bit further, I will see that this target is the etw target. We will discuss targets another day though. Just understand that there are different capabilities for some of these items.

A good one to point out here is the “private” capability. This means it is reserved for internal use by Extended Events.

Stay tuned for more on Extended Events as this series continues. Next up – Channels.


page 1 of 1

September 2015
« Aug   Oct »

Welcome , today is Saturday, May 25, 2019