Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

13.2. Resource and Activity Monitoring

Analysis Services 2008 has added new schema rowsets and extended some existing schema rowsets to help better enable monitoring of server usage and resources. As with relational SQL Server, schema rowsets provide information about the contents and state of the server and its databases. Analysis Services 2005 has schema rowsets that provide information about the content of the server, but have limited information about the state of the server. However, the new and improved schema rowsets in Analysis Services 2008 tell much more about the state of the server. These schema rowsets can show you the connections on a machine, the objects those connections use, the memory those objects consume, locks held by sessions, and other valuable information for understanding how your server is performing and diagnosing issues.

In addition to adding and extending the set of schema rowsets, Analysis Services 2008 has made it much simpler to query schema rowsets. In Analysis Services 2005 you needed a special tool such as the OLE DB rowset viewer or you needed to be comfortable writing and sending XMLA Discover queries in order to get back schema rowsets. In Analysis Services 2008, these schema rowsets have been exposed as Dynamic Management Views (DMVs). DMVs allow you to write simple SQL statements to query schema rowsets. To try this, open SQL Server Management Studio and connect to an Analysis Services database. Then open a new MDX query window and try the ...