Table Size Script Update for 2015

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: August 3, 2015

As was recently promised, I have an db_engineupdate to an old tried and true script for calculating the size of an object.

That promise was doled out in the previous article when I discussed the internal table types. You can read the article here. Information from that article was used in this script – so it could be of use prior to taking a gander at this new revision.

And of course, previous releases can be found through the link chain here.

The script was in need of an update the last time around due to the internal table types that had been updated in SQL Server. Those internal tables haven’t really changed in the latest release (at least as far as calculating space is concerned). But, I didn’t like needing to update multiple places in the script for one. For the other part of it, I did not like that It was difficult to know what exactly the internal table numbers actually represented. So I added that documentation to this script.

In addition to that little change, I also wanted to figure out if a table might have been spread across multiple filegroups. Due to this, I added in a delimited list of filegroups.

And the last minor update is that the script now has an option to exclude MS Shipped objects, or to include them, or to look just at the MS Shipped objects.

All of this is included in the effort of trying to get a more complete view of the size of the objects within a database and to be able to do so without needing to loop through the objects.

Note: A quick update to move the index creation outside of the table creation instead of inline since that is a feature not supported in older versions of SQL Server.

Database Ghosts

phantasmripRecently you may have seen my first article on this topic over at SQL Solutions Group.  If not, here is the link so you can read that article first before proceeding here.  This article is intended as a first of two part follow-up to that article.

Now that you have read that other article, we can recap it a little bit.

You will recall that in that article, I discussed the presence of Ghosts in your database.  I also discussed that those ghosts are a good thing.  They have some benefits to them, of which is a bit of a performance boost for some operations like the rollback of a delete.

In that article I discussed one method with which you could see these ghost records.  In this article, I would like to share an alternate method to be able to see these ghosts.

In order to explore this alternate means, let’s go ahead and follow the first few steps from the other article to get the setup complete so our second ghost hunting foray can begin.

Now with the setup complete, we should once again confirm that we have appropriate data available for the hunt.  Once again a simple query can suffice to show the data.

Upon execution of the check script, we should see something similar to the following data-set.



Great, the data is confirmed and we should have a wonderful chance to find some ghosts once again.  In the previous attempt, we needed an additional plasma blaster in the form of a trace flag.  In this attempt we will hunt these ghosts without that tool and see what we can find.  Why attempt it in this way?  Well, it is simply because I’d rather not use a trace flag if it is not necessary.  If you recall, that trace flag had a couple of noted effects.  One of the effects was that it turned off the ghost cleanup process.  If I can avoid it, I’d rather leave the containment unit in tact.

Now, due to that clerical error of putting Halloween on the wrong date, we need to follow the prescribed policy to delete records prior to creating the proper records.

Notice that we issued the delete in a transaction this time around.  We are going to leave that transaction open for a bit while we hunt those ghosts.

The first step is to verify that some ghosts might be present.  To do that we should run a query such as the following.

From that query we should see something like the following.


Once again we are on the right track.  We can further confirm the existence of these ghosts through a little more investigation and monitoring.  Let’s try the trick with the dblog function again.

With that query, we should see something like the following result set.



Very cool.  We once again can see that these phantasms are in the database.  We have enough information that we can proceed on to the next stage.  We can pass the PageID into DBCC PAGE in order to investigate the ghosts on the page.  If we use the PageID that is circled with the green in the preceding result set, we can get a better feel for these specters.

And we may see results such as the following.


Recall that the log says this page has ghost records on it.  When we check the page with DBCC PAGE we can definitely see that there are ghosts on the page.  This is very cool.  Now, had we tried to check for ghost records on the PFS page we would not be able to see the ghost count like we were able to see by enabling the TF.

Once again we have been able to display the existence of ghosts in the database.  In order to get these ghosts to move on from the database to their afterlife, we merely need to commit the transaction or roll back the transaction.

Stay tuned for the next article in this mini-series about ghosts.  Who knows, we may even have a bonus Halloween article since this is Halloween month.

Part III of the series can now be found here.

Table Space updated again

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: March 28, 2014

Today we have another update for an age old script on this site.  You can find the last update to the script here.

This time we have a bit of a bug update.  The details of that bug are in the notes for the script.

Alternatively, you can download the script from here (in case a plugin update breaks the script again).

Table Size and Missing FK Indexes

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 14, 2012

I am bringing an oldie back with another twist.  I recently ran into the need to correlate information between a couple of different queries that I like to use.  The two scripts returned different pieces of data about tables in a database.  This information was table size and missing foreign key indexes.

I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size.  The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database.  We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.

So, here is what I did to get that information quickly.

[codesyntax lang=”tsql”]


The change is not overly much.  I added a subquery via a cross apply to get the missing foreign key info.  Then I return the pertinent columns back to the Select query.

Database Data and Log Size Info

Categories: News, Professional, Scripts, SSC
Tags: ,
Comments: 6 Comments
Published on: December 5, 2011

Have you ever wanted or needed to get the data and log file size for all of the databases in your server?

I have seen scripts that would get this information in the past.  All of the scripts seemed to use either a cursor or the proc from Microsoft called sp_MSforeachdb.  Having seen some of these scripts, I wondered if there might be a different way of doing it (that was also different than you see in my TableSpace Scripts).

After putting some thought into it, I decided on a plan of attack and went to work on building a query that would do the trick.  But before I continue, I will advise that running a script like this on large databases may take a while to execute.

Keeping these things in mind (potential slowness when run on large db and wanting to try something different), I came up with this method (tested on Instances with small databases as well as Instances with 2TB Databases).

[codesyntax lang=”tsql”]


You will see that I did not entirely eliminate the looping mechanism.  Truth be told, it is so much faster on servers with Large Databases.

Also take note of the DMV that is in use in this query.  I am taking advantage of the performance monitor stats that are exposed through the DMV sys.dm_os_performance_counters.  One caveat to this method, is that this DMV shows us the size of the resource database as well.  I think it is fine to report that information back – but it won’t change much over time.  It is for that purpose that I use the Left Join in the query.

The other trick that I utilized is to Pivot that performance monitor data.  I think this works better than to write a bunch of sub-queries to generate the same sort of data layout.

You have probably also noted that I have chosen 250GB as the tipping point in this query.  There is no particular reason for that size – just a large enough database size to make the point.  For some, the tipping point may be a much smaller database size.  Feel free to change this value to suit your needs.

Some other caveats.  The perfmon cntr_value data is presented as an Int.  For more accurate math operations, I chose to cast many of these to a Float.

I also ran into a case where the perfmon data was reporting 0 as the Data File Size for one of my databases.  I had to throw a case statement in for the Data File Used Percent in order to avoid a Divide by 0 error.

I also wrapped the DataUsedMB in an ISNULL.  This was due to the model and mssqlsystemresource databases not having data in the dm_db_physical_stats function.  I could have left those as NULL, but wanted to show something for them both.

Check it out and Enjoy!

Table Space – CS Part Deux

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: November 21, 2011

Another script, another day.  And as promised, I am providing an update to the Table Space script that followed the sp_MStableSpace script.  Not a lot more to be said about this one, since much was said already.

I updated the script for those CS collations.  I also provided the update to show the % of DB column to be driven based on the data file usage information.

[codesyntax lang=”tsql”]


Phew, I finally took care of some of those somedays that have been nagging me.  Sure, there has been a someday that has evolved due to that – but that is a good thing.

It helps that I also need these scripts to be CS.  Add to that, that I need to use them more frequently and it was a perfect opportunity to do a little housecleaning.

Table Space revised Again

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: November 17, 2011

Since I am in the Collation Sensitive mood, I am finally getting around to updating this script.  This is the Table Space script that I have had out there for a while.  In the last release of this script, a request (by Remi) was made to update it so it will work with CS.  In addition to that, a request was made to add a few columns.  I have done both.

