Survey: nonclustered index counts (code to run)

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)

81 thoughts on “Survey: nonclustered index counts (code to run)”

Marc Brooks says:

August 4, 2011 at 4:01 pm

For my STLTweets / STLIndex working server:

Clustered 0 150
Clustered 1 116
Clustered 2 31
Clustered 3 23
Clustered 4 6
Clustered 5 20
Clustered 6 14
Heap 0 3

Reply
Mark S. Rasmussen says:

August 5, 2011 at 12:17 am

For my single production server:

Clustered 0 11091
Clustered 1 3098
Clustered 2 2
Clustered 3 324
Heap 0 2

Reply
Simon Osborne says:

August 5, 2011 at 1:46 am

Hi,

Hope this helps.

BaseType NCIndexes TableCount
Clustered 0 5983
Clustered 1 2212
Clustered 2 275
Clustered 3 102
Clustered 4 45
Clustered 5 29
Clustered 6 16
Heap 0 96
Heap 1 85
Heap 2 47
Heap 4 2

Reply
Nick Haslam says:

August 5, 2011 at 2:00 am

From our prod server.

BaseType NCIndexes TableCount
Clustered 0 1383
Clustered 1 1438
Clustered 2 611
Clustered 3 142
Clustered 4 143
Clustered 5 69
Clustered 6 26
Clustered 7 9
Clustered 8 8
Clustered 9 6
Clustered 10 9
Clustered 11 3
Clustered 12 10
Clustered 13 3
Clustered 14 1
Clustered 17 1
Clustered 18 3
Clustered 20 2
Heap 0 743
Heap 1 120
Heap 2 26
Heap 3 5
Heap 4 7
Heap 5 1
Heap 7 1
Heap 8 1
Heap 10 2
Heap 13 1

Reply
Andy Krafft says:

August 5, 2011 at 3:23 am

Production server:
BaseType NCIndexes TableCount
Clustered 0 49
Clustered 1 5
Clustered 2 3
Clustered 5 3
Clustered 6 1
Clustered 9 1
Clustered 16 1
Heap 0 1
Heap 1 4
Heap 2 2
Heap 3 3

I also added

AND [name] != ”AUTOINDEXRECS”
AND [name] != ”ManagementDataWarehouse”
AND [name] != ”SS_DBA_Dashboard”

to the script.

Reply
premkumar says:

August 5, 2011 at 3:41 am

Clustered 0 94
Clustered 1 49
Clustered 2 12
Clustered 3 2
Clustered 4 2
Clustered 7 1
Heap 0 4
Heap 2 1

Reply
Anders Gregersen says:

August 5, 2011 at 4:27 am

Clustered 0 3991
Clustered 1 2069
Clustered 2 1119
Clustered 3 352
Clustered 4 240
Clustered 5 160
Clustered 6 60
Clustered 7 25
Clustered 8 12
Clustered 9 8
Clustered 10 10
Clustered 11 5
Clustered 12 7
Clustered 13 6
Clustered 14 3
Clustered 16 2
Clustered 17 1
Clustered 18 1
Clustered 19 4
Clustered 20 2
Heap 0 1360
Heap 1 1257
Heap 2 1401
Heap 3 405
Heap 4 226
Heap 5 85
Heap 6 48
Heap 7 37
Heap 8 13
Heap 9 9
Heap 10 15
Heap 12 4
Heap 13 2
Heap 15 1

Reply
Eugene Niemand says:

August 5, 2011 at 5:36 am

This is only two servers

BaseType NCIndexes TableCount
Clustered 0 792
Clustered 1 239
Clustered 2 86
Clustered 3 47
Clustered 4 14
Clustered 5 4
Clustered 8 1
Heap 0 119
Heap 1 8
Heap 2 12
Heap 3 3

and the other one

BaseType NCIndexes TableCount
Clustered 0 252
Clustered 1 41
Clustered 2 9
Clustered 3 2
Clustered 4 4
Clustered 5 2
Clustered 6 3
Clustered 7 3
Clustered 8 1
Clustered 12 1
Clustered 15 1
Clustered 17 1
Heap 0 104
Heap 1 31
Heap 3 54
Heap 4 22
Heap 5 6
Heap 7 4
Heap 16 1

Reply
Ken Watson says:

August 5, 2011 at 6:00 am

BaseType NCIndexes TableCount
Clustered 0 4736
Clustered 1 3310
Clustered 2 1291
Clustered 3 539
Clustered 4 184
Clustered 5 172
Clustered 6 60
Clustered 7 49
Clustered 8 16
Clustered 9 6
Clustered 10 3
Clustered 11 3
Heap 0 55
Heap 1 1155
Heap 2 284
Heap 3 72
Heap 4 15
Heap 5 18
Heap 6 6
Heap 7 6
Heap 8 6
Heap 10 3
Heap 11 4
Heap 13 1

Reply
Steve Sc says:

August 5, 2011 at 6:25 am

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 238
Clustered 1 61
Clustered 2 33
Clustered 3 13
Clustered 4 7
Clustered 5 3
Clustered 6 5
Clustered 7 1
Clustered 8 3
Clustered 9 3
Clustered 10 2
Clustered 11 3
Clustered 12 2
Clustered 13 2
Clustered 14 1
Clustered 15 2
Clustered 17 2
Clustered 18 3

Reply
John Martin says:

August 5, 2011 at 6:47 am

BaseType NCIndexes TableCount
Clustered 0 6492
Clustered 1 3810
Clustered 2 2659
Clustered 3 1244
Clustered 4 868
Clustered 5 485
Clustered 6 344
Clustered 7 93
Clustered 8 73
Clustered 9 15
Clustered 10 58
Clustered 11 23
Clustered 12 24
Clustered 13 9
Clustered 14 32
Clustered 15 1
Clustered 18 17
Clustered 19 8
Clustered 20 1
Clustered 25 1
Clustered 27 2
Heap 0 411
Heap 1 357
Heap 2 374
Heap 3 306
Heap 4 64
Heap 5 68
Heap 6 14
Heap 7 14
Heap 13 2

Reply
Garry Bargsley says:

August 5, 2011 at 6:52 am

Server 1
BaseType NCIndexes TableCount
Clustered 0 140
Clustered 1 14
Clustered 3 1
Heap 0 11
Heap 1 4

Server 2
BaseType NCIndexes TableCount
Clustered 0 489
Clustered 1 103
Clustered 2 69
Clustered 3 18
Clustered 4 4
Clustered 5 3
Clustered 6 3
Clustered 8 1
Clustered 9 2
Clustered 11 1
Clustered 12 1
Clustered 21 1
Heap 0 573
Heap 1 946
Heap 2 59
Heap 3 30
Heap 4 8
Heap 5 3
Heap 6 4
Heap 7 2
Heap 11 1
Heap 14 1
Heap 16 1

Server 3
BaseType NCIndexes TableCount
Clustered 0 9490
Clustered 1 160
Clustered 2 23
Clustered 3 11
Clustered 4 8
Clustered 5 1
Clustered 6 5
Clustered 7 3
Clustered 8 2
Clustered 9 1
Clustered 13 1
Heap 0 230
Heap 1 14

Reply
Nate says:

August 5, 2011 at 6:54 am

BaseType NCIndexes TableCount
Clustered 0 1032
Clustered 1 457
Clustered 2 261
Clustered 3 41
Clustered 4 23
Clustered 5 17
Clustered 6 7
Clustered 7 4
Clustered 8 3
Clustered 9 2
Clustered 11 1
Heap 0 389
Heap 1 266
Heap 2 25
Heap 3 9
Heap 4 3
Heap 5 1

Reply
Stephen Dyckes says:

August 5, 2011 at 6:57 am

Main production server:
BaseType NCIndexes TableCount
Clustered 0 829
Clustered 1 251
Clustered 2 64
Clustered 3 23
Clustered 4 24
Clustered 5 12
Clustered 6 7
Clustered 7 6
Clustered 8 1
Clustered 9 5
Clustered 10 3
Clustered 11 3
Clustered 12 1
Heap 0 494
Heap 1 62
Heap 2 8
Heap 3 4

Secondary application Production server:

Clustered 0 348
Clustered 1 123
Clustered 2 31
Clustered 3 3
Clustered 5 9
Clustered 9 1
Clustered 10 1
Heap 0 547
Heap 1 279
Heap 2 44
Heap 3 28
Heap 4 14
Heap 5 7
Heap 6 3
Heap 7 4
Heap 9 1
Heap 10 1
Heap 11 2
Heap 13 2
Heap 22 1

Reply
wqw says:

