One of the greatest enhancements in Analysis Services 2005 is the ability to use SQL Server Profiler to troubleshoot performance issues during querying and processing. Even cooler is the ability to import Perfmon data into Profiler so you can analyze system and Analysis Services performance counters at every step of querying and processing.

Importing the Analysis Services counters into Profiler is a fairy straightforward process; however, this is a trick along the way before you can claim success. Read below to find out more.

Setup Work

1)  First you need to set up a Counter Log in Perfmon and select the System and Analysis Service counters that you want. You should pay attention to customize the appropriate sampling interval for your workload. Essentially the interval needs to be small enough so that you can see the processing or querying events fire, but not so small that you are overwhelmed by all of the data points. Also note the location of the log file. You will need to know the location in order to import the data into Profiler.

2)  And now for the trick… Thanks to Eric Jacobsen (MSFT) for this helpful tip. When you configure the Perfmon Counter Log properties, in order to capture the Analysis Services performance counters, you must use “Run As” and supply a username/password. The reason is that the Analysis Services logging is performed under an account that does not have access to the counter DLL. If you do not supply the username / password, the counters will be missing from the log!  See screen shot for the RunAs specification.

It’s Go-Time

It’s time to troubleshoot. Let’s say that you are troubleshooting a problematic query. You can perform the following steps to coordinate the Perfmon data with a Profiler trace.

1)  First decide whether or not you want to test the query with the cache populated or cleared. If you want to test on an empty cache, then it is a good idea to clear the cache prior to starting the logs– just to keep the files clean.

2)  Create and Start a Profiler trace with the relevant events for querying. In a future blog, I will discuss the most useful events for troubleshooting. Below is a list of some of the more helpful ones :

·        Progress Report Begin / End

·        Query Begin / End

·        Query Cube Begin / End

·        Get Data from Aggregation

·        Get Data From Cache

·        Query Subcube

·        Query Subcube Verbose (the readable translation of the subcube)

3)  Start the Perfmon log

4)  Run the query

5)  When the query is done, stop the trace file and stop the Perfmon log.

6)  Note that the Perfmon log is automatically stored in the file that was initially specified in the Counter Log properties dialog. The Profiler trace is NOT automatically stored. Therefore, you must save and name the Profiler trace file.

7)  Finally it is time to import the erfmon log data. If you click on the File menu in Profiler, you may notice that the Import Performance Data option is grayed out, i.e. it is not available. To make this option available, close and re-open the trace file and then the Import Performance Data option will be available.

8)  Once you select Import Performance Data, you can choose which performance counters you want to display. Finally, you should end up with something like the screen shot below.

Happy Troubleshooting!