This just simply executes the following sql in each database for a server:
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, OBJECT_NAME(a.OBJECT_ID) AS TableName, COALESCE(name, 'object with no clustered index') AS IndexName, type_desc AS IndexType, user_seeks as IndexSeeks, CASE user_seeks WHEN 0 THEN 0 ELSE CONVERT(NUMERIC(10,2),user_seeks*1.0 /(user_scans + user_seeks) * 100.0) END AS IndexSeekPercentage, user_scans as IndexScans, CASE user_scans WHEN 0 THEN 0 ELSE CONVERT(NUMERIC(10,2), user_scans*1.0 /(user_scans + user_seeks) * 100.0) END AS IndexScanPercentage, user_lookups as BookMarkLookups, DATEDIFF ( dd , (SELECT login_time FROM master..sysprocesses WHERE spid = 1) , getdate()) NumDaysNotUsed FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b ON a.index_id = b.index_id AND a.object_id = b.object_id WHERE database_id = db_id() AND type_desc != 'HEAP' ORDER BY TableName
I can’t really remember where I got the sql code from to give proper attribution.
Here is the powershell script that executes this sql on a per-database level. Nothing special going on here, just a quick script in case you need it. It just outputs it to a gridview right now, but you can change it to write to file if you like. Use at your own risk.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$indexCmd = "
SELECT
@@SERVERNAME AS ServerName,
DB_NAME() AS DatabaseName,
OBJECT_NAME(a.OBJECT_ID) AS TableName,
COALESCE(name, 'object with no clustered index') AS IndexName,
type_desc AS IndexType,
user_seeks as IndexSeeks,
CASE user_seeks WHEN 0 THEN 0
ELSE CONVERT(NUMERIC(10,2),user_seeks*1.0 /(user_scans + user_seeks) * 100.0) END AS IndexSeekPercentage,
user_scans as IndexScans,
CASE user_scans WHEN 0 THEN 0
ELSE CONVERT(NUMERIC(10,2), user_scans*1.0 /(user_scans + user_seeks) * 100.0) END AS IndexScanPercentage,
user_lookups as BookMarkLookups,
DATEDIFF ( dd , (SELECT login_time FROM master..sysprocesses WHERE spid = 1) , getdate()) NumDaysNotUsed
FROM sys.dm_db_index_usage_stats a
INNER JOIN sys.indexes b ON a.index_id = b.index_id
AND a.object_id = b.object_id
WHERE
database_id = db_id()
AND type_desc != 'HEAP'
ORDER BY TableName
"
$serverName = 'ServerName'
$serverName | %{
$srvName = $_
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
[System.Data.DataTable]$dataTable = New-Object('system.Data.DataTable')
[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')
[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')
$dbs = $srv.Databases | where{-not $_.IsSystemObject}
$dbs | %{
$c.connectionstring = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$($_.Name);Data Source=$srvName"
$c.open();
$cmd.Connection = $c;
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.commandText = $indexCmd
$dr = $cmd.ExecuteReader()
$dataTable.load($dr);
$c.Close();
}
}
$dataTable | Out-GridView
Sql Server Powershell 