August 5, 2011 at 7:22 am

BaseType NCIndexes TableCount
Clustered 0 1134
Clustered 1 1697
Clustered 2 875
Clustered 3 360
Clustered 4 165
Clustered 5 381
Clustered 6 81
Clustered 7 28
Clustered 9 1
Clustered 12 27
Heap 0 2
Heap 1 36
Heap 2 3
Heap 3 2

Reply
George says:

August 5, 2011 at 7:32 am

BaseType NCIndexes TableCount
Clustered 0 1381
Clustered 1 424
Clustered 2 207
Clustered 3 125
Clustered 4 116
Clustered 5 23
Clustered 6 10
Clustered 7 2
Clustered 8 5
Clustered 9 1
Clustered 11 2
Heap 0 2059
Heap 1 17
Heap 2 6
Heap 3 3
Heap 4 1
Heap 6 1

Time to check some of the tables, I guess…

Reply
Scott Shaw says:

August 5, 2011 at 7:34 am

Paul,

I wasn’t going to comment but you tweeted about 2 people having 22 nonclustered indexes as the most you’ve seen and said to myself – I think I can do better than that.

BaseType NCIndexes TableCount
Clustered 0 48
Clustered 1 23
Clustered 2 8
Clustered 3 2
Clustered 13 1
Heap 0 16
Heap 1 6
Heap 2 20
Heap 4 6
Heap 44 2

For this particular database the users actually have the option of creating a table and selecting to create an index for every column. I believe the application is developed by EMC.

Reply
David Howard says:

August 5, 2011 at 7:39 am

S1:
BaseType NCIndexes TableCount
Clustered 0 1137
Clustered 1 365
Clustered 2 191
Clustered 3 82
Clustered 4 31
Clustered 5 8
Clustered 6 9
Clustered 7 7
Clustered 8 2
Clustered 9 5
Clustered 10 1
Clustered 12 2
Clustered 14 3
Clustered 15 1
Clustered 16 1
Clustered 17 1
Clustered 18 1
Clustered 19 1
Clustered 26 1
Heap 0 700
Heap 1 229
Heap 2 47
Heap 3 24
Heap 4 8
Heap 5 3
Heap 6 1
Heap 7 1
Heap 8 1
Heap 9 3

S2:
BaseType NCIndexes TableCount
Clustered 0 480
Clustered 1 314
Clustered 2 195
Clustered 3 55
Clustered 4 31
Clustered 5 34
Clustered 6 35
Clustered 7 6
Clustered 8 3
Clustered 9 1
Clustered 12 2
Clustered 13 2
Clustered 15 1
Clustered 16 1
Clustered 24 1
Heap 0 126
Heap 1 143
Heap 2 72
Heap 3 23
Heap 4 5
Heap 5 3
Heap 6 1
Heap 7 1

Reply
Rudy Rordarte says:

August 5, 2011 at 7:48 am

BaseType NCIndexes TableCount
Clustered 0 1686
Clustered 1 1591
Clustered 2 567
Clustered 3 81
Clustered 4 35
Clustered 5 15
Clustered 6 1
Clustered 7 5
Clustered 8 2
Clustered 11 1
Heap 0 454
Heap 1 70
Heap 2 31
Heap 3 5

Reply
Travis Thomas says:

August 5, 2011 at 7:58 am

I just did our single production server.

BaseType NCIndexes TableCount
Clustered 0 700
Clustered 1 275
Clustered 2 184
Clustered 3 75
Clustered 4 45
Clustered 5 32
Clustered 6 16
Clustered 7 4
Clustered 8 4
Clustered 9 6
Clustered 10 6
Clustered 11 6
Clustered 12 2
Clustered 13 2
Clustered 15 2
Clustered 16 2
Clustered 24 2
Heap 0 172
Heap 1 52
Heap 2 22
Heap 3 17
Heap 4 10
Heap 5 6
Heap 8 2

Reply
Thomas Rushton says:

August 5, 2011 at 1:01 am

Before this goes any further, I would like to point out that I am not responsible for any of the schemas in place here!

For the main SQL2005 DB server:

Clustered 0 490
Clustered 1 136
Clustered 2 45
Clustered 3 12
Clustered 4 3
Clustered 6 4
Clustered 7 3
Clustered 8 1
Clustered 9 1
Clustered 10 1
Clustered 12 1
Heap 0 762
Heap 1 90
Heap 2 19
Heap 3 8
Heap 4 2
Heap 7 1

For the SSAS server:
Clustered 0 276
Clustered 1 35
Clustered 2 33
Clustered 3 23
Clustered 4 18
Clustered 5 3
Clustered 6 17
Clustered 7 16
Heap 0 748
Heap 1 286
Heap 2 382
Heap 3 13
Heap 4 3
Heap 5 1

From the Sharepoint/intranet server:
Clustered 0 276
Clustered 1 35
Clustered 2 33
Clustered 3 23
Clustered 4 18
Clustered 5 3
Clustered 6 17
Clustered 7 16
Heap 0 748
Heap 1 286
Heap 2 382
Heap 3 13
Heap 4 3
Heap 5 1

Reply
Gail Shaw says:

August 5, 2011 at 8:07 am

Results from 3 different clients’ databases.

Client 1:
Clustered 0 17
Clustered 1 13
Clustered 2 6
Clustered 5 1
Heap 0 16

I designed that DB, the heaps are error or staging tables, the ones with nothing but a cluster are lookup tables.

—————————————

Client 2:
Clustered 0 22
Clustered 1 17

This is a vendor application. Not too bad, all things considered.

—————————————

Client 3:
Clustered 0 538
Clustered 1 36
Clustered 2 7
Clustered 3 13
Clustered 4 2
Clustered 5 2
Clustered 6 2
Clustered 7 3
Clustered 8 1
Clustered 9 3
Clustered 10 2
Clustered 11 1
Clustered 13 1
Clustered 14 1
Clustered 16 1
Clustered 18 1
Clustered 19 1
Clustered 30 1
Clustered 84 1
Heap 0 54
Heap 2 2
Heap 3 1

84 Nonclustered indexes on one table. What do I win?

Reply
Ricardo Leka says:

August 5, 2011 at 8:08 am

BaseType NCIndexes TableCount
Clustered 0 695
Clustered 1 234
Clustered 2 156
Clustered 3 57
Clustered 4 38
Clustered 5 11
Clustered 6 7
Clustered 7 3
Clustered 8 2
Clustered 9 3
Clustered 10 1
Clustered 12 1
Clustered 15 1
Heap 0 205
Heap 1 14
Heap 2 4
Heap 3 4
Heap 5 1

Reply
Shawn Melton says:

August 5, 2011 at 8:33 am

server1
BaseType NCIndexes TableCount
Clustered 0 180
Clustered 1 95
Heap 0 1702

server2\instance1
Clustered 0 678
Clustered 1 377
Clustered 2 226
Clustered 3 42
Clustered 4 33
Clustered 5 6
Clustered 6 6
Clustered 7 2
Clustered 9 6
Clustered 10 1
Clustered 15 1
Clustered 18 1
Heap 0 230
Heap 1 164
Heap 2 49
Heap 3 39
Heap 4 40
Heap 5 6
Heap 6 2
Heap 7 2
Heap 9 1

server1\instance2
Clustered 0 550
Clustered 1 188
Clustered 2 244
Clustered 3 40
Clustered 4 20
Clustered 5 7
Clustered 6 3
Clustered 9 2
Clustered 10 1
Heap 0 116
Heap 1 18
Heap 2 4
Heap 4 1

Reply
Stephen Archbold says:

August 5, 2011 at 8:34 am

Disclaimer – One DB schema is out of my hands, I will now try to claw it back by declaring a Holy war on the Dev team:

BaseType NCIndexes TableCount
Clustered 0 2737
Clustered 1 282
Clustered 2 93
Clustered 3 61
Clustered 4 22
Clustered 5 37
Clustered 6 27
Clustered 7 6
Clustered 8 4
Clustered 9 6
Clustered 10 19
Clustered 12 1
Clustered 13 1
Clustered 18 1
Clustered 31 1
Clustered 164 1
Heap 0 515
Heap 1 396
Heap 2 5
Heap 3 5
Heap 5 2
Heap 7 2
Heap 13 1

Reply
David Baffaleuf says:

August 5, 2011 at 8:41 am

From a CRM:

