Início > OLAP Statistics and Internal Monitoring > SSAS–Who/What’s Killing My Server?

SSAS–Who/What’s Killing My Server?

Pingback from:  http://martinmason.wordpress.com/category/ssas/

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:

 

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 [Executed Time(in seconds)],

ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000/60, 2) AS [executed Time(in minutes)],

ROUND(CAST(SESSION_LAST_COMMAND_ELAPSED_TIME_MS AS int)/1000, 2) AS [Waiting_for_retrieval_sec],

ROUND(CAST(SESSION_LAST_COMMAND_ELAPSED_TIME_MS AS int)/1000/60, 2) AS [Waiting_for_retrieval_min],              CONVERT(varchar(5), ssasCommands.COMMAND_START_TIME, 108) AS [StartHour],   CONVERT(varchar(5), ssasCommands.COMMAND_END_TIME, 108) AS [EndHour],

ssasCommands.COMMAND_TEXT AS [MDX Command]

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                                 order BY SESSION_LAST_COMMAND_ELAPSED_TIME_MS desc, ssasCommands.COMMAND_CPU_TIME_MS desc

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.

 

  1. Ainda sem comentários.
  1. No trackbacks yet.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: