Monitoring Cube Performance and Usage - Expert Cube Development with SSAS Multidimensional Models - page 344

The result of this query will display a large number of columns; the following table
shows just the first few columns from a sample Resultset :

OBJECT_PARENT_PATH OBJECT_

ID

OBJECT_CPU_
TIME_MS

OBJECT_
READS

OBJECT_
READ_KB

HPVISTA\K8.Databases.
Adv Works -
DW.Dimensions

Date 62 1011 265

HPVISTA\K8.Databases.
Adv Works -
DW.Dimensions

Products 62 973 232

HPVISTA\K8.Databases.
Adv Works -
DW.Dimensions

Employee 46 747 278

DMV queries support only a subset of standard SQL. We can use
WHERE conditions, DISTINCT and ORDER BY , but constructs such
as GROUP BY , JOIN , LIKE , CAST, and CONVERT are not supported.
However, we can also project data using expressions and calling
some functions, such as Format .

Unfortunately, the documentation on DMVs is incomplete, although there is a lot
of useful information on Vidas Matelis' blog at http://tinyurl.com/vidasdmv ,
Darren Gosbell's blog at http://tinyurl.com/darrendmv , and Vincent Rainardi's
blog at http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-
management-view/ . We can return a list of schema rowsets that can be queried
through DMVs by running the following query:

SELECT * FROM
$System.Discover_Schema_Rowsets

Despite the lack of documentation, DMVs are very useful for monitoring Analysis
Services. When we want to monitor processing activity through DMVs, we probably
want to see which objects are consuming the most memory and CPU. Each allocation
in Analysis Services can be one of two types: shrinkable and non-shrinkable. The
shrinkable objects that consume the most memory can be returned using a query
such as this:

SELECT *
FROM $System.DISCOVER_OBJECT_MEMORY_USAGE
ORDER BY OBJECT_MEMORY_SHRINKABLE DESC