Home > Developer > SQL Server
File System Storage
Other options for snapshot storage are the use of file system storage. Such a setting does not affect SQL compression settings because the data is stored in the file system.
File system snapshot storage is also appropriate for remote directories and extended, reporting service deployments. When the file system store is available, the snapshot data is persisted to the local file system of the report server. This allows the report server to avoid detours on the directory to support session and report requests.
You can control the use of the file system by changing the value of the webserviceusefilesharestorage in the RSReportServer. On config file, open the file system and change the value as follows:
When you change the value of a key to "false", the file system is turned off.
Note that by default, the file system stores the snapshot data to the file system directory: C:Program filesmicrosoft SQL servermssqlmssql.instancereporting servicesrstempfiles. CleanupCycleMinutes, located in RSReportServer. Config file controls how frequently unwanted filesystem data should be removed. You should be aware of the exact storage requirements of your system folders to avoid exceeding the storage limit.
If the report server uses a local directory instead of a remote directory, then using file storage is meaningless. The snapshot data is already stored in the SQL Server relational database, which is highly optimized for data storage and retrieval. Using File system storage to configure a local directory system introduces nonessential storage redundancy and snapshot management.
Effects of compression and file system storage
Microsoft tested the effects of compression and file system snapshot storage on the processor remote system. Both technologies can increase the capacity of the system in heavy load. Although compression increases the workload of the processor, it also reduces the amount of traffic between the report server and the directory.
Table 5
| AVG req/sec % of baseline |
Page views/hr % of baseline |
Peak Sessions attained % of baseline |
|
| No compression |
Baseline |
Baseline |
Baseline |
| SQL compression |
Baseline |
109% |
Baseline |
| File System Storage |
126% |
129% |
113% |
File system storage allows you to support more user sessions without reducing the response time. Clearly, file system settings provide the best scalability and capabilities. In a remote directory system that tests a processor, the number of concurrent user sessions can be supported more than 13% when the file system for snapshot data is opened.
Key points
You can increase the virtual power of existing systems by using more cache instances and snapshots instead of live reports.
For a high number of reporting environments, use file system snapshots to provide the highest possible level of competency and response time in outward scaling and remote server implementations.
Best Practices for performance optimization
This section provides an overview of the work guide and best practices for competency planning and performance optimization.
Select configuration
Looking back up and scaling up the guide, there is a system configuration spectrum that is becoming clearer in terms of reporting capabilities.
At the lowest end of the spectrum is a processor reporting server with a local directory. At the highest end is a report server that extends outward, providing scalable capabilities and performance. Although the test in this article contains only an outward-extending configuration, 4 processor-capable report servers and a remote directory, scaling out can easily become larger.
Between these two options, you can make the directory remote, add the processor to a local implementation, or remotely implement it, and split the processing in a single system on the Web farm method.
To choose the right configuration for your work environment, you first need to identify the specific performance requirements that you want. Here are some simple instructions:
If your system can provide more memory, add some. This is because memory consumption is the first bottleneck you will encounter.
If you are considering extensibility and running in a local directory, the first thing you need to do is to distribute the directory to a separate server. By splitting the directory only, you can gain significant performance scalability and increase the load by adding the processor.
Scaling up and scaling out is a good way for servers that reach 4 processors. Exceeding this number, scaling out may be the safest and most scalable approach. The large SMP system has not yet finished testing.
The outward extension of the externally extended configuration allows you to maintain a certain amount of processing, such as the design of real-time reports and the booking of report operations, without being hindered by interaction. Like what:
Use a specific report server as a front-end server to process all report creation requests, so that other servers are free to process interactive reports.
Use one or more report servers to handle the production of ordered subscriptions or event-driven reports.
General performance Optimization Techniques
Because the report server directory is managed by SQL Server, it is recommended that you optimize the performance of your SQL Server database if you suspect database performance issues. This may include the source database used to create the report or the directory database that is used to support the execution of all report services. You can find information on optimizing the SQL Server database in http://www.microsoft.com/technet/, in the "the Data tier:an approach to database optimization" and the online SQL Server book's performance evaluation section.
Here are some of the most common performance tuning techniques that you use when Reporting Services, when you write them down.
Optimize your report query. Typically, a large amount of report execution time is spent on executing queries and retrieving results. If you use SQL Server, like query Analyzer and Profiler tools, you can help optimize your query. At the same time, database Tuning Advisor can suggest a better database index. You should consider adding performance by using Analysis Services.
If you do not need the report data, please do not retrieve it. Using database operations, such as filtering, grouping, and clustering, can reduce the amount of data that is required to be processed in a report, and you can also improve performance.
Make your report size and complexity moderately. Few users really want to see a 1000-page report. If you need to process a large report, look for ways to make it a small part of the process.
If the performance is very poor even for a single user, check the application restart counter in the ASP.net category. Some antivirus software is considered "touching" the configuration file, which causes the expanded application domain to restart on the Report Server Web service. For more information, see the articles on antivirus and asp.net on http://support.microsoft.com/
If the performance is slow during the first Web service access and there is no response for a period of time, the idle time-out on the Performance tab is invalidated in IIS Manager.
If possible, execute the report from the cache or snapshot data, rather than the live data.
During off-peak periods, limit unwanted background processing and avoid competing with online users for resources.
If your report server has 4GB of RAM, remember to set 3GB conversion on C:oot.ini so that it can be used when the application is processed.
Regular tasks such as cleaning up sessions in the report service will become very resource-intensive, if the user is on the system while executing. about how in RSReportServer. The instructions for adjusting the CleanupCycleMinutes interval on config file are discussed in Appendix A.
Create a report server directory on a small number of data files by placing the data in a log file on a different physical disk. These are detailed in Appendix A.
Working with Web Farms
On the MICROSOFT®WINDOWS®2003 Server Edition, you can expand by setting the maximum number of working processors. To locate the settings for IIS, open the IIS Administration Tool, and then view the properties of the application pool. By default, these are named reports and ReportServer. You must first decide what application pool is assigned to each virtual directory, by right-clicking and then selecting Options. The allocated application pool will appear at the bottom of the Virtual Directory tab.
When you know the application pool is in use, the next step is to open the attribute information for the application pool. This information can be accessed through IIS Manager. Next, locate the maximum number of working processors. You can see Performance tab by right-clicking and then selecting Options. The maximum number of working processors you set will appear at the bottom of the Performance tab.
Increased working processors have a positive impact on capabilities and scalability on both the processor and processor systems.
In particular, the maximum number of working processors from 1 to 4 on the processor system will enable the report processor to handle larger concurrent sessions before performance begins to fall. Changing settings on the processor's remote implementation is less affected than the remote implementation of the processor. If you are using a reporting service system that has more than 4 processors, 32-bit addresses may not be able to provide enough memory. You should take additional tests.
Optimization of other work
While this paper focuses on interactive work, here are some tips for scheduling and real-time reporting. Other guidance will appear in a later white paper.
Scheduled report Delivery Performance
A scheduled or subscribed report has the advantage that it can occur within your control. If your reporting service environment provides both scheduled and on-demand reports, you can use a separate report server to process the report subscriptions. You can then access directories like other report servers, but do not manage interactive requests. In this way, on-demand report processing cannot be transferred from the subscription report. As with interactive work, the extension of scheduled operations can be increased on an outward-extending configuration. All background operations are queued in the catalog database, and each connected server can launch a job for processing.
Real-Time report performance
The SQL Server 2005 Reporting Service provides a real-time reporting tool that enables business users to create reports interactively. Report creation includes a business query model that helps users build reports without having to consider too many of the following data sources.
When creating tools with reports, there is a risk that business users will consume a lot of resources when building and running complex, resource-intensive reports, which may affect not only the report server but also the source system.
If you expect the report creation tool to be widely used, you can take steps to protect the overall system performance, such as:
Apply a report creation session to a server so that unforeseen mounts do not affect the interactive report processing of other users.
Instead of having a report creation session work directly with an OLTP system, you are dealing directly with a backup of the product data source. For better performance potential, you can do some organization, such as database mirroring and replication technology, or through integration services to create a data market. In this way, you can avoid having an managed report creation impact on your product system.
Perform your own performance tests
To simulate the real world of working conditions is not a very complicated thing, take some time to do is very meaningful, but also worthy of recommendation. Many deployments have performance-related issues that do not carry out adequate stress testing prior to implementation. Go live. Proper planning of capacity and server size is essential to support product workloads, and can avoid a lot of headaches.
At a minimum, the following two tasks should be part of your deployment plan:
Simulate a representative workload and it may be the experience you need to implement a reporting service.
Perform some environmental tests that may be similar to your product environment.
By performing these two tasks and documenting some of the analysis criteria discussed above, you will be able to build a realistic performance baseline for future deployments. This baseline is very useful and you will use it to validate future changes in applications, configurations, and hardware. By using the established baseline, you can verify that some of the proposed changes do not adversely affect performance by simply running the same test again. Any time you make some important changes to the system, you should re-establish a performance baseline.
Microsoft and third parties have a number of stress testing tools that can be used to build performance baselines. The Microsoft®visual studio®2005 team Beta contains load tests for Web sites. For more information, refer to http://msdn.microsoft.com/.
Performance variables
To get a performance requirement, you should first understand your job needs. Some users use the reporting service through public access to deliver on-demand reports, others rely on scheduled report processing and delivery. Some use report creation tools to support live report creation.
Other factors that affect workload and performance are:
Number of users making report requests at the same time
The more active sessions, the more machine resources are consumed.
Size and complexity of report definitions
Complex reports, which are made up of large amounts of data, are clearly consuming more resources than simple reports.
Whether the report is generated by caching or snapshot data, or by using live data
Using cache or snapshot data to generate reports consumes less resources than using live data. In addition, the use of cached reports reduces the load of the source system.
The performance of the source data system, the report service is the source data system to obtain the data
If the query is slow on the original data system, the execution of the report will also be slow.
Formatting requests when displaying a report
Formatting like PDF or Excel consumes more resources than HTML.
Managing database server performance for a directory
As with the source data system, if the data server that manages the directory is poorly performing, the report is displayed more slowly.
Many things, for reasons of design or user configuration, can affect report performance. Include the following:
Application settings, such as the configuration of the report service profile, the configuration of IIS and the operating system.
Supports the hardware configuration and design of a reporting application.
External causes like delivery of infrastructure, including network capabilities, the performance of email servers delivered for subscriptions, and even the validity and performance of file sharing.
To better plan the ability to deploy the right Reporting Services, you first have to understand your workload characteristics. Due to business factors, your work may fluctuate from month to week or daily.
Analysis criteria
When you determine the performance baseline for your system, you should identify some meaningful criteria. Microsoft often uses the following criteria:
Synchronization session
Average number of requests per second
Value in response time
Total number of pages viewed per minute
The standard of a synchronization session is a good variable used to determine other criteria. For example, you start with 100 synchronization sessions and then generate a test workload with 100 increments each time.
The average request criteria per second helps you understand how many Web requests can be made at the same time under hypothetical configuration conditions. As long as the system is not doing a specific service, each system test should be able to equal all requests for service. When you draw the number of requests per second and synchronized sessions into graphs, look at the vertices of the graph, which is the maximum average request per second. It reflects the maximum number of peaks and sessions requested per second when the system is able to serve. It is obvious that the average request per second can easily be inferred, depending on the number of requests per minute or the number of requests per hour. Median response time is a common standard that can be used to understand how long the report service takes to respond to requests. Obviously, the shorter the time the better.
Depending on the number of pages viewed per minute, you can see the system configuration capabilities, the number of reports that can be executed, and concurrent page views. You can also get more detailed information about the page view, including the following information:
Initializing a report request
To obtain this information, simulate a user issuing an initialization request to view the first page of the report. Initializing a report request takes more time to process than concurrent page view requests.
Concurrent Page view
To get this information, simulate a user making a request to view a concurrent page of a report.
Total Page view
This is the sum of all the initialization report requests, plus all concurrent page views, and provides a summarized value that reflects the total number of pages that are requested to view.
Thinking time
In real life, before we make concurrent requests to see other pages or run other reports, we usually have to wait when we look at the results of the report.
In the internal test, Microsoft chose a random time to ponder between 25-35 seconds. This thinking time looks realistic, assuming that the user executes a report and spends a certain amount of time viewing the report results before accessing the concurrent pages.
Number of named Users vs. synchronized active sessions
A named number of users is not equal to the number of sessions that are activated by synchronization. When you plan your deployment, keep the following information in mind:
The number of users named indicates the number of people who have permission and potential to access the report service.
At any given moment, only a small number of named users can cause synchronous active sessions.
Because of the thinking time, at any given moment, only a small part of the synchronized activation session makes concurrent requests.
Summarize
Microsoft SQL Server Reporting Services are designed to meet the needs of an organization that is low in efficiency, and resilient reporting is used to optimize enterprise productivity. On the. NET Platform and Windows Server systems, Reporting Services provide scalability and reliability to meet the enterprise environment. Its standard extensible architecture, consisting of open interfaces and APIs, supports integration into almost all IT environments. In this way, it effectively uses the information associated with the users and their needs in the enterprise.
In most cases, adding machine resources enables the performance of report service deployments to grow linearly. The first logical step to increase system performance is to split the report services directory onto a remote server. You will then have to decide whether you want to expand or extend outward to achieve better reporting service performance. The Microsoft test proves that both methods are valid. Therefore, your choice depends more on your own financial situation, your desired comfort level and your preferences.
Ignoring your last choice of extension methods, it is important to build your own customer baseline. This allows you to monitor whether the system's performance improves or decreases as you change the configuration.
Only by measuring the workload to respond to user needs, you can decide whether the changes are positive or negative to the system.
Microsoft provides tools, as described in the white Paper, to help you measure and monitor the performance of your Reporting Services. Using the guidance provided in this article, you should be able to successfully perform the basic performance plan for your report service deployment while monitoring ongoing operations.
Appendix A: Settings for system configuration
When configuring your reporting service system, you should look at the following settings and options to optimize your system performance. They are very important when you create baseline performance measurements.
CleanupCycleMinutes
CleanupCycleMinutes domain controls how long Reporting Services perform specific routine transactions, such as clearing overdue or disconnected sessions. These efforts are more resource-intensive during the busy hours.
If you have disk space available to store session data for a long time, the increase in the CleanupCycleMinutes value reduces the time it took for regular transactions to execute.
The cleanupcycleminutes domain resides in RSReportServer. Config file. By default, the file is located on the C:Program filesmicrosoft SQL servermssqlmssql.instancereporting servicesreportserver. The default setting value is 10. And, to test its extensibility, Microsoft has changed the value to 1200, in order to perform these processes during the test run.
Maxactivereqforoneuser
By default, the report service restricts the apparent number of URLs and Web service requests in order to avoid DOS (denial of service) attacks. As a manager, you can add these restrictions so that more requests can be opened synchronously for each user. When the upper limit is reached, the report server automatically discards the subsequent request from that user. This value can also be found in RSReportServer.config file, with a default value of 20. You can also ask for a raise, depending on how many unique users are in the test.
Set memory configuration limits on SQL Server when using the same machine as a report server
You can divide the memory consumed by SQL Server, and the memory consumed by the serve through the "pinning" SQL Server, to a specific amount of memory. To do this, set the maximum and minimum amount of memory to a portion of the machine's physical memory.
For example, on a machine with 3GB of memory available, you can give the SQL SERVER1GB and leave the rest to reporting Services
REPORTSERVERTEMPDB partition
For extensibility testing, Microsoft created a partitioned reportservertempdb database consisting of 10 separate 1GB files and a 1GB transaction log to improve parallelism for disk I/O operations.
Partitioning the ReportServer database can also be beneficial if the snapshots in the workload have been used for testing. All 11 files are located on a separate RAID 0+1 storage device to optimize read and write operations to the directory.
The following is an example of a T-SQL script that describes the general meaning of how to proceed.
Appendix B: Performance Testing tools
This section describes a number of tools used to test and monitor report service performance.
Windows Performance Monitoring Tool
Performance statistics classes that control report services can be controlled through integrated performance tools. This tool is part of the microsoft®windows® operating system. Monitor specific performance statistics classes in report service execution so that you can do the following:
Estimate the system requirements that support the expected workload.
Create a performance baseline to measure the impact of configuration changes and application upgrades.
Monitor application performance under load to see if it's real or fake growth.
Verify that the upgrade hardware brings you the desired performance impact.
Verify that the system configuration changes bring the performance impact you expect.
If you need to monitor multiple report service instances from a single machine, you can monitor these instances together or individually. Choosing to include that instance is part of adding statistics. For information about using the performance tools contained in Windows, you can view the Windows product documentation.
Access Performance Tools
2. Click "Run" from the Start menu.
3. Enter Perfmon in the Open text box and click OK.
4. In the Performance tool, select the System Monitor object in the long square on the left and click on the Performance Map.
5. Select the Add statistic class.
You should start working on the above now.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
