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.

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!

Compressing Encrypted Backups

TSQL2sDayA common requirement, whether it be based out of pure want or truly out of necessity, is to make a large database backup file, that is encrypted, be much smaller.

This was a knock for the early days of Transparent Data encryption (circa SQL Server 2012). If TDE were enabled, then a compressed backup (though compression was available) was not an option. Not only did compression in the 2012 implementation of TDE make the database backup not smaller, it occasionally caused it to be larger.

This was a problem.  And it is still a problem if you are still on SQL 2012. Having potentially seen this problem, amongst many others, Ken Wilson (blog | twitter) decided to ask us to talk about some of these things as a part of the TSQL Tuesday Blog party. Read all about that invite here.

Encrypted and Compressed

dbsecurityWell, thankfully Microsoft saw the shortcoming as well. With SQL Server 2014, MS released some pretty cool changes to help us encrypt and compress our database backups at rest.

Now, instead of a database backup that could potentially get larger due to encryption and compression combined, we have a significant hope of reducing the encrypted backup footprint to something much smaller. Here is a quick example using the AdventureWorks2014 database.

In this little exercise, I will perform three backups. But before I can even get to those, I need to ensure I have a Master Key set and a certificate created. The encrypted backups will require the use of that certificate.

Do this in a sandbox environment please. Do not do this on a production server.

In the first backup, I will attempt to backup the AW database using both encryption and compression. Once that is finished, then a backup that utilizes the encryption feature only will be done. And the last backup will be a compressed only backup. The three backups should show the space savings and encryption settings of the backup if all goes well. The compressed and encrypted backup should also show an equivalent savings as the compression only backup.

With that script executed, I can query the backup information in the msdb database to take a peek at what happened.

This should produce results similar to the following:

backup_results

Looking at the results, I can see that the compression only backup and the compression with encryption backup show very similar space savings. The compression only dropped to 45.50MB and the Compression with encryption dropped to 45.53MB. Then the encryption only backup showed that, interestingly, the CompBackSizeMB (compressed_backup_size) got larger (which is the actual size on disk of this particular backup).

At any rate, the compression now works with an encrypted backup and your backup footprint can be smaller while the data is protected at rest. Just don’t go using the same certificate and password for all of your encrypted backups. That would be like putting all of your eggs in one basket.

With the space savings available in 2014, and if you are using SQL 2014, why not use encrypted backups?

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.

«page 1 of 90






Calendar
August 2015
M T W T F S S
« Jul    
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, August 29, 2015