building and deploying large scale ssrs using lessons learned from customer deployments

Building & Deploying Large Scale SSRS using Lessons Learned from Customer

DeploymentsDenny Lee, SQLCAT Best Practices

Lukasz Pawlowski, SQL Reporting Services

Customer Advisory Team

• Works on largest, most complex SQL Server projects worldwide• US: NASDAQ, USDA, Verizon, Raymond James…• Europe: London Stock Exchange, Barclay’s Capital• Asia and Pacific: Korea Telecom, Western Digital, Japan Railways East• ISVs: SAP, Siebel, Sharepoint, GE Healthcare

• Drives product requirements back into SQL Server from our customers and ISVs

• Shares best practices with SQL Server community• http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx• http://blogs.msdn.com/sqlcat • http://blogs.msdn.com/mssqlisv• Coming soon: http://www.sqlcat.com – technical notes and case studies

We are wearing the OrangeOrange shirts during the conference. Stop by, say hello and feel free to ask us any questions.

Agenda

• Cover the following:Cover the following:• The KEY to SuccessThe KEY to Success• Customer ScenarioCustomer Scenario• Scaling OutScaling Out• Performance OptimizationPerformance Optimization

• As time permits:As time permits:• TroubleshootingTroubleshooting

DBA-416-M • Large Scale SSRS from Customer Lessons Learned

The KEY to Success


Agenda• The KEY to Success• Customer Scenario• Scaling Out• Performance Optimization• Troubleshooting

The KEY to Success:Read the manuals!• A lot of documentation on SSRS available online

• Many mistakes in implementation could have been avoided

• Read these:• Planning for Scalability and Performance with Reporting Services

http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx

• Upgrading Reporting Services (SQL Books Online)http://technet.microsoft.com/en-us/library/ms143747.aspx

• Configuring a Report Server Scale-Out Deploymenthttp://technet.microsoft.com/en-us/library/ms156453.aspx


Customer Scenario



Reporting Services architecturebased on customer deployments

Reporting DataReporting Data

Report ServerReport Server

Customer Scenario:Enterprise Rent-A-Car RS Deployment


2 AS Servers Teradata

4 RS Servers RSDBUsers

• Real RS deploymentfrom large customer inautomotive industry

• 1800 concurrent users • using VS test• 10s think time• Mean 33-36s txn time

• This presentation incorporates lessons learned from this and other customers:

• File System Snapshots to make use of cache

• Dedicated RSDB Server to reduce DB blocking

Scaling Out


An important aspect of Reporting Services is native architecture for scaling out to handle larger

workloads and more concurrent users.


Report Catalog Report Catalog


Scaling Out Architecture:Overall Architecture


RS S

cale

Out

Dep

loym

ent

RS S

cale

Out

Dep

loym

ent

RSDB

Clients

Flat Files,OLE DB,ODBC

SQL, AS,DB2, Oracle,

Teradata, etc.

RS Server

RS Server

RS Server


NLB

Clients

Clients



Scaling Out Architecture:Quick focus on Report Application


RS S

cale

Out

Dep

loym

ent

RS S

cale

Out

Dep

loym

ent

Flat Files,OLE DB,ODBC

SQL, AS,DB2, Oracle,

Teradata, etc.

RS Server

RS Server

RS Server


NLB

Clients

Clients

Repo

rting

App

licati

onRe

porti

ng A

pplic

ation

Report Viewer

Report Viewer

Report Viewer

Clients

Scaling Out Architecture:Overview

Obtain performance benefits through architecture• For Report Server catalogs

• Move RS catalogs to a dedicated server• Move to 64-bit

• For Report Servers• Up to four cores per server • Add RAM• Move to 64-bit• Move to scale out architecture after reaching four cores

• Do these in listed order


RSDB

RS Server

Scaling Out Architecture:Report Server Catalog


Report Server Catalog (RSDB)Report Server Catalog (RSDB)Stores all report metadata including report definitions, report / history snapshots, scheduling, etc.

RS Temp DBRS Temp DBStores temporary snapshots while running reports

• These databases can be a bottleneck• Optimize by applying standard SQL DB techniques• Catalog has a lot of I/O and transactions

• Many inserts to ChunkData, SnapshotData, and SessionData tables

