Shredding Extended Event Actions

lovehatedice_v1The other day I wrote about that torrid love/hate relationship DBAs tend to have with working with XML. In that same post, I promised I would have a follow up post about XML in Extended Events.

Well, today I present to you another opportunity for you to renew your love/hate relationship with XML.

In the previous post (which you can read here), I discussed just one facet of shredding the XML related to Extended Events. Today, we have “actions” to discuss. I am not going to get into what an “action” is today. I will save that for a near future post.

For the shredding of the actions, I will use the TreeHuggerCPU Event Session I used in the last article (for the sake of consistency). The session doesn’t necessarily need to be running. I will just pull the actions related to the session from the metadata accessible via the system catalog views.

Should the session be running (and in my case it is for demonstration purposes), I could open the session data and view it as XML and see something like the following:

action_xml

 

I have highlighted two different types of nodes available in the XML data. In the previous article, I discussed the “data” nodes and I have highlighted that in red here. Today, we are talking actions, and those are highlighted in green this time around. It is the “action” nodes that we will be shredding via the following script.

 

With this script, I can either search for all actions tie to an XE Session, for the actions tied to one event within a Single XE Session, for all actions tied to a specific event across multiple sessions, or for all actions that are tied to any event tied to any event session deployed to the server.

Combine this with the previous script and suddenly all of that XML just got several times easier.

Shredding XML in XEvents

lovehateOne of the biggest pains with Extended Events is the thing we love to hate – XML. XML is so foreign to many DBAs. It’s not relational and often brings nightmares of parsing and performance issues.

Despite that, Extended Events takes advantage of XML much like we have seen in so many other areas of SQL Server. You might be familiar with execution plans, SSRS, SSIS, or maybe even the ring buffer. If you look hard enough, you will find XML within the database engine. Whether you love or hate it, you still have to deal with it. Today, I want to dive into a means of dealing with XML, as far as Extended Events is concerned.

Mad Scientist Lab

Let’s head on over to the lab to dive into XML head first. I will be using a session as an example of which I have previously written – here.

If the session is already deployed – great. If not, you may need to create it to execute (successfully) these scripts. Note that I am starting the session and then fetching some data and then stopping the session. To see some data, you may want to wait a few cycles before stopping the event session.

The sole purpose is just so I can take a look at the session data in XML format. Now that I have some data, I would see something that might look like the following:

For today’s lab, I just want to focus on the “data” node while saving the attributes of the event node, and the action node(s) for another discussion.

xml_datanode

 

The “data” node happens to be the data that is directly tied to an Extended Event event. When looking at the event metadata, this would be called the event columns. Knowing that all of these columns follow a fairly standard format can make it a bit easier to figure out how to query this data. One of the daunting things with XML is figuring out how to query the XML data to make it more relational – a format we may be more accustomed to seeing (as DBAs).

Due to the daunting task of figuring out how to query the XML and because it is a pretty decent format for consumption, I decided to simplify the entire process. Why not write some code that will write the XML parsing code for me? And that is what we have here.

This script will take an event session name, an Extended Event event name, or a combination of both (imagine having multiple events tied to a session) to produce the XML strings automagically. This script does only produce some pretty generic column aliases, so that part is left to the user of the script to edit after generating the XML parse statements.

With this script, I can quickly retrieve all of the XML parse statements for all of the data nodes within the session or event that I specify. This can significantly reduce the amount of time taken to produce a usable script to consume the event session data.

This is just the script to parse the event data. If there are actions tied to the session, this will not produce the statements for those actions. The script for that will be provided in a future article. Stay tuned!

Tables, Columns and Defaults Oh My!

Comments: No Comments
Published on: August 19, 2015

Picture this if you could. You inherit a new database either through the change of a job, or finding a black box database server, or maybe due to the acquisition of a new application.

No matter the cause, you are now responsible for maintaining that database and you also really need to become acquainted with it. Some of the acquaintance that is needed is learning how things are related and figuring out what data goes where. Not just what data, but what type of data.

I know that one of the things I am frequently asked is to figure out what data type belongs with which column. Furthermore, does this same column exist in more than one table. It’s nice when things are named consistently and you are able to tie things together by column name.

There are also times, when that column that is so consistently named, might need to be confirmed on data types between each of the implementations. I know I have run into cases when the column might be differently defined in different tables.

So, what to do to tie this all together and get that intimate acquaintance with your database?

Luckily, I have a script that will help you figure out some of these connections.

