Monitoring server performance during nightly Analysis Services processing

Hello all,

We would like to get figures about the server performance when processing Analysis Services database. The processing takes place nightly. The prosessing is automatic and takes place outside of office hours. So there's nobody in the office who could monitor the server performance.

We'd like to know for example the following issues.

- memory usage in the server

- page file usage (reads / writes)

- CPU load (we have 4 processors)

- suitable data available of Analysis Server (don't know if there's any)

I know these are very high level requirements... What I have in mind is that we would have something similar to "CPU usage history" and "Page File Usage History" in Windows Task manager, but for a longer time period. I've tried to find a suitable tool for this, but I've had no luck and have found nothing. I know that many others must have had similar problems.

So, how to get the figures? Any suggestions would be highly respected.

r,

JM

One easy way to capture all this is to schedule performance logging on your server. All the counters you mention above are available as well as many that are specific to SSAS.

To set up a log, go to Computer Management and expand Performance Logs and Alerts. Right-click Counter Logs and select New Log Settings. Give the log a meaningful name, e.g. "SSAS Nightly Logging". Add the counters of interest, set the sampling rate and a file location/format, and schedule the logging for the period of interest.

Setting the sampling rate appropriately can be tricky. Try to push it to as high a duration as you can stand because there is overhead with each sample. Also, consider how you want to consume the output. In the past, I've dumped the data to flat file and set up a little data mart and ETL layer to present the data in a simple star-schema for slicing and dicing.

Bryan

|||

Hello,

Thanks fot the advice, the idea really works.

We use now Performance Monitor to log the selected counter values to a file and later we use Performance Monitor to analyze the files. This approach is enough for our purposes now.

Seems that it's not possible to collect the performance data to a binary format log file and at the same time open the file for analysis in Performance Monitor. We have to stop the data collection first. I'm not totally sure about this. Logging and reading the log file simultaneously would be needed to make real time monitoring possible so that also history data would be available.

r,

J

|||

There are a few ways you can address this. The simplest is to cap the size of the performance log files and have the logging start a new log once the max size is reached. You can then open and consume the old files closer real-time. However, it's still not real time.

Another way is to interface with WMI. This is a little tricky and will require some custom dev work. I believe this is how Microsoft Operations Management (MOM) works (which is another option for you).

Yet another good option is to open the perfmon utility, add the counters, and monitor these in real-time through there.

Hope that helps,
Bryan