How to build your own SSAS Resource Governor with PowerShell


You might ask - why create a Powershell version?



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












    Resource Governor script for Microsoft SQL Server Analysis Services
    Author : Darren Gosbell (
    Date   : 9 Mar 2014
    Idea from -

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

$threshold = 300   # in seconds

$sqlInstance = "localhost"

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


# load the AMO library

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

    $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)

foreach ($svr in $servers)
    $connStr = "data source=$svr"
    $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 '<'

        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) 
        $srchr.PropertiesToLoad.Add("mail") > $null
        $srchr.PropertiesToLoad.Add("memberof") > $null
        $ad = $srchr.FindOne()

    # kill any sessions that were returned 
    foreach ($spid in $drCmd)
        if ($spid.InPriorityGroup -eq $true)
        $eml = $spid.Email 
        write-progress "Cancelling SPID $($spid.SESSION_SPID)"
        # log the Cancellation attempt

        # Send email notification to end user
        $msg = @"
and has been marked for cancellation by the workload management system.


For assistance with structuring efficient queries or advice about resource management, 
        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


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

Comments on this post

# re: How to build your own SSAS Resource Governor with PowerShell

Requesting Gravatar...
Woow really cool.

Thank you for sharing.
Left by Refa on Nov 06, 2015 7:13 AM


Requesting Gravatar...
this looks to be a great idea, but could you please help me in assigning the variables on top. I very new to shell script.
Please help...
Left by balaji on Feb 04, 2016 1:41 AM


Requesting Gravatar...
So the key variables are the top 4

$servers - is an array of SSAS instances for the script to monitor

$threshold - is the maximum time a query can run in seconds before being killed.

$sqlInstance - this is the name of a SQL Server where the cancellation will be logged to a table called tb_QueryCancellations (you may want to edit the script if you want to log differently)

$bypassADGroup - this is an active directory group that does not get cancelled if they exceed the threshold time.
Left by Darren Gosbell on Feb 04, 2016 4:22 PM


Requesting Gravatar...
I know this is an old post, I have no experience with Power Shell. I'd like to use this code to cancel queries that take xxxxx memory usage.

I'm thinking I can use;
$qryLongCmd = @"
Select * from $System.discover_object_memory_usage

Am I on the right track.

Do you have code for memory usage already?

thanks very very
Left by Tim White on Apr 11, 2018 11:12 PM


Requesting Gravatar...
@Tim - sorry, but you I've never tried to do this. Our main bottleneck has been CPU and the duration is a close an analog as we can get for CPU usage. You can't see the usage for a single query as such, but a query like the following will get you the memory used by a session.

Select * from $System.discover_object_memory_usage
where LEFT(OBJECT_ID,5) = 'SPID_'

I just don't know if this is updated mid-query or not. My suspicion would be that memory allocations/de-allocations during a query normally happen so fast and are so performance sensitive that they are not individually tracked, but I'm just speculating.
Left by Darren Gosbell on Apr 12, 2018 7:32 AM

Your comment:

 (will show your gravatar)