First, the script.

Now, let’s give it a trial run. See if you can spot the difference by running that against the AdventureWorks database using the value “AccountNumber” for the @ColName parameter.

ColData_Queryresult

 

Maybe there is a legitimate reason for that Column to have a different data type specification than the other two. Maybe it doesn’t. One thing for certain is that you will be able to walk away with some questions to ask and hopefully gain a better understanding!

What is DBCC?

DB Nuts and BoltsIf I ask you what is DBCC, what would your answer be?

I want you to think about that one for just a little bit. What comes to mind when you hear DBCC? Is it the default go-to used when talking about consistency checks? Is it something more involved? Maybe it is something entirely different?

I ponder this because I hear it from time to time used in complete replacement for CheckDB. I have to stop and think for a minute because as of SQL Server 2016, there are 35 DBCC statements of various use that are documented. Never-mind the bounty of un-documented statements (e.g. page, ind).

So, I ask, what does DBCC mean to you?

In trying to come up with some sort of answer, I did a little bit of research. If you look in the documentation (including the SQL 2000 documentation), one will find this definition for DBCC:

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

It seems the documentation says it pretty clearly that DBCC is Database Console Commands. (And the SQL 2000 documentation says something similar). But every now and then you hear somebody say it means Database Consistency Checker. And it seems the root of that may come from the SQL 6.5 or earlier days. I found this snippet in BOL from SQL 6.5:

Used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on. DBCC is the SQL Server “database consistency checker.” DBCC helps ensure the physical and logical consistency of a database; however, DBCC is not corrective. It is recommended that you make periodic checks to ensure the logical and physical consistency of your data.

This seems a little odd to me since not all DBCC statements are used for database consistency checking as this definition would call it. It seems more of a documentation bug that has taken hold than a legitimate acronym. Just think about it. With that definition, one will also see a list of DBCC statements – many of which do not perform consistency checks. Let’s look at them:

For instance, DBCC PINTABLE is hardly useful for checking consistency. The TRACESTATUS is also one that does not quite fit this acronym. That is likely why you will see the difference in the SQL 2000 and beyond documentation for DBCC.

Don’t be surprised if you hear me ask which DBCC statement is being inferred if I hear somebody say they ran DBCCs. It is just plain more clear to hear “CheckDB came up clean” over the alternative “DBCC came up clean”. There is little room for interpretation there.

In short, DBCC is an acronym for Database Console Command, and it seems more of a documentation mistake when it was called Database Consistency Checker.

Another interesting thought. How many people say DBCC Command(s)? Think about that one for a minute – Database Console Command Command(s). That one is less of an issue imho than the prior.

Just remember, think about what you mean to say when talking about a specific DBCC statement and if you really mean a consistency check, try saying CheckDB instead. I bet the clarity in the conversation will improve and there will be less hair tugging.

HealthySQL – A Review

How well do you know the health of your SQL Servers and databases? Ever wonder about the current health or even what the health was like a few months back? Sure, one could anecdotally say the databases and servers are healthy, but how do you quantify it?

Many have asked or pondered what to do to quantify a healthy SQL server. How do you get the hard numbers to show whether the server is or is not healthy? How do you get the numbers to show that the server is performing just as well or better than it did three months ago?

dbhealth_maintNow we have a way to answer those questions and more! Robert Pearl has finally published his book on Healthy SQL. In the book, Robert takes you on a journey from mapping out a plan, to capturing data, storing that data, and then how to report on that data. You will learn what to capture and what some of the various things actually mean (e.g. waits and indexes).

Throughout the book, you will see that Robert will introduce you to various tools. These tools can either be native to SQL Server or they could be third party tools. You will get a healthyDBgood primer on what the tool is and how to quickly use it as you become acquainted with the internals and the Health of your database and server.

As you progress through the book and learn about what makes a database healthy, you will encounter a chapter on how to store the data and make a repository to track your database health. You will want to create a repository so you can progress into the sections on reporting about your database health. Who doesn’t want to market to management how healthy the databases are?

With the tools in this book, and the repository you will create, audits and discussions will become much easier. You will be able to implement processes to help make you more aware of the environment and help keep you from getting that annoying page in the middle of the night.

Take a look at the book and enjoy.  You can get it from Amazon here.

If you are a young DBA, new to being a DBA, an accidental DBA, or just any DBA looking for a little extra help in figuring out how to check and track the health of your server, it is worth reading this book.

TSQL Recipes – 2014 Edition

