• cfradenburg (3/2/2010)


    You should also keep in mind that if one auto grows that SQL will no longer balance the load across the different files properly. Our recommendation to our clients is to make the tempDB files big enough it won't fill up and then turn off autoshrink and autogrow. If that's not an option for you then you'll have to have a way to monitor for grow events on the tempDB and shrink them back to the standard size.

    Not sure either of those is good advice.

    Nor is it appropriate to always have 1 file per CPU core. Most systems dont' have sufficient IOPS to server up tempdb and they suffer from head thrashing when having too many files. Makes things worse, not better. also many systems now have 8-32 cores but just a handful of actual CPUs. hyperthreading is another wrinkle...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service