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)

  1. 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

  2. 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

  3. 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

  4. 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.

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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…

  15. 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.

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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?

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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…

  34. 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

  35. 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

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

  37. 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

  38. 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

  39. 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

  40. 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

  41. 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

  42. 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.

  43. 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.

  44. 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

  45. 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

  46. 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

  47. 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

  48. 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

  49. 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

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

  51. 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

  52. 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

  53. 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 ?

  54. 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

  55. 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

  56. 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

  57. 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

  58. 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

  59. 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

  60. 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

  61. 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

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

  63. 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

  64. 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

  65. 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

  66. 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

  67. 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

  68. 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

  69. 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

  70. 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

  71. 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

  72. 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

  73. 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

  74. 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

  75. 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

  76. 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

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

  78. 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

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.