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