Using Profiler to Analyze Performance

SQL Server Profiler is a tool to trace operations on SQL Server and Analysis Services. This is the first release in which you have the ability to trace operations on Analysis Services through the well-known Profiler. Analysis Services exposes the commands sent to it as well as internal operations that occur within the server through what are called Events. For example, you have Events such as Command Begin, Command End, Query Begin, and Query End. Each Event has several properties associated with it such as start time, end time, and user sending the query. These properties as exposed as Event columns. SQL Profiler requests these events and event column values through a trace command to the Server. Analysis Services periodically sends the events to the clients who have subscribed to a trace. SQL Profiler shows the events and event column values in a matrix, only some columns of which might be applicable. Only administrators on Analysis Services can trace Analysis Services events. To learn more about how to use profiler follow the steps below.

  1. Make sure you are an administrator on Analysis Services. You can connect to Analysis Services through SSMS and use the Analysis Services server properties dialog to add users as administrators of Analysis Services.

  2. Launch SQL Server Profiler from Startimage from bookAll Programsimage from bookMicrosoft SQL Server 2005image from book Performance Toolsimage from bookSQL Server Profiler.

  3. You will now see the SQL Server Profiler application. Create a new trace by selecting Fileimage from book New Trace.

  4. You will see the Connect to Server dialog shown in Figure 13-40. Select the Server type as Analysis Services and type the machine name of your Analysis Services instance.

    image from book
    Figure 13-40

  5. In the Trace properties dialog type the name of the trace as FirstTrace. SQL Profiler provides three templates of traces with pre-selected events to trace. Select the Standard template as shown in Figure 13-41.

    image from book
    Figure 13-41

  6. To see the various events and event columns selected for the standard template click on the Events Selection tab. You will see the various events and the event columns that have been selected for the standard template as shown in Figure 13-42. This page only shows the events that have been selected. To see all the events and event columns supported by Analysis Services you can click on the check boxes Show all events and Show all columns. Familiarize yourself with the various events and click Run.

    image from book
    Figure 13-42

  7. You will now be able to see the various event columns within Profiler. To see certain processing operations events open the Adventure Works DW sample project and deploy it to the Analysis Services instance. You can see the various events during the processing including the processing duration of each object as shown in Figure 13-43. This will help you to identify information such as dimension processing duration, duration of processing partitions, as well as overall processing time of the entire database. You can also see the relational queries sent to data source to retrieve data during processing for each object.

    image from book
    Figure 13-43

    After the processing has completed for the Adventure Works cube send the following MDX query.

         select {[Measures].[Sales Amount],[Measures].[Gross Profit]} on 0,     [Customer].[Customer Geography].members on 1     from [Adventure Works] 

    You can see the Query events in the SQL Profiler as shown in Figure 13-44 along with the duration. One of the information that would be interesting is to notice the subcubes accessed by this query and how long each subcube query took. The subcubes events indicate the internal part of the cubes that are utilized to retrieve data from disk. You can utilize the subcube information to build custom aggregations. Please refer to download samples of this chapter on building custom aggregations.

    image from book
    Figure 13-44

  8. Assume you build aggregations using usage based optimization wizard. You would want to learn if the aggregations are being utilized. Analysis Services provides events to help you identify if the aggregations are hit. Create a new Trace and switch to the Events Selection tab. Check the box next to Show all events. Expand the events under the event class Query Processing. You can see the various events that are provided by Analysis Services as shown in Figure 13-45.

If you select the events under Query Processing and monitor the trace events you will be able to obtain information such as if Non Empty code path is being utilized, if MDX script is being evaluated, if data is retrieved from Aggregations or from existing cache. These events will help you identify more details about the queries sent by the users as well as total duration. You can later analyze the MDX queries, build usage based optimization for long running queries or try to optimize the long run MDX queries. As an administrator you definitely need to know a little bit about the internals of the server to fine tune it. We believe the ability to trace Analysis Services through SQL Profiler will help with that, so try it out.

image from book
Figure 13-45

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: