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