Continuing with my "index health" series, I've got another piece of code for you to run.
This time I'm interested in the number of columns in your clustered indexes and the consequent amount of nonclustered index space used by the clustered index keys.
Again, you're going to be really interested to see the results on your servers. When I editorialize the results I'll provide another query for you to run which will make the data actionable on your server.
Here are some results from a random customer server (yes, we already knew about these - long story :-):
NCIndexes ClusterKeys KeyWidth TableRows KeySpaceInBytes
--------- ----------- -------- -------------------- --------------------
7 3 16 129902437 14549072944
1 3 12 29199817 350397804
10 2 12 1612919 193550280
5 2 5 4266671 106666775
2 2 8 5887697 94203152
5 4 20 827975 82797500
3 3 16 1215800 58358400
7 2 5 1497746 52421110
1 3 12 2667765 32013180
1 4 25 1033063 25826575
1 3 12 989320 11871840
2 2 8 278989 4463824
1 3 12 293736 3524832
4 2 5 160696 3213920
Feel free to send the results in any format you want - Excel spreadsheet works best though. Try not to add any columns to the result set - complicates the aggregation process.
The more results the better - thanks!
Here's the code:
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] = 'SQLskillsIKSpace')
DROP TABLE tempdb.dbo.SQLskillsIKSpace;
GO
CREATE TABLE tempdb.dbo.SQLskillsIKSpace (
DatabaseID SMALLINT,
ObjectID INT,
IndexCount SMALLINT,
TableRows BIGINT,
KeyCount SMALLINT,
KeyWidth SMALLINT);
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 tempdb.dbo.SQLskillsIKSpace
SELECT DB_ID (''?''), o.[object_id], 0, 0, 0, 0
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 tempdb.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 tempdb.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 tempdb.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 tempdb.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 tempdb.dbo.SQLskillsIKSpace
WHERE
([KeyCount] = 1 AND [KeyWidth] < 9)
OR [IndexCount] = 0 OR [TableRows] = 0;
END';
GO
SELECT
[IndexCount] AS [NCIndexes],
[KeyCount] AS [ClusterKeys],
[KeyWidth],
[TableRows],
[IndexCount] * [TableRows] * [KeyWidth] AS [KeySpaceInBytes]
FROM tempdb.dbo.SQLskillsIKSpace
ORDER BY [KeySpaceInBytes] DESC;
DROP TABLE tempdb.dbo.SQLskillsIKSpace;
GO