Collect SQL Server Index Usage for All Instances with PowerShell

By: Alejandro Cobar | Updated: 2019-06-10 | Comments (5) | Related: > Indexing

Problem

As we all probably know by now, SQL Server indexes can be vital structures that come to our aid when we are experiencing poor performing queries. Of course, in the scenario that the root cause for such poor performance is due to the lack of an index structure that can be used by the query optimizer to properly get the job done. This means that you have already done the respective troubleshooting and concluded that an index structure is the way to go. But what if you inherited a bunch of servers and have little to no idea of the status of the existing index structures in the environment, and don’t know if the existing index structures are causing more damage rather than being useful?

Solution

In this tip I will present a PowerShell script that can help you gain visibility of the state of the current index structures, for a set of specified SQL Server instances. After collecting the information, you can analyze it and determine the course of action for those cases that should be addressed.

First, I will outline how the code works and then share the entire script.

#Section 1

The very first thing to address is the creation of the schema and the table that will contain the indexes information of all the SQL Server instances under your care. Ideally, this one should be hosted in a central environment under your control.

If you want, you can get rid of the schema (Monitoring). Just make sure to update the script so that it doesn’t reference it.

Every time the PowerShell script is executed, the central table is truncated so that it ends up with fresh values. If you need to keep every execution, simply remove the TRUNCATE statement from the script.

#Section 2

This section creates the stored procedure that returns the T-SQL that the PowerShell script uses against each SQL Server instance to fetch the status of the indexes.

It is done this way, so you can play with the code directly within your central SQL Server instance. Just remember to update this section of the PowerShell script if you want to make a modification to the query within the stored procedure.

#Section 3

In this section you prepare the list of instances that are under your support and from where the indexes information will be retrieved.

Feel free to change this mechanism with another one of your choosing. Perhaps you can have a .txt file with the list of instances and you feed the variable with that set.

#Section 4

This section simply executes the stored procedure created in Section 2 and saves it in the $indexStatusQuery variable.

#Section 5

For each instance that you have specified in section 3, the "for each" statement will attempt to go 1 by 1 and fetch the index information. After it has been collected, the respective "insert" statement is built and passed to the central instance to store such information.

Since a single insert statement can process up to 1000 rows within one execution, the respective logic is built in the script to perform an insert every 1000 rows built.

Sample Output

Here is sample output from the query. This will collect data from all instances specified in the input table.

Other Notes

You might want to modify the Invoke-Sqlcmd command to include your own specific credentials to establish the proper connection.
Replace "xxxx" with the values that fit your use case.
I use the schema "Monitoring" for the sake of maintaining a certain order/structure, you can either get rid of it or simply use another schema name that you like (just make sure that such schema exists first, and you should be good to go).
The PowerShell script automatically creates the stored procedure that returns the index status query to be used against all the SQL Server instances. If you need to perform any modification, remember to update the section of the PowerShell script that contains the stored procedure code (if not, your modifications will be overwritten).
This script doesn’t take partitions into account, at least not within its current version.
The script uses a tabled called instances and there are two columns: name and instance. The name column takes the format of the network name and port number for the instance and the instance is the actual SQL Server instance name. This table is used to determine which servers to collect the index information.

Complete PowerShell Script to Collect Index Information from Multiple SQL Servers

This was tested against SQL Server instances with versions 2005, 2008, 2008 R2, 2012, 2014, 2016 and 2017.

$server = "xxxx"
$inventoryDB = "xxxx"
 
#Section 1 START
#Create the Monitoring schema if it doesn't exist
$schemaCreationQuery = "
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'Monitoring') 
EXEC('CREATE SCHEMA Monitoring AUTHORIZATION dbo')
"
Invoke-Sqlcmd -Query $schemaCreationQuery -Database $inventoryDB -ServerInstance $server
   