Announcing…the book

book_coverAt long last the wait is over. After much blood, sweat and more blood and sweat, the next edition of the SQL Server Recipes book is finished.

This edition brings several changes and quite a bit of re-write. It has been updated for many of the new SQL Server 2014 (well, new, at least until SQL 2016 hits mainstream) features that were added. In addition, we revisited some of the other features that had been omitted from previous editions to try and give it a more encompassing feel.

All of that said, the book is not a comprehensive listing of every command, jot or tittle in SQL Server. There were a finite number of pages and the features in SQL Server just has far too many features and nuances to cover within a single edition.

Despite the limitation of page quantity, we do feel that this is a pretty comprehensive book covering a wide array of features with several examples of how to perform various tasks along with use-cases for when to use the example.

When you crack the covers, you will find examples of how to perform backups to blob storage in Azure, create In-memory OLTP tables, restore from blob storage, and in some cases you will see how to use Extended Events to help with troubleshooting. That is just a small sampling of the contents that fill the almost 900 pages in this book.

Combine this reference book with the previous editions, and you will have an excellent resource for SQL Server.

You can purchase the book on Amazon – here.

Now for the mushy stuff

I am grateful to the folks at Apress for letting us come on board with this book and continue writing about SQL Server and creating a great resource.

I am grateful to the other authors Jonathan Gennick and Wayne Sheffield for helping push this along. The collaboration and support provided by these guys was fantastic. Their patience was also exceptional.

Equally important was the patience and understanding afforded by my family. Writing does take a significant amount of time and they sacrificed so I could continue on this project.

Thanks to all these folks for helping make this a great achievement!

SQL Server and Defaults

TSQL2sDayWhat is that default setting?

SQL server does a fantastic job of having numerous settings at the server level and at the database level predefined for you. Additionally, the OS has a bunch of settings that are predefined. These are notoriously the default settings. Or as some would say: “Set it and Forget it Settings.” Sadly, the set it part never really happens – except during the install.

Today is the second Tuesday of the the month, and that means it is TSQL Tuesday. As I mentioned in a previous article, this month the topic is hosted by Andy Yun (blog | twitter). Andy has chosen to have everybody talk about default settings in SQL Server. You can read everything Andy has said in his invite for the month.

deadlydefaults

Defaults Defaults Defaults

I could ramble on about database settings that get changed, but I have already addressed the changing of database settings – here. While I did address the changing of the settings, I did not address that it is an awesome thing to use to find out who might be changing those default settings (at the database level) that you have already set and optimized for the instance.

Or I could go on about how the OS has various settings that are far less than optimal for SQL Server. A good example would the that tree hugger setting that I talked about last month. But rather than do that, I will advise that you just read that article – here.

Or we can belabor the point about a fantastic setting that equates to NOLOCK. But, that isn’t a default setting and one would need a pretty good reason to change the default setting to READ UNCOMMITTED. So, just read a bit about the coolness you can see in the execution plans when the NOLOCK directive is used – here.

There are just so many wonderful default settings within SQL Server (or within the Windows OS) that could be discussed or that have already been discussed.

About all of those settings, I will say this. Database Settings are not “One Size Fits ALL.” But that is what a default setting is trying to do. If you find default settings within a database, then you should probably evaluate the setting and make sure it is set appropriately.

What default to discuss then?

Rather than talk about a plethora of settings that could / should be changed, I want to speak about one I doubt most would consider. This is the default setting that can only be adjusted from between the ears!

This is a default behavior I have noticed with many DBAs. I have heard various names for this behavior and most are probably quite accurate. The default setting that I think should be changed is one that will lead to a longer more fulfilling career. Let me see if I can describe the behavior well enough.

Often times, companies will purchase monitoring software to help alert to problems within the environment. This is a good thing. A DBA can’t be available 24x7x365. So the software should be configured and tuned to each database to allow for different tolerance/alert thresholds. This is a good thing too – if it is done. When done properly, the DBA can get a good nights rest as well as feel confident the environment is running properly.

If the software is not properly tuned and configured, the DBA will probably rectify that sooner rather than later. This is not the big behavior to change.

What I do see all too often is a complete reliance on the software that is monitoring – to a fault. I have seen hundreds of DBAs just sit and watch the pretty little dials and gauges on the screen of the software and only react when one of the items turns red. A career surely can’t be built off of this kind of behavior.

