Monitor all Indexes in SQL Server with PowerShell

Problem

Having a full overview of clustered and non-clustered indexes across all SQL Server instances is very important as it can give you a quick entry point when you are experiencing performance issues or just want to look for index issues from one central location.

Solution

This module will gather information about indexes from all specified SQL Server instances.  It will connect with each server in inventory.MasterServerList and capture the data for each of these instances.  The connection to each SQL Server instance will be based on the settings in the inventory.MasterServerList table.

By having this information, at a glance we can be constantly monitor the current status of indexes deployed in all databases across all SQL Server instances under our support. This can be a starting point in a decision-making process around performance caused by indexes or general proactive maintenance (i.e. addressing unused indexes in our environment).

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

For this particular module, only one table will be created (in case it doesn’t exist), and it is the table to store index information from each monitored instance.

If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle the additional columns.

• inventory.Indexes
  • serverId – serverid ties back to inventory.MasterServerList
  • database – the name of the database in the instance
  • schema – the name of the schema housing the database in the instance
  • table – the name of the table that has the index structures
  • index – the name of the index
  • type – the type of index (i.e. Clustered, Non-Clustered)
  • allocation_unit_type – (i.e. IN_ROW_DATA, ROW_OVERFLOW_DATA, LOB_DATA)
  • fragmentation – percentage of fragmentation for the particular index
  • pages – the number of pages (8KB) that conform the index
  • writes – the number of writes that have taken place for the index
  • reads – the number of reads that have taken place for the index
  • disabled – if the index is currently disabled or not
  • stats_timestamp – the last time the statistics were updated

PowerShell Script

The PowerShell script that creates the above object and inserts data into the inventory.Indexes table is called:

• Get-MSSQL-Instance-Indexes.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want.  If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

• Right click on Get-MSSQL-Instance-Indexes.ps1 and select Run with PowerShell

Option 2

• Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-Indexes.ps1"

Option 3

• Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

• Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.Indexes table, we can see the data that has been collected.

Important note: the PowerShell script will store only the information from the very last execution. If you’d like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query: 

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Indexes'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
   CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Indexes'

Useful Queries

By collecting all the data related to the execution of jobs across all your instances, you might answer things like:

Which indexes are very fragmented?

SELECT *
FROM inventory.Indexes
WHERE fragmentation >= 85;

See all indexes listed by page count (from largest to smallest)

SELECT *
FROM inventory.Indexes
ORDER BY pages DESC;

Which indexes are currently disabled?

SELECT *
FROM inventory.Indexes
WHERE disabled = 1;

Which indexes are not being used that frequently?

By knowing this, you could have a sense of those index structures that are not contributing in any way to the performance of the queries, and could be good candidates to be deleted.

SELECT *
FROM inventory.Indexes
WHERE writes > reads AND type <> 'CLUSTERED';

Which indexes have never had their statistics updated or last time was some time ago?

Replace X with the value you consider appropriate.

SELECT *
FROM inventory.Indexes
WHERE statistics_timestamp IS NULL OR DATEDIFF(DAY, statistics_timestamp, GETDATE()) > X ;

Download Scripts

• Download the scripts for this module

Next Steps

• Download the PowerShell script to collect data for SQL Server Indexes.
• Check out the other parts of this series
  • Monitoring SQL Server with PowerShell Core Object Setup
  • Monitoring SQL Server with PowerShell Instance Data Collection
  • Monitoring SQL Server with PowerShell Instance Jobs Collection
  • Monitoring SQL Server with PowerShell Instance Jobs Last Execution
  • Monitoring SQL Server with PowerShell Instance Backups
  • Monitoring SQL Server with PowerShell Instance Database and Database Files

Alejandro Cobar

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

• MSSQLTips Awards: Author of the Year – 2020 | Rising Star (50+ tips) – 2021 | Author Contender – 2019, 2021