SQL Server 2005 BI evaluate and diagnose Analysis Services database performance
Performance - one word does it all!
You are aware that during the Performance Tuning exercise there are few quick wins within the SQL Server Relational Database Engine such as table statistics for data and indexes. When it comes to Analysis Services (BI) database then no difference as 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.
Not only this facility you can easily import the SYSMON (PERFMON) data to addup to Profiler in order to analyze the counters that are important in every step of querying and processing. Within the SQL Server Management Studio (SSMS) or Microsoft Management Control (MMC) a snap-in is attached that tracks resource usage. You can start this snap-in by typing in PerfMon at the command prompt. It is also available from Control Panel by clicking Administrative Tools, then Performance. To get more understanding on what counter will be helpful for Analysis Services you can open the Add Counters dialog box, select a performance object, and then click Explain. As usual for monitoring purpose the most important counters are CPU usage, memory usage, disk IO rate.
For the Profiler usage within Analysis Services BOL documentation clearly states that :
When you use SQL Server Profiler, note the following:
- Trace definitions are stored with the Analysis Services database by using the CREATE statement.
- Multiple traces can be running at the same time.
- Multiple connections can receive events from the same trace.
- A trace can continue when Analysis Services stops and restarts.
For a period of time you should gather the required events using Profiler you can initiate collection of these events, appropriate event classes must be selected in the Event Selection tab of the Trace Properties dialog box. It is also very useful to run the trace events along with the Performance counters on the same machine. The profiler can correlate these two based on time and display them together along a single timeline. The referred process of importing the Analysis Services counters into Profiler is a fairy straightforward process; however, this is a trick along the way before you can assume the diagnosis will fetch any result.
Then coming to the labour job of collection work, 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 and do no forget to use 'RUN AS' method for the SYSMON counter log with relevant username/password. As a security policy within the Server operating system the usage of RUN AS method with Adminsitrator privileged account 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 below:
In the next series of this blog I will go about how you can go with your collected statistics and analyze them to identify the performance issue.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.