Repo

rt C

atal

og

Repo

rt C

atal

og

RSDB

Scaling Out Architecture: Optimize Remote Report Catalog

• Place DBs on high performance disk like SAN• Local Disks:

• Smaller size disk w/ faster rotation speed (15k vs. 10k)

• Maximize and balance I/O across all spindles• Separate disks for Report Server Temp DB and

Report Server Catalog

• RAID:• Use RAID 10 like other SQL implementations• Do not use RAID 5 due to write penalty

• For High Availability, use failover clustering


Repo

rt C

atal

og

Repo

rt C

atal

og

RSDB

Scaling Out Architecture: File System (FS) Snapshots

• RS TempDB has a lot of transactions to keep report consistency (i.e. cached reports)

• Reduce RS Catalog I/O with File System File System SnapshotsSnapshots• It will store data on file system• Unlike RS/IIS setup, will require more disk space

• To enable, update RSReportServer.config file:<Add Key="WebServiceUseFileShareStorage"

Value="true" /><WindowsServiceUseFileShareStorage>True</WindowsServiceUseFileShareStorage>


RS Server

Scaling Out Architecture: Network

• Load balancing important for many client connections to RS servers

• Recommend: Use cookie persistence to preserve SSRS-to-client connection• IP affinity can work but may be

overload for browser-based connections

• Makes use of SSRS file cache• Keep round-robin for initial

connections

• Recommend: dual NIC for RS• Split browser and AS/DB traffic


RS S

cale

Out

Dep

loym

ent

RS S

cale

Out

Dep

loym

ent

Clients

RS Server

RS Server

RS Server

NLB

Clients

Clients

Scaling Out Architecture: Isolate your workloads



RS S

cale

Out

RS

Sca

le O

ut

Depl

oym

ent

Depl

oym

ent

RSDB

RS Server

RS Server


Clients Reporting DataReporting Data

SchedulingScheduling

InteractionInteraction

Benefits:Predictable WorkloadsHelps with Security ModelIsolate Performance Issues

RS S

cale

Out

RS

Sca

le O

ut

Depl

oym

ent

Depl

oym

ent

NLB

Scaling Out Architecture: Isolate your workloads (2)

• To configure workload isolation• Use Surface Area Configuration tool

• [1] Scheduled Events and Report Delivery• [2] Web Service and HTTP Access

• Programmatically change these settings

• RS WMI Provider Library > ... > SetServiceState


RS ServerClients

Scheduling [1]Scheduling [1]

Interaction [2]Interaction [2]

RS ServerClients

Scaling Out Architecture:Report Data DS Performance Considerations

• Scale out works for RS but may not work for underlying Report Data (data source)

• Reporting loads Report Data, limit impact of large numbers of users• Limit data set size using report filters• SSIS limited data from Operational data

sources• Do not let all users access all of the reports• E.g. Report Builder against Analysis Services

results in many queries being executed.


RS S

cale

Out

Dep

loym

ent

RS S

cale

Out

Dep

loym

ent

RS Server

RS Server

RS Server

Scaling Out Architecture: Analysis Services• Common scenario: place SSRS in front of SSAS to

provide fast cube reporting

• Report rendering issues may be a SSAS problem not a SSRS problem

• Remember SSRS is scale out, while SSAS is scale-up

• SSAS query scalability solution can be found at:• Scale-Out Querying with Analysis Services

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/scoqryas.mspx



Scaling Out Architecture:Scale Out Querying with Analysis Services


AS Processing Server

AS Query Server 1

AS Query Server 2

RS S

cale

Out

Dep

loym

ent

RS S

cale

Out

Dep

loym

ent

RS Server

RS Server

RS Server

RS Server

Repo

rt S

erve

rRe

port

Ser

ver

queries Synchronize

Synchronize

Notes:AS is scale-up,Scale out by synchronizing copies of same DB

queries

Performance Optimization


How do you get the most out of your Reporting Services environment


Performance Optimization:Should we use 64-bit?

• RS Catalog• It definitely helps the RS Catalog SQL servers

• Report Servers• RS requires each data set to fit in memory

• E.g. 32-bit RS will hit 3GB ceiling • IIS process recycles = reports fails

