Top Customer Support issues in Analysis Services and

Top Customer Support issues in Analysis Services and how to resolve them John Sirmon Senior Escalation Engineer – Microsoft Corporation

Microsoft CSS at PASS 2009 Pre-Conference Seminar • Tackling Top Reporting Services Issues • Mon 11/2 8: 30 am-4: 30 pm • Adam Saxton Main Conference Talks • (DBA-500 -SC) Inside SQL Server Wait Types • Tues 11/3 10: 15 – 11: 45 am 3 AB • Bob Ward • (DBA-X 69 -C) Implementing and Supporting SQL 2008 Failover Clustering • Tues 11/3 1: 30 -2: 45 pm 4 C 1 -2 • Shon Hauck • (BIA-X 45 -C) Top customer support issues in Analysis Services • Wed 11/4 1: 30 -2: 45 pm 2 AB • John Sirmon • (AD-X 43 -C ) Troubleshooting applications accessing SQL Server • Thurs 11/5 1: 00 -2: 15 pm 613 -614 • Abirami Iyer and Lakshmi Jonnakuti SQL Server Clinic • Room 611 • 11/3 – 11/5/2009 • After Keynote – 6: 00 pm…ish

Session Objectives And Takeaways Session Objective(s): • List top SSAS issues • Apply the most efficient troubleshooting tools/methods • quickly identify common SSAS errors and performance issues Takeaways • 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

Processing issues When good cubes go bad Performance • “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

Troubleshooting processing simplified 1. Perfmon Log Specify counters from SSAS 2008 Processing. xml 2. Server Trace Start. Processing. Trace. xmla Stop. Processing. Trace. xmla

Demo: measuring Process Data with Performance Monitor

Tuning processing from Perfmon Observation Action MSAS 2008: ThreadsProcessing pool job queue length > 0 and Processing pool idle threads = 0 for longer periods during processing. Increase ThreadpoolProcessMax. Threads and retest. Both MSAS 2008: ThreadsProcessing pool job queue length > 0 and Processing pool idle threads > 0 at same time during processing. Decrease Coordinator. Execution. Mode and retest. * Use Processor –% Processor Time – Total counter to tune how much to change MSAS 2008: Proc AggregationsTemp file bytes written/sec increases Aggregation buffer may be too small

Demo: Tuning processing with Perfmon Observation Action Physical. DiskAvg. Disk sec/Read, Write > . 020 (spikes should not be higher than 50 ms) I/O bottleneck. Look at RAID configuration. Physical. DiskAverage Disk Queue Length is Disk configuration. Hardware too high. RAID set too high is 2 to 3 times configuration. number of spindles; single disk too high is 2 or 3 MSAS 2005/2008: MemoryMemory Usage Msmdsrv. ini what is Total. Memory. Limit, KB consistently above Memory Limit High Low. Memory. Limit; too much parallelism, KB add more memory

Processing design best practices Relational store design • Use views for Dimension tables • Indexes to underlying table • Too many joins? Levarage ETL Dimension design • • • Use surrogate keys with smallest integer type Consider attribute properties Attribute relationships Avoid ROLAP Degenerate dimensions can be costly Attribute Relationships Cube Design • Partition your measure groups… is 20 M Rows per partition still valid? • Reasonable number of partitions • No Remote partitions

Processing configuration best practices MSMDSRV. INI • • • Coordinator. Execution. Mode ThreadpoolProcessMaxthreads Low. Memory. Limit, Total. Memory. Limit Buffer. Memory. Limit Force. Commit. Timeout, Commit. Timeout Preallocate on Windows 2003

Processing configuration best practices Data Source Settings • Maximum Number of Connections • Connection string attributes • Avoid multiple data sources Processing Attributes • Max. Parallel Error Configuration • Try to handle referential integrity in ETL • Set on objects • Do not ignore, choose convert to unknown and continue

Processing Tips / Tricks • When 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

