TempDB Configuration using 8 TempDB Data File

Question

On a production server with 512 GB RAM , 2 Sockets 24 Cores (Total Logical Processors 48), i have configured to have 8 TempDB Data Files ( on Primary) with each 4096 MB growth of 1024 MB and Log File set to 2048 MB with 1024 MB growth and Trace Flag 1117 configured in the startup.

the OLTP database of approx. 100 GB data file is been accessed by Dynamics AX application. the AX application server is on another server.

The concern is that the AX application is running very slow even after rebuilding/reorganizing indexes and setting the MAXDOP as per the AX Dynamics recommendation.

For which i tried to find the Avg Write performance of all database from sys.dm_io_virtual_file_stats (io_stall_write_ms / num_of_writes < 20) it shows above 100 ms. though the read performance is well below 10 ms.

The TempDB is stored in the different Disk Drive (which is dedicate cluster storage H Drive - free space 66 GB ) and other DB Files are stored on different Disk Disk ( Cluster Storage Drive).

What needs to be done in this case to improvise the write performance. Do i increase the TempDB Data File size more?

attaching the image of the output from sys.dm_io_virtual_file_stats.

?

Answer 1

Check these articles for some recommendation.

https://cloudblogs.microsoft.com/dynamics365/no-audience/2015/09/25/ax-performance-analyzing-key-sql-server-configuration-and-database-settings/

https://learn.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/configure-sql-server-and-storage-settings

Also, you could check for tempdb auto grow events, if there are, you could enable IFI, also check if Lock Pages in Memory is enabled.