30

th

march 2012

Load Testing Analysis Services

Bob Duffy Database Architect Prodata SQL Centre of Excellence

• • • • • •

Speaker Profile – Bob Duffy

Database Architect at Prodata SQL Centre Excellence, Dublin One of about 25 MCA for SQL Server globally (aka SQL Ranger) SQL Server MVP, 2009+ MCM on SQL 2005 and 2008 20 years in database sector, 250+ projects Senior SQL Consultant with Microsoft 2005-2008 • Worked on many 1-5TB data warehouse and SSAS projects • Blog http://blogs.prodata.ie/bob

• • • • • •

Agenda

The Basics and Approach Tools Available Creating Unit Tests Creating Load Tests Case Study Lessons Learned

What’s a Load Test A test event to verify scalability goals Typically Comprises of Unit Test – one or more MDX queries Load Test harness – execute unit tests to simulate users Analysis Tools – capture and compare performance Often you have a “baseline” and subsequent tests

Typical Goals / Reasons for Load Tests Throughput (not speed) Peak Users Queries Serviced Average Query Time Typical Comparison Scenarios Upgrade from 2005 New HW Platform must be 4 x faster No Scenario – Performance Tuning MDX Tuning Regression Testing (Performance and Functional) Usually Constraints Query Response Time must be under 3 seconds CPU must be no more than 80% for our 50 users

Case Study – Retail We have 500 stores reporting each first of the week/month Need to support 300 concurrent users Need response time for operational product report under 1 minute We currently have “black Monday” How many users can we support now ?

How much faster will 2008 make things ?

What hardware would we need to support 2,000 users ?

How much faster would some mdx tuning make things?

Creating Unit Tests Can be Single Statement or Scenario Free Tools XMLA from command prompt Visual Studio Unit Test (AS Sim) Performance Workbench

Introducing VS 2010 Test Suite Requires VS 2010 Test Edition or Ultimate Facilitates Creation and running of Unit Tests

The Dreaded Parameter Issue Reporting Services et al use OLEDB Parameters Profiler Exposes a ReportParameter Column with values Often we just simulate parameters. Eg use a set of test resellers and a test date

How to Capture a Workload If you know your workload Create Unit Tests and Scenarios Otherwise you may need to profile workload Eg Capture a Trace Use XMLA and ASCMD.exe to start trace Requires a XML trace definition QueryBegin and QueryEnd events Specific Columns, especially TextData Be careful of “Parameters” Use the “RequestParameter” column on QueryBegin to get this Use Profiler to import into data table or replay

Load Tools – What do They do Replay Scenarios or Unit Tests Usually Multi-Threaded Sometimes distributed options Capture perfmon counters (for tuning) Chart Interesting Performance Counters Simulate Users using think times Store Results Report on Results Regression Analysis Functional Testing See http://blogs.prodata.ie/post/Load-Test-Tools-for-Analysis-Services.aspx

Introducing Visual Studio Load Test

Determining Max user Load

Case Studio. Retail Sales Tests CPU Bound. Maxes Out at only 15 users.

Only 8 old cores - need more CPU or tuning to reduce CPU

Key Indicators

Counter User Load Tests/Sec Avg. Test Time % Processor Time % Processor Time Query pool job queue length Current connections Category LoadTest:Scenario LoadTest:Test LoadTest:Test Processor Processor MSAS 2005:Threads MSAS 2005:Connection Computer VS2010 VS2010 VS2010 VS2010 OLAP OLAP OLAP Color Range Min Max Avg 1,000 2 100 0 100 60 31 6.40 3.55 0.20 21.7 6.18 100 2.59 17.7 11.8 100 8.64 100 93.2 1,000 0 21 3 1,000 3 57 30

Test 1 – Scale Up to 48 Core Without “Engine Tuning” worse performance!

See “Memory Thrashing” in Pages/Sec 1050=>904 queries in 5 minutes

Tuning for Load Tests – INI Settings Only Adjust until CPU is 80% or more on single NUMA Node

Settings

MemoryHeapType HeapTypeForObjects ThreadPool\Query\MaxThreads ThreadPool\Process\MaxThreads Co-OrdinatorExecuteMode CoordinatorQueryBalancingFactor CoordinatorQueryBoostPriorityLevel PreAllocate / Lock Pages in Memory Prefetch 64* -4 -1 3 0 1

Default

1 1 10*

When to Tune

