SQL Performance Tune

In the doc there is a script…if my server has 12 vCPUs, do I put my value as such

WHERE type_desc = ‘ROWS’
SELECT @cpu_count = cpu_count
FROM sys.dm_os_sys_info
WHILE @file_count < @cpu_count * 0.08 – Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
SELECT @logical_name = ‘tempdev’ + CAST(@file_count AS nvarchar)

Regardless of the number of CPU cores, if one configures their tempdb to have eight equally sized row data files all with the same autogrowth settings, that should cover almost all use cases. We have actually modified the recommendation for tempdb files to 8 in our PDT Config Check rule for everyone, so that is what I would recommend (and I’ll look into updating the performance tuning document to reflect the current recommendations that are being used by the PDT). The script I send out when the Config Check rule for this shows a failing status for the number of tempdb files is below:

The following script will set the existing tempdb file to an initial size of 5GB with an autogrowth setting of 200MB fized with no maxsize(unlimited), and create an additional seven files with the same settings. You may want to change the values or the number of additional tempdb files depending on your use case. Using the values below, the tempdb files will consume at least 40GB of tempdb files (5GB * 8 tempdb files) on the same drive as the original/current tempdb rows data file.

USE [master]
DECLARE @file_count     int,
        @logical_name   sysname,
        @file_name      nvarchar(520),
        @physical_name  nvarchar(520),
        @alter_command  nvarchar(max)

SELECT  @physical_name = physical_name
FROM    tempdb.sys.database_files
WHERE   name = 'tempdev'

SELECT  @file_count = COUNT(*)
FROM    tempdb.sys.database_files
WHERE   type_desc = 'ROWS'

WHILE @file_count < 8
    SELECT  @logical_name = 'tempdev' + CAST(@file_count AS nvarchar)
    SELECT  @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')
    SELECT  @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' +  @file_name + ''', SIZE = 5120000KB, MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB)'
    PRINT   @alter_command
    EXEC    sp_executesql @alter_command
    SELECT  @file_count = @file_count + 1


PUBLIC SERVICE ANNOUNCEMENT: The Performance and Diagnostic Tool (PDT) Config Check contains our current best practices regarding performance, configuration, and stability settings. My recommendation for everyone is to download the latest version and execute the Config Check often. Internally we are testing a update checker mechanism with the PDT so when there is an updated version it asks you if you wish to download and apply it - hopefully that will be GA soon.


Thank you. I remember in the Perf Tuning workshop at Insights we added the 7 temp files…but thought perhaps this had changed.

Thanks again.

So I am new to this and I ran the script and the server crashed. :slight_smile:

When the 2014 instance was installed of SQL, everything was created at default. I have since updated where the MDF and LOGs reside, but the tempdb was still on C. So when I ran the script it crashed.

The joy.

How do you auto-grow your Epicor DB and LOG Files so they don’t timeout MRP Runs?

The performance and diagnostic tool (PDT) Config Check reviews the free space (aka: air aka: empty pre allocated space) of the MDF and size of the LDF. The specific KBs that discuss how to pre autogrow your MDF and shrink your LDF are below.

NOTE: So, to prevent growth of the LDF you’d follow the steps for the MDF pre-growth, but, instead select your log file instead in SSMS.

SQL003 SQL Server Available Space - MDF File space available KB0029172
SQL003 SQL Server Available Space - LDF File space available KB0029086