Manage Learn to apply best practices and optimize your operations.

Understanding database requirements for report server

Disk space requirements can vary due to many variables. Learn to understand your disk space by ,over time and during high-use periods, monitoring the size of your report server database .

A report server database provides internal storage to one or more report servers. Disk space requirements can vary widely and are difficult to predict. Variables include the number of servers and users serviced by a single database and whether you persist full reports that include data (cached reports or report histories, for example).

To understand your disk space requirements, you must monitor the database size over time and during high-use periods. The topics I mention in the following sections affect the overall space requirements in a report server database.

Reports, folders, shared data source items, and metadata

Report definitions, folders, shared data source items, and other metadata such as schedules, subscriptions, and properties are stored in a report server database. The storage space for these items is small in comparison to the overall storage.

Resources

Resources are stored as binary large objects (BLOBs). If you store image files and collateral documents with your reports, the amount of space you allocate to resources can be small. However, if you use resources as part of an archiving strategy (for example, uploading a generated report as a PDF file), your storage requirements for resources could be very large.

Session state information

Session state information is stored in temporary tables that grow in response to the number of open sessions. Space requirements vary based on the number of users. One row is created for each new session. Unless you have a very large number of users, session state data is not a significant consideration in estimating database size requirements.

Cached reports

Cached reports are stored in temporary tables for a period of time (a cached copy may expire after a number of minutes or at a scheduled time). A cached report includes query results. It can be far larger than the report definition upon which it is based. If caching reports is part of your performance plan, you should allocate a sizeable amount of space for these reports. For parameterized reports, a separate cached report can be created for every combination of parameter values. For example, if a report has a Region parameter that accepts North, South, East, and West as values, a cached copy for each region may be created.

Report history snapshots and report execution snapshots

Snapshots, whether saved as report history or used only for performance gains, are stored in the report server database (not in temporary tables). As with cached reports, these items may include a large row set. If you use report history to archive reports, you must plan on allocating more space over time to accommodate additional snapshots.

All the items I describe in the preceding sections are allocated space in a report server database. Although I describe each item separately, you cannot allocate or control space for individual item categories. For example, you cannot specify maximum limits for resources, caching, or report history. When estimating database size requirements, you must consider all these items as a whole.

Providing adequate space for caching

If caching reports is part of your performance plan, you should allocate a sizeable amount of space for these reports.

Optimal performance comes from minimizing response times and maximizing throughput; these, in turn, depend on efficient network traffic, disk I/O, and CPU time. This goal is achieved by thoroughly analyzing the application requirements, understanding the logical and physical structure of the data, and assessing and negotiating tradeoffs between database usage.

Response time is the length of time required for the first row of the result set to be returned to the user in the form of visual confirmation that a query is being processed. Throughput is the total number of queries handled by the server during a given time. As the number of users increases, so does the competition for a server's resources, which in turn increases response time and decreases overall throughput.

Factors that affect overall system performance include system resources, network architecture, the operating system, and the other database and client applications running on the server.

The previous tip was excerpted from Chapter 14, 'Optimizing Report Performance,' from the book "Microsoft SQL Server 2005 Reporting Services for Dummies" by Mark Robinson, courtesy of Wiley Publishing. Click here for the complete collection of book excerpts.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close