2 - always 0 – always When you get Queuing, and Idle threads are 0 When you get Queuing, and Idle threads are 0 When Queuing, and Idle threads > 0 1 Only adjust if blocking of small queries 0 Mostly on 2003 0 (most of the time) 2008 settings

2. Engine Tuning – Heap Type, MaxThreads After adjusting Heap and MaxThreads 1050=>2,951 queries BUT still CPU only 23% avg on SSAS Server ;-(

Case Study Part II Unanswered Questions How much faster was SQL 2008 ?

Why can we only get 23% of CPU on new hardware ?

Initial 2005 v 2008 Comparison 180 160 60 40 20 0 140 120 100 80 Same Hardware (24 core) 2005 was 8% faster on same HW. Or is it ? Chart below is on ONE thread

Throughput

SQl2005 Cold SQL2008R2 SQL2012

2005 v 2008 on 12 threads 500 400 300 200 100 0 1000 900 800 700 600 With 12 threads 2008R2 was slower 30% slower for our customer and 3% in my sample

Throughput @12 threads

SQl2005 Cold SQL 2008R2 SQL 2012

A Thread Abuse Query

2005 Sample Query

2008r2 – sample Query

200 150 100 50 0 500 450 400 350 300 250

Few Weeks later COD Hotfix

2008 Now 10% faster for test case

Load Test After Hotfix

2005 YTD Queries 2008R2 2008R2+Hotfix

80 20 0 60 40

What we should have looked for

Monitor Processing Thread “Busy”

Thread Comsumption

120 100 Proc Threads SQl2005 Warm SQL2008R2 SQL2012 Sum of Procesing Threads Max

Why only 23% CPU on a 48 core box?

2 Reasons: VS2010 configuration and NUMA 1) VS 2012 Feature Pack and Scale Out VS 2010 Agent throttled to only 1 CPU by default !!

http://msdn.microsoft.com/en-us/library/ms243155.aspx

NUMA Issues Load Balanced NUMA Nodes Single NUMA Node Single Instance Across Two NUMA Nodes 10,000 Throughput 20,000 30,000 40,000 Spotting NUMA Issues Use coreinfo from sysinternals or just plain test it: 50,000

Lessons Learned (1/3) Make sure you know your goals Tuning of the Engine / HW Determine Max users 2005 =>2008=>2012 performance or functional testing Benchmarking environment Don’t be greedy in tests. One scenario at a time.

On 2005/2008 you will need to tune ini file Watch for key concurrency issues Memory thrashing Thread starvation CPU saturation Test with and without NUMA Clear both database and file system cache Find which queries are slower on 2008

More Lessons Learned (2/3) 3500 3000 2500 2000 1500 1000 500 0 Scale out may be require sooner than you think To end the case study An 80 core server achieved 500 query throughput How much did we achieve with a four node NLB?

3,250 (over 6x) WTD YTD 2005 NLB - SNMP Algorithm NLB - Connection+CPU Algorithm

Final Lesson. Pick your hardware 80 core servers a no-no with SSAS!

My Blog with Suggestions http://blogs.prodata.ie/post/Selecting-Hardware-for-Analysis Services-(10GB-1TB-size).aspx

Thank You!

Bob Duffy Blogs

Scaling AdventureWorks for Load Testing http://blogs.prodata.ie/post/Scaling-AdventureworksDW-Cube-for BI-Testing-(2-of-2).aspx

Selecting HW for Analysis Services http://blogs.prodata.ie/post/Selecting-Hardware-for-Analysis Services-(10GB-1TB-size).aspx

Load Testing Tools for Analysis Services http://blogs.prodata.ie/post/Load-Test-Tools-for-Analysis Services.aspx

References

SSAS Load Testing Best Practices http://sqlsrvanalysissrvcs.codeplex.com/releases/view/3505 ASLoadSim, ASQueryGen, LoadTestReports, ASCMD stress testing scripts http://sqlsrvanalysissrvcs.codeplex.com/releases/view/22769 AS Performance Workbench http://asperfwb.codeplex.com/ Creating a Server Side Trace in SSAS http://www.bp-msbi.com/2012/02/counting-number-of-queries-executed-in-ssas/ SSAS 2008 Performance tuning http://www.microsoft.com/download/en/details.aspx?id=17303

Bonus – Tuning of TCP/IP Stack When the number of connections to the server was 90 or greater, we experienced stability issues and “thrashing” of Processor utilisation. This was resolved by application of the following KB Article http://support.microsoft.com/kb/951037 Single NUMA Node with TCP/IP Optimised Single NUMA Node 25000 27000 Throughput 29000 31000 33000 35000