SQL Server Analysis Services (SSAS) is a powerful tool used by businesses to analyze and process large amounts of data. However, it is important to ensure that the database is responsive and running efficiently. In this article, we will discuss how to monitor and troubleshoot SSAS using SQL Server.

One common concern with SSAS is slow performance. If the database is slow, it is important to identify the cause of the slowness. Are there any locks? What commands are being run and by whom? How much of each resource are these queries consuming? By answering these questions, you can adjust the resources dedicated to SSAS and optimize its performance.

Unfortunately, unlike SQL Server, SSAS does not have a built-in Activity Monitor or the ability to write MDX queries to gather this information. However, with the help of SQL Server, we can still monitor SSAS effectively.

The script provided below allows you to view the current activity in your SSAS database. All you need is a linked server to your SSAS server. Simply replace <OLAPLINK> with your SSAS server name and execute the script.

DECLARE @TZOffset INT, @IntervalMinutes INT, @CurTime DATETIME
SET @TZOffset = 6
SET @IntervalMinutes = 240
SET @CurTime = DATEADD(hh, @TZOffset, GETDATE())

-- Locks at current point in time. Should be as minimum as possible.
SELECT CAST([SPID] AS INT) [SessionId], CAST([LOCK_ID] AS VARCHAR(MAX)) [LockId], * INTO #Locks
  FROM OPENQUERY(<OLAPLINK>, 'SELECT [SPID], [LOCK_ID], [LOCK_TRANSACTION_ID], [LOCK_OBJECT_ID], [LOCK_STATUS], [LOCK_TYPE], [LOCK_CREATION_TIME], [LOCK_GRANT_TIME] FROM [$SYSTEM].[DISCOVER_LOCKS]')

-- current set of pending transactions on the system
SELECT CAST([TRANSACTION_SESSION_ID] AS VARCHAR(MAX)) [SessionGUID], * INTO #Transactions
  FROM OPENQUERY(<OLAPLINK>, 'SELECT * FROM [$SYSTEM].[DISCOVER_TRANSACTIONS]')

-- Resource usage and activity at the moment
SELECT CAST([SESSION_SPID] AS INT) [SessionId], CAST([SESSION_ID] AS VARCHAR(MAX)) [SessionGUID], CAST([OBJECT_PARENT_PATH] AS VARCHAR(MAX))+'.'+CAST([OBJECT_ID] AS VARCHAR(128)) [Object], * INTO #CommandObjects
  FROM OPENQUERY(<OLAPLINK>, 'SELECT * FROM [$SYSTEM].[DISCOVER_COMMAND_OBJECTS]')

-- Resource usage and activity about currently executing or last executed commands in the opened connections
SELECT CAST([SESSION_SPID] AS INT) [SessionId], * INTO #Commands
  FROM OPENQUERY(<OLAPLINK>, 'SELECT * FROM [$SYSTEM].[DISCOVER_COMMANDS]')

-- MDX/command used by each user, which SSAS database/cube they are using, session reads and writes, how much CPU and memory is used, last command
SELECT CAST([SESSION_CONNECTION_ID] AS INT) [ConnectionId], CAST([SESSION_SPID] AS INT) [SessionId], CAST([SESSION_ID] AS VARCHAR(MAX)) [SessionGUID], * INTO #Sessions
  FROM OPENQUERY(<OLAPLINK>, 'SELECT * FROM [$SYSTEM].[DISCOVER_SESSIONS]')

-- who's connecting to the server, since when, for how long, from which PC (IP) and using what client
SELECT CAST([CONNECTION_ID] AS INT) [ConnectionId], * INTO #Connections
  FROM OPENQUERY(<OLAPLINK>, 'SELECT * FROM [$SYSTEM].[DISCOVER_CONNECTIONS]')

