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:
|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?
SELECT TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_READS AS Reads, OBJECT_ROWS_SCANNED AS [Rows]
ORDER BY OBJECT_CPU_TIME_MS DESC;
2. What measure group/partition needs more aggregations?
SELECT TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_AGGREGATION_MISS AS MISS
ORDER BY OBJECT_AGGREGATION_MISS DESC;
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:
WHERE LEFT(OBJECT_ID,11) = ‘Aggregation’
Executing the query …
Internal error: An unexpected exception occurred.
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.