SSAS - What is running ?
I saw a tweet today from respected blogger john sansom asking how a SQL DBA should attempt to performance tune SSAS.
I've been through a similar scenario - and one of the tools I developed as part of my troubleshooting was my own 'SSAS version' of sp_who.
I've written a stored procedure which I run on a SQL Server instance, that connects to an SSAS instance via a linked server, I then wrote an SSRS report to use this stored proc to allow developers and support staff access to SSAS cube activity.
It's quite a basic/simple solution but it does help expose what is running - I must say on a fairly low-use SSAS server. I don't know how well this scales on a heavily used SSAS implementation.
To add an SSAS linked server first of all to your SQL Server..
EXEC master.dbo.sp_addlinkedserver @server = N'SSAS_LINKED_SERVER', @provider=N'MSOLAP', @datasrc=N'ISSAS ServerName'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS_LINKED_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'Account to run command via' ,@rmtpassword='########'
Then I created a stored procedure :-
CREATE PROC [dbo].[SSAS_whoIsActive]
SELECT ssasSessions.SESSION_SPID AS [SPID],
ssasSessions.SESSION_USER_NAME AS [User ID],
ssasSessions.SESSION_CURRENT_DATABASE AS [Database],
ssasConnections.connection_host_name AS 'Client IP address',
ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000, 2) AS [Command Time(in sec)],
ssasCommands.COMMAND_START_TIME AS [Command Start Time],
ssasCommands.COMMAND_END_TIME AS [Command End Time],
ssasCommands.COMMAND_TEXT AS [MDX Command],
connection_host_application AS 'Application',
CHARINDEX('</Process>',ssasCommands.COMMAND_TEXT) AS ProcessRunning
FROM OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_sessions') AS ssasSessions
LEFT JOIN OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_commands') AS ssasCommands ON ssasSessions.SESSION_SPID = ssasCommands.SESSION_SPID
LEFT JOIN OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_connections') AS ssasConnections ON ssasSessions.SESSION_connection_id = ssasConnections.connection_idORDER BY [Command Start Time] DESC
I use the 'ProcessRunning' column to idenitfy if a cube process command is running, and then display this MDX command in a different colour (red) in my report.
This can be done by adding an expression to the report 'MDX Command' text box properties. =IIF(Fields!ProcessRunning.Value > 0, "Red", "Black")
The MDX command column can sometimes be very large in this report output - it can be quite a shock for a DBA to see how complicated MDX generated by SSAS sometimes can be ! I usually change the font size on this output column to be 8pt.
Note my sproc is named in honour of the famous sp_whoIsActive written by Adam Machanic.