Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

15.3. Performance Analysis and Tuning Tools

Analysis Services 2008 includes significant enhancements targeted at getting the best query performance. Improvements include tools to help in designing cubes, subspace computation optimization, caching enhancements, and improved writeback query performance (you learn about this later in the chapter). You might still have queries that are not performing as expected, however, due to cube design or the way MDX has been written. To analyze and improve your query performance, you can use tools that will help you analyze the performance of your queries and then tune them to get the best performance from Analysis Services 2008.

15.3.1. SQL Server Profiler

SQL Server Profiler is a tool used to trace operations on the SQL Server and Analysis Services database engines. SQL Server Profiler is the primary performance analysis tool used to debug performance bottlenecks in SQL Server (including Analysis Services). The ability to trace Analysis Services operations through SQL Server Profiler was first introduced in SQL Server 2005.

Analysis Services exposes the commands sent to it as well as internal operations that occur within the server through what are called events. Some examples of these events are Command Begin, Command End, Query Begin, and Query End. Each event has properties associated with it such as start time, end time, and the user sending the query. These properties are shown as columns in the tool. SQL Server Profiler requests these ...