XEvent Session Supporting Cast

So far, in this series, I have discussed the means to view the metadata associated with Extended Events. I also demonstrated some quick and simple means to retrieve some metadata via the catalog views and DMVs.

Beyond the metadata in the the catalog views and DMVs, there are a few more objects that come in handy when dealing with Extended Events. I call these the supporting cast. This cast would come in the form of yet another DMV and a couple of tables. Granted this is not the end of the tools that can be used when dealing with Extended Events, the remaining tools would be classified differently and are going to be saved for another time.

Supporting Cast

Tables

First up in the supporting cast role would be the group of tables. These are not your ordinary tables. These tables could be your best friend as you work to convert yourself away from server side traces and profiler traces. These tables contain conversion (and therefor static) data to help translate from server trace events to extended event events (yes it is extended event events – sounds redundant but that is what it is).

The tables you will come to love are:

Name Description
trace_xe_event_map One row per Extended Events event that is mapped to a SQL Trace event class.
trace_xe_action_map One row per Extended Events action that is mapped to a SQL Trace column ID.

And, as you might have imagined, the quick and easy way to view what tables might be related to extended events is fairly straight forward as well.

If I query the trace_xe_event_map table, I will see the mapping between 138 trace event ids to the extended event events along with the package through which that event is exposed for extended events. Don’t worry about packages just yet, I will discuss that at a later time. If I do the same thing for the action_map table, I will see a similar result.

As I mentioned previously, these tables will be essential in converting any of those old profiler or server side traces into the XEvent platform.

DMV

Of all of the XEvent DMVs, all but one are used for the metadata of running sessions. The remaining DMV deals more with troubleshooting and session performance than it does with metadata. The name of the DMV is sys.dm_os_dispatcher_pools.

Why is this DMV thrown down to the supporting cast role? Well, despite (current) documentation, this DMV is not exclusively used by Extended Events. You will see dispatcher pools and threads for various different background tasks like XEvents, In-Memory OLTP (Hekaton), and filestream – to name a few.

dispatcherpool

The preceding results could be viewed using a query similar to the following:

If it seems there may be a problem with the server and you just don’t see events dumping into the target as quickly as you think they should, you may want to check this DMV. In the DMV, one will find things like queue_length and dispatcher_waiting_count. A high waiting_count would indicate no events are firing. While a high queue_length would indicate there are many events backing up while waiting to be processed.

Stay tuned for more quick tidbits as I continue this series.

Viewing Running XEvent Metadata

Comments: No Comments
Published on: September 3, 2015

In the previous article, I wrote about getting into some of the metadata to take a peek at session metadata relevant to deployed Extended Event sessions. Today, I will continue talking about metadata. One difference being that today I will focus on diving into the metadata of running sessions.

Running session metadata, while it can be still be viewed in the catalog views, is exposed via DMVs. Putting it a slightly different way, if I query DMVs that are related to extended events, I will only see metadata for running sessions.

DMV

Similar to the catalog views, there is a consistent naming pattern and a simple query to help us figure out what is out there related to Extended Events.

That little query will show us a list of DMVs like the following – descriptions added.

Name Description
sys.dm_xe_objects List of objects exposed by an event package.
sys.dm_xe_object_columns schema information for the objects.
sys.dm_xe_packages Lists all the packages registered with the Extended Events engine.
sys.dm_xe_sessions Lists the Running Extended Events sessions.
sys.dm_xe_session_targets Information about session targets.
sys.dm_xe_session_events Information about events tied to a running session.
sys.dm_xe_session_event_actions Information about the “actions” tied to an event in a running session.
sys.dm_xe_map_values Provides a mapping of internal numeric keys to human-readable text.
sys.dm_xe_session_object_columns Shows the configuration values for objects that are bound to a session.

Since much of this is information about running sessions, it becomes very useful in trying to query the metadata for those sessions. However, if the session is not running and you don’t realize certain DMVs (e.g. sys.dm_xe_session* named DMVs) are only applicable to running sessions – you could end up being rather frustrated.

