SQL 2008 powershell script to print Resource Governor properties
###########################################################################################
# SQL 2008 powershell script to print Resource Governor state, properties, Resource pools and corresponding workload groups
#
# How to use this powershell script:
# - Launch SQL Server Management Studio ( Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio)
# - Connect to SQL Server instance, Right click on the server node in Object Explorer and click on "Start Power Shell" menu
# - Copy the following powershell script and save to file (Ex c:\resourceGovernor.ps1)
# - in powershell window, type in script file path ( Ex: c:\resourceGovernor.ps1) to run the script
###########################################################################################
####################################
# Prints Resource Governor Configuration to console
####################################
function PrintResourceGovernorProperties
{
param($server)
$resourceGovernor = $server.ResourceGovernor
write-host "=========================================="
write-host "Resource Governor Configuration for server:", $server.Name
write-host "=========================================="
$resourceGovernor | select-object Enabled, ReconfigurePending, ClassifierFunction | format-list
PrintClassifierFunction $server
EnumerateResourcePools $resourceGovernor
}
####################################
# Prints Classifier function details to console
####################################
function PrintClassifierFunction
{
param($server)
$resourceGovernor = $server.ResourceGovernor
# Print classifier function contents only if classifier function was set for Resource Governor
if($resourceGovernor.ClassifierFunction -ne $null)
{
$classifierFunctionName = $resourceGovernor.ClassifierFunction.Replace("[","").Replace("]","")
$classifierFunctionPSPath = $server.PSParentPath + "\Databases\master\UserDefinedFunctions\" +$classifierFunctionName
write-host "--------------------------"
write-host "Classifier function details"
write-host "-------------------------, "
$classifierFunction = get-item $classifierFunctionPSPath
$classifierFunction | select-object Name, Owner, IsSchemaBound, TextHeader, TextBody |format-list
}
}
###########################################
# Enumerates Resource Pools and prints resource pool properties
###########################################
function EnumerateResourcePools
{
param($resourceGovernor)
write-host "==========================="
write-host "Resource Pools: (", $resourceGovernor.ResourcePools.Count, "pools)"
write-host "==========================="
$resourceGovernor.ResourcePools | `
select-object Name, MinimumCpuPercentage, MaximumCpuPercentage, MinimumMemoryPercentage, MaximumMemoryPercentage | `
format-table
foreach($resourcePool in $resourceGovernor.ResourcePools)
{
EnumerateWorkloadGroups $resourcePool
}
}
###########################################
# Enumerates workloadgroups and prints their properties
###########################################
function EnumerateWorkloadGroups
{
param($resourcePool)
write-host "==========================="
write-host "Resource Pool:", $resourcePool.Name
write-host "Workload Groups: (", $resourcePool.WorkloadGroups.Count, "groups)"
write-host "==========================="
$resourcePool.WorkloadGroups| `
select-object Name, Importance, GroupMaximumRequests, RequestMaximumCpuTimeInSeconds, `
RequestMaximumMemoryGrantPercentage, RequestMemoryGrantTimeoutInSeconds, MaximumDegreeOfParallelism | `
format-table
}
##Main
$server = (get-item .)
PrintResourceGovernorProperties $server
# This posting is provided "AS IS" with no warranties, and confers no rights.
# Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
