Database Reference
In-Depth Information
1. Open SQL Server Profiler , navigate to File | New Trace , and connect to the
desired Analysis Services instance.
2. To choose the desired events, switch to the Events Selection tab and en-
sure Show all events and Show all columns boxes are checked. SQL
Server Profiler displays a short description of each event and event cat-
egory.
3. Click on Run when you're happy with your selections to collect the trace.
4. Navigate to File | Stop Trace when you have collected the desired dia-
gnostics.
5. Navigate to File | Save As to save the trace output as a SQL Server table or
trace file, if you wish to refer to trace contents later.
Tip
There are a couple of SSAS trace templates included with the SQL Serv-
er Profiler installation: Standard (default) and Replay . The default tem-
plate is fine for most troubleshooting. If you find yourself re-using the
same set of counters repeatedly, you can also create your own template.
To create a new template navigate to File | New Template , and then
choose the desired events and columns.
So you collected the troubleshooting artifacts, including the SQL Server Profiler and
PerfMon output. Now what? Since processing performance depends on a number
of factors, there are no hard and fast rules for tuning. However, I can provide the list
of items that commonly cause poor performance and can be remediated as follows:
• Ensure you have appropriate indexes in the relational database. If you en-
counter slow performance during the partition processing, be sure to check
the query executed by Analysis Services against the relational source. Cut
the SQL query captured by SQL Server Profiler and paste it into SSMS (pre-
suming that your relational source is SQL Server). Examine the query execu-
tion plan and ensure necessary indexes exist.
• Ensure Analysis Services has plenty of memory available to it. If you have
multiple SSAS instances running on the same host, or if the host is shared
with the SQL Server relational database engine you can use the preallocate
configuration option to reserve some memory when the instance starts.
Be sure to review SSAS memory configuration properties found in msmd-
Search WWH ::




Custom Search