server database. The storage space for these items

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. Configuring Reporting Services Components Behind-the-scenes components of Reporting Services affect overall system performance. A basic knowledge of how to use these components, how to set their properties, and what to turn off are important in optimizing system performance. I discuss how to deal with these components in the following sections. Configuring and running large reports If you re working with a large report, you must choose report generation, rendering, and delivery options that can accommodate large documents. Report 266 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

server database. The storage space for these items

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. Chapter 14: Optimizing Report Performance 265

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

solution is the key to great performance in

Before you start tuning, consider the following recommendations: Make one change at a time. In some cases, a problem that appears to relate to a single component might be the result of bottlenecks involving multiple components. For this reason, it is important to address problems individually. Making multiple changes simultaneously may make it impossible to assess the impact of each individual change. Repeat monitoring after every change. This is important for understanding the effect of the change and to determine whether additional changes are required. Proceed methodically, making only one change at a time to the identified resource and then testing the effects of the changes on performance. Because tuning changes can affect other resources, it is important to keep a record of the changes you make and review the record after you make each change. Review event logs. In addition to monitoring, review these logs because some performance problems generate output you can display in Event Viewer. For more information about using Event Viewer, see the earlier section, Using Event Viewer. Compare the performance of network programs to locally run programs. This can help you determine whether network components are playing a part in performance problems. Understanding Database Requirements for Report Server 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 264 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

solution is the key to great performance in

solution is the key to great performance in terms of the speed of information retrieval. This means avoiding embarrassing conversations with your end users about how slow reports are running. Many aspects of a reporting solution can influence the performance of the entire solution. I cover some of these areas in the following sections. Benchmarking system performance Before system performance can be monitored, you must know what level of performance is reasonable given normal working conditions. To do this, establish a server performance baseline by monitoring the report server performance at regular intervals, even when no problems occur. Searching for performance bottlenecks Demand on system resources may become extreme enough to cause bottlenecks for the following reasons: Resources are insufficient and additional or upgraded components are required. Resources are not sharing workloads evenly and need to be balanced. A resource is malfunctioning and needs to be replaced. A program is monopolizing a particular resource; this might require substituting another program, having a developer rewrite the program, adding or upgrading resources, or running the program during periods of low demand. A resource is incorrectly configured and configuration settings need to be changed. Tuning system performance Insufficient memory is the most common cause of serious performance problems in computer systems. If you suspect other problems, check memory counters to rule out a memory shortage. Poor response time on a workstation is most likely the result of memory and processor problems; servers are more susceptible to disk and network problems. Chapter 14: Optimizing Report Performance 263

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Table 14-1 Report Services Performance Counters Counter Name

Table 14-1 (continued) Counter Name Description Total Reports Executed Total number of reports that ran successfully after the service started. Total Requests Total number of all requests made to the report server after the service started. You can use Performance console and Event Viewer to create logs and alerts about report processing and resource consumption. To create a performance log, proceed as follows: 1. In Control Panel, open Administrative Tools and open Performance. 2. In the Windows Performance tool, expand Performance Logs and Alerts, right-click Counter Logs, and then click New Log Settings. 3. Type a name for the counter log and click OK. 4. Click Add to add as many counters as necessary for your Notification Services instance and other important values, such as processor time, disk time, and database counters. The number of counters you add doesn t significantly affect system performance. 5. Click Close. 6. Click the General tab and, under the section Sample Data Every, configure a sampling interval. Start with a moderate sampling interval, such as five minutes, and then adjust the interval if necessary. The shorter the interval, the more system resources and disk space will be used. Additionally, intervals shorter than the quantum period can cause a performance report to show sporadic performance numbers because you will have processing spikes mixed with no activity. 7. On the Log Files tab, configure the properties of the log file. You can view comma-delimited files later in a reporting tool such as Microsoft Excel. 8. On the Schedule tab, configure a monitoring schedule. Strategy for Performance Tuning Having a finely-tuned engine is the key to achieving the best gas mileage. Better gas mileage means fewer trips for fill-ups. Having a finely-tuned reporting 262 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Table 14-1 Report Services Performance Counters Counter Name

Table 14-1 Report Services Performance Counters Counter Name Description Active Sessions Count of all browser sessions generated from report subscriptions (active or not). Cache Hits/Sec Number of requests per second for re-rendered cached reports. Cache Misses/Sec Number of requests per second that failed to return a report from cache to determine whether the resources used for caching (disk or memory) are sufficient. First Session Number of new user sessions started from the report Requests/Sec server cache each second. Memory Cache Number of times per second that reports are retrieved Hits/Sec from the in-memory cache (no query of SQL Server for cached content). Memory Cache Number of times per second reports could not be Misses/Sec retrieved from the in-memory cache. Next Session Number of reports rendered from a session snapshot. Requests/Sec Report Requests Number of reports currently active and being handled by the report server. Reports Executed/Sec Number of successful report executions per second. Requests/Sec Number of requests (all types) per second made to the report server. Total Cache Hits Total number of requests for reports from the cache after the service started. Total Cache Misses Total number of times a report could not be returned from the cache after the service started. Total Memory Cache Total number of cached reports returned from the in- Hits memory cache after the service started. Total Memory Cache Total number of cache misses against the in-memory Misses cache after the service started. Total Processing Total number of report processing failures that have Failures occurred after the service started. Total Rejected Threads Total number of data processing threads (one data source per thread) in excess of capacity, requiring serial processing. (continued) Chapter 14: Optimizing Report Performance 261

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Using Event Viewer, you can define logging parameters

