Here's a survey I've been meaning to run for quite a while - that Kimberly and I are really interested in.
For all the databases on your server, how many nonclustered indexes do you tables have, plus is the table a heap or a clustered index?
The code below will return the following result set (taken from a random client system):
BaseType NCIndexes TableCount
---------- --------- -----------
Clustered 0 1645
Clustered 1 832
Clustered 2 417
Clustered 3 68
Clustered 4 18
Clustered 5 13
Clustered 6 9
Clustered 7 5
Clustered 8 3
Clustered 9 3
Clustered 10 2
Heap 0 303
Heap 1 19
Heap 2 51
Heap 3 4
Heap 4 1
I think it'll make some really interesting reading, and you may be really surprised at the results for your system - how many tables with no indexes at all and how many tables with large numbers of nonclustered indexes.
And yes, I'm sure there's a slightly better way to write the code below, but I'm not an expert T-SQL developer (and yes Aaron, I know about your (seriously) fabulous sp_MSforeachdb replacement :-)
If someone wants to bang out a quick post with the PowerShell to run this on multiple servers, I'll link to it. (Update: The PowerShell script can be downloaded from the bottom of this post)
Feel free to send the results in email, in a spreadsheet, in a comment below, or even written on papyrus - anything works.
The more results the better - thanks!
Here's the code:
IF EXISTS (SELECT * FROM msdb.sys.objects WHERE [name] = 'SQLskillsPaulsIndexCounts')
DROP TABLE msdb.dbo.SQLskillsPaulsIndexCounts;
GO
CREATE TABLE msdb.dbo.SQLskillsPaulsIndexCounts (
BaseType CHAR (10),
IndexCount 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 msdb.dbo.SQLskillsPaulsIndexCounts
SELECT ''Heap'', COUNT (*)-1
FROM sys.objects o
JOIN sys.indexes i
ON o.[object_id] = i.[object_id]
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
AND o.[is_ms_shipped] = 0
AND EXISTS (
SELECT *
FROM sys.indexes
WHERE [index_id] = 0
AND [object_id] = o.[object_id])
GROUP BY i.[object_id];
INSERT INTO msdb.dbo.SQLskillsPaulsIndexCounts
SELECT ''Clustered'', COUNT (*)-1
FROM sys.objects o
JOIN sys.indexes i
ON o.[object_id] = i.[object_id]
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])
GROUP BY i.[object_id];
END';
GO
SELECT DISTINCT [BaseType], [IndexCount] AS [NCIndexes], COUNT (*) AS [TableCount]
FROM msdb.dbo.SQLskillsPaulsIndexCounts
GROUP BY [BaseType], [IndexCount]
ORDER BY [BaseType], [IndexCount];
GO
DROP TABLE msdb.dbo.SQLskillsPaulsIndexCounts;
GO
Pauls Index Survey.ps1 (2.64 kb)