• 64-bit does not increase the speed of report rendering• Allow users to view and export larger reports• May get better throughput at higher workloads due to less

memory contention• Note, RS works on x64 only

• RecommendationRecommendation: Use 64-bit to handle higher workloads, not report rendering speed


RS Server

Performance Optimization: Features

RecommendationsRecommendations• Use Report Execution Timeouts to control how a long a

report has to execute

• For large reports with data processing bottlenecks, use Scheduled Snapshots

• Rendering reports into non-browser formats like PDF and Excel, address this by scheduling Delivered Rendered reports

• Pre-populate the report cache using data driven subscriptions

• Remember, RS is designed for interactive use; If you want to export of data, use file share delivery or SSIS as your mechanisms


RS Server

Performance Optimization:Configurations

• RecommendationRecommendation: • Disable report history to reduce/remove the report history

snapshots• If history desired, establish global or report-specific limit to

the number of snapshots

• RecommendationRecommendation: • Understand the need for big reports• Consider Cache and Snapshot execution options• Mitigates against load cause by concurrent users

• When tuning Report Server • Need to tune IIS• In SQL Server 2008, this will be different


RS Server

Performance Optimization:Memory Configurations

• MemoryLimit Configuration• Default 60% of available memory• Increase help process more requests• Once threshold hit, no new requests are accepted

• MaximumMemoryLimit Configuration• Default 80% of available memory• If this threshold is met, processing is aborted

• Changing values may solve RS only to bring up other contentions

• Recommendation:Recommendation: If constantly hitting memory thresholds, consider scaling up and then scaling out


RS Server

Performance Optimization:Performance Testing• Need to understand your scenarios and reports

• Scenarios are defined by user personas & usage patterns• Reports are either test reports or actual reports • Tests should isolate Report Server from other systems

• Need tools to automate the testing• See white paper: Using Visual Studio 2005 to Perform Load

Testing on a SQL Server 2005 Reporting Services Report Serverhttp://technet.microsoft.com/en-us/library/aa964139.aspx

• Make single incremental changes between tests• Do not use SQL trace inside VSTE


Performance Optimization:Enterprise Rent-A-Car Performance Testing


RS Servers Users Mean Tx Time

Mean CPU%

1 Server 608 36.9 99

2 servers 1218 36.8 96

4 servers 2300 30.5 80

• 8GB RAM, 4 CPU core RS servers

• Graph is max # of users reached for sustained time period (>=15 min)

• 2x RAM and CPU core, only 1/3 increase in load

Performance Optimization: Enterprise Rent-A-Car Performance Testing (2)• Visual Studio Testing

• Mean Transaction Time Range 33-36s • 10s think time• 95% SSRS, 5% ProClarity users• 1 test controller with 4 agents (32-bit)• >2000 concurrent users: higher rate of failed tests

• Suspect this is due overload of test mechanism• 1800 concurrent users: low rate of failed tests

• Pre-defined workload• 75% simple, 25% complex workflows• Randomized selection of report parameters


QUESTIONS?



Troubleshooting


How to diagnose issues when running reports in the Report Server?


Troubleshooting:Overview• Reports can take up

• a lot of memory • a lot of time to execute • a lot of CPU

• It is possible for errors to occur as a result: • Out of Memory• Internal Errors• Rendering errors

• So how do we diagnose issues when running reports in the Report Server?


Troubleshooting:Start with the RS Execution and Trace Logs• Report Server log files include:

• Report execution, success and other related data • Detailed error stacks showing what the problems are• Major events are in the Windows application event log• Located under:

• <install folder>\Reporting Services\LogFiles

• References• Reporting Services Log Files details are in BOL at:

http://msdn2.microsoft.com/en-us/library/ms157403.aspx• Search the Reporting Services MSDN forum for the stack trace:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1

• RS Error code info:http://msdn2.microsoft.com/en-us/library/ms165307.aspx


Troubleshooting:Monitoring Performance• Want to look at memory consumptions, application

domain recycles, cpu usage, etc.

• Good resources for this:• This topic describes the performance countershttp://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx• Monitoring Report Execution Performance with Execution Logs http://msdn2.microsoft.com/en-us/library/aa964131.aspx• Monitoring interactive report executions via ListJobs SOAP API

