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.

Pre-requisites

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.

Conclusion

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.

 <#
.SYNOPSIS
    Resource Governor script for Microsoft SQL Server Analysis Services
.DESCRIPTION
    Automatically cancels queries that have been running longer than the maximum allowed time
.NOTES
    Author : Darren Gosbell (http://darren.gosbell.com)
    Date   : 9 Mar 2014
    Idea from - http://cwebbbi.wordpress.com/2008/12/04/killing-sessions-automatically-with-ssis/
.LINK
    http://darren.gosbell.com/
#>

##### 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 = "SSAS.Server@mycompany.com"
$cancelEmailBcc = "ssas-support@mycompany.com"
$cancelEmailServer = "mail.mycompany.com"
$supportEmail = "ssas-support@mycompany.com"

####################################

# 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.To.Add($to)
    $msg.Bcc.Add($bcc)
    $msg.Subject = $subject
    $msg.Body = $body
    
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)
    $smtp.Send($msg)
    #$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 = @"
    SELECT SESSION_SPID, COMMAND_ELAPSED_TIME_MS, COMMAND_TEXT ,COMMAND_START_TIME
    FROM `$system.discover_commands
    WHERE COMMAND_ELAPSED_TIME_MS > $($threshold * 1000) AND [COMMAND_END_TIME] = NULL
"@

    $qrySessions = @"
    select 
    SESSION_SPID
    ,SESSION_USER_NAME
    ,SESSION_CURRENT_DATABASE
    ,SESSION_USED_MEMORY
    from `$system.discover_sessions
"@

    $cnn.Open()

    # 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"
        $cnn.Close()
        continue
    }

    # 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
            continue
        }
        
        $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 
$supportEmail
"@
        # 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=`"http://schemas.microsoft.com/analysisservices/2003/engine`"><SPID>$($spid.SESSION_SPID)</SPID></Cancel>"
        $cmd.ExecuteNonQuery()
        
        # log the cancellation
        invoke-sqlcmd2 -ServerInstance $sqlInstance -Database "OlapTrace" -Query $insertCmd -As None > $null
    
    }

    $cnn.Close()
}

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

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

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

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

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

Your comment:

 (will show your gravatar)