Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Monitoring and Tuning Processing Commands
SSAS supports a number of dimension and partition processing options. Processing the entire database is impractical for most implementations. Instead, a daily processing cycle would normally include incremental processing of dimensions and full processing of any newly created partitions.
Monitoring the processing commands can show us how many objects are processed in parallel, how long it takes to run the SELECT statements against the relational data source, and how many rows have been read so far. Let's take a look at the Profiler output for a partition processing command, as shown in Figure 4.
)
Notice the SQL statement under the ExecuteSQL event subclass. This is the full query SSAS sends to the relational database to process a partition. If partition processing is slower than expected, you should copy the SQL statement into SQL Server Management Studio and check the query plan. It's possible that indexes useful for resolving the query are missing or column statistics are out of date.
You can bind each Analysis Services partition to a query, view, or table, so you have some control over the statement SSAS will execute during partition processing. Dimension-processing queries give less control to the developer, but you can bind the dimension to a view, or specify the necessary query using an out-of-line bindings-processing option. In general, the majority of processing-performance issues are due to a relational query's slowness.
We can specify the degree of parallelism within the processing XMLA command—the profiler would display the number of rows read for each object being processed in parallel. This approach is helpful when creating multiple partitions each day or each month. I recommend experimenting with the MaxParallel setting; on powerful multiprocessor hosts, SSAS sometimes tries to process more than it's capable of handling in parallel.
As Figure 5 shows, Profiler displays the duration of each processing step:
- Running the SELECT statements
- Reading data
- Writing data into multidimensional format
- Building indexes and aggregations
Checking the duration of each of these events will help you to determine the cause of poor processing performance. For example, if writing data takes longer than executing the SQL statement, perhaps you have a disk bottleneck—you should examine the disk utilization counters by using Performance Monitor (Perfmon).
)
