qDetermine 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.
qMove 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.
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).
qCreate 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.
qIf 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.
qReview 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.
AX User Database Configuration
qSet compatibility level to 90 (SQL 2005) or 100 (SQL 2008).
qSet 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.
qIf 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.
qPerform sector alignment before allocating storage volumes to SQL Server on most SAN environments.
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.
qAX User database data files and transaction log files are on separate physical stores.
qTempdb database files not on RAID 5 (RAID 1, 0+1, or 10 preferred).
qAX transaction log file is not on RAID 5 (RAID 1, 0+1, or 10 preferred).
qAX data files are not on RAID 5 (RAID 0+1, or 10 preferred).
qTempdb database files are on separate physical store from user database files.
qOther database files (if any, such as databases for performance monitoring) are on separate physical store from AX and Tempdb database files.
This entry was posted on Thursday, July 24th, 2008 at 12:36 am and is filed under How To. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.