Code to list potential cluster key space savings per table

Back in January I posted the results of the cluster key size survey I ran in 2011 and explained how the larger the cluster key is on your table, the more space is being wasted in all the nonclustered index rows. Check it out if you haven't already.

I've finally put together the code that will run through all your databases and give you a per-table indication of how much space is being taken up by cluster keys in nonclustered indexes, and the potential space savings if you converted the cluster key to a single 8-byte bigint. You can get it below or from here: KeySpaceSavingsSingleResultSet.zip (1.57 kb)

You can mess around with the code to do what you want. And I'm continuing to use sp_msforeachdb because it's the fastest way for me to knock out code for you, and it continues to irritate my good friend Aaron Bertrand :-)

Enjoy!

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

  Summary:  Potential cluster key space savings

  SQL Server Versions: 2005 onwards
——————————————————————————
  Written by Paul S. Randal, SQLskills.com

  (c) 2012, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out
   
http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
 
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

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

 

6 thoughts on “Code to list potential cluster key space savings per table

  1. Steps:
    1. Convert every table to surrogate keys to save space on nonclustered indexes and to reduce variables.
    2. Realize the code still uses the primary key.
    3. Spend thousands of man hours converting the code to only use the surrogate key.
    4. Realize that the application still displays the data elements that make up the primary key and that it now performs horribly because of all the bookmark lookups.
    5. Add the old primary key to the included columns of the surrogate key. (lol)
    6. Realize the surrogate key and real primary key are no longer in sync and you have data integrity issues.
    7. Write a bunch of jobs to keep them in sync.
    8. Realize those jobs are blocking actual production work.
    9. Remove all surrogate keys.
    10. Spend thousands of man hours removing them from the code and application.
    11. Five years later: Forget that surrogate keys suck and return to step 1.

  2. While surrogate keys have their place, I’d be wary about just changing clustered/combined primary keys based solely (or primarily) on judgement about how much space to save.
    I know it’s not the topic at hand, but – by default – I wouldn’t say ‘space is wasted’ in those situations.

    For warehouses – space is an issue. For many (most) day-to-day business applications; speed is *the* issue – and then looking up the correct data using the correct key is absolutely something one needs to consider over having to look up on a surrogate key.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.