Some 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.

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!

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).

That 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.

--in_row_data_page_count = Number of pages in use for storing in-row data in this partition. If the partition is part of a heap, the value is the number of data pages in the heap. If the partition is part of an index, the value is the number of pages in the leaf level

--lob_used_page_count = Number of pages in use for storing and managing out-of-row columns within the partition. IAM pages are included.

--row_overflow_used_page_count = Number of pages in use for storing and managing row-overflow columns within the partition. IAM pages are included.

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.

--in_row_data_page_count = Number of pages in use for storing in-row data in this partition. If the partition is part of a heap, the value is the number of data pages in the heap. If the partition is part of an index, the value is the number of pages in the leaf level

--lob_used_page_count = Number of pages in use for storing and managing out-of-row columns within the partition. IAM pages are included.

--row_overflow_used_page_count = Number of pages in use for storing and managing row-overflow columns within the partition. IAM pages are included.

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.

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.

Announcing…the book

At 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.

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.

I am about to set sail on a new venture with my next official whistle stop. This year has been plenty full of whistle stops and I plan on continuing. You can read (in full) about previous whistle stops and why they are called whistle stops here.

Amazing this thing is still going on after the sailing train comment that was made around the time of PASS Summit 2013.

Time to sink or sail, so to speak. SQL Saturday 403 in Louisville will mark the next attempt at what I hope to be a repeat performance – many times. I will be tag-teaming with Wayne Sheffield in this all day workshop event. The session is one of two all day sessions for the event in Louisville, KY. Did I mention that this is a:

That’s right! If you get two all day sessions for the price of one! Attend just one or both of the sessions that Wayne and I will be presenting. You can see full details at our eventbrite site here.

If you are a DBA or a database developer, these sessions are for you. If you are managing a database and are experiencing performance issues, these sessions are a must. We will chat with attendees about a horde of performance killers and other critical issues we have seen in our years of working with SQL Server. In short, some of these issues are pure murder on your database, DBA, developer and team in general. We will work through many of these things and show some methods to achieve a higher state of database Zen.

Description

Join Microsoft Certified Masters, Wayne Sheffield and Jason Brimhall, as they examine numerous crazy implementations they have seen over the years, and how these implementations can be murder on SQL Server. No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Course Objectives

Recognize practices that are performance pitfalls

Learn how to Remedy the performance pitfalls

Recognize practices that are security pitfalls

Learn how to Remedy the security pitfalls

Demos Demos Demos – scripts to demonstrate pitfalls and their remedies will be provided

Have fun and discuss

We might blow up a database

That is just the Murder session. Here are the details for the XE session.

A Masters Passport to Extended Events

As is commonly the case, all good things come to an end. And now is as good a time as any for the use of SQL Trace and Profiler to come to an end. Let’s face it, Trace was a good tool and had some wonderful uses. Profiler for that matter was a good tool and was useful at times.

It is time to let those old tools retire gracefully and move into the world of XE. This full day workshop will provide you the means to let Profiler and Trace be retired from your toolset as you discover all that XE has to offer.

This full day session on Extended Events will help prepare you to put this tool to immediate use as you walk back to your daily duties. This workshop will teach you about Extended Events starting with the basics and moving through how to create XE sessions that will get the right data for you, while doing so with minimal impact.

You will be exposed to advanced troubleshooting techniques as we work through complex issues that are made easier through the use of XE. Take advantage of this opportunity to dive into the world of Extended Events and learn how you can make best use of this tool in your SQL 2008+ environment.

Course Objectives

Build a knowledge base for Extended Events

Become familiar with the tools for Extended Events

Become familiar with uses for Extended Events

Get acquainted with troubleshooting scenarios for Extended Events

Begin to put Extended Events to practical use

Return to work with enough information to eradicate Profiler from the environment

Presented by:

Wayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80’s. With over 20 years in IT, he has worked with SQL Server (since 6.5 in the late 90’s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server 2012 T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne

Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments. Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant and a Microsoft Certified Master(MCM). Jason is also an MVP for SQL Server.

There will be a nice mix of real world examples and some painfully contrived examples. All will have a good and useful point.

If you will be in the area, and you are looking for high quality content with a good mix of enjoyment, come and join us. You can find registration information and event details at the Louisville SQL Saturday site – here. There are only 75 seats available for this murder mystery theater. Reserve yours now.

The cost for the class is $150 (plus fees) up through the day of the event. When you register, be sure to tell your coworkers and friends.

Wait, there’s more…

Not only will I be in Louisville for these workshops, I will also be presenting as a part of the SQLSaturday event on August 22, 2015 (the Saturday after the workshops which run Aug. 20-21, 2015). You can view the available sessions here.

Shameless plug time

I present regularly at SQL Saturdays. Wayne also presents regularly at SQL Saturdays. If you are organizing an event and would like to fill some workshop sessions, please contact either Wayne, myself or both of us for this session.

Some 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.

Announcing…the book At 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[…]

What 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.”[…]

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. Isn’t that just crazy? In some ways it seems crazy. When I received the email I was exhilarated and completely astonished.[…]

One of my pet-peeves (and consequently frequent topic of discussion) is finding database settings (or any setting that has changed) without knowing about it. Worse yet is finding that the change has occurred and nobody claims to have any knowledge of it or having done the deed. This happened again recently where a database was[…]

Dan “L” Greenland (1935 ~ 2015) Dan “L” Greenland, 80, passed away June 30, 2015. Born June 3, 1935 in Highland, Utah, to William John and Cressie Althera Loveridge Greenland. He grew up in Highland and attended schools in American Fork. Graduated from American Fork High School in 1953. Married his high school sweetheart, Eleanor Bunker, October 21,[…]

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[…]