February 13, 2013 – Sql Server Powershell

Here’s a quick script to retrieve index fragmentation via powershell. Two actually. This uses sqlpsx for both. Do not run these during the day on your production box (you shouldn’t run them at all unless you test them on a test box first to gauge impact). I like the first one because you can strip off the –dbname parameter if you like and collect all the indexes from all your databases. This uses LIMITED option (::fast), but it’s still pretty heavy-duty. Use at your own risk.

First, here is the powershell-object oriented variant:

Import-Module SqlServer

$outputs = @();
$srv = 'ServerName'
Get-SqlDatabase -sqlserver $srv -dbname 'dbName' | %{
	
	$dbName = $_.Name
	
	Get-SqlTable -Database $_ | where{$_.IsSystemObject -ne $true} | %{
		$tableName = $_.Name
		
		foreach($index in $_.Indexes)
		{
			$index.EnumFragmentation([Microsoft.SqlServer.Management.Smo.FragmentationOption]::Fast)  | %{
				$output = New-Object -TypeName PSObject -Property @{
					DatabaseName = $dbName
					TableName = $tableName
					IndexName = $index.Name
					IndexType = $_.IndexType            
					AvgFragmentationInPercent = $_.AverageFragmentation    
					SizeMB = [Math]::Truncate(($_.pages*8)/1024)
					FillFactor = $index.FillFactor
				}
				$outputs += $output				
			}

		}
	}

}
$outputs | SELECT DatabaseName, TableName, IndexName, IndexType, AvgFragmentationInPercent, SizeMB, FillFactor | Format-Table -AutoSize

Nice and clean. Unfortunately, if you run this while doing a trace, you’ll see that it’s doing much more work than necessary, but it’s very concise and easy to see/debug.

First, it executes this:

exec sp_executesql N'SELECT
i.name AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'TableName',@_msparam_3=N'SchemaName'

Then this:

exec sp_executesql N'
declare @database_id int
select @database_id = db_id()
			


SELECT
i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [MinimumRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, ''LIMITED'') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[Index_Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'IndexName',@_msparam_3=N'TableName',@_msparam_4=N'SchemaName'

Essentially, one hit to the database per table, and one per index on said table.

You could use this powershell to do one hit to the database to get all the tables, then only suffer one hit per index:

cls
Import-Module SqlServer

$outputs = @();
$srv = 'ServerName'
Get-SqlDatabase -sqlserver $srv -dbname 'DatabaseName' | %{

	$dbName = $_.Name

	$idxQuery = "
	SELECT 
		DB_ID() AS DBID,
		i.object_id, 
		OBJECT_NAME(i.object_id) AS TableName,
		i.name, 
		i.index_id, 
		SUM(dps.used_page_count) * 8 
	FROM 
	sys.dm_db_partition_stats  dps 
	INNER JOIN sys.indexes i ON dps.[object_id] = i.[object_id] AND dps.index_id = i.index_id 
	GROUP BY i.name, i.index_id, i.object_id
	"
	$indexes = Get-SqlData -sqlserver $srv -dbname $dbName -qry $idxQuery

	foreach($row in $indexes)
	{
		$objectID = $row.object_id
		$indexID = $row.index_id
		$databaseID = $row.DBID
		
		$idxFragQuery = "
			SELECT 
				i.name, 
				i.type, 
				dmf.page_count*8 AS 'size', 
				ROUND(dmf.avg_fragmentation_in_percent,0) AS avg_fragmentation_in_percent, 
				i.fill_factor 
			FROM 
			sys.dm_db_index_physical_stats($databaseID, $objectID, $indexID, null, 'limited') dmf 
			INNER JOIN sys.indexes i ON ((i.object_id = dmf.object_id) AND (i.index_id = dmf.index_id)) 
			WHERE 
			(dmf.alloc_unit_type_desc = 'IN_ROW_DATA')
		"
		$idxFragRows = Get-SqlData -sqlserver $srv -dbname $dbName -qry $idxFragQuery
		$output = New-Object -TypeName PSObject -Property @{
			DatabaseName = $dbName
			TableName = $idxFragRows.TableName
			IndexType = $idxFragRows.type
			Size = $idxFragRows.size
			AvgFragmentationInPercent = $idxFragRows.avg_fragmentation_in_percent
			FillFactor = $idxFragRows.fill_factor
		}
		$outputs += $output
		
	}
}

$outputs | Format-Table -AutoSize

But, it’s not nearly as clean. I wouldn’t run either of these on a production database during the day, but my preference would be to suffer the performance for the cleaner code, unless you have a gigantic number of tables in your database. YMMV. Use at your own risk.

Quick script to get a list of objects owned by a schema in sql server. This uses sqlpsx. This doesn’t return the objects themselves, just a list of them.

Import-Module SqlServer
$outputs = @();
$srvName = 'ServerName'
$dbName = 'DatabaseName'
$schemaName = 'SchemaName'

#do not change, used for string parsing below
$startIndex = "@Name='"
$endIndex = "' and @Schema"

$srv = Get-SqlServer -sqlserver $srvName
$db = Get-SqlDatabase -sqlserver $srv -dbname $dbName
$schema = $db.schemas | where{$_.Name -eq $schemaName}
foreach($ownedObj in $schema.EnumOwnedObjects().GetEnumerator())
{
	$arObjs = $ownedObj.Value.Split("/")
	$newName = $arObjs[2].Substring($arObjs[2].IndexOf($startIndex)+ $startIndex.Length)
	
	$output = New-Object -TypeName PSObject -Property @{
		ObjectName = $newName.substring(0, $newName.indexof("'"));
		ObjectType = $ownedObj.Type
	}
	$outputs += $output

}

$outputs | Format-Table -AutoSize

Day: February 13, 2013

Getting Index Fragmentation via Powershell

Get a list of Objects owned by a Schema in Sql Server via Powershell