Processing takeaways • Integer key types • Partition large mg • Process data + process indexes • Set max parallel to 1. 5 – 2 x 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

MDX Query Performance / failures MDX Query performance • Multi-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

Troubleshooting methodology Specific user All queries or 1 query? On demand, intermittent, under load Server operations? Blocking

Query Performance best practices Measure Group • Careful with Remote partitions • partitioning strategy • size • Storage Mode • Avoid ROLAP

Query Performance best practices Dimension Design • Use caution with : Parent. Child, Many to many, Reference • Use user hierarchies effectively • attribute relationships, attribute relationships BAD GOOD

Relationship Type • Flexible • Rigid • Process Update effect

Formula Engine vs. Storage Engine • Use 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

Demo: troubleshooting MDX Performance

MDX Query Performance - common root cause Storage Engine (SE) • Dimension Design • Partition Design • No Aggregations, redundant aggs Formula Engine (FE) • Cell-by-cell mode • Eliminate Nulls • Named sets Other • Configuration settings • ThreadpoolqueryMax. Threads • Preallocate • Hardware • Operations

Server Configuration – High Concurrency Coordinator. Query. Boost. Priority. Level* • • 0 & 1 – All Storage Engine Requests Have Same Priority 2 & 3 – Subsequent Reads for Same Storage Engine Request Receive Higher Priority Coordinator. Query. Balancing. Factor* • • -1 – No Limit, All Jobs Execute in Parallel 0 – Storage Engine Jobs are Serialized If Boost. Priority. Level 0 or 2 – Value should be Decimal Percentage of Total Process Thread Pool (ex: . 4 = 40%) If Boost. Priority. Level 1 or 3 – Value is Absolute Number of Process. Threads Allocated to Each Query Partition Job

Exceptions Fatal Events • Hangs • 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”

Combination Issues Crashes thread starvation Malformed query parallelism exceptions Hangs i/o issues Processing Poor Design Configuration Concurrency

Com-based application ADOMD Win 32 application OLE DB for OLAP Connectivity xmla http IIS Data pump . NET application SOAP based application ADOMD. NET xmla tcp/ip

Connectivity Errors Unknown host or instance • Test connection failed because of an error in initializing provider. The following system error occurred: No such host is known. • Cannot connect to <server_name>. A connection cannot be made. Ensure that the server is running. (Microsoft. Analysis. Services. Adomd. Client) No such host is known (System) SSAS refuses connection • Test connection failed because of an error in initializing provider. The following system error occurred: No connection could be made because the target machine actively refused it • Cannot connect to <server_name> A connection cannot be made. Ensure that the server is running. (Microsoft. Analysis. Services. Adomd. Client) No connection could be made because the target machine actively refused it (System) Named instance issues • Test connection failed because of an error in initializing provider. Errors in the OLE DB Provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<server_name>' server. • Cannot connect to <server_name>/<instance_name>. A connection cannot be made to redirector. Ensure that 'SQL Browser ' service is running. (Microsoft. Analysis. Services. Adomd. Client) No connection could be made because the target machine actively refused it (System) Firewall issues • Test connection failed because of an error in initializing provider. The following system error occurred: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. • Cannot connect to <server_name. A connection cannot be made to redirector. Ensure that 'SQL Browser ' service is running. (Microsoft. Analysis. Services. Adomd. Client) A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond (System)

Resources Links • Analysis Services 2008 Performance guide link • Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios link • http: //www. ssas-info. com/ • Great webcasts at http: //www. learnmicrosoftbi. com/ • MDX Studio http: //www. mdxstudio. com/ Books

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 • At the PASS Booth near registration area Sponsored by Dell

Thank you for attending this session and the 2009 PASS Summit in Seattle

Visit the Microsoft Technical Learning Center Located in the Expo Hall Microsoft Ask the Experts Lounge Microsoft Chalk Theater Presentations Microsoft Partner Village