Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 236

If you can't put tempdb on its own disk, then you'll need to manage size and autogrow a bit more
closely. You could just let it autogrow for a while and then manually set it to be a bit larger than
what it grows to, or you could just make it a reasonable size in relation to your other databases and
set large autogrow amounts.
To What Size Should Autogrow Be Set?
If you've moved tempdb to its own drive and coni gured it to almost i ll the disk, then arguably you
don't need to enable autogrow. That would be a reasonable choice in this scenario, but it may be
worth leaving it on if you still have a small amount of disk space left over.
The best way to think of autogrow for any database, not just tempdb, is as a last resort. Your
databases should be sized appropriately so they don't need to autogrow, but you still coni gure it just
in case you need it.
Using i xed-growth amounts is generally a better approach for autogrow because it makes autogrow
events more predictable. Autogrowing a 10GB transaction log by 10%, for example, will take a long
time and will affect the availability of the database.
The Instant File Initialization (IFI) feature in Windows Server 2003 and later can make things a bit
easier for autogrowing the data i les, but it doesn't work for log i les because of the way they
are used.
IFI is used automatically by SQL Server if the service account is a local administrator (which it
shouldn't be as a security best practice) or if the account has the Manage Volume Maintenance
Tasks advanced user rights. To give the service account the necessary rights, you can use the Local
Group Policy Editor, shown in Figure 8-18, by running gpedit.msc.
FIGURE 8-18