So, in a similar vein to the query presented in the previous article; if I want to query the DMVs to find where I might be storing session data, I would run something like the following.

This query will show us the file location for each of the deployed sessions that have data saved to a file on the operating system (as opposed to memory). But look at the results real quick. The target_data is more than just the file path. Sure you can manually parse that information from the results. Or you could go back to just using the query provided in the previous article. Or, get daring and try a little XML parsing – with this query.

And here we have yet another one of those frustrating things when dealing with Extended Events. Not only do the DMVs just show the information as it pertains to running sessions, but you also have to deal with XML now. Don’t think this is the last of dealing with XML in XEvents – not by a long shot!

This is just one quick example of the type of data that can be viewed from the running session metadata. Imagine wanting to figure out how long the session has been running. Or maybe you want to know if there is something that might be blocking an event from firing. The data in these DMVs can help you access that kind of data – quickly!

Stay tuned for more XE content soon to come!

Viewing Deployed XEvent Metadata

Comments: 1 Comment
Published on: September 2, 2015

Today will be the first of a few introductory level posts about Extended Events.

When dealing with Extended Events, there are a few ways to take a look some of the metadata. Not all metadata is created equal when dealing with Extended Events. Some of the metadata is pertinent to a running XEvent session and some is pertinent to a deployed session.

If you have a session that has been configured/deployed to an instance of SQL Server, you will want to look at the Catalog Views – especially if that session is not running. Today, I will take a quick look at the catalog views. Yes – quick!

Catalog Views

First, I want to cover a quick query that will reveal what catalog views are available for your use. The query is very simple.

Straight forward, catalog views for Extended Events have a very common naming pattern – server_event_sessions%. The rest of the predicate is just for eye candy.

Among the list returned by that query we will see a list of results such as this:

Use the following catalog views to obtain the metadata that is created when you create an event session.

Name Description
sys.server_event_sessions Lists all editable, deployed event sessions.
sys.server_event_session_actions List of actions on each event of an event session.
sys.server_event_session_events List of each event in an event session.
sys.server_event_session_fields List of customizable columns that were set on events and targets.
sys.server_event_session_targets List of event targets for an event session.

Beyond just a list of the Catalog views, I find it useful to combine some of these to garner the information that would be useful in querying data from the session or even to possibly rebuild the event session if needed.

One may want to figure out where the data for an event session is being stored. The first inclination may be to look at the server_event_session_targets catalog view. Doing that could cause a little frustration since you will only see some binary data and the type of target attached to the session.

But, playing with the views a little more and becoming a little more familiar with the data presented by the views, you may notice that the server_event_session_fields suddenly becomes more attractive. Why? Well, because it contains the filepaths that would be necessary to query session data – if the session is deployed to a file target. Suddenly, hope is not lost.

To get that data, one would need to write a query like this:

This demonstrates one of the more frustrating (not by far the most frustrating part though) things about dealing with extended events. That is the entity attribute value model employed to store metadata. This is nothing new within SQL Server (e.g. agent jobs, schedules, or even sysobjvalues – internally). Sadly that doesn’t make dealing with the metadata terribly easy – but knowing can make it more manageable.

So, my recommendation here is to play around a bit and start to get to know the catalog views as they pertain to extended event metadata.

Stay tuned for more posts like this. As was mentioned, this is the first in what will be a long series on Extended Events.

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!

Compressing Outcomes

Categories: News, Professional, SSC
Comments: No Comments
Published on: August 24, 2015

You find yourself cruising along, just doing your thing as a database administrator. Then the CIO plops down into a spare chair in your cubicle to talk about the weather.

Well, not entirely true there. The CIO dropped in because the alerts for disk space have been blowing up everybody’s inbox. He informs you that the situation isn’t going to change anytime soon because new shelves for the SAN just can’t be ordered at the moment. In the end, you are given a directive – just without so many words. Fix the problem, and do it without more disk space.

