Databases Reference
In-Depth Information
Although the autogrow feature enables a more hands-off approach to maintaining many SQL Server
installations, it's not necessarily desirable because the i les cannot be used while they are autogrowing,
and it can lead to fragmentation of the i les 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 (i les will be
reused if they already exist) and sized to the value specii ed in the database properties, which as
you've just seen is only 8MB for the data i le and 1MB for the log i le by default.
We've reviewed many SQL Server installations with tempdb i les 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 8-17 illustrates an example scenario of tempdb sizing.
SQL Server
is
restarted
Operations in tempdb cause
database to autogrow
DBA sets the
tempdb size
appropriately
Initial
State
DBA set
State
Working
State
After Restart
State
tempdev 8.0 MB
templog 0.5 MB
tempdev 200 MB
templog 50 MB
tempdev 2450 MB
templog 560 MB
tempdev 200 MB
templog 50 MB
FIGURE 8-17
In this case, you can see the size of the initial i les, which the DBA has set to 200MB and 50MB. The
workload running against SQL Server has then caused the tempdb i les 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 fuli ll the workload.
To What Size Should Tempdb Be Set?
This is obviously a difi cult 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 coni gure it to almost i ll 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.
Search WWH ::
Custom Search