###########################################################################################
# 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