-- results
SELECT [c].[ConnectionId], [se].[SessionId], [se].[SessionGUID], [cm].[COMMAND_TEXT] [Command], [c].[CONNECTION_USER_NAME] [User], [cm].[COMMAND_CPU_TIME_MS]/1000 [CPUinSec],
       CASE [l].[LOCK_TYPE] WHEN 0 THEN 'No lock'
                            WHEN 1 THEN 'Inactive session; does not interfere with other locks'
                            WHEN 2 THEN 'Read lock during processing'
                            WHEN 4 THEN 'Write lock during processing'
                            WHEN 8 THEN 'Commit lock, shared'
                            WHEN 10 THEN 'Commit lock, exclusive'
                            WHEN 20 THEN 'Abort at commit progress'
                            WHEN 40 THEN 'Commit in progress'
                            WHEN 80 THEN 'Invalid lock'
                            ELSE '' END [LockType], DATEADD(HH, -@TZOffset, [l].[LOCK_GRANT_TIME]) [LockTime], [l].[LOCK_OBJECT_ID] [ObjectLocked],
       [cm].[COMMAND_READS] [CommandReads], [cm].[COMMAND_WRITES] [CommandWrites], ([cm].[COMMAND_READ_KB]+[cm].[COMMAND_WRITE_KB])/1024/1024 [CommandPhysicalIOinGB],
       [se].[SESSION_USED_MEMORY]/1024/1024 [SessionMemoryinGB], CASE [se].[SESSION_STATUS] WHEN 0 THEN 'Idle' WHEN 1 THEN 'Active' WHEN 2 THEN 'Blocked' WHEN 3 THEN 'Cancelled' END [SessionStatus],
       DATEADD(HH, -@TZOffset, [t].[TRANSACTION_START_TIME]) [TransStartTime], [c].[CONNECTION_HOST_NAME] [Host], [se].[SESSION_CURRENT_DATABASE] [Database],
       [c].[CONNECTION_HOST_APPLICATION] [Program], [se].[SESSION_PROPERTIES] [Properties], DATEADD(HH, -@TZOffset, [cm].[COMMAND_START_TIME]) [StartTime],
       DATEADD(HH, -@TZOffset, [se].[SESSION_START_TIME]) [LoginTime], DATEADD(HH, -@TZOffset, @CurTime) [CollectionTime], [co].[Object], [co].[OBJECT_ROWS_SCANNED] [RowsScanned],
       [co].[OBJECT_ROWS_RETURNED] [RowsReturned]
  FROM #Connections [c]
INNER JOIN #Sessions [se] ON [se].[ConnectionId] = [c].[ConnectionId]
INNER JOIN #Commands [cm] ON [cm].[SessionId] = [se].[SessionId] AND [cm].[COMMAND_START_TIME] > DATEADD(MI, -@IntervalMinutes, @CurTime)
INNER JOIN #CommandObjects [co] ON [co].[SessionId] = [cm].[SessionId] AND ([co].[OBJECT_ROWS_SCANNED] > 0 OR [co].[OBJECT_ROWS_RETURNED] > 0)
LEFT JOIN #Transactions [t] ON [t].[SessionGUID] = [se].[SessionGUID]
LEFT JOIN #Locks [l] ON [l].[LockId] = (SELECT TOP 1 [l2].[LockId] FROM #Locks [l2] WHERE [l2].[SessionId] = [se].[SessionId] ORDER BY [l2].[LOCK_TYPE] DESC)
ORDER BY [ConnectionId], [Object]

DROP TABLE #Connections
DROP TABLE #Sessions
DROP TABLE #Commands
DROP TABLE #CommandObjects
DROP TABLE #Transactions
DROP TABLE #Locks

-- memory usage for each object
SELECT * INTO #ObjectMemoryUsage FROM OPENQUERY(<OLAPLINK>, 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC')
SELECT * FROM #ObjectMemoryUsage
DROP TABLE #ObjectMemoryUsage

The script above retrieves information about locks, transactions, resource usage, and activity in your SSAS database. It also provides details about the MDX/command used by each user, session reads and writes, CPU and memory usage, and more.

By executing this script, you can gain valuable insights into the activity on your SSAS database and troubleshoot any performance issues. The results can be stored and analyzed over time to identify trends and optimize your SSAS environment.

It is worth noting that the script allows you to adjust the time zone offset and the interval of commands to be included in the results. This flexibility ensures that you can view the activity in your local time zone and focus on recent commands.

In addition to monitoring activity, the script also provides information about memory usage for each object in your SSAS database. This can help you identify any objects that are consuming excessive memory and optimize their usage.

In conclusion, monitoring and troubleshooting SSAS using SQL Server can provide valuable insights into the performance and activity of your SSAS database. By executing the script provided in this article, you can gather important information and optimize your SSAS environment for better performance.