Use performance monitoring tools to monitor report server performance by evaluating server activity, observing trends, diagnosing system bottlenecks, and gathering data that can help you determine whether the current system configuration is adequate. To access Performance console, follow these steps: 1. Choose Start.Performance from the Administrative Tools menu to open the System Monitor. The System Monitor appears. 2. Add Counters to the list by right-clicking in the grid showing various counters in the view and selecting Add Counters from the menu that appears (see Figure 14-1). 3. Select the RSWeb service counters. In the Add Counters dialog, select RSWeb Service from the drop- down box for the Performance object. Then click the All Counters radio button. Finally, click OK. 4. View the System Monitor. You see all RD Web counters below the chart displayed on the performance monitor, each corresponding to a unique color on the display. I describe the counters available for monitoring in Table 14-1. Figure 14-1: Security roles in the Object Explorer with the task permission for the Browser role on the right side. 260 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Using Event Viewer, you can define logging parameters

Using Event Viewer, you can define logging parameters for each kind of event log. To define parameters, right-click a log in the console tree and click Properties. On the General tab, you can set the maximum size of the log and specify whether the events are overwritten or stored for a certain period of time. Using Performance console You can use the Performance console to monitor specific performance counters. These counters enable you to Estimate system requirements needed to support an anticipated workload Create a performance baseline to measure the impact of configuration changes or application upgrades Monitor application performance under certain loads, whether real or artificially generated Verify that hardware upgrades have the desired impact on performance Validate that changes made to your system configuration have the desired impact on performance Reporting Services includes two performance objects: RS Web Service, which monitors report server performance, and RS Windows Service, which monitors scheduled operations and report delivery. The RS Web Service performance object includes a collection of counters used to track report server processing typically initiated through interactive report viewing operations. These counters are reset whenever ASP.NET stops the Reporting Services Web service. The RS Windows Service performance object includes a collection of counters used to track report processing that is initiated through scheduled operations. Scheduled operations include subscription and delivery, report execution snapshots, and report history. If you have multiple report server instances on a single computer, you can monitor the instances together or separately. Choose which instances to include when adding a counter. For more information about using Performance console and adding counters, see the Microsoft Windows product documentation. Chapter 14: Optimizing Report Performance 259

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Chapter 14 Optimizing Report Performance In This Chapter

Using Task Manager Task Manager provides information about programs and processes running on your computer. You can use Task Manager to monitor key indicators of your report server s performance. You can also assess the activity of running processes and view graphs and data on CPU and memory usage. Using Event Viewer Using Event Viewer, you can view and set logging options for event logs in order to gather information about hardware, software, and system problems. A server records events in three kinds of logs: Application log: Contains events logged by applications or programs. For example, a database program might record a file error in the application log. Application developers decide which events to log. Security log: Records events such as valid and invalid login attempts, as well as events related to resource use such as creating, opening, or deleting files or other objects. For example, if login auditing is enabled, attempts to log in to the system are recorded in the security log. System log: Contains events logged by Windows system components. For example, the failure of a driver or other system component to load during startup is recorded in the system log. The event types logged by system components are predetermined by the server. Event Viewer displays five types of events: Error: A significant problem, such as loss of data or loss of functionality. For example, if a service fails to load during startup, an Error is logged. Warning: An event that is not necessarily significant, but might indicate a possible future problem. For example, when disk space is low, a Warning might be logged. Information: An event that describes the successful operation of an application, driver, or service. For example, when a network driver loads successfully, an Information event is logged. Success Audit: Any audited security event that succeeds. For example, a user s successful attempt to log on to the system is logged as a Success Audit event. Failure Audit: Any audited security event that fails. For example, if a user tries to access a network drive and fails, the attempt is logged as a Failure Audit event. 258 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Chapter 14 Optimizing Report Performance In This Chapter

Chapter 14 Optimizing Report Performance In This Chapter Monitoring report server performance Implementing a strategy for performance tuning Understanding report server database requirements Configuring Reporting Services components How many times do you have to stand in line on a given day? And how patient are you in that line? I would go as far as paying someone else to stand in a long line for me so that I can do something more productive (such as reprogramming my Gameboy to do my taxes) until it s my turn. This same logic applies to waiting for information in a report. This highlights the importance of optimizing your reporting system s performance. This chapter is about the performance considerations for your reporting environment. Monitoring Report Server Performance You can use performance monitoring tools to monitor report server performance to evaluate server activity, observe trends, diagnose system bottlenecks, and gather data that can help you determine whether the current system configuration is adequate. A combination of technologies and tools enables you to get comprehensive information about how the system is performing. Microsoft Windows Server operating systems provide performance information through the following tools: Task Manager, Event Viewer, and Performance console.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services