Running Paul’s nonclustered index count survey against multiple instances

Paul put up a new survey today titled Survey: nonclustered index counts (code to run).  If you’d like to run the query for this survey against multiple SQL Servers at once using PowerShell here is the script to do it:

# Load the Snapins for SqlServer if they aren't currently loaded 
if((Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null)  
{    Add-PSSnapin SqlServerCmdletSnapin100    }

if((Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)  
{      Add-PSSnapin SqlServerProviderSnapin100    }


$Query = "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"

# To use a text file list of servers, place each server name on a separate line in the file
# then uncomment the following line to read the list from the text file and comment out the
# listed declaration for the servers

#$servers = get-content "serverlist.txt"

# To specify a list of servers, place each server name in double quotes separated by a comma
# multiple lines are allowed.
$ServerList = "AlwaysOnLab1", "AlwaysOnLab2", "AlwaysOnLab3"

foreach ($CurrentServer in $ServerList) 
{ 
    
    Invoke-Sqlcmd -Server $CurrentServer -Database "master" -Query $Query 
}

There are two different ways to feed the server list into the above script, either as a file or as an array of comma separated server names.

Enjoy and don’t forget to post your results back on Paul’s original post as a comment.

One thought on “Running Paul’s nonclustered index count survey against multiple instances

  1. Hey Jonathan!

    What about enumerating all the instances in the network using [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()?
    And there’s a typo in variable name $servers (should be $ServerList) in commented line with get-content.

    I will use definitely once I’m back from my holiday and report the results.

    Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Bitten by SSD Bit Rot

Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number

Explore

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.