BaseType NCIndexes TableCount
Clustered 0 540
Clustered 1 85
Clustered 2 25
Clustered 3 84
Clustered 4 34
Clustered 5 15
Clustered 6 3
Clustered 7 2
Clustered 8 4
Clustered 9 2
Clustered 10 3
Clustered 15 2
Heap 0 296
Heap 1 109
Heap 2 123
Heap 3 250
Heap 4 55
Heap 5 42
Heap 6 3
Heap 7 6
Heap 8 9
Heap 10 3
Heap 11 3
Heap 12 3
Heap 13 3
Heap 14 6
Heap 15 6
Heap 16 141
Heap 17 16
Heap 18 6
Heap 19 6
Heap 20 9
Heap 23 2
Heap 24 1
Heap 31 3
Heap 33 3
Heap 40 3

Reply
Bob K says:

August 5, 2011 at 8:54 am

Clustered 0 489
Clustered 1 185
Clustered 2 37
Clustered 3 17
Clustered 4 7
Clustered 5 10
Clustered 6 2
Clustered 8 1
Clustered 9 1
Heap 0 100
Heap 1 92
Heap 2 20
Heap 3 11
Heap 4 4
Heap 5 3
Heap 8 1
Clustered 0 1255
Clustered 1 181
Clustered 2 51
Clustered 3 31
Clustered 4 12
Clustered 5 6
Clustered 6 7
Clustered 7 5
Clustered 8 10
Clustered 9 10
Clustered 10 4
Clustered 12 4
Clustered 15 2
Clustered 24 1
Heap 0 268
Heap 1 203
Heap 2 60
Heap 3 15
Heap 4 1
Heap 5 2
Heap 6 2
Heap 8 2
Heap 9 2
Heap 10 1
Heap 19 2
Heap 23 1
Clustered 0 197
Clustered 1 100
Clustered 2 39
Clustered 3 14
Clustered 4 4
Clustered 5 4
Clustered 6 1
Heap 0 128
Heap 1 561
Heap 2 213
Heap 3 108
Heap 4 26
Heap 5 3
Heap 6 4
Heap 7 4
Heap 8 1
Heap 9 3
Heap 11 1
Clustered 0 3225
Clustered 1 182
Clustered 2 59
Clustered 3 38
Clustered 4 8
Clustered 5 15
Clustered 6 5
Clustered 7 4
Clustered 8 1
Clustered 9 1
Clustered 10 1
Clustered 11 1
Heap 0 125
Heap 1 1727
Heap 2 17
Heap 3 16
Heap 4 3
Heap 5 3
Heap 6 2
Clustered 0 33
Clustered 1 2
Clustered 2 6
Clustered 3 2
Clustered 5 1
Clustered 6 1
Heap 0 3
Clustered 0 721
Clustered 1 469
Clustered 2 965
Clustered 3 212
Clustered 4 92
Clustered 5 84
Clustered 6 17
Clustered 7 9
Clustered 8 8
Clustered 9 10
Clustered 10 17
Clustered 11 2
Clustered 12 4
Clustered 13 6
Clustered 15 2
Clustered 16 2
Clustered 17 2
Clustered 18 2
Clustered 26 1
Clustered 27 4
Clustered 29 1
Heap 0 2679
Heap 1 31
Heap 2 9
Heap 5 4
Heap 8 1
Clustered 0 5904
Clustered 1 283
Clustered 2 107
Clustered 3 63
Clustered 4 10
Clustered 5 26
Clustered 6 10
Clustered 7 8
Clustered 8 2
Clustered 9 2
Clustered 10 2
Clustered 11 2
Heap 0 113
Heap 1 3332
Heap 2 28
Heap 3 9
Heap 4 4
Heap 5 2
Heap 6 2
Heap 8 1
Clustered 0 218
Clustered 1 426
Clustered 2 37
Clustered 3 33
Clustered 4 9
Clustered 5 13
Clustered 6 2
Clustered 7 1
Clustered 10 1
Clustered 11 1
Heap 0 88
Heap 1 16
Heap 2 5
Heap 3 4
Heap 5 2
Heap 23 1
Clustered 0 524
Clustered 1 1350
Clustered 2 941
Clustered 3 68
Clustered 4 65
Clustered 5 14
Clustered 6 6
Clustered 9 3
Clustered 10 2
Clustered 11 1
Clustered 12 1
Heap 0 2521
Heap 1 36
Heap 2 13
Heap 3 3
Heap 4 5
Heap 7 1
Heap 10 2
Heap 13 1

Reply
Norman Heyen says:

August 5, 2011 at 9:12 am

On a production 2008 server, basically a datawarehouse database

BaseType NCIndexes TableCount
Clustered 0 16536
Clustered 1 1341
Clustered 2 502
Clustered 3 81
Clustered 4 49
Clustered 5 2
Clustered 6 2
Clustered 7 7
Clustered 8 1
Clustered 9 1
Clustered 10 3
Clustered 11 1
Clustered 15 1
Clustered 18 1
Clustered 19 1
Clustered 20 1
Clustered 23 1
Heap 0 227
Heap 1 36
Heap 2 18
Heap 3 4

Reply
Vitor Pombeiro says:

August 5, 2011 at 9:19 am

I am not responsible for any of the schemas in place here!
SERVER1 Clustered 0 728
SERVER1 Clustered 1 141
SERVER1 Clustered 2 37
SERVER1 Clustered 3 20
SERVER1 Clustered 4 8
SERVER1 Clustered 6 1
SERVER1 Heap 0 155
SERVER1 Heap 1 14
SERVER1 Heap 2 2
SERVER1 Heap 3 3
SERVER1 Heap 5 1
SERVER2 Clustered 0 139
SERVER2 Clustered 1 46
SERVER2 Clustered 2 8
SERVER2 Clustered 3 4
SERVER2 Clustered 4 1
SERVER2 Heap 0 48
SERVER3 Clustered 0 134
SERVER3 Clustered 1 38
SERVER3 Clustered 2 8
SERVER3 Clustered 3 2
SERVER3 Clustered 4 1
SERVER3 Heap 0 48
DW1 Clustered 0 127
DW1 Clustered 1 5
DW1 Clustered 2 1
DW1 Heap 0 434
DW1 Heap 2 1
DW1 Heap 3 1
DW2 Clustered 0 616
DW2 Clustered 1 58
DW2 Clustered 2 19
DW2 Clustered 3 4
DW2 Heap 0 157
DW2 Heap 1 9
DW2 Heap 2 4
DW2 Heap 3 2
DW2 Heap 4 3

Reply
Vitor Pombeiro says:

August 5, 2011 at 9:21 am

I’m not responsible for any of the schemas.

SERVER1 Clustered 0 728
SERVER1 Clustered 1 141
SERVER1 Clustered 2 37
SERVER1 Clustered 3 20
SERVER1 Clustered 4 8
SERVER1 Clustered 6 1
SERVER1 Heap 0 155
SERVER1 Heap 1 14
SERVER1 Heap 2 2
SERVER1 Heap 3 3
SERVER1 Heap 5 1
SERVER2 Clustered 0 139
SERVER2 Clustered 1 46
SERVER2 Clustered 2 8
SERVER2 Clustered 3 4
SERVER2 Clustered 4 1
SERVER2 Heap 0 48
SERVER3 Clustered 0 134
SERVER3 Clustered 1 38
SERVER3 Clustered 2 8
SERVER3 Clustered 3 2
SERVER3 Clustered 4 1
SERVER3 Heap 0 48
DW1 Clustered 0 127
DW1 Clustered 1 5
DW1 Clustered 2 1
DW1 Heap 0 434
DW1 Heap 2 1
DW1 Heap 3 1
DW2 Clustered 0 616
DW2 Clustered 1 58
DW2 Clustered 2 19
DW2 Clustered 3 4
DW2 Heap 0 157
DW2 Heap 1 9
DW2 Heap 2 4
DW2 Heap 3 2
DW2 Heap 4 3

Reply
Miguel says:

August 5, 2011 at 10:09 am

this is from my worst performance server

BaseType NCIndexes TableCount
Clustered 0 333
Clustered 1 54
Clustered 2 17
Clustered 3 1
Clustered 4 3
Clustered 5 1
Heap 0 257
Heap 1 49
Heap 2 19
Heap 3 3

Reply
Calvin Jones says:

August 5, 2011 at 10:33 am

BaseType NCIndexes TableCount
Clustered 0 561
Clustered 1 702
Clustered 2 686
Clustered 3 438
Clustered 4 214
Clustered 5 205
Clustered 6 94
Clustered 7 114
Clustered 8 40
Clustered 9 46
Clustered 10 7
Clustered 11 1
Clustered 12 2
Clustered 13 1
Clustered 15 2
Clustered 16 21
Clustered 17 2
Clustered 20 1
Clustered 25 2
Clustered 26 10
Clustered 27 2
Heap 0 312
Heap 1 81
Heap 2 7
Heap 3 7
Heap 5 2
Heap 6 2
Heap 7 2
Heap 16 15
Heap 17 2
Heap 26 15
Heap 27 2

