Monitoring Analysis Services
You can monitor the performance of Microsoft SQL Server 2005 Analysis Services (SSAS) by using SQL Server Profiler or Performance, an application sometimes referred to as PerfMon. SQL Server Profiler provides you a window on what the server is doing during processing and query resolution. Performance gives you a view of product status as indexed through certain counters, which are discussed in the next section.
Note
For more information about monitoring, see Project REAL M o nit o ring and Instrumentation, and the section “Tuning Server Resources” in the SQL Server 2005 Analysis Services Performance Guide.
Performance
Performance is a Microsoft Management Control (MMC) snap-in that tracks resource usage. You can start this MMC snap-in by typing in PerfMon at the command prompt. It is also available from Control Panel by clicking Administrative Tools, then Performance. Performance lets you monitor server and process performance and activity by using predefined objects and counters, and to monitor events by using user-defined counters. Performance (called Performance Monitor in Microsoft Windows NT 4.0) collects counts instead of data about the events, for example, memory usage, number of active transactions, or CPU activity. You can also set thresholds on specific counters to generate alerts that notify operators.
Performance works on Windows Server 2003, Windows XP, Windows 2000, or Windows NT. Performance can monitor (remotely or locally) an instance of Analysis Services or SQL Server running on any of these operating systems.
To see the description of any counter that can be used with SQL Server 2005 Analysis Services, in Performance, open the Add Counters dialog box, select a performance object, and then click Explain. The most important counters are CPU usage, memory usage, disk IO rate. It is recommended you start with these important counters, and move to more detailed counters when you have a better idea of what else could be improved through monitoring. For more information about how to use Performance, see your Windows documentation.
SQL Server Profiler
SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction, and it captures data about those events, thus enabling you to monitor server and database activity (for example, user queries or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on the same or another Analysis Services instance to see exactly what happened. You can replay events in real time or on a step-by-step basis. It is also very useful to run the trace events along with the Performance counters on the same machine. The profiler can correlate these two based on time and display them together along a single timeline. Trace events will give you more details while Performance counters give you an aggregate view. For information about how to create and run traces, see Creating Traces for Replay.
In This Section
The following topic explains the various aspects of monitoring Analysis Services:
| Topic | Description |
|---|---|
Shows how trace events are used by database administrators to monitor service activity and to playback prior sessions. Also, includes sub-topic on relevant Event Classes for programmatic implementations. |
See Also
Other Resources
Administering Analysis Services