[…] Initial sizing and autogrowth (see part 2) […]
This second part of a three-part article consolidating a number of best practices for configuring SQL Server tempdb focuses on initial sizing and autogrowth for tempdb. You won’t just find prescriptive rules here, but also the background to the recommendations and guidance on how to choose the best configuration for any particular environment. In particular this section covers the following:
- Where to place tempdb (see part 1)
- Initial sizing and autogrowth
- Configuring multiple files (see part 3)
Initial sizing and autogrowth of tempdb
A default installation of any SQL Server edition will create a tempdb database with an 8MB data file and a 1MB transaction log file. For a lot of SQL Server installations these file sizes won’t be enough, but they are configured to autogrow by 10% as needed. You can see the properties window for tempdb on a default installation of SQL Server 2012 Developer Edition in Figure 1.
Figure 1.
Although the autogrow feature enables a more hands-off approach to maintaining many SQL Server installations, it’s not necessarily desirable because the files cannot be used while they are autogrowing, and it can lead to fragmentation of the files on the hard disk, leading to poor performance.
This is a recommendation that would apply to any SQL Server database, but for tempdb it’s even more relevant. When you restart your SQL Server instance, tempdb is re-created (files will be reused if they already exist) and sized to the value specified in the database properties, which as you’ve just seen is only 8MB for the data file and 1MB for the log file by default.
We’ve reviewed many SQL Server installations with tempdb files of tens of GBs that have autogrown to that size and have the default properties set. The next time SQL Server is restarted, tempdb will be just 8MB and will have to start autogrowing all over again.
Figure 2 illustrates an example scenario of tempdb sizing.
Figure 2.
In this case, you can see the size of the initial files, which the DBA has set to 200MB and 50MB. The workload running against SQL Server has then caused the tempdb files to autogrow to 2450MB and 560MB.
SQL Server is then restarted and tempdb returns to 200MB and 50MB, as set by the DBA, and would have to autogrow again to fulfill the workload.
To what size should tempdb be set?
This is obviously a difficult question to answer without more details about the workload, but there is still some guidance that you can use. First of all, unless you’re running SQL Server Express, set tempdb to be bigger than the default; that’s an easy one.
Next, if you can give tempdb its own disk, then configure it to almost fill the drive. If nothing else will ever be on the drive, then you’re better off setting it to be larger than you’ll ever need. There’s no performance penalty, and you’ll never have to worry about autogrow again.
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 configured it to almost fill 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 configure it just in case you need it.
Using fixed-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 files, but it doesn’t work for log files 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 3, by running gpedit.msc.
Figure 3.
Once IFI is working, you can set autogrow to be large fixed amounts for data files. 50MB or 500MB are good values depending on the size of the database, but any size is created virtually instantly so you avoid any downtime.
NOTE:
If you’ve configured multiple data files and you want to allow autogrow, consider enabling trace flag 1117, which will force all data files to grow uniformly so you don’t break the load balancing between files.
For transaction log files, however, you need to be a lot more conservative and use a figure that balances the time it takes to autogrow and the usefulness of the extra space. Autogrowing by 1MB, for example, is quick, but you might need to do it so often that it becomes a bottleneck. Autogrowing by at least 10MB for the transaction log is a good place to start, but you may need it to be higher to provide enough space to avoid autogrowing again quickly. The best option is to avoid autogrowing in the first place by correctly sizing the files.
“First of all, unless you’re running SQL Server Express, set tempdb to be bigger than the default; that’s an easy one.”
What if you are running sql server express? What are the best practices?
Thank you.
Fahd, thanks for the comment. You might check Microsoft Books Online for the SQL Server Express version you’re working with.
Well, Express Edition cannot be really helpful for dealing with bigger data – given the fact its technical limitations and “query power” with respect to query optimization. That being said – tempdb to be sized based on the usage of “Internal” and “External” like DBCC, SORT_IN_TEMPDB, SNAPSHOT Isolation, Complex Querying and the like.
Each tempdb file gets separate GAM, SGAM, and PFS allocation pages on a round-robin basis for data
allocation and proportional fill. So multiple tempdb files with comparatively lesser size for each file would help us a lot rather than one single big sized file. When someone ask us for the tempdb size, we need consider te above said factors…
Sai Phanindra
http://www.sqlschool.com
This article is nice – very simplistic, down to the basics, and not a lot of wording. I came here after visiting http://cc.davelozinski.com/sql/increase-sql-server-tempdb-performance and like how you keep your articles easy enough to read for us non DBA types. 🙂
Good Explanation, but when we shrink the tempdb either using DBCC SHRINKDATABASE or SHRINK FILE will get the size of tempdb to minimum as 8MB but not the Minimum size which was set to file reinitialize during the restart. Any reason why it does? how to make sure it stays every time at the initial size instead of minimum size.
It would be great if you can explain or give a solution to do so.