Monitor all Indexes in SQL Server with PowerShell

By:   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring


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.

database objects

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.

query results

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

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar 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


Comments For This Article




Thursday, March 18, 2021 - 4:01:34 PM - Dennis Anderson Back To Top (88438)
Two more things to consider. I ran into an issue of duplicate entries for some indexes. They all had an allocation_unit_type of either 'LOB_DATA' or 'ROW_OVERFLOW_DATA' I filtered those out of the results to avoid the errors. I also found that sp_MSForEachDB doesn't exist in a couple of instances so you may want to switch to one of the alternatives, like maybe the one on Brent Ozar's site:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_ineachdb.sql

Wednesday, March 17, 2021 - 9:41:56 AM - Dennis Anderson Back To Top (88428)
I found a couple of things I had to change to get the index script to work for me. 1) in the settings.ini I had to make the server_name use the same format as for my instances (i.e. server_name, port). That fixed the serverId issue. 2) In the Execute-Query fucntion I changed the -ErrorAction in the catch portion to Continue. Otherwise the script would stop at the first error and not process the other indexes. 3) In the Perform insert in the inventory.Indexes section, I changed the if($i -eq 500){ down to 1. Otherwise if the ran into an error executing the query, none of the records in that insert statement would get into the table. This way only the duplicates in my case were reported as errors while all the rest of the indexes were inserted as expected

Monday, March 15, 2021 - 7:23:39 PM - Alejandro Cobar Back To Top (88403)
Hi Dennis!
So for some reason the $serverId value is not set in your particular case. I find it a bit weird because the $instanceLookupQuery string includes it and if it exists in your own MasterServerList table, then when it is called like $instance.serverId, it should return a value and not NULL. If you'd like I can help you to troubleshoot this in a live call/session when we both can find a time that best suits our schedule... let me know.

Monday, March 15, 2021 - 4:02:11 PM - Dennis Back To Top (88400)
In order to get a connection to default instances, I've had to add the port number to the server_name column of the MasterServerList table. All other script work except these 2: Get-mssql-Instance-Indexes-v2.ps1 and Get-MSSQL-Instance-Top5CPU.ps1. They both give nearly identical errors:
Fetching Top 5 CPU Queries information from instance MyServerName,1535\MyInstanceName
Invoke-Sqlcmd : Cannot insert the value NULL into column 'serverId', table 'DBA.monitoring.ErrorLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure , Line 1.
At C:\temp\Get-MSSQL-Instance-Top5CPU.ps1:48 char:13
+ Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB - ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

It's as if the $serverId variable isn't set correctly. Any ideas as to how to fix this?

Tuesday, November 3, 2020 - 2:50:46 PM - romnomb Back To Top (87752)
Thank your the swift response. I used the script provided nothing changed apart from "Get content". I have more than 30 databases in my test environment.

In DBA databases, I have inventory.Backups, inventory.Databases, inventory.DBDataFiles, inventory.Indexes, inventory.MasterServerList, monitoring.BufferPool, monitoring.ErrorLog and monitoring.Top5CPU tables

Here's the script:

"Get-Content "D:\WMD\SQL\_ps_monitoring_scripts\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server = $h.Get_Item("centralServer")
$inventoryDB = $h.Get_Item("inventoryDB")

if($server.length -eq 0){
Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
exit
}
if($inventoryDB.length -eq 0){
Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
exit
}

$mslExistenceQuery = "
SELECT Count(*) FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1
"
$result = Invoke-Sqlcmd -Query $mslExistenceQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop

if($result[0] -eq 0){
Write-Host "The table [inventory].[MasterServerList] wasn't found!!!" -BackgroundColor Red
exit
}

$enoughInstancesInMSLQuery = "
SELECT COUNT(*) FROM inventory.MasterServerList WHERE is_active = 1
"
$result = Invoke-Sqlcmd -Query $enoughInstancesInMSLQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop

if($result[0] -eq 0){
Write-Host "There are no active instances registered to work with!!!" -BackgroundColor Red
exit
}

if ($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0) {
$username = $h.Get_Item("username")
$password = $h.Get_Item("password")
}

#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted){
if($trusted -eq 1){
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop -QueryTimeout 0
}
catch{
[string]$message = $_
$errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Indexes','"+$message.replace("'","''")+"',GETDATE())"
Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
}
}
else{
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop -QueryTimeout 0
}
catch{
[string]$message = $_
$errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Indexes','"+$message.replace("'","''")+"',GETDATE())"
Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
}
}
}

