Sql-server – Tempdb change settings and recommendation

sql serversql-server-2012tempdbtransaction-log

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive .
So, as per recommendation I am planning to change it

enter image description here

Change #1 Adding 2 more .mdf files on different drive.

Change #2 Change initial size of tempdb .mdf size to 1024 and autogrowth to 10%.

Change #3 Change auto-growth of .ldf to 50%

I have three questions:

  • Do you have recommendation or correction needs to be made?
  • If I create new file and when I will change the intial size or autogrowth, do I need to restart SQL Server or can it impact SQL Server?
  • Do I have to create additional .ndf or log file also?

Reference of my other question

How to minimize log operations in SQL Server to avoid "log full" error

Dealing with non active transaction in log file and shrinking

IO request time and less write latency

As per paul recommendation, do I have to create 20 temdb files

enter image description here

Best Answer

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed.

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Now also make sure that the tempdb files are created such that you don't need auto growth and that they are all the same size with the same growth parameters not in percentiles.

Your tempdb is now ~140 GB so that is about the size you need to provision for. Create 8x15-20 GB datafiles and a single log file (as transaction logs are used sequentially) set all the files with exactly the same growth parameters, something that makes sense on your storage 512mb is fine if you have instant file allocation. As your transaction log is just about 5GB create a single 6-8GB file for the transaction log and set autogrowth to be in MB. Log growth is not affected by instant file allocations so make that parameter smaller than the one on the database files.

If you still see contention then go for 16x 8-10 GB files and dont change the transaction log parameters

Related Question