Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Performance Monitoring
Starting with MSAS 2005, we can use SQL Profiler to monitor server events in great detail. Profiler is by far the most useful tool for learning what is happening under the hood—how MSAS processes various objects and how storage engine and formula engine resolve queries. In addition to collecting traces using Profiler’s graphical user interface, you can also start and stop traces using XMLA commands. Profiler traces can be recorded on a production server and replayed on test servers to validate performance tuning results. MSAS records a Flight Recorder trace—a default trace—which can be extremely helpful for troubleshooting issues that have already taken place by the time you get a call (complaints) from irate users.
MSAS 2008 introduces dynamic management views (DMVs) that allow monitoring connections, sessions, transactions, commands, locking and much more through simple SELECT statements. In fact, some of this information was available with MSAS 2005, but you had to use a sample tool called Activity Viewer or submit XMLA discover commands and interpret their output. You can obtain Activity Viewer tool from http://www.codeplex.com, and the tool is very easy to use. However, DMVs offer much additional functionality and flexibility for implementing your own monitoring. Some of the frequently used DMVs include:
$system.discover_connections $system.discover_sessions $system.discover_commands $system.discover_object_memory_usage $system.discover_object_activity $system.discover_locks
You can query each of these DMVs through simple SELECT * FROM DMV_name statements.
You can collect MSAS performance counters using the Windows Perfmon utility to monitor and gain valuable insight into inner workings of processing and querying activity. For example, you could examine “number of bulk-mode evaluation nodes” and “number of cell-by-cell evaluation nodes” counters under the MDX group to see if your MDX queries use the efficient calculation mode.
If you don’t care to analyze details of querying/processing performance but need to quickly obtain MSAS resource utilization values, you can use Task Manager and look for the msmdsrv.exe process.