Rather than sit there and wait for something to fail, why not proactively check the servers? Why not try to build a script repository that will do everything (and more) that the monitoring software can do? While building that repository, think of the skills that will be gained and the knowledge that can be retained across jobs! In addition, I have been some places where a script repository was able to replace the purchased software and saved 100’s of thousands of dollars per year in software maintenance costs.

One of my favorite statements is that a “Senior DBA should be able to script his own solutions!” Being able to create monitoring scripts to replace that canned app will certainly get you to that next level. It will also get you out of that default behavior of complete reliance on the canned software and imminent career stagnation.

Learn a little and grow your career.

Oh, and there are some great monitoring tools out there. They can provide a great asset to a company – if and when used properly.

saynoribbon

PASS Summit 2015 – Guess What?

Comments: No Comments
Published on: July 13, 2015

Quick run the other way!

PASS Summit will never be the same!

Why?

Well, because for the first time in the history of me, I have been selected to speak at such a prestigious event.

I'm Speaking Graphic_Large

Isn’t that just crazy?

In some ways it seems crazy. When I received the email I was exhilarated and completely astonished. I had hoped I would be selected, but did not entirely think it would happen this year.

I have to confess that I am honored to be one of the many to have been selected. This is really cool. You may be wondering why I am blogging about this opportunity soooo late after the speakers were announced. Well, the answer is rather straight forward. Life has been a bit crazy the past few weeks.

The last night of SQL Cruise I received a phone call from my family letting me know that my grandfather had become fatally ill. I spent the next several days with him in the hospital until he passed away a week later. Then there were the holidays and the funeral. And to cap that off, I flew down to Phoenix to speak to the two user groups last week. SO, it has been a whirlwind!

All of that said, I am announcing it now and really am excited to be a apart of such an AWESOME event.

Now for the surprise.

When I discovered which session had been selected, I was very surprised. I expected one of my Extended Events sessions to be picked. Nope! Instead, one of my other fun sessions was picked. We will be talking about Compression in SQL Server at Summit.

I am hopeful we are able to make this an exciting and enjoyable session for the attendees. I hope the attendees will also be able to take the information home and be able to use it for better (or worse) in their environments.

I do hope to see many of you in attendance. On the agenda for the attendees in this session will be:

  1. Dive into functions available to help learn about compression
  2. Evaluation of compression settings and possible savings
  3. Dive into the Pages
  4. A quick look at the CD and compressed data types
  5. Levity

Of course, I could just decide to change this on a whim – buahaha!

New Extended Events for 2016

As we begin to get a grasp of all that is available with SQL Server 2016 and all of the new features, it is a great idea to see what else has been made available to us in order to help support and troubleshoot these new features. With that, it is well worth a look at the new changes to Extended Events to support all the shiny new stuff.

Here is a list of what I could find as of CTP 2 (I have yet to look at CTP 2.1). This list alone has some really cool stuff in it.  Browse through it and you will be able to find new stuff to help support and administer things like Stretch Database as well as JSON. You won’t find these events within Trace or profiler – only XE.

The presence of these new events (even some new ones for Query Store) is quite enough to get me excited for that next release of SQL Server. There is some great stuff coming down the pipe.

I have randomly highlighted some of the new events to support some of these new features. (And since this is only CTP 2.0, one can be sure there will be changes made to this list with the actual release of SQL Server 2016.)