to see which long running reports are currently executinghttp://msdn2.microsoft.com/en-gb/library/aa225969(SQL.80).aspx• Use the SQL Server Profiler to monitor actions of the Report Server

database


Troubleshooting:Diagnosing ProblemsSpecific actions to help diagnose problems:• Adjust memory limits:

• Refer to “Report Size in Memory” section of http://msdn2.microsoft.com/en-US/library/ms156002.aspx

• Adjust schedule concurrency:• Do this to isolate which are your problem reports• May want to reduce number of simultaneous report executions

to 1 especially for extremely large reports• In rsreportserver.configrsreportserver.config, set <MaxQueueThreads><MaxQueueThreads>

• Zero (0) means RS server automatically determines the “right number”


Troubleshooting:Common problems• Error 401 is an authentication error

• Providing no or incorrect credentials• Also can be seen during an ASP.NET application reset as well• Happens if server starts running out of memory• Check logs for InternalCatalogException;

• in the stack trace, you should see the method that failed

• Within trace logs - Application Domain Recycles• App domain recycles used to clear out memory• Often will be correlated with interactive report execution failures• Indicates RS server is under memory pressure


Troubleshooting:Common problems (Continued)• SQL Query Plan optimization

• Stored Procedures require query plan optimization• Use the With Recompile directive in Stored Procedures• Use fully qualified names to access across DB objects • Refresh on SQL Query Performance:

• http://msdn2.microsoft.com/en-us/library/ms187032.aspx

• Report Design with drop-down parameter lists• Evaluating drop downs results in query load on DBMS• Optimize parameter, prompts, and valid values


Troubleshooting:References• Processing Large Reports recommendations (incl. config,

rendering, deployment, and distribution)http://msdn2.microsoft.com/en-US/library/ms159638.aspx

• Performance Whitepaper (Very Important)http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx

• How to configure a scale-out deployment: scale outs can increase throughput, reliability, and concurrencyhttp://msdn2.microsoft.com/en-us/library/ms159114.aspx

• How to scale test your report server deployment:http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx


Many Thanks!

• Craig Utley and David Reinhold for the great customer implementation data• Customer Support: Binh Cao, Perry Birch, Scott Reagin, Charles

Liptaak, Margaret Shoptaw

• Also thanks to Brian Welcker, Bill Emmert, and Nicholas Dritsas for additional resources.


SQL CAT Presentations at PASS 2007Session Code Session Title Speakers Date Time

DBA-410-M Designing for Petabyte using Lessons Learned from Customer Experiences

Lubor Kollar;Lasse Nedergaard

9/19/2007 9:45 AM - 11:00 AM

DBA-411-M Optimizing OLTP Systems using Lessons Learned from MySpace

Michael Thomassy; Burzin Patel;Hala Al-Adwan

9/19/2007 1:30 PM - 2:45 PM

DBA-314-M Building High Performance Systems for SQL Server

Juergen Thomas 9/19/2007 3:00 PM – 4:15 PM

DBA-412-M ISV configuration & implementation using Lessons Learned from Customer Deployments

Juergen Thomas 9/20/2007 10:30 AM - 11:45 AM

DBA-413-M Building Highly Available SQL Server Implementations using Lessons Learned from Customer Deployments

Prem Mehra;Lindsey Allen;Sanjay Mishra

9/20/2007 1:30 PM - 2:45 PM

DBA-416-M Building and Deploying Large Scale SSRS Farms using Lessons Learned from Customer Deployments

Denny Lee;Lukasz Pawlowski

9/21/2007 9:45 AM - 11:00 AM

DBA-415-M Building & Maintaining large Cubes using Lessons Learned from Customer Deployments

Nicholas Dritsas;Eric Jacobsen

9/21/2007 1:00 PM - 2:15 PM

DBA-414-M SQL Server 2005 Server Consolidation Update Prem Mehra 9/21/2007 2:30 PM – 3:45 PM

BI-406 Diagnosing Analysis Services 2005 MDX Query Performance Bottlenecks

Carl Rabeler; Eric Jacobsen

9/21/2007 4:00 PM – 5:15 PM


Thank you!

Thank you for attending this session and the

2007 PASS Community Summit in Denver

building and deploying large scale ssrs using lessons learned from customer deployments

Technology