Tuning SSAS Processing
Performance
Pragmatic Works
Agenda
Processing Overview
Tuning Process
Bottlenecks
Bandages
Surgery
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
About Me
John Welch, SQL Server MVP, SSAS Maestro 2008
Coauthor on
SQL Server MVP Deep Dives
Smart Business Intelligence Solutions with Microsoft SQL
Server 2008
Working with SQL Server since 1996
My Contact Info
http://agilebi.com/jwelch
Twitter: @john_welch
Email: jwelch@pragmaticworks.com
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Dimension Processing
Build Attribute
Stores
MAKING BUSINESS INTELLIGENT
Build Hierarchy
Stores
Build Decoding
Stores
Build Bitmask
Indexes
www.pragmaticworks.com
Attribute Processing
Country
Region
Income
Gender
City
Customer
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Hierarchy Processing
Hierarchy stores accelerate access to
dimension hierarchy members
Hierarchies process in parallel
Ordering members in the hierarchy can
be time consuming
Disable ordering by setting
AttributeHierarchyOrdered = FALSE
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Decoding Stores
Decoding tables enable lookup of IDs for
high level members by the ID for a lower
level member
Unlikely to present performance
bottlenecks
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Build Bitmask Indexes
Bitmask indexes help look up related
attributes
Depends on Decoding Stores
Loads decoding stores for related
attributes into memory
AttributeHierarchyOptimizedState =
NotOptimized disables the index
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Partition Processing
Process Fact
Data
Build
Aggregations &
Indexes
ProcessData
ProcessIndexes
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Process Data
Read Data
Reads from
database
MAKING BUSINESS INTELLIGENT
Process Data
Maps data
to Dim IDs
Aggregates
data
Write Data
Sorts data
Compresses
and writes
data
www.pragmaticworks.com
Process Indexes
Creates indexes and aggregations
Loads decoding stores for all attributes
related to granularity attributes
Except for attributes that are NotOptimized
Aggregations for large partitions can
spill to disk
Avoid where possible
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
SMART Tuning Steps
Set Goals
Test and
Evaluate
Remove
Bottleneck
MAKING BUSINESS INTELLIGENT
Measure
Assess
Bottlenecks
www.pragmaticworks.com
Performance Monitor
MSOLAP: Processing
MSSQL: Memory Manager
Rows read/sec
Rows written/sec
MSOLAP: Proc Aggregations
Temp File Bytes Writes/sec
Rows created/Sec
Current Partitions
Total Server Memory
Target Server Memory
% Processor Time msmdsrv.exe
and sqlservr.exe
MSOLAP: Memory
Memory Limit High KB
Memory Limit Low KB
Memory Usage KB
MAKING BUSINESS INTELLIGENT
Logical Disk:
MSOLAP: Threads
Processing pool idle threads
Processing pool job queue length
Processing pool busy threads
Process
Avg. Disk Read Queue Length
Avg. Disk Write Queue Length
Avg. Disk sec/Read
Avg. Disk sec/Write
Disk Transfers/sec
Processor:
% Processor Time Total
www.pragmaticworks.com
SQL Profiler
SQL
Performance/
Showplan XML Statistics
Profile
TSQL/
SQL:BatchCompleted
MAKING BUSINESS INTELLIGENT
Analysis Services
Command Events
Command Begin
Command End
Progress Reports
Progress Report Begin
Progress Report End
Progress Report Error
www.pragmaticworks.com
Tuning
Run Processing in 3 steps
Dimension processing
Partition ProcessData
Partition ProcessIndexes
Use the most appropriate processing type
Machine is maxed when CPU utilization =
100%
Improvements past that require new hardware
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Warning!
Change one thing at
a time!
Most of these items
involve tradeoffs
Need to understand
impact of each
setting
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Dimension Processing Bottlenecks
Data retrieval
Bandage: Add Indexes
Surgery: Use SQL views to support dimensions
Processing attributes
Reduce the number of attributes
Add attribute relationships
AttributeHierarchyOptimizedState = Not Optimized
for attributes related to key with high cardinality
Use Member Properties
AttributeHierarchyEnabled = False
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Partition ProcessData Bottlenecks
Data Retrieval
Bandages
Add Indexes
Use Compression
Add lock hints (TABLOCK)
Add database connections
Surgery
Align relational and analytical partitions 1:1
Update data types
Evaluate database server I/O and network
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Partition ProcessData Bottlenecks
CPU underutilized
Bandages
Processing pool job queue length > 0
If Processing pool idle threads = 0, increase
Threadpool\Process\MaxThreads
If Processing pool idle threads > 0, decrease
CoordinatorExecutionMode
Surgery
Increase the number of partitions
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Partiton ProcessIndexes Bottlenecks
Not enough memory (Temp File Bytes Writes/sec
> 0)
Bandages
Reduce memory usage
Decrease parallelism
Make sure TempDir is pointed to a fast disk
Surgery
Add physical memory
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Partiton ProcessIndexes Bottlenecks
CPU underutilized
Bandages
If Processing pool idle threads = 0, increase
Threadpool\Process\MaxThreads
If Processing pool idle threads > 0, decrease
CoordinatorExecutionMode
Increase
OLAP\Process\AggregationMemoryLimitMin
Surgery
Increase partitions
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
I/O
Disk Queue Lengths, Avg. Reads and
Writes are high
No bandages here
(other than reducing disk contention)
Surgery:
More / faster spindles
Short-stroke the disks
Go solid-state
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Wrap Up
Tuning takes time
Set clear goals initially and revisit them
Change one thing at a time
Document along the way
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
Resources
Analysis Services Performance Guide
http://www.microsoft.com/downloads/en/details.
aspx?FamilyID=3be0488d-e7aa-4078-a050ae39912d2e43&DisplayLang=en
SQLCAT
http://sqlcat.com
My Blog
http://agilebi.com/jwelch
Twitter: @john_welch
MAKING BUSINESS INTELLIGENT
www.pragmaticworks.com