#Create the central table where you will store the information gathered from all the instances
$indexStatusTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'IndexStatus' AND xtype = 'U')
CREATE TABLE [Monitoring].[IndexStatus](
   [instance] [varchar](255) NULL,
   [database] [varchar](255) NULL,
   [schema] [varchar](255) NULL,
   [table] [varchar](255) NULL,
   [index] [varchar](255) NULL,
   [type] [varchar](255) NULL,
   [allocation_unit_type] [varchar](255) NULL,
   [fragmentation] [decimal](10, 2) NULL,
   [pages] [int] NULL,
   [writes] [int] NULL,
   [reads] [int] NULL,
   [disabled] [tinyint] NULL,
   [stats_timestamp] [datetime] NULL
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $indexStatusTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
#Clean the Monitoring.IndexStatus table
Invoke-Sqlcmd -Query "TRUNCATE TABLE Monitoring.IndexStatus" -Database $inventoryDB -ServerInstance $server
#Section 1 END
 
#Section 2 START
$spCreationQuery = "
USE ["
 
$spCreationQuery += $inventoryDB
 
$spCreationQuery += "]
GO

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'indexes_status'
          AND SCHEMA_NAME(schema_id) = 'Monitoring'
          AND type = 'P'
       )      
DROP PROC Monitoring.indexes_status
GO
 
-- =============================================
-- Author:      Alejandro Cobar
-- Create date: 2/20/2019
-- Description: Grabs index information status for all databases in an instance
-- =============================================
CREATE PROCEDURE [Monitoring].[indexes_status] 
AS
BEGIN
   SET NOCOUNT ON;
 
   DECLARE @query VARCHAR(MAX);
 
   SET @query = '
   DECLARE @command VARCHAR(MAX)
 
   DECLARE @SDBA_IndexFragmentation TABLE(
      database_name VARCHAR(255),
      Eschema VARCHAR(255),
      xTable VARCHAR(255),
      xIndex VARCHAR(255),
      xType VARCHAR(255),
      xAllocUnitType VARCHAR(255),
      avg_fragmentation_percent DECIMAL(10,2),
      page_count INT,
      writes INT,
      reads INT,
      disabled TINYINT,
      xStatsTimestamp DATETIME
   )
 
   SELECT @command = '+CHAR(39)+'Use [?]
   DECLARE @DB_ID INT;
   SET @DB_ID = DB_ID();
 
   SELECT 
   db_name(@DB_ID) db_Name, 
   s.name AS ESchema,
   t.name AS xTable,
   i.name AS xIndex,
   i.type_desc AS xType,
   ips.alloc_unit_type_desc AS xAllocUnitType,
   CONVERT(DECIMAL(10,2),ips.avg_fragmentation_in_percent) AS fragmentation,
   ips.page_count,
   ISNULL(ius.user_updates,0) AS writes,
   ISNULL(ius.user_seeks + ius.user_scans + ius.user_lookups,0) AS reads,
   i.is_disabled AS disabled, 
   STATS_DATE(st.object_id, st.stats_id)
   FROM sys.indexes i 
   JOIN sys.tables t ON i.object_id = t.object_id 
   JOIN sys.schemas s ON s.schema_id = t.schema_id
   JOIN sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, NULL) ips ON ips.database_id = @DB_ID AND ips.object_id = t.object_id AND ips.index_id = i.index_id
   LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = @DB_ID AND ius.object_id = t.object_id AND ius.index_id = i.index_id
   JOIN sys.stats st ON st.object_id = t.object_id AND st.name = i.name
   WHERE i.index_id > 0'+CHAR(39)+'
  
   INSERT INTO @SDBA_IndexFragmentation
   EXEC sp_MSForEachDB @command
 
   SELECT SERVERPROPERTY(''SERVERNAME'') AS [instance], database_name AS [database], Eschema AS [schema], xTable AS [table], xIndex AS [index], xType AS [type], xAllocUnitType AS [allocation unit type],avg_fragmentation_percent AS [fragmentation], page_count AS [pages], writes, reads , disabled, xStatsTimestamp AS [stats timestamp]
   FROM @SDBA_IndexFragmentation 
   WHERE database_name NOT IN (''msdb'',''master'',''model'',''tempdb'');
   '
 
   SELECT @query AS tsql;
END   
"
Invoke-Sqlcmd -Query $spCreationQuery -Database $inventoryDB -ServerInstance $server
#Section 2 END
 
 
#Section 3 START
#Fetch all the instances from the list you specify
<#
   This is an example of the result set that your query must return
   ###################################################
   # name                     #  instance            #
   ###################################################
   # server1.domain.net,45000 #  server1             #
   # server1.domain.net,45001 #  server1\MSSQLSERVER1# 
   # server2.domain.net,45000 #  server2             #
   # server3.domain.net,45000 #  server3             #
   # server4.domain.net       #  server4\MSSQLSERVER2#
   ###################################################               
#>
 
#Put in your query that returns the list of instances as described in the example result set above
$instanceLookupQuery = "SELECT name, instance FROM instances"
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
#Section 3 END
 
#Section 4 START
$indexStatusQuery = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query "EXEC Monitoring.indexes_status" -MaxCharLength 8000
#Section 4 END
 