Reply
Bob Pusateri says:

August 5, 2011 at 10:38 am

I can’t touch any of these indexes and it drives me crazy!

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 11018
Clustered 1 693
Clustered 2 254
Clustered 3 147
Clustered 4 93
Clustered 5 29
Clustered 6 24
Clustered 7 19
Clustered 8 15
Clustered 9 2
Clustered 10 6
Clustered 11 12
Clustered 12 1
Clustered 13 5
Clustered 14 2
Clustered 15 3
Clustered 16 1
Clustered 17 2
Clustered 18 5
Clustered 19 2
Clustered 21 1
Clustered 22 2
Clustered 25 1
Clustered 26 1
Clustered 28 1
Clustered 43 1
Heap 0 2591
Heap 1 160
Heap 2 91
Heap 3 213
Heap 4 943
Heap 5 266
Heap 6 302
Heap 7 49
Heap 8 24
Heap 9 26
Heap 10 11
Heap 11 12
Heap 12 11
Heap 13 8
Heap 14 8
Heap 15 9
Heap 16 5
Heap 17 4
Heap 18 3
Heap 19 6
Heap 20 1
Heap 21 1
Heap 23 1
Heap 25 1
Heap 29 1
Heap 30 1
Heap 31 1
Heap 33 2
Heap 34 1
Heap 40 1
Heap 41 1
Heap 48 1
Heap 56 1
Heap 62 1
Heap 65 1
Heap 90 1
Heap 96 2
Heap 99 1
Heap 125 1

Reply
Stefan says:

August 5, 2011 at 10:39 am

Server 1
Clustered 0 54
Clustered 1 32
Clustered 2 13
Clustered 3 2
Clustered 5 2
Clustered 6 1
Clustered 7 4
Heap 0 11
Heap 1 351
Heap 2 136
Heap 3 24
Heap 4 22
Heap 5 15
Heap 7 6
Heap 8 9
Heap 9 3

Server 2
Clustered 0 477
Clustered 1 135
Clustered 2 21
Clustered 3 3
Clustered 4 3
Heap 0 23
Heap 1 6
Heap 2 4

Server 3
Clustered 0 9
Clustered 1 11
Clustered 2 1
Clustered 9 1
Heap 0 12
Heap 1 401
Heap 2 58
Heap 3 27
Heap 4 11
Heap 5 7
Heap 6 2
Heap 7 1
Heap 8 1

Reply
Drew says:

August 5, 2011 at 10:57 am

BaseType NCIndexes TableCount
Clustered 0 4685
Clustered 1 1238
Clustered 2 227
Clustered 3 104
Clustered 4 27
Clustered 5 23
Clustered 6 9
Clustered 7 19
Clustered 8 19
Clustered 9 10
Clustered 10 7
Clustered 11 4
Clustered 12 1
Clustered 13 3
Clustered 14 19
Clustered 15 5
Clustered 17 9
Clustered 18 17
Clustered 19 1
Clustered 22 1
Clustered 24 8
Clustered 32 1
Clustered 44 7
Clustered 48 9
Clustered 55 1
Clustered 64 1
Clustered 69 1
Clustered 72 1
Clustered 152 1
Clustered 173 1
Heap 0 3068
Heap 1 241
Heap 2 69
Heap 3 56

Oh the shame…

Reply
John D says:

August 5, 2011 at 12:15 pm

My 2 production DB’s

BaseType NCIndexes TableCount
Clustered 0 1262
Clustered 1 320
Clustered 2 104
Clustered 3 50
Clustered 4 32
Clustered 5 25
Clustered 6 13
Clustered 7 8
Clustered 9 8
Clustered 10 6
Clustered 11 2
Clustered 12 6
Clustered 13 4
Clustered 17 2
Clustered 18 2
Clustered 21 2
Clustered 27 4
Heap 0 142
Heap 1 151
Heap 2 28
Heap 3 10
Heap 4 2
Heap 5 4
Heap 6 4
Heap 9 2
Heap 11 2

BaseType NCIndexes TableCount
Clustered 0 276
Clustered 1 143
Clustered 2 215
Clustered 3 37
Clustered 4 23
Clustered 5 17
Clustered 6 2
Clustered 7 2
Clustered 8 3
Clustered 9 1
Clustered 10 1
Heap 0 493
Heap 1 9
Heap 2 2
Heap 3 1

Reply
ishtiaq says:

August 5, 2011 at 1:30 pm

BaseType NCIndexes TableCount
Clustered 0 181
Clustered 1 20
Clustered 2 14
Clustered 4 2
Clustered 5 2
Heap 0 7
Heap 1 4
Heap 2 4
Heap 3 2

Reply
Collectibles says:

August 5, 2011 at 6:47 am

Clustered 0 94
Clustered 1 49
Clustered 2 12
Clustered 3 2
Clustered 4 2
Clustered 7 1
Heap 0 4
Heap 2 1

Reply
Tom Powell says:

August 5, 2011 at 7:01 am

BaseType NCIndexes TableCount
——– ——— ———-
Clustered 0 3529
Clustered 1 1665
Clustered 2 673
Clustered 3 202
Clustered 4 92
Clustered 5 39
Clustered 6 20
Clustered 7 14
Clustered 8 10
Clustered 9 3
Clustered 10 4
Clustered 11 9
Clustered 12 34
Clustered 13 2
Clustered 14 2
Clustered 15 2
Clustered 18 3
Clustered 24 1
Heap 0 1115
Heap 1 162
Heap 2 1652
Heap 3 88
Heap 4 12
Heap 6 2
Heap 7 5
Heap 10 2
Heap 13 1
Heap 18 1
Clustered 0 770
Clustered 1 224
Clustered 2 65
Clustered 3 17
Clustered 4 8
Clustered 5 4
Clustered 6 6
Clustered 7 3
Clustered 8 1
Clustered 10 1
Clustered 13 2
Clustered 14 1
Clustered 15 1
Heap 0 339
Heap 1 230
Heap 2 37
Heap 3 27
Heap 5 3
Heap 6 3
Heap 7 1
Heap 10 1
Heap 12 1
Heap 14 1
Clustered 0 263
Clustered 1 171
Clustered 2 20
Clustered 3 10
Clustered 4 1
Clustered 5 3
Clustered 7 1
Clustered 9 1
Clustered 10 1
Heap 0 103
Heap 1 8
Heap 2 8
Heap 4 1
Clustered 0 54
Clustered 1 17
Clustered 2 6
Clustered 3 4
Clustered 4 1
Clustered 5 1
Heap 0 14
Heap 1 1

Reply
John Glasgow says:

August 5, 2011 at 7:32 am

Production server, internally developed apps and third party apps.

BaseType NCIndexes TableCount
Clustered 0 364
Clustered 1 216
Clustered 2 75
Clustered 3 29
Clustered 4 4
Clustered 5 6
Clustered 6 5
Clustered 8 2
Clustered 11 4
Clustered 12 2
Clustered 14 3
Heap 0 117
Heap 1 33
Heap 2 20
Heap 3 9
Heap 4 2
Heap 6 1
Heap 7 1
Heap 8 2

Reply
Fatherjack says:

August 5, 2011 at 7:34 am

