posted on Monday, September 13, 2004 10:36 AM by hitachi

Profiling is a Good Thing


Well, using SQL Server 2005 Profiler to create a trace of Analysis Services events is a good thing.

I’ve spent a lot of time reading Analysis Services 2000 process logs while trying to optimize the processing of cubes. So I was quite frustrated when I couldn’t find how to turn on the process log in AS2K5. I was looking in the wrong place for the info. SQL Server 2005 Profiler can now be used to create a trace of Analysis Services events.

I started off using the Standard (default) trace template provided with Profiler. Because I wanted to monitor processing events, I used the Events Selection tab of the Trace Properties dialog box to deselect all event categories except Progress Reports, Command Events, and Errors and Warnings. The Events Selection tab also has an Organize Columns button that allows you to rearrange the order of the columns. I found the following columns useful:

  • ObjectPath
  • EventSubclass
  • TextData
  • IntegerData
  • CurrentTime
  • StartTime
  • EndTime
  • Duration
  • CPUTime
  • JobID

In Profiler you can view the trace in real time and have the trace saved to a SQL Server table, Profiler trace file, or XML file. I liked saving a trace to SQL Server because I could then write SQL queries to filter and sort the events. There are events that will give you the SQL that Analysis Services used to populate the dimension or partition and you can see how long each object took to process.

- Scott


Comments