4

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

Improve this question
3
  • 1
    (1) why do you have 2 log files? (2) why are all your files different sizes and different autogrow settings? (3) why would you ever use % for autogrow? (4) are you using TF 1117/1118? (5) are you sure SQL Server is using all of your processors (check the error log)? (6) how many actual different physical drives do you have?
    – Aaron Bertrand
    Commented May 11, 2015 at 15:40
  • @AaronBertrand It had been set by previous developers and I am an accidental DBA :( I have around 6 drives with 1 drive is 1 TB where they have placed tempdb database,
    – Zerotoinfinity
    Commented May 11, 2015 at 15:46
  • check this stuff for tempdb. It covers what Aaron has already told you.
    – Kin Shah
    Commented May 11, 2015 at 16:28

1 Answer 1

Reset to default
4

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

Improve this answer
2
  • +1 for clear instruction. I have a question here. Will there be any impact on my database if I change the tempdb settings ? Like changing autogrowth, intital size, deleting extra log file and adding new data file to it?
    – Zerotoinfinity
    Commented May 11, 2015 at 17:31
  • Not on your user databases. You might even be able to shrink the primary tempdb file down and get the temp database configured without restarting the engine.
    – Spörri
    Commented May 11, 2015 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.