Server Name BaseType NCIndexes TableCount
Server01 Clustered 0 1
Server01 Heap 0 3
Server02 Clustered 0 408
Server02 Clustered 1 158
Server02 Clustered 2 79
Server02 Clustered 3 43
Server02 Clustered 4 15
Server02 Clustered 5 9
Server02 Clustered 6 6
Server02 Clustered 7 2
Server02 Clustered 9 3
Server02 Clustered 10 2
Server02 Clustered 11 1
Server02 Clustered 12 1
Server02 Clustered 17 1
Server02 Clustered 19 1
Server02 Heap 0 232
Server02 Heap 1 61
Server02 Heap 2 7
Server02 Heap 3 2
Server02 Heap 4 1
Server02 Heap 6 1
Server02 Heap 7 1
Server03 Clustered 0 1026
Server03 Clustered 1 407
Server03 Clustered 2 77
Server03 Clustered 3 22
Server03 Clustered 4 7
Server03 Clustered 5 7
Server03 Clustered 6 4
Server03 Clustered 7 1
Server03 Clustered 8 1
Server03 Clustered 10 1
Server03 Heap 0 483
Server03 Heap 1 65
Server03 Heap 2 30
Server03 Heap 3 10
Server04 Clustered 0 58
Server04 Clustered 1 12
Server04 Clustered 4 1
Server04 Heap 0 22
Server04 Heap 1 2
Server04 Heap 2 1
Server05 Clustered 0 640
Server05 Clustered 1 262
Server05 Clustered 2 52
Server05 Clustered 3 23
Server05 Clustered 4 6
Server05 Clustered 5 4
Server05 Clustered 6 4
Server05 Clustered 7 3
Server05 Clustered 8 2
Server05 Clustered 10 2
Server05 Clustered 11 2
Server05 Clustered 16 1
Server05 Clustered 23 1
Server05 Clustered 25 1
Server05 Heap 0 199
Server05 Heap 1 67
Server05 Heap 2 53
Server05 Heap 3 6
Server05 Heap 4 1
Server05 Heap 5 1
Server05 Heap 6 3
Server05 Heap 13 3
Server05 Heap 26 1
Server06 Clustered 0 1271
Server06 Clustered 1 248
Server06 Clustered 2 50
Server06 Clustered 3 21
Server06 Clustered 4 5
Server06 Clustered 5 4
Server06 Clustered 6 4
Server06 Clustered 7 2
Server06 Clustered 8 1
Server06 Clustered 10 2
Server06 Clustered 11 2
Server06 Clustered 23 1
Server06 Clustered 25 1
Server06 Heap 0 273
Server06 Heap 1 72
Server06 Heap 2 53
Server06 Heap 3 6
Server06 Heap 4 1
Server06 Heap 5 1
Server06 Heap 6 1
Server07 Clustered 0 420
Server07 Clustered 1 213
Server07 Heap 0 40
Server07 Heap 1 59
Server07 Heap 2 2

Reply
Gianluca Sartori says:

August 5, 2011 at 8:11 am

Is there a prize for the worst indexing? :-)

Server Name BaseType NCIndexes TableCount
SERVER01 Clustered 0 38
SERVER01 Clustered 1 99
SERVER01 Clustered 2 10
SERVER01 Clustered 3 10
SERVER01 Clustered 4 4
SERVER01 Clustered 5 2
SERVER01 Clustered 6 1
SERVER01 Clustered 11 1
SERVER01 Heap 0 19
SERVER01 Heap 1 4
SERVER04 Clustered 0 352
SERVER04 Clustered 1 56
SERVER04 Clustered 2 19
SERVER04 Clustered 3 15
SERVER04 Clustered 4 8
SERVER04 Clustered 5 4
SERVER04 Clustered 6 3
SERVER04 Clustered 8 2
SERVER04 Clustered 14 1
SERVER04 Clustered 15 1
SERVER04 Clustered 18 1
SERVER04 Heap 0 428
SERVER04 Heap 1 118
SERVER04 Heap 2 11
SERVER04 Heap 3 4
SERVER06 Clustered 0 149
SERVER06 Clustered 1 204
SERVER06 Clustered 2 279
SERVER06 Clustered 3 102
SERVER06 Clustered 4 35
SERVER06 Clustered 5 38
SERVER06 Clustered 6 10
SERVER06 Clustered 7 12
SERVER06 Clustered 8 20
SERVER06 Clustered 9 17
SERVER06 Clustered 10 4
SERVER06 Clustered 11 4
SERVER06 Clustered 12 4
SERVER06 Clustered 13 3
SERVER06 Clustered 14 1
SERVER06 Clustered 16 2
SERVER06 Clustered 17 1
SERVER06 Clustered 19 1
SERVER06 Heap 0 162
SERVER06 Heap 1 1
SERVER06 Heap 2 2
SERVER06 Heap 3 1
SERVER06 Heap 4 1
SERVER06 Heap 5 3
SERVER06 Heap 6 1
SERVER10 Clustered 0 1844
SERVER10 Clustered 1 238
SERVER10 Clustered 2 39
SERVER10 Heap 0 225
SERVER10 Heap 1 234
SERVER10 Heap 2 3
SERVER02 Clustered 0 1274
SERVER02 Clustered 1 163
SERVER02 Clustered 2 36
SERVER02 Clustered 3 25
SERVER02 Clustered 4 5
SERVER02 Clustered 5 10
SERVER02 Clustered 6 3
SERVER02 Clustered 10 2
SERVER02 Clustered 12 2
SERVER02 Clustered 20 5
SERVER02 Heap 0 909
SERVER02 Heap 1 592
SERVER02 Heap 2 24
SERVER02 Heap 3 8
SERVER09 Clustered 0 1595
SERVER09 Clustered 1 452
SERVER09 Clustered 2 220
SERVER09 Clustered 3 95
SERVER09 Clustered 4 56
SERVER09 Clustered 5 34
SERVER09 Clustered 6 13
SERVER09 Clustered 7 12
SERVER09 Clustered 8 10
SERVER09 Clustered 9 5
SERVER09 Clustered 10 5
SERVER09 Clustered 11 2
SERVER09 Clustered 12 2
SERVER09 Clustered 13 3
SERVER09 Clustered 14 6
SERVER09 Clustered 15 5
SERVER09 Clustered 16 3
SERVER09 Clustered 18 1
SERVER09 Clustered 19 2
SERVER09 Clustered 20 6
SERVER09 Clustered 22 1
SERVER09 Clustered 23 1
SERVER09 Clustered 24 3
SERVER09 Clustered 25 1
SERVER09 Clustered 26 3
SERVER09 Clustered 29 1
SERVER09 Clustered 30 1
SERVER09 Clustered 34 2
SERVER09 Clustered 36 4
SERVER09 Clustered 37 3
SERVER09 Clustered 38 2
SERVER09 Clustered 40 3
SERVER09 Clustered 41 7
SERVER09 Clustered 42 2
SERVER09 Clustered 44 2
SERVER09 Clustered 45 1
SERVER09 Clustered 46 1
SERVER09 Clustered 54 2
SERVER09 Clustered 66 1
SERVER09 Heap 0 912
SERVER09 Heap 1 284
SERVER09 Heap 2 14
SERVER09 Heap 3 4
SERVER03 Clustered 0 862
SERVER03 Clustered 1 264
SERVER03 Clustered 2 63
SERVER03 Clustered 3 25
SERVER03 Clustered 4 4
SERVER03 Clustered 5 5
SERVER03 Clustered 6 2
SERVER03 Clustered 8 2
SERVER03 Clustered 11 4
SERVER03 Clustered 12 2
SERVER03 Clustered 28 2
SERVER03 Heap 0 427
SERVER03 Heap 1 32
SERVER03 Heap 2 22
SERVER03 Heap 3 8
SERVER03 Heap 4 6
SERVER03 Heap 5 1
SERVER08 Clustered 0 877
SERVER08 Clustered 1 263
SERVER08 Clustered 2 115
SERVER08 Clustered 3 32
SERVER08 Clustered 4 13
SERVER08 Clustered 5 8
SERVER08 Clustered 6 3
SERVER08 Clustered 7 4
SERVER08 Clustered 8 2
SERVER08 Clustered 10 3
SERVER08 Clustered 11 1
SERVER08 Clustered 12 2
SERVER08 Clustered 14 1
SERVER08 Clustered 20 1
SERVER08 Clustered 21 1
SERVER08 Clustered 29 1
SERVER08 Clustered 30 1
SERVER08 Heap 0 971
SERVER08 Heap 1 2512
SERVER08 Heap 2 383
SERVER08 Heap 3 95
SERVER08 Heap 4 31
SERVER08 Heap 5 26
SERVER08 Heap 6 6
SERVER08 Heap 7 4
SERVER08 Heap 8 4
SERVER08 Heap 9 4
SERVER08 Heap 10 2
SERVER08 Heap 12 1
SERVER08 Heap 14 2
SERVER08 Heap 15 2
SERVER07 Clustered 0 7804
SERVER07 Clustered 1 2650
SERVER07 Clustered 2 293
SERVER07 Clustered 3 91
SERVER07 Clustered 4 53
SERVER07 Clustered 5 40
SERVER07 Clustered 6 30
SERVER07 Clustered 7 36
SERVER07 Clustered 9 58
SERVER07 Heap 0 623
SERVER07 Heap 1 440
SERVER07 Heap 2 3
SERVER05 Clustered 0 12591
SERVER05 Clustered 1 135
SERVER05 Clustered 2 244
SERVER05 Clustered 3 376
SERVER05 Clustered 4 7
SERVER05 Clustered 5 7
SERVER05 Clustered 7 321
SERVER05 Clustered 8 2
SERVER05 Heap 0 4091
SERVER05 Heap 1 17
SERVER05 Heap 2 9
SERVER11 Clustered 0 411
SERVER11 Clustered 1 29
SERVER11 Clustered 2 25
SERVER11 Heap 0 1939
SERVER11 Heap 1 86
SERVER11 Heap 2 320
SERVER11 Heap 3 203
SERVER12 Clustered 0 485
SERVER12 Clustered 1 79
SERVER12 Clustered 2 84
SERVER12 Clustered 3 36
SERVER12 Clustered 4 9
SERVER12 Clustered 5 3
SERVER12 Clustered 6 4
SERVER12 Clustered 7 3
SERVER12 Clustered 8 1
SERVER12 Clustered 9 3
SERVER12 Heap 0 2149
SERVER12 Heap 1 207
SERVER12 Heap 2 337
SERVER12 Heap 3 210
SERVER12 Heap 4 8

