Fork me on GitHub

AX/SQL Best Practices

Check current SQL Server values: 

EXEC sp_configure;

1) Trace flags 4136 ,1117 and 4199 enabled on SQL server. 

DBCC TRACEON(4136,-1) 
DBCC TRACEON(1117,-1) /* If you've configured multiple data files and you want to allow auto grow, consider enabling trace flag 1117, which will force all data files to grow uniformly so you don't break the load balancing between files. */ 
DBCC TRACEON(4199,-1) 
DBCC TRACESTATUS

2) Disabling index hints from AX configuration and thereby allowing SQL to decide the best way to process the query.

3) Making sure that entire table cache is not enabled for tables which get frequently updated (Most of tables are updated frequently).

4) SQL Server service account is not granted SE_MANAGE_VOLUME_NAME rights (I keep them granted). 1 2 3

5) Reduce the MAXDOP setting to 1 and test it. MAXDOP Settings to Limit Query to Run on Specific CPU (Tested. No Difference.). 4

USE MicrosoftDynamicsAX 
GO 
SELECT * 
FROM SALESLINE 
ORDER BY NAME 
GO 
SELECT * 
FROM SALESLINE 
ORDER BY NAME 
OPTION (MAXDOP 1) 
GO

MAXDOP

6) Disable Power Saving setting on DB server and change to High Performance. Taking reference from Microsoft© on performance improvement, power saving will reduce SQL performance by 50%.

7) Disable Debug Settings (Not tested yet.).

8) Disabling Error Reporting in AOS servers (Not tested yet.).

9) Add AOS processes to antivirus software exclusions list (AX32.exe, AX32serv.exe, etc.)

10) For MicrosoftDynamicsAX database: 

ALTER DATABASE MicrosoftDynamicsAX 
SET COMPATIBILITY_LEVEL = 100 
GO
ALTER DATABASE MicrosoftDynamicsAX 
SET READ_COMMITTED_SNAPSHOT ON 
GO
ALTER DATABASE MicrosoftDynamicsAX 
SET AUTO_CREATE_STATISTICS ON 
GO 
ALTER DATABASE MicrosoftDynamicsAX 
SET AUTO_UPDATE_STATISTICS ON 
GO 
ALTER DATABASE MicrosoftDynamicsAX 
SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO 
ALTER DATABASE MicrosoftDynamicsAX 
SET AUTO_SHRINK OFF 
GO

11) For SQL Server instance (AXSQL) 5: 

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
sp_configure 'max degree of parallelism', 1; /* For production environment */ 
GO 
RECONFIGURE WITH OVERRIDE; 
GO

12) Configure max server memory: 

/* Turn on advanced options */ 
EXEC sp_configure'Show Advanced Options', 1; 
GO 
RECONFIGURE; 
GO 
/* Set max server memory = 50000MB for the AXSQL server with 64GB RAM */ 
EXEC sp_configure'max server memory (MB)', 50000; 
GO 
RECONFIGURE; 
GO

13) Move tempdb (tempdev and templog files) to high-speed storage (SSD or at least another HDD).

14) Spread tempdb over multiple files (8). 6 7: 

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb3.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb4.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb5.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb6.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb7.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb8.ndf' , SIZE = 512MB, FILEGROWTH = 256MB); 
GO

15) Use RAID 10. Do not use RAID 5.

16) Store the data files for the MicrosoftDynamicsAX database on separate physical stores from the transaction log files.

References

  1. SQL Server Premier Field Engineer Blog, 22 December 2009, How and Why to Enable Instant File Initialization [online] Available at: http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx [Accessed 17 October 2014]
  2. Aschenbrenner K., SQLpassion, 18 February 2014, Improving SQL Server Performance by using Instant File Initialization [online] Available at: http://www.sqlpassion.at/archive/2014/02/18/improving-sql-server-performance-by-using-instant-file-initialization/ [Accessed 17 October 2014]
  3. Bolton C., Langford J., Berry G., Payne G., Banerjee A., Farley R, LogicalRead, n.d., Configuration Best Practices for SQL Server Tempdb Initial Sizing [online] Available at: http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-initial-sizing-w01/#.VI3Fd9IpW-9 [Accessed 17 October 2014]
  4. Dave P., SQLAuthority, 1 March 2010, SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU [online] Available at: http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/ [Accessed 17 October 2014]
  5. Microsoft TechNet, n.d., max degree of parallelism Option [online] Available at: http://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx [Accessed 17 October 2014]
  6. zarez.net, 17 July 2013, Add Data Files to SQL Server tempdb Database [online] Available at: http://zarez.net/?p=2130 [Accessed 17 October 2014]
  7. Slicer J. C., StackOverflow, 06 April 2009, How to spread tempdb over multiple files? [online] Available at: http://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files/719953#719953 [Accessed 17 October 2014]

Sources

Comments !

links

social