November 5, 2019 at 4:35 am
Hi All ,
I am reviewing the tempdb size and autogrowth .
I did search in the internet that saying "As a general rule, if the number of logical processors is less than 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."
Based on this rule , I review some of my servers , and I can see :
Server A
CPU : 2
Tempdb : 8 Tempdb data files
Server B
CPU : 4
TempDB : 5 Tempdb data files
Do I need to reduce the number of tempdb data file into this below configuration ?
Server A
CPU : 2
Tempdb : 2 Tempdb data files
Server B
CPU : 4
TempDB : 4 Tempdb data files
Also How do I know the correct size of my TempDb ? as in initial size and autogrowth ?
I want to know the best practice
SQL version is 2012 and 2016
Thank you
Your feedback is much appreciated
November 5, 2019 at 11:39 am
my understanding is that it is number of processors -1 up to a maximum of 8 for the number of tempdb files
initial size , you are going to have to guess based on your current size (effectively the biggest transaction you have had)
growth - do not use 10%, but don't make the growth rate too small... you will get constant delays while sql talks to the io subsystem... equally make it too big and you will have to wait for the extension to finalise.. - best idea, put tempdb on a separate disk and make it as big as you can afford
MVDBA
November 5, 2019 at 1:44 pm
To add something,
Its recommended to have all the tempdb data files the same size, that's why Microsoft have added a feature in which not only one file but all of them grow, it's available in 2016.
This is because, SQL Server would put more pressure in the largest file instead of balancing the load.
best regards,
November 5, 2019 at 7:38 pm
To add something,
Its recommended to have all the tempdb data files the same size, that's why Microsoft have added a feature in which not only one file but all of them grow, it's available in 2016.
This is because, SQL Server would put more pressure in the largest file instead of balancing the load.
best regards,
I hate MS for that change and similar "It just runs faster" changes. I had to rewrite some code that ran once per month.
I used to monitor TempDB a whole lot and it never grew individual files. They all stayed the same size naturally except for when I needed to run that once per month job, which caused a sort on a huge Clustered Index because MS also screwed up and made it so that even a minimally logged INSERT/SELECT that used IDENTITY INSERT to sort the who damned thing in TempDB. With the advent of a permanent TF 1117 in TempDB, what would grow into a single monster file (which I could easily shrink after the fact) now caused all 8 files to grow and I ran out of room in TempDB.
While I appreciate MS trying to help the common man with performance, I need for them to allow people like me to override that kind of stuff when I need to do something the common man wouldn't.
On a similar note, it's a good thing MS has TF 692 or I'd be on my way to Redmond in a postal uniform with a 4 banded pork chop launcher. "Just runs faster", in-freakin'-deed. 🙁
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply