Код для оценки потенциальной экономии пространства ключа кластеризации для каждой таблицы

Автор: Paul Randal, Code to list potential cluster key space savings per table

Чем больше ключ кластеризации в вашей таблице, тем больше пространства тратится впустую во всех строках некластерных индексов.

Я подготовил код, который выполнит обход всех ваших баз данных и покажет для каждой таблицы, сколько места занимают ключи кластеризации в некластерных индексах, а также потенциальную экономию пространства, если преобразовать ключ кластеризации в один 8-байтовый bigint.

Вы можете модифицировать код по своему усмотрению. И я продолжаю использовать sp_msforeachdb, потому что это самый быстрый способ для меня написать код для вас, и это продолжает раздражать моего хорошего друга Аарона Бертрана (Aaron Bertrand) :-)

Наслаждайтесь!

/*============================================================================
  File:     KeySpaceSavingsSingleResultSet.sql

  Summary:  Потенциальная экономия пространства ключа кластеризации

  SQL Server Versions: 2005 и новее
——————————————————————————
  Автор: Paul S. Randal, SQLskills.com

  (c) 2012, SQLskills.com. Все права защищены.

  Для получения дополнительных скриптов и примеров кода посетите
    http://www.SQLskills.com

  Вы можете изменять этот код для своих *некоммерческих* целей. Вы можете
  публиковать изменённый код при условии включения этого уведомления об авторских
  правах и указания авторства, но вы должны получить предварительное разрешение
  перед публикацией этого кода в блоге.
 
  ЭТОТ КОД И ИНФОРМАЦИЯ ПРЕДОСТАВЛЯЮТСЯ "КАК ЕСТЬ" БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ,
  ЯВНЫХ ИЛИ ПОДРАЗУМЕВАЕМЫХ, ВКЛЮЧАЯ, НО НЕ ОГРАНИЧИВАЯСЬ, ПОДРАЗУМЕВАЕМЫМИ
  ГАРАНТИЯМИ ТОВАРНОЙ ПРИГОДНОСТИ И/ИЛИ ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЁННОЙ ЦЕЛИ.
============================================================================*/

IF EXISTS (SELECT * FROM msdb.sys.objects WHERE [name] = 'SQLskillsIKSpace')
    DROP TABLE msdb.dbo.SQLskillsIKSpace;
GO
CREATE TABLE msdb.dbo.SQLskillsIKSpace (
    DatabaseID SMALLINT,
 SchemaName SYSNAME,
 ObjectName SYSNAME,
    ObjectID INT,
    IndexCount SMALLINT DEFAULT (0),
    TableRows BIGINT DEFAULT (0),
    KeyCount SMALLINT DEFAULT (0),
    KeyWidth SMALLINT DEFAULT (0));
GO

EXEC sp_MSforeachdb
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT [name]
    FROM sys.databases WHERE [state_desc] = ''ONLINE''
        AND [database_id] > 4
        AND [name] != ''pubs''
        AND [name] != ''Northwind''
        AND [name] != ''distribution''
        AND [name] NOT LIKE ''ReportServer%''
        AND [name] NOT LIKE ''Adventure%'') AS names WHERE [name] = ''?'')
BEGIN
USE [?]

INSERT INTO msdb.dbo.SQLskillsIKSpace
(DatabaseID, SchemaName, ObjectName, ObjectID)
SELECT DB_ID (''?''), SCHEMA_NAME (o.[schema_id]), OBJECT_NAME (o.[object_id]), o.[object_id]
FROM sys.objects o
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 1
            AND [object_id] = o.[object_id]);

UPDATE msdb.dbo.SQLskillsIKSpace
SET [TableRows] = (
    SELECT SUM ([rows])
    FROM sys.partitions p
    WHERE p.[object_id] = [ObjectID]
    AND p.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');
 
UPDATE msdb.dbo.SQLskillsIKSpace
SET [IndexCount] = (
    SELECT COUNT (*)
    FROM sys.indexes i
    WHERE i.[object_id] = [ObjectID]
    AND i.[is_hypothetical] = 0
    AND i.[is_disabled] = 0
    AND i.[index_id] != 1)
WHERE [DatabaseID] = DB_ID (''?'');

UPDATE msdb.dbo.SQLskillsIKSpace
SET [KeyCount] = (
    SELECT COUNT (*)
    FROM sys.index_columns ic
    WHERE ic.[object_id] = [ObjectID]
    AND ic.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');

UPDATE msdb.dbo.SQLskillsIKSpace
SET [KeyWidth] = (
    SELECT SUM (c.[max_length])
    FROM sys.columns c
    JOIN sys.index_columns ic
    ON c.[object_id] = ic.[object_id]
    AND c.[object_id] = [ObjectID]
    AND ic.[column_id] = c.[column_id]
    AND ic.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');

DELETE msdb.dbo.SQLskillsIKSpace
WHERE
    ([KeyCount] = 1 AND [KeyWidth] < 9)
    OR [IndexCount] = 0 OR [TableRows] = 0;

END';
GO

SELECT
 DB_NAME ([DatabaseID]) AS [Database],
 [SchemaName] AS [Schema],
 [ObjectName] AS [Table],
    [IndexCount] AS [NCIndexes],
    [KeyCount] AS [ClusterKeys],
    [KeyWidth],
    [TableRows],
    [IndexCount] * [TableRows] * [KeyWidth] AS [KeySpaceInBytes],
    ([IndexCount] * [TableRows] * ([KeyWidth] – 8)) AS [PotentialSavings]
FROM msdb.dbo.SQLskillsIKSpace
ORDER BY [PotentialSavings] DESC;

DROP TABLE msdb.dbo.SQLskillsIKSpace;
GO