PowerShell: Finding Unused Indexes in a Database

Question:

How can I use PowerShell to find unused indexes on databases over multiple servers?

Answer:

Using the SMO namespace. Specifically, the server class of the Microsoft.SQLServer.management.SMO namespace.

In this example we create the SQL statement that will query the DMV’s to return indexes that are not being used.

We build the SQL query as a “here” document, and then use the INVOKE-SQLCMD Cmdlet to execute the query against the specific server[s] and database.

function Get-UnusedIndexes
{[CmdLetBinding()] Param
([Parameter (
Mandatory = $true,
ValueFromPipeline = $true)] [string[]] $computername,[Parameter (
Mandatory = $true,
ValueFromPipeline = $true)] [string] $database,[switch] $ShowSQL
)
BEGIN
{
Import-Module SQLPS -DisableNameChecking | Out-Null
# Here document – SQL query definition
$SQL = @”
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i.name AS [Unused Index Name] , i.type_desc AS [Index Type] , [Index Enabled] =
CASE
WHEN i.is_disabled = 1 THEN ‘FALSE’
WHEN I.is_disabled = 0 THEN ‘TRUE’
ELSE ‘UNKNOWN’
END
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON
i.[object_id] = o.[object_id] WHERE i.index_id NOT IN ( SELECT ddius.index_id
FROM sys.dm_db_index_usage_stats AS ddius
WHERE ddius.[object_id] = i.[object_id] AND   i.index_id = ddius.index_id
AND   database_id = DB_ID()
)
AND o.[type] = ‘U’
AND i.name IS NOT NULL
ORDER BY OBJECT_NAME(i.[object_id]) ASC,[Index Type];
“@

} # End Begin block

PROCESS
{
foreach ( $computer in $computername )
{
try
{
$result = $null

$computer = $computer.ToUpper()

$test = Test-Connection -ComputerName $computer -Count 1 -Quiet  -ErrorAction Stop

if ( $test -eq $false )
{
throw “Unable to Ping Server: $computer”
}

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $computer -ErrorAction Stop

if ( $ShowSQL )
{
Write-Output “SQL Commands:”
Write-Output $SQL
}

Write-Output “”
Write-Output “Working Server: $computer”

if ( $server.Databases[$database].IsAccessible )
{
$result = Invoke-Sqlcmd -Query $SQL -ServerInstance $server -Database $database -ErrorAction Stop
}
else
{
throw “Database: $database, Is Not Accessible”
}

if ($result -ne $null)
{
$result | ft -AutoSize
} else {
Write-Output “No Unused Indexes Found on Server: $computername, Database: $database”
}

} catch {
Write-Output “An Error Has Occurred:”
$_.exception.message
Write-Output “”
continue
}
}
} # End Process block

END {}
} # End Function Get-UnusedIndexes

Execute the function:

PS> Get-UnusedIndexes –computername MyServerName  -database MyTestDB

Working Server:  MyServerName

Table Name Unused Index Name          Index Type                Index Enabled
———-        —————–                       ———-                      ————-
TBL1            PK__TBL1                         CLUSTERED            TRUE        
TBL1            IDX_TBL1_COLC              NONCLUSTERED    TRUE        
TBL2            IDX_TBL2_COLC              NONCLUSTERED    FALSE

The query used identifies any index located in the sys.indexes view. If the index exists in one (sys.indexes) and not the other (sys.dm_db_index_usage_stats) then it’s not being used.  An entry is placed in sys.dm_db_index_usage_stats as soon as the index is used.

 

If no unused indexes are found, the following message is displayed:

Working Server: MyServerName
No Unused Indexes Found on Server: MyServerName, Database: MyTestDB

The function has two mandatory parameters: computername and database.  Computername is a string array that can accommodate several comma separated server names.  The database is singular, but the code could be modified to check one, or all, databases on the server.  Two try/catch blocks are used, one to ping the server, and the other to test if the database is available.

The switch, ShowSQL, simply writes out the SQL statement for the user.

Summary:

In this example, I’ve shown how to use the SMO namespace, along with a SQL qeury to find unused indexes on your database. This can be helpful when troubleshooting a query that is performing poorly and the optimizer has chosen not to use an [existing] index within the execution plan.

Resources:

Find out more information here:

SMO Namespace:

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(v=sql.120).aspx

SMO/server class:

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server(v=sql.120).aspx

DMV: sys.dm_db_index_usage_stats:

https://msdn.microsoft.com/en-us/library/ms188755(v=sql.110).aspx

PowerShell: Finding Database Sizes in SQL Server using SMO

Question: How can I use PowerShell to find out my database sizes on multiple servers?
Answer:  We can use the SQLPS module and the SMO -> Server class to find out the sizes of all databases on a server instance.
Here is the code:

function Get-DatabaseSizes
{

param
(
[string[]] $computername       #an array of computer names
)
Import-Module sqlps -DisableNameChecking | Out-Null
$srv = New-Object -TypeName microsoft.sqlserver.management.smo.server -ArgumentList $computername -ErrorAction Stop
# Where … gets rid of system db’s and makes sure db’s are available
$databases = $srv.Databases.Where({ -not $_.IsSystemObject -and $_.IsAccessible })
$databases |
select Name,
@{N = “Size(MB)”; E={$_.size}},
@{N = “DataSpaceUsage(MB)”;  E={$_.dataspaceusage/1024}},
@{N = “IndexSpaceUsage(MB)”; E={$_.indexspaceusage/1024}},
@{N = “SpaceAvailable(MB)”;  E={$_.Spaceavailable/1024}} |
ft -AutoSize

}

Load the function into your environment, or module file, and then execute it:

PS> Get-DatabaseSizes  -computername ServerOne, ServerTwo, … Server_N

Here is the output  (click on the image for an enlarged view):