SQL Server – мощная технология, которая предлагает много сложностей. Иногда, чтобы понять внутреннее устройство SQL Server, нужно погрузиться в его внутренности. Недавно я оказался в кроличьей норе, пытаясь разобраться, как по умолчанию рассчитывается размер таблицы. В своих предыдущих статьях я давал информацию о нахождении размера таблицы, но обнаружил, что некоторая информация была неверной. Это побудило меня на поиск истины.

Одним из распространенных методов расчета размера таблицы является использование хранимой процедуры sp_spaceused. За годы были внесены изменения в эту хранимую процедуру, и каждое обновление ссылается на системную таблицу sys.internal_tables, чтобы исключить определенные внутренние типы из расчетов. Я хотел знать, какие именно внутренние типы были исключены, но мой поиск в документации и онлайн-ресурсах дал неполную информацию.

Итак, что же я сделал? Я обратился к группе экспертов и попросил их помощи. Несмотря на их помощь, я так и не смог найти полный список внутренних типов, исключенных sp_spaceused. Это заставило меня провести собственные тесты. Я экспериментировал с различными функциями и настройками, пока наконец не составил полный список внутренних типов таблиц, исключенных sp_spaceused.

Теперь вы можете задаться вопросом, почему я столько старался. Ну, это не только для моего собственного удовольствия. У меня скоро будет обновление для скрипта пространства таблицы, и я хотел предоставить точную информацию, а не полагаться на объяснение “потому что так написано в документации”. Это исследование уголков и закоулков движка базы данных было необходимо для обеспечения точности скрипта.

Для тех, кто заинтересован, вот фрагмент скрипта, который я использовал для определения внутренних типов таблиц:

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'системная база данных' ),
( 202, N'XML_INDEX_NODES', N'пользовательская база данных' ),
( 203, N'FULLTEXT_CATALOG_FREELIST', N'Пользовательская база данных' ),
( 204, N'FULLTEXT_CATALOG_MAP (BOL)/FULLTEXT_INDEX_MAP (REALITY)', N'Пользовательская база данных' ),
( 205, N'QUERY_NOTIFICATION', N'Пользовательская база данных' ),
( 206, N'SERVICE_BROKER_MAP', N'системная база данных' ),
( 207, N'EXTENDED_INDEXES', N'пользовательская база данных' ),
( 208, N'FILESTREAM_TOMBSTONE', N'системная база данных' ),
( 209, N'CHANGE_TRACKING', N'Пользовательская база данных' ),
( 210, N'TRACKED_COMMITTED_TRANSACTIONS', N'системная база данных' ),
( 211, N'FULLTEXT_AVDL', N'пользовательская база данных' ),
( 212, N'FULLTEXT_COMP_FRAGMENT', N'пользовательская база данных' ),
( 213, N'FULLTEXT_DOCID_STATUS', N'пользовательская база данных' ),
( 214, N'FULLTEXT_INDEXED_DOCID', N'пользовательская база данных' ),
( 215, N'FULLTEXT_DOCID_FILTER', N'пользовательская база данных' ),
( 216, N'FULLTEXT_DOCID_MAP', N'пользовательская база данных' ),
( 217, N'FULLTEXT_THESAURUS_METADATA_TABLE', N'системная база данных' ),
( 218, N'FULLTEXT_THESAURUS_STATE_TABLE', N'системная база данных' ),
( 219, N'FULLTEXT_THESAURUS_PHRASE_TABLE', N'системная база данных' ),
( 220, N'CONTAINED_FEATURES', N'системная база данных' ),
( 221, N'SEMPLAT_DOCUMENT_INDEX_TABLE', N'пользовательская база данных' ),
( 222, N'SEMPLAT_TAG_INDEX_TABLE', N'пользовательская база данных' ),
( 223, N'SEMPLAT_MODEL_MAPPING_TABLE', N'системная база данных' ),
( 224, N'SEMPLAT_LANGUAGE_MODEL_TABLE', N'системная база данных' ),
( 225, N'FILETABLE_UPDATES', N'системная база данных' ),
( 236, N'SELECTIVE_XML_INDEX_NODE_TABLE', N'пользовательская база данных' ),
( 240, N'QUERY_DISK_STORE_QUERY_TEXT', N'системная база данных' ),
( 241, N'QUERY_DISK_STORE_QUERY', N'системная база данных' ),
( 242, N'QUERY_DISK_STORE_PLAN', N'системная база данных' ),
( 243, N'QUERY_DISK_STORE_RUNTIME_STATS', N'системная база данных' ),
( 244, N'QUERY_DISK_STORE_RUNTIME_STATS_INTERVAL', N'системная база данных' ),
( 245, N'QUERY_CONTEXT_SETTINGS', N'системная база данных' );

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