The CS request was not too big of a deal – just took a minute to actually sit down and do it.  Then it was a matter of setting a test database to CS and confirming that the script continued to work.  A friend did the same legwork (thx Remi) and posted his update in a thread I had been planning on getting back to with the update.  Now it will just get a link to this, and then there can be a circular reference.

The second part of the request was for a change in calculations and possibly additional columns.  I just added columns and someday hope to get back to this script and parameterize the whole thing so that a variable set of columns can be returned – based on user input.  Oh the glory of those someday goals.

So, here is the updated Table Size script.

[codesyntax lang=”tsql”]


If you recall, I did two versions of the table size script.  One followed the path of sp_spaceused and the other followed sp_MStablespace.  This script is the one that follows the sp_spaceused version.  I will post an update for the sp_MStablespace version shortly.

T-SQL Tuesday #024: Prox ‘n’ Funx

Comments: No Comments
Published on: November 8, 2011

I find myself just now sitting down to write this as the deadline for TSQL Tuesday 24 fast approaches.  Fortunately I started planning out what I wanted to cover several days ago.

I could have tried writing this last night – but I ended up falling asleep at the keyboard.  Drowsy typing can be a very dangerous thing.  So it is a good thing I didn’t start then.

Alas, I digress.  This month, a TSQL guru (BRAD SCHULZ), is at the helm.  He has challenged us to write about procs and functions.  We get decide which direction we take it.

I thought about this and figured there could be a few functions and paths to take on this.  So let’s get started and see where this bird-walk can take us.

Function for which I am thankful

I decided that this is a topic that must be discussed.  It is the month of Thanksgiving afterall.

For this topic, I want to give a shout out to Jeff Moden for a function that he wrote about and published.  This function has proved useful to solve some performance issues.

In a recent case where this function helped, I was a bit reluctant because it just didn’t seem possible that the function already in place could have that big of an impact.  Guess what, it did.  The function that Jeff did was his string splitter.  The strings in question were short and each value was not very big.  Despite that, the string splitter was chewing up resources.

After implementing the new string splitter, we saw immediate performance gains.  Using this function has paid off big dividends and has saved me a grundle of time.  With time being more valuable as time passes, I am very thankful to Jeff for the work he did on his function.

Function I can’t live without

That might be overstating it a bit.  The reality is, there are some functions that I use far more in my scripting and troubleshooting than others.  One function, above all others, seems to come into use more frequently.

This function is a dynamic management object and is called dm_db_index_physical_stats.  There are several parameters to this function and there are several uses for it as I have seen and demonstrated on my blog.

To learn more on the parameters, read this.  As MSDN states, this function “Returns size and fragmentation information for the data and indexes of the specified table or view.”  So you can ascertain quickly that the most likely use of this index is in index fragmentation analysis.

I have written about this function for index analysis, table size analysis, BLOB index analysis, and finding ghost records.  I even referenced it when doing a table compression analysis.

You can check out all of those articles here.

There is just so much good info that can be gleamed from this function and so many ways to use it.  That is why I find myself coming back to it time and time again.

Funcs for Fun

There are many functions that are useful.  Some are more interesting than others.  Some, I use because I find it fun delving into the internals of SQL Server.  If the function can provide useful information and I learn something along the way, then it is pretty fun.

Here are some of those functions that I have enjoyed using.






You can find good information on most of these in MSDN.  For sys.fn_physloccracker, you may want to rely on this from Paul Randal.  You can even read what little I wrote about it a few months ago.  I came across this little function while working on a pet project (that I still need to finish – d’oh).


Functions are very handy.  They can also prove to be the cause of poor performance (such as was the case with that string splitter).  When used appropriately, you can provide well-performing SQL as well as some very handy information.

Quick and Dirty Index Info

Categories: News, Professional, Scripts, SSC
Tags: ,
Comments: No Comments
Published on: November 1, 2011

From time to time, somebody needs to find some information about the indexes in the database.  As DBA’s, there are a few tools at your disposal to look up that information.  The required/requested information is not always the same.  That said, you can usually adapt a query quickly to find what you need.

Where to Look

SQL 2005 and up have provided us with some very useful means to gain insight into the indexes in our databases.  The following DMOs is a small representation of that information.

  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_group_stats
  • sys.dm_db_missing_index_groups
  • sys.dm_db_index_usage_stats
  • sys.dm_db_index_physical_stats

Add to that, there are some catalog views to help with finding information relevant to indexes in the databases.

  • sys.indexes
  • sys.index_columns

With just these few objects, there is a wealth of information at your fingertips.  I often find myself querying for information about indexes.  I also find myself being asked how to find this information on a relatively frequent interval.  Usually, I just end up writing out the query again (depends on mood and day).

Rewriting the query doesn’t take too much time when you know what you need to query.  That said, sometimes it is nice to have a base query ready to go.  From this query you can add/remove items as you see fit to get the information that you really need.

For instance, here is a query to get some of the fragmentation information.  I would probably just re-use this query over and over where applicable – only changing the parts that mattered to get the necessary result set.

[codesyntax lang=”tsql”]


There are many types of queries we could write for the different relevant information pertaining to our indexes.  It is all a matter of knowing where to start.  I hope that knowing about these DMOs and catalog views, it will help shorten your efforts to retrieving this information.

Haunting a Database Near You

Comments: 7 Comments
Published on: October 31, 2011

Today, we have a special Halloween edition.  For me, Halloween and computer geek go quite well together.  And thinking about it, I wanted to try to better understand if there was a correlation.  As a DBA, have you wondered the same thing?

Well, I have a short list of five things that may help you to correlate your affinity for Halloween with your love for Databases.


Did you know that a tombstone is a legitimate thing in SQL Server?

Tombstones are replica related.  They are deleted items in the replica and are used to make sure the deleted item doesn’t get put back in the replica inadvertently.

You can read a lot more about tombstones from the msdn article here.

Tombstones are not unique to SQL Server.  These are commonplace in Active Directory as well.



Not all rows that are deleted move on to the afterlife quickly like they should.  Some like to hang around due to unfinished business.

The unfinished business in this case is the server running a cleanup thread.  This has to be done when the server is not too busy and has enough free resources to help these records move on to the afterlife.

You can see the evidence of these ghosts with specialized equipment.  By the use of a DMO, we can see the ghost record count on a per index basis.  The DMO is sys.dm_db_index_physical_stats.  Take a look at the ghost_record_count column in the returned record set.

With more specialized equipment, the engine takes care of the cleanup and removal of these ghosts.  Here is an in-depth foray into the world of SQL ghost hunting.  Whatever you do, don’t cross the streams.


It’s alive!!

No, I killed it!!

It can’t be…How is it still alive?

The transaction will not commit and may get rolled back.  The zombie has reared its’ ugly head.  A transaction that cannot commit but keeps going (or rolls back) due to an unrecoverable error is a zombie transaction.

From MSDN, here is a more specific definition of a zombie.

Rowsets can become zombies if the internal resource on which they depend goes away because a transaction aborts.


The spawn of SQL server is not so much like the Spawn character of the comics.  Nor is it much like the spawn shown to the right.

That is unless it is not managed very well.  SQL can spawn multiple threads if the optimizer deems it necessary for a query.  This is also known as parallelism.

Parallelism can be a good thing or it can be a bad thing.  Understanding it can help keep it on the good side.  You might want to check out some of Paul White’s articles on the topic.

Children of the Corn

Well, this one is not really something in SQL server.  That said, every time I think of orphaned users in SQL server – children of the corn comes to mind.

An orphaned user is one in which the login SID does not match for one reason or another.  This makes it so that the user can no longer log in to SQL server.

If you don’t know about these kids, they can really make for a frustrating day.  Read more here.


I have just covered five things in SQL server that correlate quite closely to Halloween.  But this by no means is an exhaustive list.  For instance, an obvious correlation is the “KILL” command.  Another good one is the monster known as the Blob (read more about that monster here and here).

With the opportunity to have Halloween every day, it’s no wonder I like being a DBA.

Happy Halloween

«page 1 of 2

July 2018
« Jun    

Welcome , today is Saturday, July 21, 2018