SQL Server 2016 Reporting Services Optimization Tips
Use Reporting Services web portal.
The Reporting Services web portal introduces in SQL Server 2016 and replaces Report Manager
from previous releases. This is a modern portal which incorporates KPIs (Key performance
indicators), Mobile Reports, Paginated Reports, Excel and Power BI Desktop files.
Consider using the Chart data region.
SQL Server 2016 supports the Chart data region. The Chart data region supports automatic
interval labeling to avoid label collisions, customizable rotation angles, font size and
text-wrap properties for axis label calculations and so on.
Use text box report item enhancements.
In the text box report item you can mix fonts and colors, add bold and italic styles, use
paragraph styles such as alignment and hanging indents or you can format specific text,
numbers, expressions, or fields within the text box.
Consider using the Microsoft Word rendering extension.
SQL Server 2016 supports the Microsoft Word rendering extension that allows render a report
as a Word document that is compatible with Word 2007-2010 as well as Word 2003 with the
Microsoft Office Compatibility Pack. In this case, the reports exported to Microsoft Word
appear as a nested table that represents the report body. The file extension is DOCX.
Use Mobile Report Publisher.
The SQL Server Mobile Report Publisher allows you to create and publish SQL Server mobile
reports to your Reporting Services web portal.
Consider using the Microsoft Excel rendering extension.
SQL Server 2016 Reporting Services supports the Reporting Services Excel rendering extension.
This extension renders a report as an Excel document that is compatible with Excel 2007-2010
as well as Excel 2003 with the Microsoft Office Compatibility Pack. The file extension is XLSX.
Use Windows Performance console to monitor Report Server performance.
For example, to monitor Report Server performance open Performance console from
Administrative Tools in Windows 2008 Server and check the performance counters for the
RS Web Service performance object. For example, to check the number of active report
sessions you can see the “Active Sessions” performance counter; to check the number of
requests per second made to the report server you can see the “Requests/Sec” performance
counter; to find out whether the resources used for caching are sufficient you can see
the “Cache Misses/Sec” performance counter, and so on.
Consider using interactive sorting in reports.
In SQL Server 2016 you can change the sort order of your data while viewing a report. You
can sort data after a report is rendered by clicking the column headings in table and matrix
reports.
Use the fixed table headers for reports with a large amount of data.
This option can be set in the Table Properties dialog box. When this option enabled the table
header remains visible on the screen while a user scrolls down the report. This can simplify
the report viewing when report contains a large amount of data.
Consider using ad hoc reports.
In SQL Server 2016 you can create reports on an ad hoc basis by using the Report Builder report
authoring tool. Only the users who are assigned to the Content Manager role can create and edit
reports in Report Builder. For example, the local administrators are automatically assigned to
this role.
Avoid compressing temporary snapshots.
You can compress temporary snapshots before they are written to disk. The temporary snapshots
compression can be used to reduce the amount of space consumed by temporary snapshots and to
improve scalability if you have a larger number of report execution snapshots. Because the
temporary snapshots compression may significantly decrease reports performance, you should
only enable compression if the trade off in performance is worth the space you save through
compression.
Consider using the PowerPoint Rendering and Export.
SQL Server 2016 Reporting Services (SSRS) supports the Microsoft PowerPoint (PPTX) format
rendering extension. You can export reports in the PPTX format from the usual applications;
Report Builder, Report Designer, and the web portal.
Try to restrict the report queries result set by using the WHERE clause and by returning
only the particular columns from the table.
This can results in good performance benefits, because SQL Server will return to client
only particular rows and columns, not all rows and columns from the table(s). This can
reduce network traffic and boost the overall performance of the report query.
Consider dedicating a specific Report Server as the front end for all Report
Builder requests.
When dedicate Report Server is used to isolate Report Builder sessions the unpredictable
load cannot affect interactive report processing for other users.
Use Power View in SQL Server 2016 Reporting Services.
SQL Server 2016 Reporting Services support Power View an interactive data exploration,
visualization, and presentation browser-based application, launched from Microsoft SharePoint
Server 2016. It provides access to data represented by tabular models based on PowerPivot
workbooks published in a PowerPivot Gallery. Power View reports are in a new file format, RDLX.
Consider specifying two or more values for a single parameter.
SQL Server 2016 Reporting Services supports multivalued parameter, so you can specify
two or more values for a single parameter. You can define a multivalued parameter by
selecting the Multivalued option in the Report Parameters dialog box in Report Designer.
If you use scheduled reports, try to schedule them during periods of low database access.
Because reports scheduling may be very resource effective, try to schedule them during CPU idle
time and slow production periods.
Consider hosting the report catalog on a separate server.
You can split out the report catalog to gain significant scalability in performance and load
by adding processors.
Check Reporting Services log files.
You should check these files because Reporting Services log files record information about
report server operations that can be useful for performance tuning. The Reporting Services
events are logged into Windows Application Log, into Reporting Services Trace Logs and into
Reporting Services Execution Logs. If you need to optimize report’s performance, the first
place to check should be a Reporting Services Execution Log. The Reporting Services Execution
Log captures data specific to individual reports, including when a given report was run,
identification of the user who ran it, where the report was delivered, the size of each
generated report, and which rendering format was used.