Archive

Posts Tagged ‘sp_who2’

SSAS–Who/What’s Killing My Server?

February 2, 2011 3 comments

When diagnosing a problem with the database engine, one of the first tools I use is to execute a sp_who2 command to determine who’s running what. Wouldn’t it be that nice if there was an equivalent command for SSAS. Using SSAS 2008 DMV’s (Yaniv Mor blog post), one can use linked servers to create a nearly equivalent command. I created a simple DB Engine stored procedure, sp_SSAS_who, that accomplishes what I needed it to do. Below is the script:

CREATE PROCEDURE dbo.sp_SSAS_who AS BEGIN

        -- *******************************************************

        -- Variable declarations

        -- *******************************************************

        SELECT        ssasSessions.SESSION_SPID AS [SPID],

                ssasSessions.SESSION_USER_NAME AS [User ID],

                ssasSessions.SESSION_CURRENT_DATABASE AS [Database],

                ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000, 2) AS [Command Time(in sec)],

                ssasCommands.COMMAND_START_TIME AS [Start],

                ssasCommands.COMMAND_END_TIME AS [End],

                ssasCommands.COMMAND_TEXT AS [MDX Command]

        FROM        OPENQUERY(SSAS_LINKED_SERVER, ') AS ssasSessions

                        SELECT        *

                        FROM        $system.DISCOVER_Sessions

                '

        LEFT        JOIN OPENQUERY(SSAS_LINKED_SERVER, ') AS ssasCommands

                        SELECT        *

                        FROM        $system.DISCOVER_Commands

                '

                ON        ssasSessions.SESSION_SPID = ssasCommands.SESSION_SPID

SSAS_LINKED_SERVER is a linked server to my SSAS instance. You’d need to create the definition of the linked server on your box before this would be useful. Ideally, I should parameterize this so that the name of the linked server is a variable. One of the bonuses of this approach is that I can execute sp_who2 and sp_SSAS_who in the same window to see what’s going on with both services.

I apologize if someone else has published this approach elsewhere. I Googled and can up with zilch so thought I would post.

Categories: SSAS Tags: