Get Index usage for all datases via powershell

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: