I was looking at benchmarks and best practices that one should follow. Following is the extract from various articles, blogs & sites. It varies from client to client and depends on number of users, hardware and various other factors.
We are only considering settings for SQL server 2008 R2 on Windows server 2008 R2, there may be some more recommendations for other environments.
Note: To reduce the length of the blog, I am not mentioning “How tos?” in this post.
Physical Storage
– Data files and transaction log files should be on separate physical stores.
– Tempdb files should not be on RAID 5 (RAID 1, 0+1, or 10 preferred).
– AX transaction log file should not be on RAID 5 (RAID 1, 0+1, or 10 preferred).
– AX data files should not be on RAID 5 (RAID 0+1, or 10 preferred).
– Other database files (if any, such as databases for performance monitoring) should be on separate physical store from AX and Tempdb database files.
Temp DB
– Determine TempDB size and allocate it, need to avoid auto grow. Single processor should have one TempDB file.
– Move TempDB data file and log file to high-speed storage. Re-size data file proportionally to total size and resize log file to required size.
– Review tempdb data and log files to ensure that they are all sized correctly and data files remain of equal size.
DB Configuration
– Set compatibility level to 100
– Set Read-Committed Snapshot Isolation = true
– Set Auto-Create Statistics, Auto Update Statistics = True
– Set Auto Update Statistics Asynchronously = FALSE
– Ensure Auto Shrink = False.
– Auto grow should be configured in MB 100 or 500 MB instead of % for both data and log files.
System:
– Set “Adjust for best performance of” parameter to “Background services”.
– Run SQL Server service under a Domain Account with minimum necessary privileges. [This is b/c of security and to avoid any hacking]
– Enable TCP/IP network protocol. It may already be enable.
– Disable Hyper threading.
– Set “Max Degree of Parallelism (MAXDOP)” to 1 i.e. disable it.
Disclaimer: these are just my findings through reading different blogs and articles, anyone may agree or disagree with me and can have different opinion.
References:
http://blogs.msdn.com
http://technet.microsoft.com/en-us/library/cc966425.aspx#XSLTsection133121120120
http://www.techrepublic.com/blog/datacenter/capturing-sql-server-2005-database-file-size-information/292
http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/
http://technet.microsoft.com/en-us/library/ee355047(AX.50).aspx
http://technet.microsoft.com/en-us/library/cc966534.aspx
http://sqlcat.com/sqlcat/b/top10lists/archive/2010/05/20/top-tips-for-maximizing-the-performance-amp-scalability-of-dynamics-ax-2009-systems-on-sql-server-2008.aspx
http://technet.microsoft.com/en-us/library/cc966414.aspx
http://blogs.msdn.com/b/ukax/archive/2009/09/29/sizing-tips-on-ax-2009.aspx
