Viewpoint Estimating SQL Server Tuning Guidelines
Descriptions of tuning parameters and settings for SQL Server and SQL Server Reporting Services (SSRS) which improve server performance.
Consider each setting and its potential effect to make an informed decision about its relevance to your system, workload, and performance goals.
SQL Server
- SQL Server Properties
- Service instances of SQL Server automatically tune themselves based on the production hardware and workload when the default configuration options are left in place. Under most circumstances, the server properties don’t need to be modified from their default installation values.
- tempdb
- The tempdb system database is commonly a performance bottleneck because it
is a global resource utilized by SQL server for many operations. Some of
these are:
- spools
- sorting
- data modification transactions
- Service Broker operations
For details on tempdb, including improving its performance, see this Microsoft article:
technet.microsoft.com/en-US/library/ms190768(v=sql.100).aspx
Pre-Allocation
The SQL Server creates a new tempdb each time SQL Server starts, so one technique to improve performance is to pre-allocate the size of tempdb’s data files (*.mdf) and log files (*.ldf) and make sure that auto-shrink is turned off for tempdb.
- Identify the window of time of your heaviest production load
- Monitor the total size of the tempdb files during this time
- Allocate at least 10% above that size.
More information can be found in this article:
technet.microsoft.com/en-US/library/ms345368(v=sql.100).aspx
Auto-Growth
As the size of your data set expands, performance will tend downward. You can intervene.
Set growth so that continuous growth operations don’t occur when you experience a spike beyond your initial size setting. The default setting of 10% should be adequate; however, keep in mind the performance of the disk subsystem. For example, if your I/O subsystem can initialize a file at 50 MB/second, set the growth increment to a maximum of 6 GB (rather than the 10% default setting), regardless of the tempdb file size.
For additional details on setting these parameters, see technet.microsoft.com/en-US/library/ms175527(v=sql.100).aspx.
In addition, you may want to use instant file initialization. See technet.microsoft.com/en-US/library/ms175935(v=sql.100).aspx.
Multiple Processor Cores, Multiple tempdb Data Files
Using multiple data files (.mdf) reduces tempdb contention and yields better scalability. Create as many files as needed to maximize disk bandwidth, being careful not to create too many files.
As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files as needed. A dual-core CPU is considered to be two CPUs; however, be careful not to include any virtual cores (such as hyper-threaded virtual cores).
Make sure each data file is the same size; this allows for optimal proportional fill performance.
For additional details, see technet.microsoft.com/en-US/library/ms175527(v=sql.100).aspx.
I/O Subsystem
Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
In addition, put the tempdb database on disks that differ from those used by the Viewpoint Estimating databases and other user databases.
Ideally, the Viewpoint Estimating databases, the tempdb data (.mdf) files, and the tempdb log (.ldf) file would all be on separate disks and disk subsystems.
If possible, place the tempdb files on a RAID 1 or RAID 10 array due to the high write activity. If you can use RAID 1 or RAID 10 arrays for each physical database file for the tempdb, you should achieve increased performance.
See the following Microsoft Technet article for additional details:
technet.microsoft.com/en-US/library/ms175527(v=sql.100).aspx
Antivirus Software
Microsoft provides information on antivirus software for SQL Server, including the following:
- Directories and file-name extensions to exclude from virus scanning
- Processes to exclude from virus scanning
- Considerations for clustering
See the following Microsoft Knowledge Base article for additional details:
Viewpoint Estimating User Database
Viewpoint Estimating system data is stored in a Viewpoint Estimating user database.
I/O Subsystem
Put the Viewpoint Estimating database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
In addition, put the database on disks that differ from those used by tempdb and other user databases.
Ideally, the Viewpoint Estimating database data files (.mdf) and log files (.ldf) would be on separate disks and disk subsystems as well as other user databases and tempdb.
If possible, place the Viewpoint Estimating data files on a RAID 1 or RAID 10 array to improve both read and write activities.
Advanced Tuning for High-End Server Hardware
Additional options are available for high-end server hardware to reduce tempdb contention; however, these options require advanced knowledge of SQL Server and Windows Server, so use caution if you explore these options.
See the following Microsoft support articles for additional details:
support.microsoft.com/kb/2154845
support.microsoft.com/kb/319942
In addition, Microsoft offers the System Center Advisor to help diagnose and make tuning recommendations.
SQL Server Reporting Services
In general, Viewpoint recommends that you run a 64-bit operating system and 64-bit version of SQL Server Reporting Services.
Memory
For installations where there is a large amount of memory installed, you may want to pin SQL Server to an amount of memory in order to guarantee a certain amount of free memory for SQL Server Reporting Services.
For example, if your server has 64 GB of memory, and (after a clean reboot) there is 50 GB of memory available to all Windows programs, set both the minimum and maximum memory values for SQL Server to 32 GB. This ensures that there is 18 GB free for SQL Server Reporting Services.
Note that doing this on systems with less memory may impact the overall performance of Viewpoint Estimating. In this case, tune the performance balance between SQL Server and SQL Server Reporting Services based on your hardware and your needs.
As always, the more memory you have installed, the better your performance is. More memory lessens the likelihood that tuning will be needed (which may impact performance elsewhere).
For additional details, consult the following MSDN article:
msdn.microsoft.com/en-us/library/ms159206(v=sql.100).aspx
Database Tuning
The recommendations described in this guide should be applied to the Report Server databases as well. ReportServer$PCMX correlates to the Viewpoint Estimating user database recommendations; ReportServer$PCMXTempDB correlates to the tempdb recommendations.
Running SQL Server Reporting Services on the same server as SQL Server and the Viewpoint Estimating user databases increases the likelihood that tempdb will suffer performance degradation. For this reason, Viewpoint recommends that you follow the recommendations for tempdb tuning described in this guide.
IIS-Hosted Tuning (Reporting Services 2005)
You can increase performance by changing the Maximum Number of Worker Processes setting in Windows.
To locate this Internet Information Server (IIS) setting, open the IIS Manager and view the properties for the application pool assigned to each of the Reporting Services virtual directories (vdir). By default, these are named Reports and ReportServer. You must first determine what application pool is assigned to each vdir by right-clicking on it and selecting the Properties option. The assigned Application Pool appears at the bottom of the Virtual Directory tab.
After you determine which Application Pool is in use, the next step is to view the properties of the assigned Application Pool. This is also accessible from within the IIS Manager. Next, locate the setting for Maximum Number of Worker Processes. Do this by right-clicking the assigned Application Pool, selecting the Properties option, and viewing the Performance tab. The setting for Maximum Number of Worker Processes appears at the bottom of the Performance tab.
On two-processor systems, set the maximum number of worker processes to 2.
On systems with four processors or more, set the maximum number of worker processes to 4.
Installing a Viewpoint Estimating server is a step in the installation process. See Install or Upgrade Viewpoint Estimating for details. The next step is reviewing the Viewpoint Estimating options. See Install Compatible Applications for details.
