tim laqua dot com Thoughts and Code from Tim Laqua

15Apr/112

Dealing With Long Running SSAS Queries using Powershell

So, your Analysis Services instance keeps eating all the memory on your server despite proper memory configuration? This happens when queries get too big for their britches - SSAS memory limits are basically soft limits. Whatever an active query requires in terms of resources, SSAS will attempt to provide it. Even if it has to page the entire operating system out to disk to do it. You can mitigate this with good cube design, better aggregations, user training, etc - but it's bound to happen. When your cube gets big enough, one of your users will blow the entire server up.

First things fist - the original thought for this came form Chris Webb's article, Killing sessions automatically with SSIS. Now on to my solution - which is basically identical in theory, just implemented in PowerShell with an email notification at the end.

Create a SQL Agent job with a PowerShell step that runs the following script and schedule it to run every 30 seconds

#======= START Config ========
$asInstance = "ssasinstance";
$emailServer = "1.2.3.4";
$fromAddress = "you@yourcompany.com";
$toAddress = "you@yourcompany.com";
$maxQueryElapsedMS = 30000;
$bypassUserPattern = "yourusername";
#======== END Config =========
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") ;
[Microsoft.AnalysisServices.xmla.xmlaclient]$client = 
  new-object Microsoft.AnalysisServices.Xmla.XmlaClient ;
$client.Connect($asInstance) ;
$commands = "";
$sessions = "";
$client.Discover("DISCOVER_COMMANDS", "", "", [ref] $commands, 0, 0, 1) ;
$client.Discover("DISCOVER_SESSIONS", "", "", [ref] $sessions, 0, 0, 1) ;
$sessionsXml = [xml]$sessions;
$sessionsRows = $sessionsXml.return.root.row;
$commandsXml = [xml]$commands;
$commandsRows = $commandsXml.return.root.row;
 
foreach ($row in $commandsRows) 
{
  if([int]$row.COMMAND_ELAPSED_TIME_MS -gt $maxQueryElapsedMS -and $row.COMMAND_TEXT -match "SELECT") 
  {
    $spid = $row.SESSION_SPID;
    $commandText = $row.COMMAND_TEXT;
    $session = ($sessionsRows | ? { [int]$_.SESSION_SPID -eq [int]$spid });
    $user = $session.SESSION_USER_NAME;
    $db = $session.SESSION_CURRENT_DATABASE;
    if($user -notmatch $bypassUserPattern) {
      $cancelXMLA = "<Cancel xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><SPID>$spid</SPID></Cancel>";
      $client.Send($cancelXMLA, $null);
      $smtp = new-object Net.Mail.SmtpClient($emailServer); 
      $smtp.Send(
        "$asInstance <$fromAddress>", 
        $toAddress, 
        "Killed SPID: $spid", 
        "SPID $spid was detected as the owner of a long running query and was killed.`r`nUser: $user`r`nDB: $db`r`n`r`n$commandText"
      );
    }
  }
}
$client.Disconnect();

Great. Now sessions with active queries (aka "commands" with the word "SELECT" in them) taking over 30 seconds will be cancelled and we'll get an email w/ the query so we can debug! So you wake up the next morning and Japan called - a bunch of their pivots errored out last night and they're not happy. Hmm... Pry should have warned someone before we started cancelling queries...

Phase 2: Turn off the query/session killer, turn on the logging

Create the Log table:

CREATE TABLE [dbo].[LongRunningMDXQueryLog](
	[SPID] [int] NOT NULL,
	[CommandStartTime] [datetime] NOT NULL,
	[Username] [nvarchar](255) NULL,
	[Database] [nvarchar](255) NULL,
	[ElapsedTimeMS] [int] NOT NULL,
	[CommandText] [nvarchar](max) NULL
)

Create the MERGE proc to handle log entries

CREATE PROCEDURE [dbo].[LongRunningMDXQueryLog_Merge]
	 @SPID INT
	,@CommandStartTime DATETIME
	,@Username VARCHAR(255)
	,@Database VARCHAR(255)
	,@ElapsedTimeMS INT
	,@CommandText NVARCHAR(MAX)
