can your bi solution scale?
DESCRIPTION
DBI330. Can Your BI Solution Scale?. Teo Lachev MVP, MCSD, MCITP, MCT [email protected]. About Me. Consultant, author, and mentor with focus on Microsoft BI Owner of Prologika –BI consulting and training company based in Atlanta ( www.prologika.com ) - PowerPoint PPT PresentationTRANSCRIPT
About Me
Consultant, author, and mentor with focus on Microsoft BIOwner of Prologika –BI consulting and training company based in Atlanta (www.prologika.com)Microsoft SQL Server MVP for 5 yearsLeader of Atlanta BI group (atlantabi.sqlpass.org)
Agenda
Present practical load testing methodologyLoad test Reporting ServicesLoad test Analysis ServicesAnalyze results and performance bottlenecksShare performance best practices
Why Load Test?
Determine server throughputUnderstand how load impact server resourcesPlan server hardware
wikipedia.org
Typical BI Solution
DW
SSAS
SSRS
Transactional Reports
Analytical Reports
Analytical Reports
SSAS Clients
How to Load Test?
Step 1: Establish performance goalStep 2: Prepare load testsStep 3: Run and analyze load testsDo we meet the goal?
Yes – we are doneNo – identify and eliminate performance bottlenecksGo to Step 3.
Establish Performance Goal
• Use the SSRS Execution Log, capture SSAS trace• Look for peak times
Gatherworkload usage
• How many more users?• Double estimate just in caseEstimate
future loads
• Reports/sec or queries/sec• Number of concurrent users• Concurrent users != Deployed users
Derive performance goal
Deployed vs. Concurrent Users
Time
Users
2 concurrent users
Establish Performance GoalCase Study
Gather report workloadPeak report usage – November 22th, 9 AM – 10 AM200 reports executed by 20 distinct users200/3,600 = 0.05 reports/sec
Estimate future loads500 users – x25 increase (500/20)0.05 x 25 = 1.25 reports/sec
Derive performance goalLet's double 2 x 1.25 = 2.5 reports/sec
Establish Performance GoalAbout reports & queries
Reports are not born equalA "report" or "query" is an abstractionThink of "vehicle" if testing highway capacity
Analyzing report andquery usage
demo
Prepare TestsReporting Services
Identify a report mix10-15 reportsSlow and fast reports
Obtain report URLs and parameter valuesUse Visual Studio (Ultimate or Test edition) to create:
Web performance test – "quick and dirty" testsUnit test – custom tests, e.g. for parameter handling
Create Load TestReporting Services
Use Create New Load Test wizard and set upLoad pattern – constant or steppedTest mix – a collection of web or/and unit testsCounter sets – CPU, memory, disk utilizationRun settings – counters, warm-up time, test duration or iterations
Tip: Increase the MaxActiveReqForOneUser setting in rsreportserver.config to a large value, e.g. 999999
Create Load TestReporting Services
demoCreating SSRS load test
Creating SSRS load test
demo
Run and Analyze Load Tests
Run the load test with stepped loadAim for no more than 80% server utilization Obtain and record:
Reports/secConcurrent users
Watch for errors!VS supports 250 virtualusers Users
ThroughputReports/sec
80%
Finding Performance Bottlenecks
Every system has a saturation pointUse Windows performance counters to discover bottlenecksTypical bottlenecks
CPU (Processor: % Processor Time)Memory (Memory: Available MBytes)HDD (PhysicalDisk: Current Disk Queue Length)Network (Network Interface: Bytes Sent/sec, Bytes Received/sec)
CPU
RAM
NETWORK HDDBUS
Run and Analyze Tests
demoRun and analyze SSRSload tests
Run and analyze SSRSload tests
demo
Load Testing Analysis Services
Download the SSAS load testing frameworkAS Query Generator (ASQueryGenerator)
Generates query templatesSupports parameterized queries
AS Load Simulator (ASLoadSim)Implements a Visual Studio custom test pluginIncludes SSAS Load Testing Best Practices document – read it!
Another option that doesn't require Visual StudioAS Performance Workbenchhttp://asperfwb.codeplex.com/
demoLoad test Analysis Services
Performance Best Practices
Plan for load testing early in implementation cycle Tune and optimize before scaling up or outReporting Services
Analyze ExecutionLog and tune queriesRead SSRS Performance Optimizations whitepaperhttp://tinyurl.com/rsperf
Analysis ServicesUse SQL Profiler to get SE and FE time breakdownTune server and queries – read Analysis Services 2008 Performance GuideUse Xperf to understand if queries are disk or CPU bound http://preview.tinyurl.com/xperfssas
Performance Best PracticesContinued
Upgrade to SSRS 2008 or laterRedesigned processing engineMuch less memory boundScale to 3-4 x number of users than 2005
Upgrade to SSAS 2008 or laterBlock computation modeQueries execute 20-60% faster
demoIdentifying performancebottlenecks
Identifying performancebottlenecks
demo
Scaling Out
Resources
Using VS to Perform Load Testing on SSRS by Runying Mao and Heidi Steenhttp://msdn.microsoft.com/en-us/library/aa964139(SQL.90).aspxSSRS Performance Optimizations by Denny Lee, Lukasz Pawlowski http://tinyurl.com/rsperfAS Load Simulator and Query Generatorhttp://sqlsrvanalysissrvcs.codeplex.comAS Performance Workbenchhttp://asperfwb.codeplex.comAnalysis Services 2008 Performance Guidehttp://tinyurl.com/ssas2008perfguideSQL CAT website - Scaling Up SSRS vs. SSRS 2005: Lessons Learnedhttp://tinyurl.com/rs2005to2008My website and bloghttp://www.prologika.com
Selected Case Studies
Fresenius Medical Care 20,000 reports per dayPremier Bankcard500 SSRS users, 200 concurrent averageBangkok Bank Public1,000 usersEsurance15Tb of data, hundreds of users, 375 reports
Related Content
DBI405: Scale-Out Deployment of Microsoft SQL Server Reporting ServicesDBI320: Upsizing and Modernizing with the Microsoft BI Stack and Fast Track Data WarehouseDBI301: Microsoft SQL Server Reference Architecture and AppliancesDBI320: Upsizing and Modernizing with the Microsoft BI Stack and Fast Track Data Warehouse
Find Me Later At DBI TLC
DPR Track Resources
http://www.microsoft.com/visualstudio http://www.microsoft.com/visualstudio/en-us/lightswitch http://www.microsoft.com/expression/http://blogs.msdn.com/b/somasegar/http://blogs.msdn.com/b/bharry/http://www.microsoft.com/sqlserver/en/us/default.aspxhttp://www.facebook.com/visualstudio
Resources
www.microsoft.com/teched
Sessions On-Demand & Community Microsoft Certification & Training Resources
Resources for IT Professionals Resources for Developers
www.microsoft.com/learning
http://microsoft.com/technet http://microsoft.com/msdn
Learning
http://northamerica.msteched.com
Connect. Share. Discuss.
Complete an evaluation on CommNet and enter to win!
Scan the Tag to evaluate this session now on myTech•Ed Mobile
