Microsoft SQL Server 2012 Bible

Reviewing Query Performance with SQL Profiler

Efficient and fast queries are the cornerstone to a successful Analysis Services solution. Key for delivering the expected performance is ensuring that the system is optimized.

The main tool used to monitor the performance of Analysis Services is SQL Server Profiler. SQL Server Profiler contains a vast array of Analysis Services events that can be used to monitor performance, determine poorly performing MDX statements, step through MDX statements, replay events on another system, and audit activity. This section provides a brief introduction to using SQL Server Profiler for monitoring Analysis Services.

Event categories that can prove useful for tracing the Analysis Services server activity and investigating the performance of MDX queries submitted to the server to process user requests include:

• Command events: These provide insight into the actual types of statements issued to perform actions.
• Discovery events: These detail requests for metadata about server objects such as open connections.
• Error and Warning events: These events alert the developer to issues with building and deploying Analysis Services databases.
• Notification events: These events notify the user through tracing that certain query actions are taking place.
• Query events: These events notify the developer or administrator that query steps are taking place, for example, query begin, query end.
• Query Processing: These provide detailed insights into how a query was prepared ...