About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo
Pyramid Analytics

Search

Pyramid Analytics
blank
Report Portal
blank

SSAS 2008 CTP6 – new DMV $SYSTEM. DISCOVER_ OBJECT_ ACTIVITY

March 18th, 2008 by Vidas Matelis

Today I was reading Chris Webb post “Visualising Analysis Services Trace Information in Reporting Services” where at the end he talks about tools to monitor SSAS, that made me thinking about SSAS 2008 DMVs again. So I went back to my SSAS 2008 installation to see what do we have new in CTP6. Just after release Darren Gosbell already blogged about new DMVs in his post “SSAS 2008: What’s new in the Schema Rowsets?“. But just after I started to run queries I realized how cool these new DMVs are. After quick test on them, I decided to blog today about my favorite DMV so far: $SYSTEM.DISCOVER_OBJECT_ACTIVITY.

The best thing about this new $SYSTEM.DISCOVER_OBJECT_ACTIVITY DMV is the fact that it provides resource usage per object since the start of the service. When querying $SYSTEM.DISCOVER_SESSIONS DMV, as soon as user disconnects, all information is lost. But this new DMV has aggregated statistic for all users since Analysis services start. So now quick queries can give you idea where Analysis Services works hardest.

First, BOL provides this information about fields in this DMV:

Field Description
OBJECT_PARENT_PATH The path to the parent of current object.
OBJECT_ID The ID of the object as defined at creation time
OBJECT_AGGREGATION_HIT The number of times an aggregation of the object has been hit since the start of the service.
OBJECT_AGGREGATION_MISS The number of times an existing aggregation, of the object, has not been missed (that is, has not been used) since the start of the service.
OBJECT_CPU_TIME_MS The CPU time, in milliseconds, consumed by the object since the beginning of the service.
OBJECT_DATA_VERSION The lineage number of the data in the object; this number increments each time the object is processed.
OBJECT_HIT The number of times the object has been hit in the cache since the start of the service.
OBJECT_MISS The number of times the object has been missed in the cache since the start of the service.
OBJECT_READ_KB The accumulated value of data read by the object since the start of the service, in kilobytes.
OBJECT_READS The accumulated number of read operations by the object since the start of the service.
OBJECT_ROWS_RETURNED The number of rows returned by the object to the caller since the start of the service.
OBJECT_ROWS_SCANNED The number of rows scanned by the object since the start of the service.
OBJECT_VERSION The metadata version number of the object; this number changes every time the object is altered.
OBJECT_WRITE_KB The accumulated value of data written by the object since the start of the service, in kilobytes.
OBJECT_WRITES The accumulated number of write operations by the object since the start of the service.

So now lets run some queries:

1. Question: Where SSAS spends most of CPU time?

Query:

 SELECT TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_READS AS Reads, OBJECT_ROWS_SCANNED AS [Rows]
   FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
  ORDER BY OBJECT_CPU_TIME_MS DESC; 

Result:

2. What measure group/partition needs more aggregations?

Query:

SELECT  TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_AGGREGATION_MISS AS MISS
   FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
  ORDER BY OBJECT_AGGREGATION_MISS DESC;

Results:

Similar query ordered by OBJECT_MISS column will show objects that are have most cache misses.

And object information is very detail – you can see info on dimension, MDX script, cube, partitions and even each aggregation. So there is a lot of information to analyze.

I got an error when I was trying to run query to return information about all aggregations:

SELECT *
  FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
 WHERE LEFT(OBJECT_ID,11) = ‘Aggregation’

Error was:

Executing the query …
Internal error: An unexpected exception occurred.
Execution complete 

Not a big deal, I am sure this will be fixed in the next CTP.

Conclusion: Very very useful DMV, and it can be used to build quite a few nice reports.

Question I have right now – can I actually query these DMVs as a SSAS cube from Excel or other front end? Can I somehow specify cube name as $System and slice and dice info in these rowsets? Anyone knows this?

I’ll keep testing this and other new DMVs and I’ll post my findings here in my blog.

Posted in SSAS 2008 - Katmai | 4 Comments »

4 Responses

  1. Darren Gosbell Says:

    Hi Vidas, unless you have heard from someone on the team that this internal error has been fixed already I would suggest that you log it on the connect site https://connect.microsoft.com/SQLServer/feedback (I would normally have just posted this issue myself, but MS are running a “Bug Bash” at the moment and you can go into the draw to win an Xbox 360)

  2. Vidas Matelis Says:

    Darren,

    Thanks for the tip. I submitted this as a bug:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=334089

    Vidas

  3. carl rabeler Says:

    no error in current build

  4. Retrieving Cube Metadata in Reports - Prologika (Teo Lachev's Weblog) Says:

    […] Gosbell provides a great overview of the SSAS rowsets here and Vidas Matelis gives more insights here. Thanks to the schema rowsets, getting a list of measures can't simpler. SELECT * from […]

XL Cubed