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.

The script

DECLARE @ServerMajorVersion DECIMAL(4, 2)
SELECT @ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.'
+ PARSENAME(dt.fqn, 3))
FROM ( SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))
) dt ( fqn );
IF OBJECT_ID('tempdb.dbo.#InternalTables') IS NOT NULL
BEGIN
DROP TABLE #InternalTables;
END
CREATE TABLE #InternalTables
(
[internal_type] [TINYINT] NULL
INDEX CI_InternalType CLUSTERED
, [internal_type_desc] [VARCHAR](60) NULL
, [DBSource] [VARCHAR](16) NULL
)
ON[PRIMARY];
INSERT INTO #InternalTables
( [internal_type], [internal_type_desc], [DBSource] )
VALUES( 201, N'QUEUE_MESSAGES', N'system database' )
,( 202, N'XML_INDEX_NODES', N'user database' )
,( 203, N'FULLTEXT_CATALOG_FREELIST', N'User Database' )
,( 204, N'FULLTEXT_CATALOG_MAP (BOL)/FULLTEXT_INDEX_MAP (REALITY)',
N'User Database' )
,( 205, N'QUERY_NOTIFICATION', N'User Database' )
,( 206, N'SERVICE_BROKER_MAP', N'system database' )
,( 207, N'EXTENDED_INDEXES', N'user database' )
,( 208, N'FILESTREAM_TOMBSTONE', N'system database' )
,( 209, N'CHANGE_TRACKING', N'User Database' )
,( 210, N'TRACKED_COMMITTED_TRANSACTIONS', N'system database' )
,( 211, N'FULLTEXT_AVDL', N'user database' )
,( 212, N'FULLTEXT_COMP_FRAGMENT', N'user database' )
,( 213, N'FULLTEXT_DOCID_STATUS', N'user database' )
,( 214, N'FULLTEXT_INDEXED_DOCID', N'user database' )
,( 215, N'FULLTEXT_DOCID_FILTER', N'user database' )
,( 216, N'FULLTEXT_DOCID_MAP', N'user database' )
,( 217, N'FULLTEXT_THESAURUS_METADATA_TABLE', N'system database' )
,( 218, N'FULLTEXT_THESAURUS_STATE_TABLE', N'system database' )
,( 219, N'FULLTEXT_THESAURUS_PHRASE_TABLE', N'system database' )
,( 220, N'CONTAINED_FEATURES', N'system database' )
,( 221, N'SEMPLAT_DOCUMENT_INDEX_TABLE', N'user database' )
,( 222, N'SEMPLAT_TAG_INDEX_TABLE', N'user database' )
,( 223, N'SEMPLAT_MODEL_MAPPING_TABLE', N'system database' )
,( 224, N'SEMPLAT_LANGUAGE_MODEL_TABLE', N'system database' )
,( 225, N'FILETABLE_UPDATES', N'system database' )
,( 236, N'SELECTIVE_XML_INDEX_NODE_TABLE', N'user database' )
,( 240, N'QUERY_DISK_STORE_QUERY_TEXT', N'system database' )
,( 241, N'QUERY_DISK_STORE_QUERY', N'system database' )
,( 242, N'QUERY_DISK_STORE_PLAN', N'system database' )
,( 243, N'QUERY_DISK_STORE_RUNTIME_STATS', N'system database' )
,( 244, N'QUERY_DISK_STORE_RUNTIME_STATS_INTERVAL',
N'system database' )
,( 245, N'QUERY_CONTEXT_SETTINGS', N'system database' );
IF OBJECT_ID('tempdb.dbo.#SpaceVersions') IS NOT NULL
BEGIN
DROP TABLE #SpaceVersions;
END
CREATE TABLE #SpaceVersions
(
Product VARCHAR(32)
, ServerMajorVersion DECIMAL(4, 2) INDEX CI_ServerMajorVer CLUSTERED
, TypesList VARCHAR(256)
);
INSERT INTO#SpaceVersions
( Product
, ServerMajorVersion
, TypesList
)
VALUES
( 'SQL Server 2005', 9.00 , '202,204')
,( 'SQL Server 2008', 10.00 , '202,204,211,212,213,214,215,216')
,( 'SQL Server 2008R2', 10.50 , '202,204,211,212,213,214,215,216')
,( 'SQL Server 2012', 11.00 , '202,204,207,211,212,213,214,215,216,221,222,236')
,( 'SQL Server 2014', 12.00 , '202,204,207,211,212,213,214,215,216,221,222,236')
,( 'SQL Server 2016', 13.00 , '202,204,207,211,212,213,214,215,216,221,222,236')
SELECT sv.Product, myit.internal_type, myit.internal_type_desc
FROM #SpaceVersions sv 
CROSS APPLY AdminDB.dbo.stringsplitter(sv.TypesList,',') ss
/* must have DelimitedSplit8K installed http://bit.ly/Moden8KDL *//* Change database name in accordance with 8k splitter location */INNER JOIN #InternalTables myit
ON ss.item = myit.internal_type
WHERE sv.ServerMajorVersion = @ServerMajorVersion;

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!