AS
BEGIN
	;MERGE LongRunningMDXQueryLog a USING
	(
		SELECT
			 @SPID 
			,CAST(@CommandStartTime AS DATETIME)
			,@Username 
			,@Database
			,@ElapsedTimeMS 
			,@CommandText 
	) b
	([SPID]
	,[CommandStartTime]
	,[Username]
	,[Database]
	,[ElapsedTimeMS]
	,[CommandText])
	ON
	(a.[SPID] = b.[SPID]
	 AND a.[CommandStartTime] = b.[CommandStartTime]
	 AND a.[Username] = b.[Username]
	 AND a.[Database] = b.[Database]
	 AND a.[CommandText] = b.[CommandText])
	WHEN NOT MATCHED THEN
	INSERT
			   ([SPID]
			   ,[CommandStartTime]
			   ,[Username]
			   ,[Database]
			   ,[ElapsedTimeMS]
			   ,[CommandText])
		 VALUES
			   ([SPID]
			   ,[CommandStartTime]
			   ,[Username]
			   ,[Database]
			   ,[ElapsedTimeMS]
			   ,[CommandText])
	WHEN MATCHED THEN
	UPDATE
	SET
		[ElapsedTimeMS] = b.[ElapsedTimeMS]
	;
END

Create a job to run the following Powershell script every 30 seconds:

#======= START Config ========
$asInstance = "ssasinstance";
$logConnectionString = 
  "server=sqlserverinstance;" + 
  "database=databaseYouCreatedTheLogTableIn;" + 
  "trusted_connection=true;";
$maxQueryElapsedMS = 30000;
#======== END Config =========
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") ;
[Microsoft.AnalysisServices.xmla.xmlaclient]$client = 
  new-object Microsoft.AnalysisServices.Xmla.XmlaClient ;
$client.Connect($asInstance) ;
$commands = "";
$sessions = "";
$client.Discover("DISCOVER_COMMANDS", "", "", [ref] $commands, 0, 0, 1) ;
$client.Discover("DISCOVER_SESSIONS", "", "", [ref] $sessions, 0, 0, 1) ;
$sessionsXml = [xml]$sessions;
$sessionsRows = $sessionsXml.return.root.row;
$commandsXml = [xml]$commands;
$commandsRows = $commandsXml.return.root.row;
 
foreach ($row in $commandsRows) 
{
  $elapsedTimeMS = $row.COMMAND_ELAPSED_TIME_MS;
  if([int]$elapsedTimeMS -gt $maxQueryElapsedMS -and $row.COMMAND_TEXT -match "SELECT") 
  {
    $spid = $row.SESSION_SPID;
    $commandText = $row.COMMAND_TEXT -replace "'", "''";
    $session = ($sessionsRows | ? { [int]$_.SESSION_SPID -eq [int]$spid });
    $user = $session.SESSION_USER_NAME;
    $db = $session.SESSION_CURRENT_DATABASE;
    $commandStartTime = 
      ([string]$row.COMMAND_START_TIME -replace '\.\d+$', '' -replace 'T', ' ');
    $sqlConn = New-Object System.Data.SQLClient.SQLConnection($logConnectionString);
    $sqlConn.Open();
    $sqlCommand = New-Object System.Data.SQLClient.SQLCommand;
    $sqlCommand.Connection = $sqlConn;
    $sqlCommand.CommandText = 
      "EXEC LongRunningMDXQueryLog_Merge " + 
        "$spid, " +
        "'$commandStartTime', " + 
        "'$user', " + 
        "'$db', " + 
        "$elapsedTimeMS, " + 
        "'$commandText'";
    $sqlCommand.ExecuteNonQuery()
    $sqlConn.Close();
  }
}
$client.Disconnect();

And now you have a table filled with queries that will be cancelled (read: "error out") when the query killing job is running. So now you fix it:

  1. locate these users
  2. figure out what crazy pivots are causing the problem
  3. Refactor the pivot OR determine what dimensionality is missing from the cube

Once you go a day or so w/ a quiet log table, you can turn on the query killer and go on your merry way. It's been suggested that we should send the user an email - let them know that it wasn't an error... that we cancelled their query. Maybe - but honestly, you should probably email or call them personally to hash it out. If it happens once, chalk it up to happenstance. If it continues to occur, it's time for some user training and/or requirements gathering.

The real problem is that most end users don't know that 30 seconds is ridiculously long - they shouldn't have to wait that long. Not only does it hurt them, it hurts others using the system as well.

UPDATE: You may want to, at some point, actually view one of these monster queries to see what on earth they were querying for. Over a certain size, getting it out of SSMS can be pretty troublesome - A query similar to the following should do the trick:

DECLARE @SQLcommand VARCHAR(4000)
SET @SQLcommand = 'bcp "select CommandText 
from Database.dbo.LongRunningMDXQueryLog 
where SPID = 511519" 
queryout "c:\output.dtsx" -T -c'
 
EXEC xp_cmdshell @SQLcommand
Comments (2) Trackbacks (1)
  1. This is a great, proactive approach! Users don’t want to wait – not even 30 seconds. Getting a call from their friendly business analyst offering assistance will show that someone cares. And solving the problem will benefit everyone using the system, as well as the analyst’s state-of-mind!

  2. WOW it is a Fantastic code for powershell beginners


Leave a comment