Reply
Nathan says:

August 6, 2011 at 6:41 am

Clustered 0 638
Clustered 1 1650
Clustered 2 996
Clustered 3 251
Clustered 4 114
Clustered 5 126
Clustered 6 19
Clustered 7 1
Clustered 8 9
Clustered 16 5
Clustered 17 1
Heap 0 860
Heap 1 373
Heap 2 146
Heap 3 54
Heap 4 131
Heap 5 98
Heap 6 2
Heap 16 7

Reply
tobi says:

August 6, 2011 at 2:21 pm

What a great data-set. Maybe you should ask all people to give you an anonymized copy of all of their schemas (tables and cols numbered). _that_ would be a nice data-set.

Reply
Robert Miller says:

August 7, 2011 at 1:48 am

BaseType NCIndexes TableCount
Clustered 0 166
Clustered 1 101
Clustered 2 25
Clustered 3 14
Clustered 4 10
Clustered 5 3
Clustered 6 3
Clustered 7 2
Clustered 10 2
Clustered 11 2
Clustered 12 1
Heap 0 28
Heap 1 6
Heap 4 1

The Heap tables are either temporary historical tables for data changes or tables which are read and cached by an application.

Reply
Pete Danes says:

August 7, 2011 at 3:41 am

Something I’m currently developing. The one with 0 clustered indexes is a control db, that has code which manipulates the other two. The one with 1 clustered index is the production version, the one with 2 is a newer version, which I’m currrently tuning for deployment.

Clustered 0 42
Clustered 1 22
Clustered 2 1
Heap 0 2
Heap 3 2
Heap 9 1
Heap 10 1

Reply
Ken Tong says:

August 7, 2011 at 8:49 pm

Clustered 0 165
Clustered 1 134
Clustered 2 17
Clustered 3 4
Clustered 4 2
Clustered 11 1
Heap 0 27

Reply
Andre du Plessis says:

August 7, 2011 at 11:31 pm

The First Server:

Clustered 0 2876
Clustered 1 771
Clustered 2 388
Clustered 3 297
Clustered 4 73
Clustered 5 69
Clustered 6 8
Clustered 7 27
Clustered 8 4
Clustered 9 2
Clustered 10 3
Clustered 11 2
Clustered 12 1
Clustered 18 1
Heap 0 1243
Heap 1 438
Heap 2 219
Heap 3 141
Heap 4 123
Heap 5 74
Heap 6 34
Heap 7 14
Heap 8 15
Heap 9 7
Heap 10 1
Heap 11 2
Heap 12 2
Heap 14 2
Heap 15 1

The Second Server :

Clustered 0 90
Clustered 1 38
Clustered 2 24
Clustered 3 15
Clustered 4 13
Clustered 5 1
Clustered 6 1
Clustered 7 1
Heap 0 21
Heap 1 49
Heap 2 25
Heap 3 18
Heap 4 31
Heap 5 20
Heap 6 12
Heap 7 3
Heap 8 2
Heap 9 1

Reply
Theo Ekelmans says:

August 8, 2011 at 12:12 am

A few of my servers (we host web solutions) nearly all external apps

The first one is my worst nightmare!

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 1054
Clustered 1 220
Clustered 2 112
Clustered 3 65
Clustered 4 96
Clustered 5 153
Clustered 6 28
Clustered 7 14
Clustered 8 2
Clustered 9 2
Clustered 10 6
Clustered 11 2
Clustered 12 6
Clustered 13 6
Clustered 14 2
Clustered 16 2
Clustered 19 4
Clustered 39 2
Heap 0 65
Heap 1 9
Heap 2 1

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 1041
Clustered 1 274
Clustered 2 79
Clustered 3 26
Clustered 4 10
Clustered 5 13
Clustered 6 9
Clustered 7 5
Clustered 8 2
Clustered 9 1
Clustered 10 2
Clustered 11 2
Clustered 15 1
Clustered 16 2
Clustered 18 1
Clustered 19 4
Clustered 28 2
Heap 0 476
Heap 1 195
Heap 2 30
Heap 3 13
Heap 4 1

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 200
Clustered 1 60
Clustered 2 28
Clustered 3 9
Clustered 4 6
Clustered 5 4
Clustered 6 2
Clustered 8 3
Clustered 10 1
Clustered 11 1
Clustered 12 1
Clustered 14 2
Clustered 16 2
Heap 0 31
Heap 1 19
Heap 2 2
Heap 3 1
Heap 8 1

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 672
Clustered 1 541
Clustered 2 1343
Clustered 3 188
Clustered 4 96
Clustered 5 30
Clustered 6 30
Clustered 9 2
Clustered 10 1
Heap 0 195
Heap 1 15
Heap 2 3
Heap 4 1

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 76
Clustered 1 9
Clustered 2 4
Clustered 4 1
Clustered 11 1
Heap 0 54
Heap 1 2
Heap 4 1

Reply
Ludo Bernaerts says:

August 8, 2011 at 1:33 am

From our SQL instance with 173 prod databases

BaseType NCIndexes TableCount
Clustered 0 2633
Clustered 1 520
Clustered 2 204
Clustered 3 65
Clustered 4 45
Clustered 5 19
Clustered 6 6
Clustered 7 10
Clustered 8 3
Clustered 9 7
Clustered 10 1
Clustered 11 3
Clustered 14 1
Clustered 17 2
Clustered 26 1
Heap 0 1125
Heap 1 490
Heap 2 113
Heap 3 40
Heap 4 28
Heap 5 11
Heap 6 2
Heap 7 1

Reply
Remi says:

August 8, 2011 at 5:58 am

So cool, I don’t have any heaps (1 doesn’t really count :-p). This is just ONE erp DB. Starts with MS D and ends with ynamics Nav.

BaseType NCIndexes TableCount
Clustered 0 4834
Clustered 1 1971
Clustered 2 584
Clustered 3 249
Clustered 4 170
Clustered 5 135
Clustered 6 90
Clustered 7 55
Clustered 8 51
Clustered 9 69
Clustered 10 17
Clustered 11 28
Clustered 12 13
Clustered 13 28
Clustered 14 7
Clustered 15 7
Clustered 17 15
Clustered 19 1
Clustered 20 7
Clustered 24 7
Heap 0 1

Reply
Prakash says:

August 8, 2011 at 7:07 am

For one of my production high transaction databases

BaseType NCIndexes TableCount
Clustered 0 196
Clustered 1 32
Clustered 2 11
Clustered 3 10
Clustered 4 2
Clustered 5 3
Clustered 6 2
Clustered 7 3
Clustered 8 1
Clustered 10 1
Clustered 12 1
Heap 0 25
Heap 1 20
Heap 2 3
Heap 12 1

Reply
SuperCoolMoss says:

August 8, 2011 at 7:29 am

I think the script needs to exclude hyperthetical indexes from DTA! (I almost fainted when I saw 34 non-clustered indexes on 1 table :))

Reply
Ralph Bley says:

August 8, 2011 at 8:07 am

Non-SAP production servers:

Clustered 0 1022
Clustered 1 596
Clustered 2 288
Clustered 3 65
Clustered 4 25
Clustered 5 23
Clustered 6 25
Clustered 7 12
Clustered 9 1
Clustered 14 1
Heap 0 273
Heap 1 157
Heap 2 107
Heap 3 8
Heap 4 1
Heap 10 1
Heap 11 1

Reply
SuperCoolMoss says:

August 8, 2011 at 8:15 am

Have now dropped the hypotheticals (thanks, your script was useful for highlighting them – I understand they can cause performance issues if left).

