1 / 20

Reporting Services Perf Tuning and Best Practices

Reporting Services Perf Tuning and Best Practices . Jaishree Karira Partner Technical Consultant | Microsoft Corporation. Objectives and Takeaways. Introduction Architecture Using Report Builder 2.0 Extending and Customizing Reports Comparing Report Authoring Tools

kalona
Download Presentation

Reporting Services Perf Tuning and Best Practices

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Reporting Services Perf Tuning and Best Practices Jaishree Karira Partner Technical Consultant | Microsoft Corporation

  2. Objectives and Takeaways • Introduction • Architecture • Using Report Builder 2.0 • Extending and Customizing Reports • Comparing Report Authoring Tools • Best Practices : RS Catalogs, Scale Out, Performance

  3. What is Reporting Services 2008 ? Enterprise Reporting Platform • Author Impactful Reports • Powerful Designers • Flexible Report Layout • Rich Visualizations • Manage any Workload • Enterprise Scale Platform • Central Deployment • Strong Manageability • Deliver Personalized Reports • Interactive Reports • Rendering in the Format Users Want • Delivery to Location Users Want Web Report Embed Reporting XML

  4. Report Server 2008 Architecture • Report Server is not hosted in IIS • RS 2008 uses HTTP.SYS directly • Uses SQL Server’s networking stack • IIS is replace by SQL Server internal components • SQL OS • SQL CLR • SQL Network Interface • Design Goal: Don’t break “anything”!

  5. Report Authoring Tools • Report Designer • Part of BIDS-VS Look alike • Report Builder 2.0 • Downloadable-Office Look alike • Report Builder 1.0 • Part of RS – Click once application

  6. Report Builder 2.0 Features • Data region wizard • Ribbon enhancements • Paragraph formatting • Number formatting • Query designers • SQL Server, Hyperion, SAPBW • Use server resources • Shared data sources • Server processing • More…

  7. DEMO Using Report Builder 2.0

  8. Report Server Programmability : What is New? • New Methods for the ReportService2006 Class • New Report Definition Customization Extension • New Report Server Methods for ReportService2006 Class

  9. DEMO Customizing Reports Using Report Designer

  10. Report Designer Feature Comparison VS Integration Office 12 Look & Feel Full RDL Support Shared Layout Surface Shared Dialogs Report Builder 2.0 Report Models Integrated Query and Layout Full Report Model Support Clickthrough Reports Limited RDL Support Report Builder 1.0

  11. Best Practises • Deployment : Scale up and then Scale out • Consider Dedicated server for Report Catalog • Report Catalog Sizing • Report Server (RSDB) Database Size • Report Server TempDB (RSTempDB) Database Size • Turn on Cache Execution • Load Balance Your Network • Isolate Your Workloads • Limit the dataset size by using query filters • Limit the number of reports that users can access

  12. Scaling Out Architecture:Overall Architecture Report Server RS Scale Out Deployment Report Catalog Reporting Data

  13. Report Catalog Best PracticesReport Server Catalog Breakdown Report Server Catalog (RSDB) Stores all report metadata including report definitions, report / history snapshots, scheduling, etc. • Dedicated Server • These databases can be a bottleneck • Optimize by applying standard SQL DB techniques • Catalog has a lot of I/O and transactions • RS2005: Many inserts to ChunkData, SnapshotData, and SessionData tables • RS2008: Many inserts Segment; takes majority of transactions of RSTempDB Report Catalog RS Temp DB Stores temporary snapshots while running reports

  14. Report Catalog Best PracticesHigh Performance Disk • Check out Predeployment I/O Best Practices • Have more smaller size disks with faster rotation speeds (>=15k RPM) vs. fewer larger disks with slower rotations • Maximize/balance I/O across ALL available spindles • Separate disks between RSDB and RSTempDB • RSDB a lot of small transactions (report metadata) • RSTempDB has more (not as many) larger transactions • Pre-grow your databases • Stripe dB files to number of cores (0.25 – 1.0) • Minimize allocation contention • Easier to rebalance database when new LUNs are available • Use RAID 10, not RAID 5

  15. Performance OptimizationFeatures • Recommendation: • Understand the need for big reports • Use aggregations in the database to reduce report data size • Remove unused columns from report data sets • Consider Cache and Snapshot execution options • Mitigates against load cause by concurrent users • Increases size of RSDB and RSTempDB • 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-populatethe report cache using data driven subscriptions • Remember, RS is designed for Reporting; If you want to extract data (ETL), use SSIS or file share delivery • Recommendation: • Disable report history to reduce/remove the report history snapshots • If history desired, establish global or report-specific limit to the number of snapshots • Mitigates size of RSDB

  16. Summary • Use Reporting Services for enterprise reporting – make data available to anyone, any time in any format. • Choose between various tools to author your reports • Increase its capabilities to include custom logic • Increase performance of your reports by implementing best practices and recommendations – Report Catalog, Scale out architecture, etc

  17. References • Overview of Reporting Services 2008 http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx • Reporting Services Scale-Out Architecture http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx • Configuring a Report Server Scale-Out Deployment http://technet.microsoft.com/en-us/library/ms156453.aspx • Reporting Services Scale-Out Deployment Best Practices http://sqlcat.com/technicalnotes/archive/2008/10/21/reporting-services-scale-out-deployment-best-practices.aspx • Reporting Services Performance Optimizations http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx • 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

  18. Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form at: << Feedback URL – Ask your organizer for this in advance>> For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!

  19. આભાર ধন্যবাদ நன்றி धन्यवाद ధన్యవాదాలు ಧನ್ಯವಾದಗಳು ଧନ୍ୟବାଦ നിങ്ങള്‍‌ക്ക് നന്ദി ਧੰਨਵਾਦ

More Related