Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Collect SQL Server Index Usage Information for all Instances with PowerShell


By:   |   Last Updated: 2019-06-10   |   Comments (5)   |   Related Tips: More > 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.

sample query output

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):

After executing the script, the IndexStatus table should contain values that look like this

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


Last Updated: 2019-06-10


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 26, 2019 - 4:59:53 PM - Ted Back To Top

Very nice post, Alejandro, thanks for your work on this.  It certainly makes the sys.dm_db_index_physical_stats dmv output much more digestible and useful.  


Friday, June 14, 2019 - 11:40:04 AM - David Waller Back To Top

Ok, I got the table setup, now i think it's failing due to permissions.

Invoke-Sqlcmd : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


Tuesday, June 11, 2019 - 8:49:24 PM - Alejandro Cobar Back To Top

Hi David,

You have to build a table and populate it with your instances so that the end result is a table that looks like the one I tried to draw.

That way, the query stored in $instanceLookupQuery = "SELECT name, instance FROM instances" will retrieve the list of instances you inserted.

*My recommendation for you is to create the instances table, insert one of your instances and try the script. After you are confident with the output you are getting, then you can populate it with the rest.


Monday, June 10, 2019 - 12:09:42 PM - David Waller Back To Top

I am not understanding section 3. How do I put the list in the script?

Thanks


Monday, June 10, 2019 - 7:55:30 AM - jampa Back To Top

Great post!!


Learn more about SQL Server tools