Month: February 2014

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

Create an index with included columns in powershell

I had a need the other day to add a new index to a table that existed in multiple databases on multiple servers.  The index consisted of one indexed column and two included columns.  This script checks for existence of the index, and if it does not exist, adds the index.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$outputs = @();

$servers = 'ServerName'

try{
	$servers | %{
		$srvName = $_
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $srvName
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

		$srv.Databases | where{-not $_.IsSystemObject} | %{
			$dbName = $_.Name
			$_.Tables | where{$_.Name -eq 'TableName'} | %{
				$indexes = $_.Indexes | where{$_.Name -eq 'IndexName'}
				if($indexes.Count -gt 0){
					$output = New-Object -TypeName PSObject -Property @{
					    ServerName = $srvName
					    DatabaseName = $dbName
					    TableName = $_.Name
						Message = 'Index already exists'
					}
					$outputs += $output
					continue;
				}
				
				#create new index object on current $_ table
				$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -argumentlist $_, "IndexName"

				#add indexed column
				$idxCol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $index, "IndexedColumn1", $true
				$index.IndexedColumns.Add($idxCol)
				
				#add included columns
				$incCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $index, "IncludedColumn1", $true
				$incCol1.IsIncluded = $true;
				$index.IndexedColumns.Add($incCol1)
				$incCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $index, "IncludedColumn2", $true
				$incCol2.IsIncluded = $true;
				$index.IndexedColumns.Add($incCol2)

				$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None 
				$index.IsClustered = $false
				$index.FillFactor = 90
				$index.Create()
				
				$output = New-Object -TypeName PSObject -Property @{
				    ServerName = $srvName
				    DatabaseName = $dbName
				    TableName = $_.Name
					Message = 'Index added'
				}
				$outputs += $output
			}
		}
	}	
}
catch{
	$_ | fl -Force
}
finally{
	$outputs | SELECT ServerName, DatabaseName, TableName, Message | Out-GridView
}