Fantastic you think to yourself. What to do now. Then you get the Gru light-bulb and it is on!

compressionEnter compression. Ok, compression isn’t something entirely new. It has been a feature shipped with SQL Server since 2008 Enterprise edition and can be just the thing to save the day (and everybody’s inbox before they start ignoring all alerts).

The decision has been made and the biggest tables in the database are now compressed using page compression. But you are concerned that it didn’t work because the space savings isn’t necessarily what you had hoped it would be. Lucky for you, you are running SQL Server 2012 and have the ability to start diving into the internals to confirm whether your worry is fact or fiction.

Using this handy dandy query that you are about to copy and paste from the internet, you can quickly investigate to see if there are pages in the tables that were unaffected by your attempts to compress the table.

Thanks to an undocumented function called dm_db_database_page_allocations, we can investigate quickly whether there are pages that failed to compress. We can also see which compression state they are in – if they are compressed at all.

Adding a script like this to your repository can be an easy aid in the struggle to ensure your expected results either match or don’t match. This would save a bit of time and allow you to move on to bigger and badder things – like 2048.

In addition to looking at the compression status for each page, I have thrown in a little extra. Call it the “considerations” column. Based on activity hitting the table or index, you may want to consider a different level of encryption. This additional data on the end of the output will help start you in that direction.

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.

Internal Tables and Space Used

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: July 29, 2015

engine_indbSome of the beauty of SQL Server is the complexity that it holds. Some of that complexity is right before our eyes. And some of that requires a little bit of diving into the internals to figure out what is happening or even why it is complex.

I enjoy the complexity that is offered to us through this great technology. Because of some of the hidden complexity, I found myself in a rabbit hole recently trying to figure out what exactly was happening with how table size is being calculated by default.

I have written previously on how to find some of the information pertinent to table size. And sadly, thanks to recent rabbit hole excursions, I found that some of the information in the previous articles was wrong (and accordingly there is even a bit of an error in some documentation but that is a different story – connect filed here).

One of the most common means to calculate size in a database is through the use of sp_spaceused. Over the recent editions, there have been a few minor updates to this stored procedure. But a common theme in every update has been to reference the sys.internal_tables system table to get some internal_types excluded from certain calculations.

I found myself wanting to know just exactly what these internal_types were. I searched BOL and Google. I found some references to some of the table types but they always seemed to just be in code. There was a short list in BOL that had some information, but it was far from complete. What to do? What to do?

What did I do?

Since I couldn’t find all of the internal_types for the internal_tables, I was left to do but one last thing. Well, actually, I resorted to asking around a bit first. I asked a group of MCMs and some people (e.g. Paul Randal – b|t ) for a little help. After those inquiries, I found myself still short of a complete list. That is, complete as far as the exclusion list for sp_spaceused.

My last resort for the time being was to begin testing. I tested various different features and configurations. I did this until I was able to come up with a complete list (with regard to sp_spaceused again). In addition to that complete list, I found a handful of additional internal table types.

Now this investigation and rabbit hole was not just for my own enjoyment. I have to admit it was rather frustrating. I ran into test failure after test failure trying to find the exact internal table types that were referenced in that blasted stored procedure.

I was asked by a friend (blog | twitter) why I was submitting myself to this kind of pain and why it was so important. Well, it’s not just for my enjoyment. SPOILER ALERT: I have an update for the table space script that was planned, and it needs to have a little bit better information in lieu of the “because it says so in BOL” explanation that I had made in previous releases of the script.

But I digress. That will all be better discussed in the next installment of the script. As for today, I want to share my findings of this expedition into the nooks and crannies of the database engine.

The script

I have hard-coded a few things in this script that you will possibly need to change. The most important being that I hard-coded a reference to the AdminDB database to the string splitter that I use. There is a note of that string-splitter in the script. And to use the same one (By Jeff Moden with Community contributions) that I have employed, you can download it from here.

You can see that I have included the types for versions from 2005 through 2016 that I have found to date. In addition, SQL Server 2016 has the same exclusions (for now) as 2012 and 2014. That is, at least within sp_spaceused. In 2016, sp_spaceused does make a call to an extended stored proc called sp_spaceused_remote_data_archive, and I do not yet know what is within that proc. You can bet though, that it is related to the new Stretch feature.

The Tease!

Stay tuned! The new release for the table space script will be coming to you on the other side of this short blogging break!

What’s the Size of that Index?

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: July 27, 2015

I have previously written about finding what the big space consumers are within the database. I even shared scripts in those articles on how to find those big consumers (read all about it here).

sizematters2That is grand when it comes to quickly finding which objects happen to be consuming the greatest amount of space within the database. It really helps to quickly assess where some extra TLC may need to be spent. But what about more specific details? What if you wished to figure out what the size was for each of the indexes within that object?

Well, the solution is pretty simple. I have another script to help determine which indexes are the big space consumers within the specified table for the given database.

 

First, let’s see what that script is.

As you can see, I am parsing a few different measures on space usage from the dynamic management view sys.dm_db_partition_stats. This will tell me information such as how much space is being reserved for each index as well as how much space is actually being used on each index. Then I use a grouping set to summarize that size information so I can then compare the total index size back to the size I would have seen from the table space script (discussed lightly earlier).

Why might I want to do something like this?

That is a really good question! The first obvious answer to a question such as this is in the off chance that you discover (again, from the use of the previous script), that one of your terabyte tables is actually only 150GB of data and the rest of that space consumption is actually from all of the indexes on that table.

When I see that a table has more space allocated due to indexes than due to data, I might be curious which indexes are contributing to that consumption. In addition, I can look at numbers much faster to see if maybe there are multiple indexes that are the exact same size. If they are, I might want to evaluate and dig a little deeper into the definitions of those indexes. We would only really love to have 850GB of indexes on a table to only find that three or four of those indexes are exact duplicates.

From there, I might even decide to look at the usage stats for the indexes in question. Why go about it in this fashion? Well, on some databases it is much faster to run a few quick queries than to try and run a big query against the entire database to get all of the index usage stats. I am sure you are familiar with those times when trying to query physical and operational stats takes hours to complete.

But But But…

I can hear you thinking right now. Another question has popped into your head. What about sp_spaceused. That old proc does work just fine for this as well. But there is something about being able to get to know what contributes to the space of an index and being able to work through how to get it calculated. Additionally, with a minor tweak, this query can provide that (detailed) information very quickly for all objects in the database.

Tweaked query

 

Extended Events Removed from 2016

Comments: No Comments
Published on: July 22, 2015

SQL Server 2016 is on the horizon. We have a few CTP versions already released for download, allowing people to become more and more familiar with the product.

Recently, I wrote about how SQL Server 2016 has many more Extended Events that have been added for our use. You can read about the new events added – here.

One thing I did not discuss in that previous article was the apparent deprecation of a few events. Yes, events do get removed from time to time. Every now and again, old events are not removed (e.g. databases_data_file_size_changed and databases_log_file_size_changed which you can read more about here). It would be nice to remove those old events but that is for another article!

As of this writing, the following image shows what has been removed and no longer available.

nomore

 

Looking closely at this list of three events, I don’t know that it is terribly concerning. Two of the events are for the query store that did not work in 2014. So there is nothing really lost there. The other event appears to be more of an event used for debugging column store code (the event was in the debug channel).

So overall, the impact of the loss of these events should be very limited. And on the positive side, it is still evidence that Microsoft continues to push forward to make XEvents even bigger and badder. Extended Events are a wonderful tool-set for use by DBAs to be better informed about their environment.

«page 1 of 4






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

SQLHelp


Welcome , today is Saturday, September 5, 2015