q Determine total size of data and transaction log required for tempdb to avoid autogrow, and number of data files required based on # of processors (logical or physical).
Determine the number of processors exposed to SQL Server. Unless you are using an affinity mask (not covered here), this is the total number of processors you see in the Windows Task Manager Performance tab.
SQL Server creates one visible scheduler (for processing user requests) for each processor, and we generally want to maintain one tempdb data file per processor. This recommendation is based on performance testing on the Dynamics AX OLTP workload.
q Move tempdb primary data file and log file to high-speed storage (if available); at the same time, resize data file proportionally to total size, and resize log file to required size.
Moving tempdb primary data and/or transaction log file must be performed in two steps: first, alter the database and move the files; second, restart SQL Server instance to complete the move. The primary tempdb data file cannot be moved while SQL Server instance is running. NOTE: tempdb data and transaction log files may reside on the same storage device.
See http://technet.microsoft.com/en-us/library/ms174269.aspx (Section G) for detailed instructions on how to move tempdb database files.
Isolating tempdb on its own storage can improve performance. However, it is more critical to separate user database data and transaction log files (see section below).
q Create additional data files depending on number of processor cores, of equal size, totaling the data size determined in previous step, so that the aggregate size of data files including the primary data file meets the total size requirement. All data files must be identical in size.
Additional database data files can be created from SQL Server Management Studio (SSMS) UI or by using the ALTER DATABASE command.
Creating multiple files for tempdb data, even if these files reside on the same storage device, can improve performance of tempdb operations, especially for databases (such as the AX user database, see below) that run in Read-Committed Snapshot Isolation (RCSI) mode. RCSI stores row versions in tempdb.
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx contains a comprehensive set of Best Practices for managing the tempdb database.
q If space is available on the drive where tempdb files are allocated, configure auto grow in MB (100-500) rather than percent for both data and log files.
Auto grow configuration can be set from the SSMS UI or by using ALTER TABLE command.
Auto grow should not be used for space management, but rather as a “relief valve” in case tempdb files exhaust their initially allocated space. If files auto grow, the initial allocation should be adjusted to avoid auto grow in the future. Configuring auto grow in MB rather than percent increments allow for more predictable space allocation, and helps avoid extremely small or large growth increments.
q Review tempdb data and log files to ensure that they are all sized correctly and that data files remain of equal size.
The easiest way to check is to use the database properties -> files UI in SQL Server Management Studio; scan the data files size to ensure that they are the same and at their initial values. If not, it means more space was required and one or more files grew, and the initial files sizes may have to be adjusted accordingly to ensure that they files remain at the same size at all times.
q Set compatibility level to 90 (SQL 2005) or 100 (SQL 2008).
q Set Read-Committed Snapshot Isolation = true (cannot be performed through GUI).
Execute the following command with no other active connections in the database:
ALTER DATABASE <ax database name>
SET READ_COMMITTED_SNAPSHOT ON;
Query the row in sys.sysdatabases and ensure that the column is_read_committed_snapshot_on = 1. Replace <ax database name> with the appropriate name.
Testing with Dynamics AX ERP shows superior performance when using Read Committed Snapshot Isolation.
http://technet.microsoft.com/en-us/library/ms175095.aspx includes complete instruction on enabling RCSI.
http://technet.microsoft.com/en-us/library/ms188277.aspx includes a discussion contrasting Read Committed Snapshot Isolation and Read Committed Isolation.
q Set Auto-Create Statistics, Auto Update Statistics= True; set Auto Update Statistics Asynchronously = FALSE (this is a change from our previous recommendation)
These settings can be enabled from SQL Server Management Studio (SSMS) UI or by issusing the appropriate ALTER DATABASE statement.
Testing with Dynamics AX ERP shows superior performance when using these options.
q Ensure Auto Shrink = False.
q If autogrow is configured, use MB (usually 100-500) rather than percent for both data and log files.
Autogrow configuration can be set from SQL Server Management Studio (SSMS) UI or by issusing the appropriate ALTER or CREATE DATABASE statement.
When autogrow is specified in absolute increments rather than as a percent of the total size, the effects of autogrow are easier to anticipate and manage. Percent-based autogrow setting can err on either extreme, of creating too many small file increments in rapid succession, or creating overly large increments.
Autogrow should be considered a “relief valve” to allow database files to grow when absolutely necessary, rather than causing an error, but it should not be used as a long-term storage management solution. When an autogrow event occurs, the DBA should respond by reviewing the size of all database files and adjusting accordingly, to minimize the probability of future autogrow events. Space should be periodically reviewed and adjusted when necessary based, rather than having the system periodically expand files on its own.
Physical Storage Configuration
Note: Compliance is optional depending on storage resources available. Some SAN vendors may have alternate recommendation that take precedence. Recommendations are listed in priority order.
q Perform sector alignment before allocating storage volumes to SQL Server on most SAN environments.
NOTE: The following recommendations for sector alignment do not apply to Windows Server 2008. Partitions created under Windows 2008 are aligned to sector 1024 by default, which is an acceptable setting.
Consult with your SAN vendor if possible for specific guidance on the storage product you are using. In the absence of vendor-specific recommendations, volume alignment should be set on a 64K offset for SQL Server. See the Predeployment I/O Best Practices paper referenced below for detailed instructions. It is generally not necessary to perform volume alignment using Windows Server 2008, but check with your SAN vendor if in doubt.
Sector alignment (or volume alignment) ensures that logical disk sectors conform to physical sector boundaries on the disk geometry. Windows 2003 does not align sectors optimally for SQL Server workloads by default, causing performance problems because each logical sector spans two physical sectors.
Predeployment I/O Best Practices
Physical Database Storage Design
q AX User database data files and transaction log files are on separate physical stores.
q Tempdb database files not on RAID 5 (RAID 1, 0+1, or 10 preferred).
q AX transaction log file is not on RAID 5 (RAID 1, 0+1, or 10 preferred).
q AX data files are not on RAID 5 (RAID 0+1, or 10 preferred).
q Tempdb database files are on separate physical store from user database files.
q Other database files (if any, such as databases for performance monitoring) are on separate physical store from AX and Tempdb database files.