How to build your own SSAS Resource Governor with PowerShell

A few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take it a step further and show you an automated solution. The idea behind this technique is inspired by a blog post Chris Webb did some years ago. Chris implemented his solution using SSIS while this version uses PowerShell.

You might ask - why create a Powershell version?

Well it does a little bit more in that it logs the cancelled queries and sends the user an email. It also uses membership in an Active Directory group to control who bypasses the governor. This makes that list easy to maintain separately from the script. Scripts are easy to view and change as all you need is a text editor. I also think that there is probably less overhead in running a Powershell script as compared to starting an SSIS package.

This script works with both Tabular and Multi-dimensional instances. And it could easily be extended to monitor multiple instances. 

The basic operation of the resource governor script is as follows:

1. Query $system.DISCOVER_COMMANDS for any commands that have been running longer than x minutes or seconds. (Excluding any XMLA commands (processing, deploying, tracing, etc)

2. Look up the session for these commands in $system.DISCOVER_SESSIONS to find out the LoginName and the database they are querying 

3 Check if the user is in the bypass list for the Resource Governor, if they are log this fact and exit. Otherwise cancel the query, log the details of it to a table in SQL Server and send them a nice email.

This script then gets run every minute during business hours by a SQL Agent job.


Before running this script you need to download invoke-sqlcmd2 from the Technet Gallery. It’s a nice lightweight way of running SQL commands from Powershell.

And to use the script as is you will need to use the following script to create some logging tables in a SQL database and create an SQL Agent job.


If you are interested in running this script on one of your servers you might want to consider commenting out the bits that cancels the query and sends the user an email. Then just let it log the actions it would have taken. Once you start cancelling queries you’ll want to monitor the log tables. In some cases you will discover opportunities to improve your cubes. In other cases you will be able to assist the users with a better way to achieve their desired result.

Below is the full script, but you can also download it from here

All the key variables are declared at the top of the script. You should just need to change these to suit your environment in order to use this script.

    Resource Governor script for Microsoft SQL Server Analysis Services
    Automatically cancels queries that have been running longer than the maximum allowed time
    Author : Darren Gosbell (
    Date   : 9 Mar 2014
    Idea from -

##### initialization variables #####

$servers = "localhost\tabular" ,"localhost\multidim"
$threshold = 300   # in seconds

$sqlInstance = "localhost"
$bypassADGroup = "CN=ImportantGroup,OU=Distribution Lists,OU=Shared Mailboxes,DC=mycompany,DC=com"

$cancelEmailSubject = "Analysis Services Long Running Query Cancellation"
$cancelEmailFrom = ""
$cancelEmailBcc = ""
$cancelEmailServer = ""
$supportEmail = ""


# load the AMO library
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.adomdclient") > $null

# load the invoke-sqlcmd2 cmdlet
. $PSScriptRoot\invoke-sqlcmd2.ps1

## ============ Start Helper Functions =================
Function Send-Email( $to, $subject, $body )
    $emailFrom = $cancelEmailFrom
    $bcc = $cancelEmailBcc
    $smtpServer = $cancelEmailServer
    $msg = new-object Net.Mail.MailMessage
    $msg.From = $emailFrom
    $msg.Subject = $subject
    $msg.Body = $body
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)
    #$smtp.Send($emailFrom, $emailTo, $subject, $body)

foreach ($svr in $servers)
    $connStr = "data source=$svr"
    [Microsoft.AnalysisServices.adomdclient.adomdconnection]$cnn = new-object Microsoft.AnalysisServices.adomdclient.adomdconnection($connStr)
    $cmd = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
    $cmd.Connection = $cnn

    $qryLongCmd = @"
    FROM `$system.discover_commands

    $qrySessions = @"
    from `$system.discover_sessions


    # get a list of current commands that exceeded the time threshold
    $cmd.CommandText = $qryLongCmd
    $da = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($cmd)
    $dsCmd = new-Object System.Data.DataSet
    $da.Fill($dsCmd) > $null
    # filter out any xmla commands that start with '<'
    $drCmd = $dsCmd.Tables[0].rows | where {$_.COMMAND_TEXT.StartsWith("<") -eq $false }

    if (@($drCmd.count).count -eq 0)
        write-host "no excessive queries found"

    # get a list of the current sessions
    $cmd.CommandText = $qrySessions
    $da = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($cmd)
    $dsSess = new-Object System.Data.DataSet
    $da.Fill($dsSess) > $null

    # Lookup the session information for each long running command
    foreach ($c in $drCmd) 
        $s = $dsSess.Tables[0].select("SESSION_SPID = $($c.SESSION_SPID)");
        $c |Add-Member -NotePropertyName "SESSION_USER_NAME" -NotePropertyValue $s.SESSION_USER_NAME ;
        $c |Add-Member -NotePropertyName "SESSION_CURRENT_DATABASE" -NotePropertyValue $s.SESSION_CURRENT_DATABASE ;             
        $c |Add-Member -NotePropertyName "COMMAND_ELAPSED_TIME" -NotePropertyValue $([System.Timespan]::FromMilliseconds($c.COMMAND_ELAPSED_TIME_MS))
        $user = $s.SESSION_USER_NAME.Replace("ACCOUNT-01\","")
        $srchr = (New-Object DirectoryServices.DirectorySearcher "(&(ObjectClass=user)(Name=$user))")
        $srchr.PropertiesToLoad.Add("mail") > $null
        $srchr.PropertiesToLoad.Add("memberof") > $null
        $ad = $srchr.FindOne()
        $InPriorityGroup = $ad.Properties["memberof"]  -Contains $bypassADGroup
        $c |Add-Member -NotePropertyName "InPriorityGroup" -NotePropertyValue $InPriorityGroup
        $c |Add-Member -NotePropertyName "Email" -NotePropertyValue $($ad.Properties["mail"])

    # kill any sessions that were returned 
    foreach ($spid in $drCmd)
        if ($spid.InPriorityGroup -eq $true)
            write-output "Bypassing SPID: $($spid.SESSION_SPID) as it is in the Workload Priorty Group"
            $bypassLogCmd = "INSERT INTO tb_WorkloadBypassLog (UserLogin, CommandDurationMS, SessionDatabase, SPID) VALUES ('$($spid.SESSION_USER_NAME)', '$($spid.COMMAND_ELAPSED_TIME_MS)', '$($spid.SESSION_CURRENT_DATABASE)', '$($spid.SESSION_SPID)')"
            invoke-sqlcmd2 -ServerInstance $sqlInstance -Database "OlapTrace" -Query $bypassLogCmd -As None > $null
        $eml = $spid.Email 
        write-progress "Cancelling SPID $($spid.SESSION_SPID)"
        # log the Cancellation attempt
        $qry = $spid.COMMAND_TEXT.Replace("'","''")
        $insertCmd = "INSERT INTO tb_CancelledQueries (UserLogin, CommandStart, CommandDurationMS, SessionDatabase, Query, Email) VALUES ('$($spid.SESSION_USER_NAME)', '$($spid.COMMAND_START_TIME)', '$($spid.COMMAND_ELAPSED_TIME_MS)', '$($spid.SESSION_CURRENT_DATABASE)', '$qry', '$($spid.Email)')"

        # Send email notification to end user
        $msg = @"
Your query against the '$($spid.SESSION_CURRENT_DATABASE)' Analysis Services database has consumed excessive resources
and has been marked for cancellation by the workload management system.

Please wait a few minutes or try requesting a smaller set of data.

For assistance with structuring efficient queries or advice about resource management, 
please forward this email and a brief outline of what you are trying to achieve to 
        # if we have an email registered in AD send the user a notification
        if ($ad.Properties.Contains("mail"))
            Send-Email $eml $cancelEmailSubject $msg
        # cancel the query
        $cmd.CommandText = "<Cancel xmlns=`"`"><SPID>$($spid.SESSION_SPID)</SPID></Cancel>"
        # log the cancellation
        invoke-sqlcmd2 -ServerInstance $sqlInstance -Database "OlapTrace" -Query $insertCmd -As None > $null


Print | posted on Monday, March 31, 2014 6:04 AM