BaseType NCIndexes TableCount
Clustered 0 186
Clustered 1 40
Clustered 2 11
Clustered 3 8
Clustered 4 1
Clustered 5 4
Clustered 6 4
Clustered 8 2
Clustered 10 1
Heap 0 466
Heap 1 17
Heap 2 6

Reply
Andy Korfin says:

August 8, 2011 at 8:43 am

Here are my results:

BaseType NCIndexes TableCount

Clustered 0 126
Clustered 1 10
Clustered 2 1
Clustered 3 4
Clustered 4 1
Clustered 7 1
Heap 0 102
Heap 1 26
Heap 2 14
Heap 3 2
Heap 7 2

Not sure how should I interpret them ?

Reply
Mike says:

August 8, 2011 at 8:54 am

BaseType NCIndexes TableCount
Clustered 0 118
Clustered 1 79
Clustered 2 4
Clustered 3 2
Clustered 4 2
Heap 0 30
Heap 1 55
Heap 2 7
Heap 3 6

Reply
Nury Johnson says:

August 8, 2011 at 9:16 am

From one of the production servers

Clustered 0 2171
Clustered 1 1037
Clustered 2 317
Clustered 3 118
Clustered 4 92
Clustered 5 118
Clustered 6 53
Clustered 7 28
Clustered 8 15
Clustered 9 16
Clustered 11 1
Clustered 14 1
Clustered 15 1
Clustered 18 1
Clustered 26 1
Heap 0 738
Heap 1 2128
Heap 2 1615
Heap 3 810
Heap 4 476
Heap 5 196
Heap 6 134
Heap 7 114
Heap 8 97
Heap 9 22
Heap 11 2
Heap 12 7
Heap 13 14
Heap 14 7

Reply
Patrick Cahill says:

August 8, 2011 at 9:24 am

BaseType NCIndexes TableCount
Clustered 0 484
Clustered 1 192
Clustered 2 49
Clustered 3 21
Clustered 4 9
Clustered 5 1
Clustered 7 1
Clustered 9 2
Clustered 13 1
Clustered 14 1
Heap 0 3
Heap 1 65
Heap 2 5

Reply
Tracy Sells says:

August 8, 2011 at 5:39 am

From a development server with all sorts of test databases on it.

Clustered 0 281
Clustered 1 3061
Clustered 2 1957
Clustered 3 1126
Clustered 4 370
Clustered 5 187
Clustered 6 32
Clustered 7 129
Clustered 8 37
Clustered 9 9
Clustered 10 23
Clustered 11 14
Clustered 12 51
Clustered 13 32
Clustered 14 23
Clustered 15 23
Clustered 16 5
Clustered 24 23
Heap 1 100
Heap 2 40
Heap 3 65
Heap 4 91
Heap 5 25
Heap 6 23
Heap 7 14
Heap 11 9

Reply
JP Negri says:

August 8, 2011 at 1:46 pm

BaseType NCIndexes TableCount
Clustered 0 1607
Clustered 1 306
Clustered 2 59
Clustered 3 13
Clustered 4 9
Clustered 5 8
Clustered 6 3
Clustered 7 1
Clustered 9 1
Clustered 10 2
Clustered 15 1
Heap 0 55
Heap 1 26
Heap 2 8
Heap 3 1

Reply
Verone Lazio says:

August 8, 2011 at 2:18 pm

BaseType NCIndexes TableCount
Clustered 0 950
Clustered 1 312
Clustered 2 117
Clustered 3 67
Clustered 4 23
Clustered 5 17
Clustered 6 7
Clustered 7 4
Clustered 8 3
Clustered 9 2
Clustered 11 5
Clustered 12 2
Clustered 13 1
Clustered 17 1
Clustered 18 1
Clustered 19 2
Clustered 20 1
Clustered 29 1
Clustered 41 1
Heap 0 414
Heap 1 45
Heap 2 15
Heap 3 15
Heap 4 4
Heap 5 1
Heap 6 1
Heap 7 1
Heap 8 1
Heap 13 1

Reply
Muthukkumaran Kaliyamoorthy says:

August 9, 2011 at 3:59 am

Hi Paul,
Here is mine from one of our server.

Clustered 0 110
Clustered 1 31
Clustered 2 15
Clustered 3 1
Clustered 4 3
Clustered 5 1
Clustered 6 3
Clustered 16 7
Heap 0 252
Heap 1 13
Heap 2 6
Heap 15 2
Heap 17 1
Heap 18 2

Reply
Henrik Staun Poulsen says:

August 9, 2011 at 4:11 am

here is our results:
BaseType NCIndexes TableCount
Clustered 0 127
Clustered 1 300
Clustered 2 412
Clustered 3 210
Clustered 4 13
Clustered 5 4
Clustered 6 4
Clustered 7 1
Clustered 8 1
Clustered 12 1
Clustered 17 1
Heap 0 485
Heap 1 46
Heap 3 1

Reply
Tommy Högman says:

August 9, 2011 at 5:51 am

BaseType NCIndexes TableCount
Clustered 0 238
Clustered 1 43
Clustered 2 10
Clustered 3 1
Clustered 5 1
Heap 0 9
Heap 1 1

Reply
Jay says:

August 9, 2011 at 12:26 pm

Hi Paul This is from one of the server

BaseType NCIndexes TableCount
Clustered 0 173
Clustered 1 70
Clustered 2 32
Clustered 3 15
Clustered 4 2
Clustered 5 5
Clustered 6 2
Clustered 7 2
Clustered 9 2
Clustered 11 2
Clustered 13 1
Clustered 21 1
Clustered 35 1
Clustered 73 1
Heap 0 35
Heap 1 25
Heap 2 4
Heap 4 1

Reply
Tristan Andrews says:

August 9, 2011 at 5:48 pm

Production Database

Clustered 0 50
Clustered 1 215
Clustered 2 128
Clustered 3 83
Clustered 4 41
Clustered 5 28
Clustered 6 13
Clustered 7 6
Clustered 8 8
Clustered 9 6
Clustered 10 1
Clustered 11 1
Clustered 12 1
Clustered 13 1
Clustered 15 2
Clustered 16 1
Clustered 17 1
Clustered 19 2
Clustered 25 1
Clustered 54 1

Reply
David says:

August 9, 2011 at 9:01 pm

Clustered 0 107
Clustered 1 19
Clustered 2 5
Clustered 3 1
Clustered 4 2
Clustered 5 1
Clustered 121 1
Heap 0 20
Heap 1 29
Heap 2 13

Reply
Nebojsa Ilic says:

August 10, 2011 at 5:42 am

Server01 Clustered 0 270
Server01 Clustered 1 42
Server01 Clustered 2 7
Server01 Clustered 3 6
Server01 Clustered 4 1
Server01 Clustered 5 1
Server01 Clustered 6 1
Server01 Heap 0 197
Server01 Heap 1 137
Server01 Heap 2 7

Server02 Clustered 0 969
Server02 Clustered 1 92
Server02 Clustered 2 29
Server02 Clustered 3 27
Server02 Heap 0 1193
Server02 Heap 1 255
Server02 Heap 2 73
Server02 Heap 3 43
Server02 Heap 4 28
Server02 Heap 5 13
Server02 Heap 6 7
Server02 Heap 7 7

Server03 Clustered 0 136
Server03 Clustered 1 12
Server03 Clustered 2 7
Server03 Clustered 4 3
Server03 Heap 0 114
Server03 Heap 1 8
Server03 Heap 2 1

Note: Databases on Server02 are mostly outsource

Reply
Juergen says:

August 10, 2011 at 6:18 am

Results for 2 SQL2K8-Servers:

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 194
Clustered 1 44
Clustered 2 13
Clustered 3 5
Clustered 4 5
Clustered 5 4
Heap 0 846
Heap 1 66
Heap 2 5
Heap 3 1
Heap 4 7
Heap 5 1
Heap 6 1

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 170
Clustered 1 63
Clustered 2 23
Clustered 3 16
Clustered 4 6
Clustered 5 4
Clustered 6 1
Clustered 11 1
Clustered 14 1
Clustered 17 1
Heap 0 390
Heap 1 58
Heap 2 10
Heap 3 2
Heap 5 1

Reply
JohnV says:

August 10, 2011 at 9:23 am

BaseType NCIndexes TableCount
Clustered 0 449
Clustered 1 136
Clustered 2 71
Clustered 3 55
Clustered 4 32
Clustered 5 22
Clustered 6 17
Clustered 7 6
Clustered 8 2
Clustered 9 8
Clustered 10 5
Clustered 11 4
Heap 0 583
Heap 1 54
Heap 2 8
Heap 3 2

