Ax Database Configuration Checklist Part 2
NOTE: This section has been updated to include special considerations for running SQL Server on Windows Server 2008. We have also modified our recommendations for setting Auto Update Statistics Asynchronously to FALSE from TRUE.
Tempdb database storage configuration
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).
How:
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.
Why:
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.
How:
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 https://technet.microsoft.com/en-us/library/ms174269.aspx (Section G) for detailed instructions on how to move tempdb database files.
Why:
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.
How:
Additional database data files can be created from SQL Server Management Studio (SSMS) UI or by using the ALTER DATABASE command.
Why:
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.
References:
https://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.
How:
Auto grow configuration can be set from the SSMS UI or by using ALTER TABLE command.
Why:
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.
How:
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
q Set compatibility level to 90 (SQL 2005) or 100 (SQL 2008).
q Set Read-Committed Snapshot Isolation = true (cannot be performed through GUI).
How:
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.
Why:
Testing with Dynamics AX ERP shows superior performance when using Read Committed Snapshot Isolation.
References:
https://technet.microsoft.com/en-us/library/ms175095.aspx includes complete instruction on enabling RCSI.
https://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)
How:
These settings can be enabled from SQL Server Management Studio (SSMS) UI or by issusing the appropriate ALTER DATABASE statement.
Why:
Testing with Dynamics AX ERP shows superior performance when using these options.
q Ensure Auto Shrink = False.
How:
These settings can be enabled from SQL Server Management Studio (SSMS) UI or by issusing the appropriate ALTER DATABASE statement.
Why:
Automatically shrinking database files can incur potentially severe performance problems. If it’s necessary to shrink these files, it can be performed manually at a time designed by the DBA.
References:
https://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
q If autogrow is configured, use MB (usually 100-500) rather than percent for both data and log files.
How:
Autogrow configuration can be set from SQL Server Management Studio (SSMS) UI or by issusing the appropriate ALTER or CREATE DATABASE statement.
Why:
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.
How:
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.
Why:
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.
References:
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.
Comments
Anonymous
October 28, 2008
This is important for DBAs! We are looked at as face of DB performance, even if it is really mis-configured hardware or poor code that is reducing performance. Know your SAN admins and make sure they know what they are doing.Anonymous
March 21, 2009
One important thought on tempdb -As tempdb IO and data IO are both random, and as long as IO does not exceed the controler's bandwidth:On seperate drives you may find the data drive saturated while there is still some capacity left on the tempdb drive.To avoid this situation, create a single bigger drive that has more bandwith from the disks for tempdb and data and put them both on this same drive. This way the controller balances the IO.Anonymous
May 24, 2009
Hi David,I am using AX4 on SQL2005. Is there any way to utilize snapshot isolation level with AX4? It probably involve code change as i cannot see any setting available, or it is even not possible?Thank you in advanced.Anonymous
May 25, 2009
What about table partition with large tables? Is it recommended for better performance with AX?Anonymous
December 01, 2009
hi,i think there is a mistake on how to check for the RCSI status of the db after running the commandALTER DATABASE <ax database name> SET READ_COMMITTED_SNAPSHOT ON;i think we should be querying sys.databases instead of sys.sysdatabases catalog view in order to check on the status.Anonymous
January 20, 2010
Hello, Do you have to create additional data files depending on number of processor cores, of equal size also for the AX USR database too? Thanks in advance TomAnonymous
February 01, 2010
The recommendation to allocate multiple data files, one per core, applies only to tempdb, not to the AX user database.Anonymous
May 13, 2010
Following on from above, it states that you should have 1 tempdb file for each core of a processor so if I have a 16 processor system each with 4 cores do I really need 64 files.This seems a little much?Anonymous
November 02, 2010
"1 tempdbfile per core" is no longer the case, starting with SQL 2005, see www.sqlskills.com/.../A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspxAs long as partitioning is NOT supported by your AX version, don't implement it (ever thought about resync)Anonymous
January 27, 2011
The comment has been removedAnonymous
February 23, 2011
q AX data files are not on RAID 5 (RAID 0+1, or 10 preferred).Is this right? I was under the impression RAID 5 or 10 preferred?Anonymous
June 28, 2011
My company is about to rollout Dynamics AX live next weekend.The database sizes are enormous.Is database partitioning allowed on AX databases?Anonymous
June 28, 2011
@JohnnyReaction: yes, you can partition the AX database. Other than making management a little more difficult you should not have any problems with regards to performance.Anonymous
June 28, 2012
Microsoft Dynamics CRM training will help you manage and prioritize your business goals, customize.we teach MS Dynamics CRM training and class available at Hyderabad. www.arrowsoltraining.com/microsoft-dynamics-crmAnonymous
April 30, 2013
On the recommendation of this blog post we implemented Read Committed Snapshot Isolation on our AX 2009 database several weeks ago. As I suspected, we saw a dramatic performance improvement and the elimination of the vast majority of our deadlocking issues.However, ever since RCSI was turned on, our weekly index rebuild job is failing to complete - it gets stuck, apparently waiting to acquire a table lock on the AIF tables (we're on SQL Server Standard, so no online index rebuilds). We are fairly heavy AIF users, and our public website hits AIF even in the middle of the night when our index rebuild job runs.We have tried putting the database in single-user mode, turning RCSI off, setting back to multi-user mode, performing the reindex, back to single-user mode, turning RCSI back on, then back to multi-user mode, but this past weekend the statement turning RCSI off was chosen as a deadlock victim so the job failed, leaving the database in single-user mode (bad news).How do we go about reindexing with RCSI enabled? Have you seen this issue with other AX implementations?Anonymous
December 03, 2015
Thanks for this checklist. Regarding the upper limit for autogrowth (500MB) I've often heard that 1024 is a recommended growth size / our DBA uses that as his standard. Is the 500MB upper limit based on a strong justification, or is it given as a fairly arbitrary ballpark figure just to say "don't make the growth amount too big"? Thanks in advance.