1 John Sirmon Senior Escalation Engineer – Microsoft CorporationTop Customer Support issues in Analysis Services and how to resolve them John Sirmon Senior Escalation Engineer – Microsoft Corporation
2 Pre-Conference SeminarMicrosoft CSS at PASS 2009 Pre-Conference Seminar Tackling Top Reporting Services Issues Mon 11/2 8:30am-4:30pm Adam Saxton Main Conference Talks (DBA-500-SC) Inside SQL Server Wait Types Tues 11/3 10:15 – 11:45am 3AB Bob Ward (DBA-X69-C) Implementing and Supporting SQL 2008 Failover Clustering Tues 11/3 1:30-2:45pm 4C1-2 Shon Hauck (BIA-X45-C) Top customer support issues in Analysis Services Wed 11/4 1:30-2:45pm 2AB John Sirmon (AD-X43-C ) Troubleshooting applications accessing SQL Server Thurs 11/5 1:00-2:15pm Abirami Iyer and Lakshmi Jonnakuti SQL Server Clinic Room 611 11/3 – 11/5/2009 After Keynote – 6:00pm…ish
3 Session Objectives And TakeawaysList top SSAS issues Apply the most efficient troubleshooting tools/methods quickly identify common SSAS errors and performance issues Session Objective(s): solve common problems with minimal SSAS knowledge Understanding of the tools/techniques CSS team uses to resolve critical customer issues. knowledge of SSAS problem areas Takeaways
4 Processing issues When good cubes go badPerformance “processing used to take 30 minutes, now it takes 4 hours” Query performance during processing Failure Msmdsrv.exe is crashing “Operation has been cancelled” Out of memory Incorrect data Both Slower and slower then it hangs Taking so long DBA killed processing jobs
5 Troubleshooting processing simplified1. Perfmon Log Specify counters from SSAS2008Processing .xml 2. Server Trace StartProcessingTrace.xmla StopProcessingTrace.xmla
6 Demo: measuring Process Data with Performance Monitor
7 Tuning processing from PerfmonObservation Action MSAS 2008:Threads\Processing pool job queue length > 0 and Processing pool idle threads = 0 for longer periods during processing. Increase Threadpool\Process\MaxThreads and retest. Both MSAS 2008:Threads\Processing pool job queue length > 0 and Processing pool idle threads > 0 at same time during processing. Decrease CoordinatorExecutionMode and retest. * Use Processor –% Processor Time – Total counter to tune how much to change MSAS 2008:Proc Aggregations\Temp file bytes written/sec increases Aggregation buffer may be too small
8 Demo: Tuning processing with PerfmonObservation Action PhysicalDisk\Avg. Disk sec/Read, Write > .020 (spikes should not be higher than 50ms) I/O bottleneck. Look at RAID configuration. Disk configuration. Hardware configuration. PhysicalDisk\Average Disk Queue Length is too high. RAID set too high is 2 to 3 times number of spindles; single disk too high is 2 or 3 MSAS 2005/2008:Memory\Memory Usage KB consistently above Memory Limit High KB Msmdsrv.ini what is TotalMemoryLimit, LowMemoryLimit; too much parallelism, add more memory
9 Processing design best practicesUse views for Dimension tables Indexes to underlying table Too many joins? Levarage ETL Relational store design Use surrogate keys with smallest integer type Consider attribute properties Attribute relationships Avoid ROLAP Degenerate dimensions can be costly Attribute Relationships Dimension design Partition your measure groups… is 20M Rows per partition still valid? Reasonable number of partitions No Remote partitions Cube Design
10 Processing configuration best practicesMSMDSRV.INI CoordinatorExecutionMode Threadpool\Process\Maxthreads LowMemoryLimit, TotalMemoryLimit BufferMemoryLimit ForceCommitTimeout, CommitTimeout Preallocate on Windows 2003
11 Processing configuration best practicesData Source Settings Maximum Number of Connections Connection string attributes Avoid multiple data sources Processing Attributes MaxParallel Error Configuration Try to handle referential integrity in ETL Set on objects Do not ignore, choose convert to unknown and continue
12 Processing Tips / TricksWhen to use Process Full vs. Process Data + Process Indexes Combine multiple data sources Separate processing server then synch Avoid aggregations > 1/3 size of fact data check size Dir *.agg* /s Dir *.fact* /s
13 Processing takeaways Integer key types Partition large mgProcess data + process indexes Set max parallel to 1.5 – 2x cpu Attribute relationships String key types 1 monster partition (no sliding window) Process full on large dbs Let UI determine max parallel No attribute relationships
14 MDX Query Performance / failuresMulti-user performance Slow while processing Long running queries blocking MDX Query Failures Hangs, Crashes “operation has been cancelled” Timeouts Both Blocking queries cause hang behavior Forcecommittimeout kicks in during processing
15 Troubleshooting methodologySpecific user All queries or 1 query? On demand, intermittent, under load Server operations? Blocking
16 Query Performance best practicesCareful with Remote partitions partitioning strategy size Storage Mode Avoid ROLAP Measure Group
17 Query Performance best practicesUse caution with : Parent-Child, Many to many, Reference Use user hierarchies effectively attribute relationships, attribute relationships, attribute relationships Dimension Design BAD GOOD
18 Relationship Type Flexible Rigid Process Update effect
19 Formula Engine vs. Storage EngineUse Trace to determine if bottleneck is in SE or FE Query Time = FE + SE FE Computes result Cell by cell mode Block mode SE Gets raw agg data No aggs Too many aggs
20 Demo: troubleshooting MDX Performance
21 MDX Query Performance - common root causeStorage Engine (SE) Dimension Design Partition Design No Aggregations, redundant aggs Formula Engine (FE) Cell-by-cell mode Eliminate Nulls Named sets Other Configuration settings Threadpool\query\MaxThreads Preallocate Hardware Operations
22 Server Configuration – High ConcurrencyCoordinatorQueryBoostPriorityLevel* 0 & 1 – All Storage Engine Requests Have Same Priority 2 & 3 – Subsequent Reads for Same Storage Engine Request Receive Higher Priority CoordinatorQueryBalancingFactor* -1 – No Limit, All Jobs Execute in Parallel 0 – Storage Engine Jobs are Serialized If BoostPriorityLevel 0 or 2 – Value should be Decimal Percentage of Total Process Thread Pool (ex: .4 = 40%) If BoostPriorityLevel 1 or 3 – Value is Absolute Number of ProcessThreads Allocated to Each Query Partition Job
23 Exceptions Fatal EventsHangs SSAS not responsive SSAS won’t start Crashes and Errors .mdmp in logs directory Sqldumper.exe Most common SSAS Exceptions “operation has been cancelled” “Internal error: An unexpected exception occurred” “Internal error: The operation terminated unsuccessfully” “Unspecified error”
24 Combination Issues Crashes Poor Design Processing i/o issuesConcurrency Configuration parallelism Malformed query Poor Design thread starvation exceptions i/o issues Processing Hangs Crashes
25 Connectivity IIS Win32 Com-based .NET application SOAP basedOLE DB for OLAP ADOMD.NET ADOMD IIS Data pump xmla http xmla tcp/ip xmla tcp/ip
26 Connectivity Errors Test connection failed because of an error in initializing provider. The following system error occurred: No such host is known. Cannot connect to
27 Resources Links Analysis Services 2008 Performance guide linkResolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios link Great webcasts at MDX Studio Books
28 Complete the Evaluation Form & Win!You could win a Dell Mini Netbook – every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: Within each presentation room At the PASS Booth near registration area Drop off your completed Form: Near the exit of each presentation room Sponsored by Dell
29 for attending this session and the 2009 PASS Summit in SeattleThank you for attending this session and the PASS Summit in Seattle
30 Microsoft Technical Learning Center Located in the Expo HallVisit the Microsoft Technical Learning Center Located in the Expo Hall Microsoft Ask the Experts Lounge Microsoft Chalk Talk Theater Presentations Microsoft Partner Village