##################################
#Indexes inventory table creation#
##################################
$indexesInventoryTableQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[Indexes]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE inventory.Indexes(
[serverId] INT NOT NULL,
[database] VARCHAR(128) NOT NULL,
[schema] VARCHAR(128) NOT NULL,
[table] VARCHAR(128) NOT NULL,
[index] VARCHAR(255) NOT NULL,
[type] VARCHAR(64) NOT NULL,
[allocation_unit_type] VARCHAR(64) NOT NULL,
[fragmentation] DECIMAL(10,2) NOT NULL,
[pages] INT NOT NULL,
[writes] INT NOT NULL,
[reads] INT NOT NULL,
[disabled] TINYINT NOT NULL,
[stats_timestamp] DATETIME NULL,
[data_collection_timestamp] DATETIME NOT NULL

CONSTRAINT PK_IndexesInventory PRIMARY KEY CLUSTERED (serverId,[database],[schema],[table],[index]),
CONSTRAINT FK_IndexesInventory_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE NO ACTION
) ON [PRIMARY]
END
"
Execute-Query $indexesInventoryTableQuery $inventoryDB $server 1

#TRUNCATE the inventory.Indexes table to always store a fresh copy of the information from all the instances
Execute-Query "TRUNCATE TABLE inventory.Indexes" $inventoryDB $server 1

