{"id":514,"date":"2012-02-06T13:20:00","date_gmt":"2012-02-06T13:20:00","guid":{"rendered":"\/blogs\/paul\/post\/Code-to-list-potential-cluster-key-space-savings-per-table.aspx"},"modified":"2017-04-13T09:49:48","modified_gmt":"2017-04-13T16:49:48","slug":"code-to-list-potential-cluster-key-space-savings-per-table","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/","title":{"rendered":"Code to list potential cluster key space savings per table"},"content":{"rendered":"

\nBack in January I posted the <\/font>results of the cluster key size survey<\/font><\/a> 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.<\/font>\n<\/p>\n

\nI'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)<\/a><\/font>\n<\/p>\n

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

\nEnjoy!<\/font>\n<\/p>\n

\n\/*============================================================================
\n  File:     KeySpaceSavingsSingleResultSet.sql<\/font>\n<\/p>\n

\n  Summary:  Potential cluster key space savings <\/font>\n<\/p>\n

\n  SQL Server Versions: 2005 onwards
\n——————————————————————————
\n  Written by Paul S. Randal, SQLskills.com<\/font>\n<\/p>\n

\n  (c) 2012, SQLskills.com. All rights reserved.<\/font>\n<\/p>\n

\n  For more scripts and sample code, check out
\n    <\/font>http:\/\/www.SQLskills.com<\/font><\/a>\n<\/p>\n

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

\nIF EXISTS (SELECT * FROM msdb.sys.objects WHERE [name] = 'SQLskillsIKSpace')
\n    DROP TABLE msdb.dbo.SQLskillsIKSpace;
\nGO
\nCREATE TABLE msdb.dbo.SQLskillsIKSpace (
\n    DatabaseID SMALLINT,
\n SchemaName SYSNAME,
\n ObjectName SYSNAME,
\n    ObjectID INT,
\n    IndexCount SMALLINT DEFAULT (0),
\n    TableRows BIGINT DEFAULT (0),
\n    KeyCount SMALLINT DEFAULT (0),
\n    KeyWidth SMALLINT DEFAULT (0));
\nGO <\/font>\n<\/p>\n

\nEXEC sp_MSforeachdb
\n    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT [name]
\n    FROM sys.databases WHERE [state_desc] = ''ONLINE''
\n        AND [database_id] > 4
\n        AND [name] != ''pubs''
\n        AND [name] != ''Northwind''
\n        AND [name] != ''distribution''
\n        AND [name] NOT LIKE ''ReportServer%''
\n        AND [name] NOT LIKE ''Adventure%'') AS names WHERE [name] = ''?'')
\nBEGIN
\nUSE [?] <\/font>\n<\/p>\n

\nINSERT INTO msdb.dbo.SQLskillsIKSpace
\n(DatabaseID, SchemaName, ObjectName, ObjectID)
\nSELECT DB_ID (''?''), SCHEMA_NAME (o.[schema_id]), OBJECT_NAME (o.[object_id]), o.[object_id]
\nFROM sys.objects o
\nWHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
\n    AND o.[is_ms_shipped] = 0
\n    AND EXISTS (
\n        SELECT *
\n        FROM sys.indexes
\n        WHERE [index_id] = 1
\n            AND [object_id] = o.[object_id]); <\/font>\n<\/p>\n

\nUPDATE msdb.dbo.SQLskillsIKSpace
\nSET [TableRows] = (
\n    SELECT SUM ([rows])
\n    FROM sys.partitions p
\n    WHERE p.[object_id] = [ObjectID]
\n    AND p.[index_id] = 1)
\nWHERE [DatabaseID] = DB_ID (''?'');
\n 
\nUPDATE msdb.dbo.SQLskillsIKSpace
\nSET [IndexCount] = (
\n    SELECT COUNT (*)
\n    FROM sys.indexes i
\n    WHERE i.[object_id] = [ObjectID]
\n    AND i.[is_hypothetical] = 0
\n    AND i.[is_disabled] = 0
\n    AND i.[index_id] != 1)
\nWHERE [DatabaseID] = DB_ID (''?''); <\/font>\n<\/p>\n

\nUPDATE msdb.dbo.SQLskillsIKSpace
\nSET [KeyCount] = (
\n    SELECT COUNT (*)
\n    FROM sys.index_columns ic
\n    WHERE ic.[object_id] = [ObjectID]
\n    AND ic.[index_id] = 1)
\nWHERE [DatabaseID] = DB_ID (''?''); <\/font>\n<\/p>\n

\nUPDATE msdb.dbo.SQLskillsIKSpace
\nSET [KeyWidth] = (
\n    SELECT SUM (c.[max_length])
\n    FROM sys.columns c
\n    JOIN sys.index_columns ic
\n    ON c.[object_id] = ic.[object_id]
\n    AND c.[object_id] = [ObjectID]
\n    AND ic.[column_id] = c.[column_id]
\n    AND ic.[index_id] = 1)
\nWHERE [DatabaseID] = DB_ID (''?''); <\/font>\n<\/p>\n

\nDELETE msdb.dbo.SQLskillsIKSpace
\nWHERE
\n    ([KeyCount] = 1 AND [KeyWidth] < 9)
\n    OR [IndexCount] = 0 OR [TableRows] = 0; <\/font>\n<\/p>\n

\nEND';
\nGO <\/font>\n<\/p>\n

\nSELECT
\n DB_NAME ([DatabaseID]) AS [Database],
\n [SchemaName] AS [Schema],
\n [ObjectName] AS [Table],
\n    [IndexCount] AS [NCIndexes],
\n    [KeyCount] AS [ClusterKeys],
\n    [KeyWidth],
\n    [TableRows],
\n    [IndexCount] * [TableRows] * [KeyWidth] AS [KeySpaceInBytes],
\n    ([IndexCount] * [TableRows] * ([KeyWidth] – 8)) AS [PotentialSavings]
\nFROM msdb.dbo.SQLskillsIKSpace
\nORDER BY [PotentialSavings] DESC; <\/font>\n<\/p>\n

\nDROP TABLE msdb.dbo.SQLskillsIKSpace;
\nGO<\/font>\n<\/p>\n

\n <\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,47,66],"tags":[],"class_list":["post-514","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-indexes-from-every-angle","category-performance-tuning"],"yoast_head":"\nCode to list potential cluster key space savings per table - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Code to list potential cluster key space savings per table - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-02-06T13:20:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:49:48+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/\",\"name\":\"Code to list potential cluster key space savings per table - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2012-02-06T13:20:00+00:00\",\"dateModified\":\"2017-04-13T16:49:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Code to list potential cluster key space savings per table\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Code to list potential cluster key space savings per table - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/","og_locale":"en_US","og_type":"article","og_title":"Code to list potential cluster key space savings per table - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/","og_site_name":"Paul S. Randal","article_published_time":"2012-02-06T13:20:00+00:00","article_modified_time":"2017-04-13T16:49:48+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/","name":"Code to list potential cluster key space savings per table - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2012-02-06T13:20:00+00:00","dateModified":"2017-04-13T16:49:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-list-potential-cluster-key-space-savings-per-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Code to list potential cluster key space savings per table"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/514","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=514"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/514\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=514"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=514"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=514"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}