multiple tempdb files - no sgam contention.

Question

multiple tempdb files - no sgam contention.

snomadj

SSCarpal Tunnel

Points: 4491
More actions
April 27, 2016 at 8:23 am

#316090

I can't see any sgam contention. Is there any point in me having multiple tempdb files?
Background:
I'm just reviewing configs across our estate. I've been running a job for a week to monitor any tempdb related waits. Where I meet the cpu etc. criteria should I bother creating multiple files for "best practice" or is there no need?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BLOB EATER SSChampion Points: 14609 More actions · Answer 1

I only change the number of files when / if I see PFS / SGAM contention. (However I know people that change the number of files regardless...)

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 2

Thanks blob_eater. I'll probably leave it then. Seems a bit pointless unless there's another reason I don't know about.

BLOB EATER SSChampion Points: 14609 More actions · Answer 3

Change the default sizes as that's usually a poor configuration setup - you know, the initial sizes / auto growth settings.

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 4

The new defaults in SQL 2016 are 1 per logical core, up to 8. I haven't typically worried about tempdb in many installs, but this seems like a small preventative item to just change to if you can. Administratively it shouldn't be anything to manage.

I'd make sure the files are the same size, and on similar hardware. Don't mix HDD and SSD. Also, change the default autogrow, as mentioned above.