SQLVersion EventName PackageName EventDescription
Microsoft SQL Server 2016 (CTP2.0) availability_replica_database_fault_reporting sqlserver Occurs when a database reports a fault to the availability replica manager which will trigger a replica restart if the database is critical
Microsoft SQL Server 2016 (CTP2.0) backup_restore_progress_trace sqlserver Prints backup/restore progress trace messages with details
Microsoft SQL Server 2016 (CTP2.0) batchmode_sort_spill_file sqlserver Record the spill file read/write information for batch mode sort
Microsoft SQL Server 2016 (CTP2.0) batchmode_sort_status sqlserver Record batch mode sort status
Microsoft SQL Server 2016 (CTP2.0) cl_duration XtpCompile Reports the duration of the C compilation.
Microsoft SQL Server 2016 (CTP2.0) clr_context_dump sqlclr ClrContextDump triggered.
Microsoft SQL Server 2016 (CTP2.0) column_store_expression_filter_apply sqlserver An expression bitmap filter was applied on a rowgroup column batch.
Microsoft SQL Server 2016 (CTP2.0) column_store_expression_filter_bitmap_set sqlserver An expression bitmap filter was set on a rowgroup column at rowgroup compile time.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_closed_rowgroup_with_generationid_found sqlserver Delete buffer can not be flushed due to existence of one or more closed rowgroups with generation ID.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_flush_failed sqlserver Columnstore delete buffer flush failed.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_state_transition sqlserver Occurs when closed delete buffer state changes.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delta_rowgroup_closed sqlserver A delta rowgroup was closed.
Microsoft SQL Server 2016 (CTP2.0) columnstore_no_rowgroup_qualified_for_merge sqlserver A user invoked a REORG command but based on the policy, no rowgroup qualified.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_compressed sqlserver A compressed rowgroup was created.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_merge_complete sqlserver A MERGE operation completed merging columnstore rowgroups together.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_merge_start sqlserver A MERGE operation started merging columnstore rowgroups together.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_begin_delete_buffer_flush sqlserver Columnstore tuple mover started flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_compression_stats sqlserver Statistics about the movement of a deltastore to a compressed rowgroup, including duration, size, etc.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_flush_requirements_not_met sqlserver Occurs when column store tuple mover was not able to acquire required locks for flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met sqlserver Occurs when column store tuple mover was not able to acquire required locks for truncating a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_truncated sqlserver Columnstore tuple mover truncated delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffers_swapped sqlserver Columnstore tuple mover swapped delete buffers.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_end_delete_buffer_flush sqlserver Columnstore tuple mover completed flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_met_requirements_for_delete_buffer_flush sqlserver Occurs when column store tuple mover has acquired required locks and is ready to start flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_met_requirements_for_delete_buffer_truncate sqlserver Occurs when column store tuple mover has acquired required locks and is ready to start truncating a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_x_dbfl_acquired sqlserver Occurs when X Delete Buffer Flush Lock is acquired.
Microsoft SQL Server 2016 (CTP2.0) compressed_alter_column_is_md_only sqlserver Occurs during an alter column operation. Indicates whether the alter column is metadata only or not.
Microsoft SQL Server 2016 (CTP2.0) connection_accept sqlserver Occurs when a new connection is accepted by (or duplicated into) the server. This event serves to log all connection attempts.
Microsoft SQL Server 2016 (CTP2.0) connection_duplication_failure sqlserver Occurs when connection duplication fails
Microsoft SQL Server 2016 (CTP2.0) data_purity_checks_for_dbcompat_130 sqlserver Occurs when an operation that may require a data purity check for dbcompat level 130 occurs.
Microsoft SQL Server 2016 (CTP2.0) database_recovery_times sqlserver Database recovery times
Microsoft SQL Server 2016 (CTP2.0) database_tde_encryption_scan_duration sqlserver Database TDE Encryption Scan
Microsoft SQL Server 2016 (CTP2.0) database_transaction_yield sqlserver Occurs when a database transaction yields execution due to TDS buffer being full.
Microsoft SQL Server 2016 (CTP2.0) ex_raise2 sqlos Raised exception
Microsoft SQL Server 2016 (CTP2.0) fulltextlog_written sqlserver Errorlog written
Microsoft SQL Server 2016 (CTP2.0) global_transaction sqlserver Occurs when global transaction is started.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle sqlserver Occurs when DB log generation throttle changes.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle_configuration_parameters sqlserver Occurs when DB log generation throttle configuration parameters are read.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle_input sqlserver Occurs when HADR Fabric log management component updates log throttle.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_marked_for_reseed sqlserver Occurs when a HADR secondary DB falls too far behind primary and is marked for reseed.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_remote_harden_failure sqlserver A harden request as part of a commit or prepare failed due to remote failure.
Microsoft SQL Server 2016 (CTP2.0) hadr_log_block_send_complete sqlserver Occurs after a log block message has been sent. This event is only used for failpoints.
Microsoft SQL Server 2016 (CTP2.0) hadr_partner_log_send_transition sqlserver Log send transition between log writer and log capture.
Microsoft SQL Server 2016 (CTP2.0) hadr_partner_restart_scan sqlserver Restart partner scans for this partner.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_backup_state_change sqlserver Physical Seeding Backup Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_failure sqlserver Physical Seeding Failure Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_forwarder_state_change sqlserver Physical Seeding Forwarder Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_forwarder_target_state_change sqlserver Physical Seeding Forwarder Target Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_progress sqlserver Physical Seeding Progress Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_restore_state_change sqlserver Physical Seeding Restore Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_submit_callback sqlserver Physical Seeding Submit Callback Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_send_harden_lsn_message sqlserver Occurs when we’re crafting a message to send containing a new hardened LSN on a secondary. Test only.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_configuration_state sqlserver Indicates session state changes
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_dropped_message sqlserver Use this event to trace dropped HADR transport messages throughout the system.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_failure_message sqlserver Use this event to help trace HADR failure messages.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_preconfig_message sqlserver Use this event to help trace HADR preconfig messages.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_sync_send_failure sqlserver Synchronous send failure in hadr transport.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_ucs_registration sqlserver Reports UCS registration state changes
Microsoft SQL Server 2016 (CTP2.0) json_depth_error sqlserver Occurs when depth of json text being parsed is bigger than 128.
Microsoft SQL Server 2016 (CTP2.0) json_parsing_error sqlserver Indicates json parser error. Occurs when json format is not valid.
Microsoft SQL Server 2016 (CTP2.0) json_stackoverflow_error sqlserver Json parser stack overflow.
Microsoft SQL Server 2016 (CTP2.0) json_unescaped_character sqlserver Jsonparser hitted unescaped character in json string.
Microsoft SQL Server 2016 (CTP2.0) log_pool_cache_miss sqlserver Occurs when a log consumer attempts to lookup a block from the log pool but fails to find it.
Microsoft SQL Server 2016 (CTP2.0) log_pool_push_no_free_buffer sqlserver Occurs when log pool push fails to get a free buffer and bails out.
Microsoft SQL Server 2016 (CTP2.0) login_event sqlserver This is an abbreviated version of process_login_finish, containing only the columns required by external monitoring telemetry pipeline.
Microsoft SQL Server 2016 (CTP2.0) notify_on_clr_disabled sqlclr Event_ClrDisabled has been triggered in ClrHost.
Microsoft SQL Server 2016 (CTP2.0) on_app_domain_failure sqlclr AppDomain hit a failure.
Microsoft SQL Server 2016 (CTP2.0) on_app_domain_unloading sqlclr AppDomain is unloading.
Microsoft SQL Server 2016 (CTP2.0) on_host_policy_callback sqlclr IHostPolicyManager received an event.
Microsoft SQL Server 2016 (CTP2.0) on_host_policy_failure sqlclr IHostPolicyManager received an event.
Microsoft SQL Server 2016 (CTP2.0) page_cache_trace sqlserver Modification of the page cache.
Microsoft SQL Server 2016 (CTP2.0) premature_systemthread_wakeup sqlos system thread is woken up prematurely
Microsoft SQL Server 2016 (CTP2.0) private_login_accept sqlserver TDS connection accept event that is logged to private MDS table.
Microsoft SQL Server 2016 (CTP2.0) private_login_finish sqlserver TDS login finish event that is logged to private MDS table.
Microsoft SQL Server 2016 (CTP2.0) process_login_finish sqlserver This event is generated when server is done processing a login (success or failure).
Microsoft SQL Server 2016 (CTP2.0) query_execution_batch_filter sqlserver Occurs when batch processing filters one batch using expression services.
Microsoft SQL Server 2016 (CTP2.0) query_execution_batch_spill_started sqlserver Occurs when batch operator runs out of granted memory and initiates spilling to disk of another partition of in-memory data.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_rowgroup_scan_finished sqlserver Occurs when row bucket processor finishes column store row group scan.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_segment_scan_finished sqlserver Occurs when row bucket processor finishes column store segment scan.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_segment_scan_started sqlserver Occurs when column segment scan starts.
Microsoft SQL Server 2016 (CTP2.0) query_memory_grant_blocking sqlserver Occurs when a query is blocking other queries while waiting for memory grant
Microsoft SQL Server 2016 (CTP2.0) query_memory_grant_usage sqlserver Occurs at the end of query processing for queries with memory grant over 5MB to let users know about memory grant inaccuracies
Microsoft SQL Server 2016 (CTP2.0) query_store_background_task_persist_finished qds Fired if the background task for Query Store data persistence is completed successfully
Microsoft SQL Server 2016 (CTP2.0) query_store_background_task_persist_started qds Fired if the background task for Query Store data persistence started execution
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_abort_capture qds Fired when an UNDECIDED query failed to transition to CAPTURED.
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_evaluate qds Fired when the capture policy is evaluated for a query.
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_start_capture qds Fired when an UNDECIDED query is transitioning to CAPTURED.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_data_structs_not_released qds Fired if Query Store data structures are not released when feature is turned OFF.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_diagnostics qds Periodically fired with Query Store diagnostics on database level.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_settings_changed qds Fired when Query Store settings are changed.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_whitelisting_changed qds Fired when Query Store database whitelisting state is changed.
Microsoft SQL Server 2016 (CTP2.0) query_store_generate_showplan_failure qds Fired when Query Store failed to store a query plan because the showplan generation failed.
Microsoft SQL Server 2016 (CTP2.0) query_store_global_mem_obj_size_kb qds Periodically fired with Query Store global memory object size.
Microsoft SQL Server 2016 (CTP2.0) query_store_load_started qds Fired when query store load is started
Microsoft SQL Server 2016 (CTP2.0) query_store_schema_consistency_check_failure qds Fired when the QDS schema consistency check failed.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_finished qds Fired when size retention policy clean-up task is finished.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_skipped qds Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_started qds Fired when size retention policy clean-up task is started.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_plan_cost qds Fired when eviction cost is calculated for the plan.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_query_cost qds Fired when query eviction cost is calculated for the query.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_query_deleted qds Fired when size based retention policy deletes a query from Query Store.
Microsoft SQL Server 2016 (CTP2.0) query_trace_column_values sqlserver Trace output column values of each row on each query plan operator
Microsoft SQL Server 2016 (CTP2.0) recalculate_mem_target sqlos New Memory Targets which are set after RecalculateTarget is executed
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_db_ddl sqlserver Occurs when the database T-SQL ddl for stretching data is processed.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_provision_operation sqlserver Occurs when a provisioning operation starts or ends.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_query_rewrite sqlserver Occurs when RelOp_Get is replaced during query rewrite for Stretch.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_table_ddl sqlserver Occurs when the table T-SQL ddl for stretching data is processed.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_telemetry sqlserver Occurs whenever an on premise system transmits a telemetry event to Azure DB.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_telemetry_rejected sqlserver Occurs whenever an AzureDB Stretch telemetry event is rejected
Microsoft SQL Server 2016 (CTP2.0) report_login_failure sqlserver This event is generated for a TDS login failure.
Microsoft SQL Server 2016 (CTP2.0) rpc_starting_aggregate sqlserver Occurs periodically, aggregating all occasions an rpc call is started.
Microsoft SQL Server 2016 (CTP2.0) rpc_starting_aggregate_xdb sqlserver Occurs periodically, aggregating all occasions an rpc call is started.
Microsoft SQL Server 2016 (CTP2.0) sql_batch_starting_aggregate sqlserver Occurs periodically, aggregating all occasions a sql batch is started.
Microsoft SQL Server 2016 (CTP2.0) sql_batch_starting_aggregate_xdb sqlserver Occurs periodically, aggregating all occasions a sql batch is started.
Microsoft SQL Server 2016 (CTP2.0) startup_dependency_completed sqlserver Occurs on the completion of a startup dependency in the SQL Server startup sequence
Microsoft SQL Server 2016 (CTP2.0) stretch_codegen_errorlog sqlserver Reports the output from the code generator
Microsoft SQL Server 2016 (CTP2.0) stretch_codegen_start sqlserver Reports the start of stretch code generation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_migration_proc_start sqlserver Reports the start of migration procedure creation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_remote_table_start sqlserver Reports the start of remote table creation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_update_trigger_start sqlserver Reports the start of create update trigger for remote data archive table
Microsoft SQL Server 2016 (CTP2.0) stretch_database_disable_completed sqlserver Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command
Microsoft SQL Server 2016 (CTP2.0) stretch_database_enable_completed sqlserver Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command
Microsoft SQL Server 2016 (CTP2.0) stretch_database_events_submitted sqlserver Reports the completion telemetry transfer
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_debug_trace sqlserver Debug trace of stretch migration actions.
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_queue_migration sqlserver Queue a packet for starting migration of the database and object.
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_sp_stretch_get_batch_id sqlserver Call sp_stretch_get_batch_id
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_start_migration sqlserver Start migration of the database and object.
Microsoft SQL Server 2016 (CTP2.0) stretch_sync_metadata_start sqlserver Reports the start of metadata checks during the migration task.
Microsoft SQL Server 2016 (CTP2.0) stretch_table_codegen_completed sqlserver Reports the completion of code generation for a stretched table
Microsoft SQL Server 2016 (CTP2.0) stretch_table_provisioning_step_duration sqlserver Reports the duration of a stretched table provisioning operation
Microsoft SQL Server 2016 (CTP2.0) stretch_table_remote_creation_completed sqlserver Reports the completion of remote execution for the generated code for a stretched table
Microsoft SQL Server 2016 (CTP2.0) stretch_table_row_migration_event sqlserver Reports the completion of the migration of a batch of rows
Microsoft SQL Server 2016 (CTP2.0) stretch_table_row_migration_results_event sqlserver Reports an error or completion of a successful migration of a number of batches of rows
Microsoft SQL Server 2016 (CTP2.0) stretch_table_unprovision_completed sqlserver Reports the completion removal of local resources for a table that was unstretched
Microsoft SQL Server 2016 (CTP2.0) stretch_table_validation_error sqlserver Reports the completion of validation for a table when the user enables stretch
Microsoft SQL Server 2016 (CTP2.0) stretch_unprovision_table_start sqlserver Reports the start of stretch table un-provisioning
Microsoft SQL Server 2016 (CTP2.0) trace_dump_deleted_object_table_row XtpEngine Dump deleted object table row
Microsoft SQL Server 2016 (CTP2.0) trust_verification_failed sqlserver Occurs when a SQL Server binary fails Authenticode signature verification.
Microsoft SQL Server 2016 (CTP2.0) ucs_negotiation_completion ucs UCS transport connection negotiation completed
Microsoft SQL Server 2016 (CTP2.0) unable_to_verify_trust sqlserver Occurs when SQL Server is unable to perform Authenticode signature verification on binaries.
Microsoft SQL Server 2016 (CTP2.0) xio_blob_properties_obtained sqlserver Windows Azure Storage blob property is obtained from response header.
Microsoft SQL Server 2016 (CTP2.0) xio_failed_request sqlserver Failed to complete a request to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_header_obtained sqlserver Response header is obtained from request to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_read_complete sqlserver Read complete from Windows Azure Storage response.
Microsoft SQL Server 2016 (CTP2.0) xio_request_opened sqlserver A request is opened to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_send_complete sqlserver Request send to Windows Azure Storage is complete.
Microsoft SQL Server 2016 (CTP2.0) xio_write_complete sqlserver Request send to Windows Azure Storage is complete.
Microsoft SQL Server 2016 (CTP2.0) xstore_acquire_lease sqlserver The properties of the lease acquire reques.
Microsoft SQL Server 2016 (CTP2.0) xstore_create_file sqlserver Creating an XStore file has been attempted with the options below.
Microsoft SQL Server 2016 (CTP2.0) xstore_debug_trace sqlserver Telemetry tracing event has occurred.
Microsoft SQL Server 2016 (CTP2.0) xstore_lease_renewal_request sqlserver Attempt to renew blob lease
Microsoft SQL Server 2016 (CTP2.0) xtp_alter_table sqlserver Occurs at start of XTP table altering.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_abort_checkpoint XtpEngine Indicates that the checkpoint close thread aborted a checkpoint.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_close_checkpoint XtpEngine Indicates that the checkpoint close thread hardened a checkpoint.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_close_install_merge XtpEngine Indicates that the checkpoint close thread installed a merge.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_new_segment_definition XtpEngine Indicates that the checkpoint controller processed a new segment definition.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_storage_array_grow XtpEngine Indicates the XTP storage array has grown in size.
Microsoft SQL Server 2016 (CTP2.0) xtp_drop_table sqlserver Occurs after an XTP table has been dropped.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_request_start XtpEngine Indicates that an XTP storage merge was requested.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_request_stop XtpEngine Indicates that an XTP storage merge request ended.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_start XtpEngine Indicates that an XTP storage merge range is starting.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_stop XtpEngine Indicates that an XTP storage merge range completed.
Microsoft SQL Server 2016 (CTP2.0) xtp_redo_log_corruption XtpEngine Indicates that the redo process failed due to log corruption.
Microsoft SQL Server 2016 (CTP2.0) xtp_root_file_deserialized XtpEngine Indicates that the load of a checkpoint root file is complete.
Microsoft SQL Server 2016 (CTP2.0) xtp_root_file_serialized XtpEngine Indicates that the write of the checkpoint root file is complete.

There you have it, currently 165 new events available to help monitor and troubleshoot SQL Server.

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.

Enjoy!

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.

xe_results

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.

«page 1 of 19






Calendar
September 2015
M T W T F S S
« Aug    
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, September 3, 2015