SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

In my last post I listed changes that are expected in SSAS 2008 next CTP 4. But it looks like none of the changes made into July CTP. They probably were postponed last minute. Even new Microsoft documentation (read Chris Web  blog) lists some of the features as present. For example block computation is listed in documentation, but just today in webcast I saw block computation example where in CTP4 it took query to execute 1min, and in new SSAS build the same query come back in a few seconds. So I hope that all changes listed in my previous post will be in the next CTP5, that is targeted for September 18th release (during PASS conference).

First of all, we should expect enhancements to aggregation designer. Algorithm that builds aggregations will be improved, there will be support for manual edit/create/delete of aggregations and we should be able to see what aggregates were designed. Also aggregation designer will have built-in validations for optimal design assistance. In my opinion this is very welcome change as I absolutely would love to have much better control on what aggregations are build.

And the another new enhancement will be DMV (Data Dynamic Management Views) that we will be able to use to access information about Analysis Services. As I understood, there will be something like “Default Resource cube”, that DMV will query and come back with useful information. During webcast presenter demonstrated 2 views:

• $system.discover_connections
• $system.discover_sessions

To access DMV, you simply write SELECT statement:

SELECT * FROM $system.discover_connections

Below are example of queries that list just some (!) of the columns available in these DMVs:

SELECT
connection_id
, connection_user_name
, connection_host_application
, connection_start_time
FROM $system.discover_connections

SELECT
session_id
, session_spid
, session_user_name
, session_last_command
, session_start_time
, session_cpu_time_ms
, session_reads
, session_writes
, session_status
, session_current_database
, session_used_memory
, session_properties
, session_start_time
, session_elapsed_time_ms
, session_last_command_start_time
, session_last_command_end_time
FROM $system.discover_sessions

As you can see there is a lot of info in session records. For example it is quite easy to get session ID and resources used by the session.

To list SSAS sessions that are currently active you can filter session DMV using WHERE session_status = 1:

To kill session, get session id from DMV and execute XMLA:

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>
  <SPID>###HereGoesSessionID###</SPID>
</Cancel>

Note: In 2008 cancel is reliable and should kill session instantly.

To get a list of columns available in DMV you can execute following queries: 

select * FROM $system.DBSCHEMA_columns WHERE table_name = ‘discover_sessions’
select * FROM $system.DBSCHEMA_columns WHERE table_name = ‘discover_connections’

I am sure DMVs will be very big help in finding runaway queries and monitoring SSAS.