Share via

Introduction to Monitoring Analysis Services with SQL Server Profiler

  • Article

You can use SQL Server Profiler to monitor events generated by an instance of Microsoft SQL Server Analysis Services. By using SQL Server Profiler, you can do the following:

  • Monitor the performance of an instance of Analysis Services.

  • Debug Multidimensional Expressions (MDX) statements.

  • Identify MDX statements that run slowly.

  • Test MDX statements in the development phase of a project by stepping through statements to confirm that the code works as expected.

  • Troubleshoot problems in Analysis Services by capturing events on a production system and replaying them on a test system. This approach is useful for testing or debugging purposes and lets users continue to use the production system without interference.

  • Audit and review activity that occurred on an instance of Analysis Services. A security administrator can review any one of the audited events. This includes the success or failure of a login try and the success or failure of permissions in accessing statements and objects.

  • Display data about the captured events to the screen, or capture and save data about each event to a file or SQL Server table for future analysis or playback. When you replay data, you can rerun the saved events as they originally occurred, either in real time or step by step.

Using SQL Server Profiler

To use SQL Server Profiler to create or replay traces, you must be a member of the Analysis Services server role. If you are a member of the Analysis Services server role, you can start SQL Server Profiler from the Microsoft SQL Server program group on the Start menu.

When you use SQL Server Profiler, note the following:

  • Trace definitions are stored with the Analysis Services database by using the CREATE statement.

  • Multiple traces can be running at the same time.

  • Multiple connections can receive events from the same trace.

  • A trace can continue when Analysis Services stops and restarts.

    Note

    Passwords are not revealed in trace events, but are replaced by ****** in the event.

For optimal performance, use SQL Server Profiler to monitor only those events in which you are most interested. Monitoring too many events adds overhead and can cause the trace file or table to grow very large, especially when you monitor over a long period of time. In addition, use filtering to limit the amount of data that is collected and to prevent traces from becoming too large.

See Also

Analysis Services Trace Events
Create Profiler Traces for Replay (Analysis Services)