#Select the instances from the Master Server List that will be traversed
$instanceLookupQuery = "
SELECT
serverId,
trusted,
CASE instance
WHEN 'MSSQLSERVER' THEN server_name
ELSE CONCAT(server_name,'\',instance)
END AS 'instance',
CASE instance
WHEN 'MSSQLSERVER' THEN ip
ELSE CONCAT(ip,'\',instance)
END AS 'ip',
CONCAT(ip,',',port) AS 'port'
FROM inventory.MasterServerList
WHERE is_active = 1
"
$instances = Execute-Query $instanceLookupQuery $inventoryDB $server 1

#For each instance, fetch the desired information
$indexesInformationQuery = "
DECLARE @command VARCHAR(MAX)

DECLARE @DBA_IndexInformation TABLE(
[database] VARCHAR(255),
[schema] VARCHAR(255),
[table] VARCHAR(255),
[index] VARCHAR(255),
[type] VARCHAR(255),
[allocation_unit_type] VARCHAR(255),
[fragmentation] DECIMAL(10,2),
[pages] INT,
[writes] INT,
[reads] INT,
[disabled] TINYINT,
[stats_timestamp] DATETIME)

SELECT @command = 'Use [?]
DECLARE @DB_ID INT;
SET @DB_ID = DB_ID();
SELECT
db_name(@DB_ID) AS db_name,
s.name,
t.name,
i.name,
i.type_desc,
ips.alloc_unit_type_desc,
CONVERT(DECIMAL(10,2),ips.avg_fragmentation_in_percent),
ips.page_count,
ISNULL(ius.user_updates,0),
ISNULL(ius.user_seeks + ius.user_scans + ius.user_lookups,0),
i.is_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'

INSERT INTO @DBA_IndexInformation
EXEC sp_MSForEachDB @command

SELECT * FROM @DBA_IndexInformation WHERE [database] NOT IN ('msdb','master','model','tempdb');
"

foreach ($instance in $instances){
if($instance.trusted -eq 'True'){$trusted = 1}else{$trusted = 0}
$sqlInstance = $instance.instance

#Go grab the information for the instance
Write-Host "Fetching indexes information from instance" $instance.instance

#Special logic for cases where the instance isn't reachable by name
try{
$results = Execute-Query $indexesInformationQuery "master" $sqlInstance $trusted
}
catch{
$sqlInstance = $instance.ip
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Indexes','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1

try{
$results = Execute-Query $indexesInformationQuery "master" $sqlInstance $trusted
}
catch{
$sqlInstance = $instance.port
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Indexes','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1

try{
$results = Execute-Query $indexesInformationQuery "master" $sqlInstance $trusted
}
catch{
[string]$message = $_
$query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Indexes','"+$message.replace("'","''")+"',GETDATE())"
Execute-Query $query $inventoryDB $server 1
}
}
}

#Perform the INSERT in the inventory.Indexes only if it returns information
if($results.Length -ne 0){

#Build the insert statement
$i = 0
$insert = "INSERT INTO inventory.Indexes VALUES"
foreach($result in $results){
$i++
if($result['stats_timestamp'].ToString().trim() -eq [String]::Empty){$statsTimestamp = "''"}else{$statsTimestamp = $result['stats_timestamp']}

$insert += "
(
"+$instance.serverId+",
'"+$result['database']+"',
'"+$result['schema']+"',
'"+$result['table']+"',
'"+$result['index']+"',
'"+$result['type']+"',
'"+$result['allocation_unit_type']+"',
"+$result['fragmentation']+",
"+$result['pages']+",
"+$result['writes']+",
"+$result['reads']+",
"+$result['disabled']+",
'"+$statsTimestamp+"',
GETDATE()
),
"

if($i -eq 500){
$insert = $insert -replace "''",'NULL'
$insert = $insert -replace "NULLNULL",'NULL'
Execute-Query $insert.Substring(0,$insert.LastIndexOf(',')) $inventoryDB $server 1
$insert = "INSERT INTO inventory.Indexes VALUES"
$i = 0
}
}
if($insert -ne "INSERT INTO inventory.Indexes VALUES"){
$insert = $insert -replace "''",'NULL'
$insert = $insert -replace "NULLNULL",'NULL'
Execute-Query $insert.Substring(0,$insert.LastIndexOf(',')) $inventoryDB $server 1
}
}
}

Write-Host "Done!""

Monday, November 2, 2020 - 8:01:40 PM - Alejandro Cobar Back To Top (87747)
Hi romnomb, would you be able to share the schema of your table and the script of the particular index that's giving you the error? Perhaps I can reproduce it and apply a fix to the PowerShell script to help you out.

Sunday, November 1, 2020 - 11:41:57 AM - romnomb Back To Top (87736)
Hi,

I am getting the following errors:
"
Violation of PRIMARY KEY constraint 'PK_IndexesInventory'. Cannot insert duplicate key in object 'inventory.Indexes'. The duplicate key value is (1, AdventureWorks2014, HumanResources, JobCandidate, PK_JobCandidate_JobCandidateID). The statement has been terminated. Msg 2627, Level 14, State 1, Procedure , Line 1.
Violation of PRIMARY KEY constraint 'PK_IndexesInventory'. Cannot insert duplicate key in object 'inventory.Indexes'. The duplicate key value is (1, SQLWATCH, dbo, sqlwatch_config_check_action_template, pk_sqlwatch_config_action_template). The statement has been terminated. Msg 2627, Level 14, State 1, Procedure , Line 1.
"

Tuesday, October 6, 2020 - 8:13:23 AM - Greg Robidoux Back To Top (86607)
Hi Jeff,

Thanks for your input. The original implementation was using Windows authentication only and I asked Alejandro to add something for situations where a trusted connection was not possible. I agree it is not the best approach.

If you have some ideas of how to better approach this for SQL authentication please let us know.

Thanks
Greg

Monday, October 5, 2020 - 10:00:02 PM - Jeff Moden Back To Top (86603)
I really appreciate you trying to help folks out but, lordy... I read the following snippet of code and shivers run up and down my spine. It implies that you're storing user names and passwords with the server names in clear text. To me, that would be a major security violation and I'm a bit surprised that you'd accommodate such a thing.

if ($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0) {
$username = $h.Get_Item("username")
$password = $h.Get_Item("password")
}

Wednesday, September 2, 2020 - 7:25:52 AM - Yefimov Dmytro Back To Top (86403)
Thanks for fixing. What about indexes in partitioned table?

Thursday, August 27, 2020 - 9:20:01 PM - Alejandro Cobar Back To Top (86377)
@Yefimov: You are probably getting that error because the amount of indexes for a particular SQL instance is quite high (more than 1000 for a particular instance). I have submitted the updated script so that you don't bump into this error, so I suggest that when the script has been uploaded you delete the inventory.Indexes table and run it again.

@NyDMX: In the updated script I submitted, I added the QueryTimeout 0 parameter so that the query doesn't time out if the instance being evaluated is quite busy.
In regards to connecting using a username and password, I explain this in the tip called "https://www.mssqltips.com/sqlservertip/6354/monitoring-sql-server-with-powershell-core-object-setup/". Search for the section called "Settings.ini" which tells you how to use a username and password for your connection strings.
*You must make sure that the exact same SQL login exists in all the instances you have specified in the inventory.MasterServerList table, and also make sure to flip the "trusted" bit to 0 so that the credentials you specify in the Settings.ini file are used. Last but not least, make sure that the SQL login you create in each instance has enough privileges to do what the PowerShell script is trying to accomplish.

Wednesday, August 26, 2020 - 3:28:27 PM - NyDMX Back To Top (86368)
I have two questions one how can i connect to servers and pass a username and password - you showed how to do that for the inventory db - but not for the instances we need to monitor - currently if you dont have Wind Auth - none of the scripts will work

2- I have an instance with large db and lots of Indexes - when running the Get-MSSQL-Instance-Index .PS1 - i am getting the following error being logged Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

is there a way to extend the timeout settings?

Wednesday, August 26, 2020 - 11:15:40 AM - Yefimov Dmytro Back To Top (86364)
SELECT
[message]
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Indexes'
output:
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

is there a way to avoid this error?














get free sql tips
agree to terms