{"id":43,"date":"2008-03-18T23:17:11","date_gmt":"2008-03-19T04:17:11","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/43_ssas-2008-ctp6-new-dmv-systemdiscover_object_activity"},"modified":"2008-04-07T18:11:52","modified_gmt":"2008-04-07T23:11:52","slug":"ssas-2008-ctp6-new-dmv-systemdiscover_object_activity","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/43_ssas-2008-ctp6-new-dmv-systemdiscover_object_activity","title":{"rendered":"SSAS 2008 CTP6 – new DMV $SYSTEM. DISCOVER_ OBJECT_ ACTIVITY"},"content":{"rendered":"

Today I was reading Chris Webb post\u00a0“Visualising Analysis Services Trace Information in Reporting Services<\/a>” where at the end he talks about tools to monitor SSAS, that made me thinking about SSAS 2008 DMVs again.\u00a0So I went back to my SSAS 2008 installation to see what do we have new in CTP6. Just after release Darren Gosbell\u00a0already blogged about new DMVs in his post “SSAS 2008: What’s new in the Schema Rowsets?<\/a>“. But just\u00a0after 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.<\/p>\n

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.<\/p>\n

First, BOL provides this information about fields in this DMV:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Field<\/td>\nDescription<\/td>\n<\/tr>\n
OBJECT_PARENT_PATH<\/td>\nThe path to the parent of current object.<\/td>\n<\/tr>\n
OBJECT_ID<\/td>\nThe ID of the object as defined at creation time<\/td>\n<\/tr>\n
OBJECT_AGGREGATION_HIT<\/td>\nThe number of times an aggregation of the object has been hit since the start of the service.<\/td>\n<\/tr>\n
OBJECT_AGGREGATION_MISS<\/td>\nThe 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.<\/td>\n<\/tr>\n
OBJECT_CPU_TIME_MS<\/td>\nThe CPU time, in milliseconds, consumed by the object since the beginning of the service.<\/td>\n<\/tr>\n
OBJECT_DATA_VERSION<\/td>\nThe lineage number of the data in the object; this number increments each time the object is processed.<\/td>\n<\/tr>\n
OBJECT_HIT<\/td>\nThe number of times the object has been hit in the cache since the start of the service.<\/td>\n<\/tr>\n
OBJECT_MISS<\/td>\nThe number of times the object has been missed in the cache since the start of the service.<\/td>\n<\/tr>\n
OBJECT_READ_KB<\/td>\nThe accumulated value of data read by the object since the start of the service, in kilobytes.<\/td>\n<\/tr>\n
OBJECT_READS<\/td>\nThe accumulated number of read operations by the object since the start of the service.<\/td>\n<\/tr>\n
OBJECT_ROWS_RETURNED<\/td>\nThe number of rows returned by the object to the caller since the start of the service.<\/td>\n<\/tr>\n
OBJECT_ROWS_SCANNED<\/td>\nThe number of rows scanned by the object since the start of the service.<\/td>\n<\/tr>\n
OBJECT_VERSION<\/td>\nThe metadata version number of the object; this number changes every time the object is altered.<\/td>\n<\/tr>\n
OBJECT_WRITE_KB<\/td>\nThe accumulated value of data written by the object since the start of the service, in kilobytes.<\/td>\n<\/tr>\n
OBJECT_WRITES<\/td>\nThe accumulated number of write operations by the object since the start of the service.<\/td>\n<\/tr>\n<\/table>\n

So now lets run some queries:<\/p>\n

1. Question: Where SSAS spends most of CPU time?<\/p>\n

Query:<\/p>\n

\u00a0SELECT TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_READS AS Reads, OBJECT_ROWS_SCANNED AS [Rows]
\n\u00a0\u00a0 FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
\n\u00a0 ORDER BY OBJECT_CPU_TIME_MS DESC;\u00a0<\/p><\/blockquote>\n

Result:<\/p>\n

<\/p>\n

2. What measure group\/partition needs more aggregations?<\/p>\n

Query:<\/p>\n

SELECT\u00a0 TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_AGGREGATION_MISS AS MISS
\n\u00a0\u00a0 FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
\n\u00a0 ORDER BY OBJECT_AGGREGATION_MISS DESC;<\/p><\/blockquote>\n

Results:<\/p>\n

<\/p>\n

Similar query ordered by OBJECT_MISS column will show objects that are have most cache misses.<\/p>\n

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.<\/p>\n

I got an error when I was trying to\u00a0run query to return information about all aggregations:<\/p>\n

SELECT *
\n\u00a0 FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
\n\u00a0WHERE LEFT(OBJECT_ID,11) = ‘Aggregation’<\/p><\/blockquote>\n

Error was:<\/p>\n

Executing the query …
\nInternal error: An unexpected exception occurred.
\nExecution complete\u00a0<\/p><\/blockquote>\n

Not a big deal, I am sure this will be fixed in the next CTP.<\/p>\n

Conclusion: Very very useful DMV, and it can be used to build quite a few nice reports.<\/p>\n

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?<\/p>\n

I’ll keep testing this and other new DMVs and I’ll post my findings here in my blog.<\/p>\n","protected":false},"excerpt":{"rendered":"

Today I was reading Chris Webb post\u00a0“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.\u00a0So I went back to my SSAS 2008 installation to see what do we have new in CTP6. Just after release Darren […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/43"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=43"}],"version-history":[{"count":0,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/43\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}