#Section 5 START
#For each instance, grab the index information for all the databases
foreach ($instance in $instances){
    Write-Host "Fetching indexes information for instance" $instance.instance
   
   #Go grab the indexes information for all the databases in the instance
   $results = Invoke-Sqlcmd -Query $indexStatusQuery.tsql -ServerInstance $instance.name -ErrorAction Stop
    
   #Perform the INSERT in the IndexStatus table only if it returned at least 1 row
    if($results.Length -ne 0){
        $counter = 0
 
      #Build the insert statement
      $insert = "INSERT INTO Monitoring.IndexStatus VALUES"
      foreach($result in $results){
            $counter++
 
            if($result['instance'].ToString().trim() -eq [String]::Empty){$instance = "''"} else{$instance = $result['instance'] }
            if($result['database'].ToString().trim() -eq [String]::Empty){$database = "''"} else{$database = $result['database'] }
            if($result['schema'].ToString().trim() -eq [String]::Empty){$schema = "''"} else{$schema = $result['schema']}
            if($result['table'].ToString().trim() -eq [String]::Empty){$table = "''"} else{$table = $result['table']}
            if($result['index'].ToString().trim() -eq [String]::Empty){$index = "''"} else{$index = $result['index'] }
            if($result['type'].ToString().trim() -eq [String]::Empty){$type = "''"} else{$type = $result['type']}
            if($result['allocation unit type'].ToString().trim() -eq [String]::Empty){$allocationUnitType = "''"} else{$allocationUnitType = $result['allocation unit type']}
            if($result['fragmentation'].ToString().trim() -eq [String]::Empty){$fragmentation = "''"} else{$fragmentation = $result['fragmentation']}
            if($result['pages'].ToString().trim() -eq [String]::Empty){$pages = "''"} else{$pages = $result['pages'] }
            if($result['writes'].ToString().trim() -eq [String]::Empty){$writes = "''"} else{$writes = $result['writes'] }
            if($result['reads'].ToString().trim() -eq [String]::Empty){$reads = "''"} else{$reads = $result['reads'] }
            if($result['disabled'].ToString().trim() -eq [String]::Empty){$disabled = "''"} else{$disabled = $result['disabled'] }
            if($result['stats timestamp'].ToString().trim() -eq [String]::Empty){$statsTimestamp = "''"} else{$statsTimestamp = $result['stats timestamp']}
                   
         $insert += "
         (
         '"+$instance+"',
         '"+$database+"',
         '"+$schema+"',
         '"+$table+"',
         '"+$index+"',
         '"+$type+"',
         '"+$allocationUnitType+"',
         "+$fragmentation+",
         "+$pages+",
         "+$writes+",
         "+$reads+",
         "+$disabled+",
            '"+$statsTimestamp+"'
         ),
         "
            $insert = $insert -replace "''",'NULL'
            $insert = $insert -replace "NULLNULL",'NULL'
 
            if($counter -eq 1000){
                Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
                $counter = 0
                $insert = "INSERT INTO Monitoring.IndexStatus VALUES" 
            }
 
      } 
 
      #Store the results in the local Monitoring.IndexStatus table in our Lab Server instance
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
   }   
}
#Section 5 END
 
Write-Host "Done!"

After executing the script, the IndexStatus table should contain values that look like this (this is just a short sample):

From the output you can determine the following:

Instance "TEST1"
- Index "IX_t1_userId" could be a potential candidate for an index rebuild operation. However, if you notice the "reads" column it states 0, which means that the index hasn’t been used, so it could be a potential candidate for being dropped.

Instance "TEST2"
- Index "IX_t2_productId" is very fragmented and could be a potential candidate of an index reorganize operation (due to the small number of pages that conform it).

These are just two examples of the many cases you might find after fetching the information from all of your SQL Server instances.

Next Steps

The output of the PowerShell script has basic information that I considered important first hand. However, you can add as much information you want to fit your particular use case.
You can automate this solution (weekly executions sound good) and build a reporting/alerting mechanism around it, so it can warn you about any potential cases that can be treated as a "red flag".
With this you gain the visibility of the state of the indexes across all your SQL Server instances, but that is just the first chapter of the whole story. The very next step is actually being able to address all those cases that require it, in the fashion of:
- Shall I get rid of the indexes that are not being used that much?
- Shall I schedule an index maintenance task on certain SQL Server instances?
- Does a particular table have way too many indexes?
- The statistics of certain indexes haven’t been updated in a very long time, or never?
Here are some useful resources written by members of the MSSQLTips community, that can help you with the maintenance part:

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-06-10

Collect SQL Server Index Usage Information for all Instances with PowerShell