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:
- DMV queries return information about operations and resource consumption that are not available through other means.
- 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.
- Resultset of DMVs is table rather than XML so it’s not difficult to interpret the results.