Reply
Amanda says:

August 10, 2011 at 3:39 pm

well – this is from my nightmare server. Usual disclaimers.
Now, if there was a contest for index size, I would probably win. Or – almost as good – table with the most indexes!
BaseType NCIndexes TableCount
Clustered 0 695
Clustered 1 215
Clustered 2 68
Clustered 3 24
Clustered 4 11
Clustered 5 8
Clustered 6 4
Clustered 8 1
Clustered 9 2
Clustered 10 2
Clustered 12 2
Clustered 29 1
Heap 0 973
Heap 1 84
Heap 2 21
Heap 3 3
Heap 7 1

Reply
Toby says:

August 11, 2011 at 11:29 pm

BaseType NCIndexes TableCount
Clustered 0 710
Clustered 1 608
Clustered 2 103
Clustered 3 44
Clustered 4 22
Clustered 5 22
Clustered 6 7
Clustered 7 7
Clustered 8 7
Clustered 9 1
Clustered 11 2
Clustered 24 2
Heap 0 364
Heap 1 65
Heap 2 71

Reply
Jay says:

August 12, 2011 at 10:24 am

This is from another prod server
BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 655
Clustered 1 332
Clustered 2 117
Clustered 3 48
Clustered 4 24
Clustered 5 12
Clustered 6 2
Clustered 7 3
Clustered 8 2
Clustered 9 5
Clustered 10 1
Clustered 12 1
Clustered 14 1
Clustered 16 1
Clustered 26 1
Clustered 35 1
Heap 0 26
Heap 1 3
Heap 2 2
Heap 3 27
Heap 4 67
Heap 5 80
Heap 6 23
Heap 7 7
Heap 8 3
Heap 9 1
Heap 10 2
Heap 14 3

Reply
DaveClark says:

August 13, 2011 at 7:42 pm

BaseType NCIndexes TableCount
Clustered 0 1554
Clustered 1 1010
Clustered 2 163
Clustered 3 43
Clustered 4 11
Clustered 5 12
Clustered 6 3
Clustered 7 2
Clustered 8 6
Clustered 9 2
Clustered 11 2
Clustered 12 2
Clustered 13 1
Clustered 14 2
Clustered 22 2
Clustered 23 2
Clustered 26 2
Clustered 28 2
Heap 0 516
Heap 1 90
Heap 2 46
Heap 3 6
Heap 4 2
Heap 5 2
Heap 6 2
Heap 8 2
Heap 10 2

Reply
David Skelton says:

August 15, 2011 at 8:10 am

Clustered 0 135
Clustered 1 152
Clustered 2 136
Clustered 3 76
Clustered 4 21
Clustered 5 4
Clustered 6 7
Clustered 7 2
Clustered 9 1
Clustered 13 1

Reply
Jan says:

August 16, 2011 at 12:48 am

Many of the same type of database for different customers on this server.

BaseType NCIndexes TableCount
Clustered 0 26783
Clustered 1 14560
Clustered 2 7130
Clustered 3 4663
Clustered 4 3036
Clustered 5 2116
Clustered 6 979
Clustered 7 753
Clustered 8 448
Clustered 9 265
Clustered 10 114
Clustered 11 8
Clustered 12 25
Clustered 13 63
Clustered 14 9
Clustered 15 102
Clustered 16 24
Clustered 17 66
Clustered 18 21
Clustered 19 64
Clustered 20 2
Clustered 21 59
Clustered 22 55
Heap 0 3157
Heap 1 1744
Heap 2 243
Heap 3 93
Heap 4 36
Heap 5 75
Heap 6 40
Heap 7 14
Heap 8 7
Heap 9 1
Heap 10 3

Reply
dpackrat says:

August 16, 2011 at 5:42 am

BaseType NCIndexes TableCount
———- ——— ———–
Clustered 0 885
Clustered 1 57
Clustered 2 9
Clustered 3 77
Clustered 4 2
Clustered 5 1
Clustered 6 1
Heap 0 1107
Heap 1 9
Heap 3 2
Heap 24 1

Reply
wh says:

August 19, 2011 at 4:39 am

here is the result from sap erp 6.0 ehp4
BaseType NCIndexes TableCount
Clustered 0 66469
Clustered 1 5733
Clustered 2 2197
Clustered 3 629
Clustered 4 262
Clustered 5 140
Clustered 6 76
Clustered 7 37
Clustered 8 19
Clustered 9 12
Clustered 10 4
Clustered 11 4
Clustered 16 1
Clustered 17 1
Heap 0 1
Heap 1 1
Heap 5 1

Reply
Andrew Haugen says:

August 25, 2011 at 9:27 am

BaseType NCIndexes ——– Clustered 0 Clustered 1 Clustered 2 Clustered 3 Clustered 4 Clustered 5 Clustered 6 Clustered 7 Clustered 8 Clustered 9 Clustered 10 Clustered 11 Clustered 12 Clustered 13 Clustered 14 Clustered 15 Clustered 16 Clustered 17 Clustered 18 Clustered 19 Clustered 20 Clustered 21 Clustered 23 Clustered 24 Clustered 25 Clustered 30 Clustered 34 Clustered 36 Clustered 37 Clustered 38 Clustered 57 Clustered 58 Heap 0 Heap 3 Clustered 0 Clustered 1 Clustered 2 Clustered 3 Clustered 4 Clustered 5 Clustered 6 Clustered 7 Clustered 8 Clustered 9 Clustered 10 Clustered 11 Clustered 12 Clustered 13 Clustered 14 Clustered 15 Clustered 16 Clustered 17 Clustered 18 Clustered 19 Clustered 20 Clustered 21 Clustered 23 Clustered 24 Clustered 25 Clustered 26 Clustered 27 Clustered 29 Clustered 30 Clustered 33 Clustered 34 Clustered 35 Clustered 38 Clustered 39 Clustered 57 Clustered 58 Heap 0 Heap 1 Heap 2 Heap 3 Heap 4 Heap 5 Heap 6 Heap 7 Heap 8 Heap 9 Heap 10 Heap 11 Heap 12 Heap 13 Heap 14 Heap 15 Heap 16 Heap 18 Heap 19 Heap 20 Heap 56 Clustered 0 Clustered 1 Clustered 2 Clustered 3 Clustered 4 Clustered 5 Clustered 6 Clustered 7 Clustered 8 Clustered 9 Clustered 10 Clustered 11 Clustered 12 Clustered 13 Clustered 14 Clustered 15 Clustered 16 Clustered 17 Clustered 18 Clustered 19 Clustered 20 Clustered 21 Clustered 24 Clustered 25 Clustered 27 Clustered 30 Clustered 34 Clustered 35 Clustered 37 Clustered 38 Clustered 58 Heap 0 Heap 3 Clustered 0 Clustered 1 Clustered 2 Clustered 3 Clustered 6 Clustered 7 Clustered 23 Heap 0 Heap 1 Heap 2 Heap 3 TableCount
——— ———-
176
14
799
1981
199
118
72
46
26
12
14
17
9
5
7
9
7
7
5
7
3
4
1
1
5
2
6
1
1
2
1
1
228
2
1578
377
2243
5103
713
420
219
140
70
35
48
45
34
22
26
19
29
19
14
27
9
9
4
2
5
2
2
1
6
3
13
3
5
2
3
2
1042
1232
1105
512
255
129
54
30
39
27
15
21
6
15
9
3
6
3
6
3
3
109
10
399
993
100
59
36
24
12
5
9
8
5
2
3
3
5
3
4
3
1
2
2
1
1
1
2
1
1
1
1
119
1
464
115
15
4
1
1
1
37
9
6
1

Reply
Anthony says:

August 30, 2011 at 12:18 pm

BaseType NCIndexes TableCount
Clustered 0 70
Clustered 1 13
Clustered 2 3
Clustered 3 3
Heap 0 424
Heap 1 947
Heap 2 1574
Heap 3 66
Heap 4 50
Heap 5 18
Heap 6 5
Heap 7 5
Heap 8 6
Heap 10 4
Heap 11 4
Heap 16 2
Heap 17 1
Heap 21 2
Heap 22 1
Heap 23 1

Reply

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.

Survey: nonclustered index counts (code to run)

81 thoughts on “Survey: nonclustered index counts (code to run)”

Leave a Reply Cancel reply

Other articles

Black Friday Super Sale!

Summer School Savings SALE is live!

The Curious Case of… missing log information

The Curious Case of… missing 832 errors during memory corruption

The Curious Case of… recovering from a ransomware attack

30%-off health checks and self-service via credit card – no waiting!

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