Hi All,

As you must have heard or started using DMVs (Dynamic Management View) available in SQL Server to get information about local server operations and server health. But today I would like to focus specially on DMVs for monitoring SSAS (SQL Server Analysis Services) and dig-down to find out how powerful it can be.

DMVs for SSAS are known as “Analysis Services Dynamic Management View“. We can query these views by simple T-SQL SELECT statement. When use these views, they exposes analysis services schema rowsets in form of table, hence information presented will become self-explanatory.

Major benefits of using Analysis Services DMVs:

1. DMV queries return information about operations and resource consumption that are not available through other means.
2. DMV queries are an alternative to running XMLA Discover commands. For most administrators, writing a DMV query is simpler because the query syntax is based on SQL.
3. Resultset of DMVs is table rather than XML so it’s not difficult to interpret the results.

You can write these SELECT statements in DMX window and not in SQL Query window as we run these queries on SSAS and not on relational databases.

Note: SSAS DMVs are available in all versions after SQL Server 2005. They are not available in SQL Server 2000 & 2005.

To use the following DMVs, you can run a DMV query by using a SELECT statement and the $System schema with an XML/A schema rowset.

E.g. how to use following DMVs.
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

List of DMVs:
•    DBSCHEMA_CATALOGS
•    DBSCHEMA_COLUMNS
•    DBSCHEMA_PROVIDER_TYPES
•    DBSCHEMA_TABLES
•    DISCOVER_CALC_DEPENDENCY
•    DISCOVER_COMMAND_OBJECTS
•    DISCOVER_COMMANDS
•    DISCOVER_CONNECTIONS
•    DISCOVER_CSDL_METADATA
•    DISCOVER_DB_CONNECTIONS
•    DISCOVER_DIMENSION_STAT
•    DISCOVER_ENUMERATORS
•    DISCOVER_INSTANCES
•    DISCOVER_JOBS
•    DISCOVER_KEYWORDS
•    DISCOVER_LITERALS
•    DISCOVER_LOCKS
•    DISCOVER_MEMORYGRANT
•    DISCOVER_MEMORYUSAGE
•    DISCOVER_OBJECT_ACTIVITY
•    DISCOVER_OBJECT_MEMORY_USAGE
•    DISCOVER_PARTITION_DIMENSION_STAT
•    DISCOVER_PARTITION_STAT
•    DISCOVER_PERFORMANCE_COUNTERS
•    DISCOVER_PROPERTIES
•    DISCOVER_SCHEMA_ROWSETS
•    DISCOVER_SESSIONS
•    DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
•    DISCOVER_STORAGE_TABLE_COLUMNS
•    DISCOVER_STORAGE_TABLES
•    DISCOVER_TRACE_COLUMNS
•    DISCOVER_TRACE_DEFINITION_PROVIDERINFO
•    DISCOVER_TRACE_EVENT_CATEGORIES
•    DISCOVER_TRACES
•    DISCOVER_TRANSACTIONS
•    DISCOVER_XEVENT_TRACE_DEFINITION
•    DMSCHEMA_MINING_COLUMNS
•    DMSCHEMA_MINING_FUNCTIONS
•    DMSCHEMA_MINING_MODEL_CONTENT
•    DMSCHEMA_MINING_MODEL_CONTENT_PMML
•    DMSCHEMA_MINING_MODEL_XML
•    DMSCHEMA_MINING_MODELS
•    DMSCHEMA_MINING_SERVICE_PARAMETERS
•    DMSCHEMA_MINING_SERVICES
•    DMSCHEMA_MINING_STRUCTURE_COLUMNS
•    DMSCHEMA_MINING_STRUCTURES
•    MDSCHEMA_CUBES
•    MDSCHEMA_DIMENSIONS
•    MDSCHEMA_FUNCTIONS
•    MDSCHEMA_HIERARCHIES
•    MDSCHEMA_INPUT_DATASOURCES
•    MDSCHEMA_KPIS
•    MDSCHEMA_LEVELS
•    MDSCHEMA_MEASUREGROUP_DIMENSIONS
•    MDSCHEMA_MEASUREGROUPS
•    MDSCHEMA_MEASURES
•    MDSCHEMA_MEMBERS
•    MDSCHEMA_PROPERTIES
•    MDSCHEMA_SETS

Notes:
•    You must have system administrator permissions on the Analysis Services instance to query DMVs.
•    Not all schema rowsets have a DMV interface. So, if a DMV is not available for a given rowset, the server returns the following error: “The <schemarowset> request type was not recognized by the server”. All other errors point to problems with the syntax. You can find more details on MSDN.

I hope you will enjoy working with these cool DMVs.
Thanks, Khilit