White Papers

47 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
(LineItem-~300 GB LineItem) and volume size (600 GB), to avoid any issues during query
processing. The tempdb I/O profile observed for the TPC-H data warehouse database tests
conducted in his paper is shown in Table 10.
• A tempdb I/O can be sequential or random. However, running multiple users or workloads can
make the tempdb I/O be more random even when the data warehouse workload is sequential.
Tempdb IO profile Table 10
Tempdb
performance metrics
Values
Read/Write block size
64 K/64 K
Read/Write % 50/50
The above profile is specific to the nature of the TPC-H workload used in the tests; other user databases
can generate an I/O that is very different. Periodic monitoring of the existing set up would help identify the
nature of tempdb. The Microsoft recommendations below were followed for the tests conducted in this
paper.
• Set the recovery model of tempdb to simple. This model automatically reclaims log space to
keep space requirements small.
• Pre-allocate space for all tempdb files by setting the file size to a value large enough to
accommodate the typical workload in the environment. This prevents tempdb from expanding
too frequently, which can affect performance. The tempdb database should be set to auto grow
to increase disk space for unplanned exceptions. Data warehouse workloads consume tempdb
for their queries. Pre-allocating space for all tempdb files manually would be beneficial.
• Set the file growth increment to a reasonable size to avoid the tempdb database files from
growing too frequently by a small value. Microsoft recommends the following general guidelines
for setting the file growth increment for tempdb files. For more details, refer to ”Optimizing
tempdb performance” located at
http://msdn.microsoft.com/en-
us/library/ms175527%28v=sql.105%29.aspx
Tempdb file growth recommendations Table 11
Tempdb file size File growth increment
0 to 100 MB
10 MB
100 to 200 MB 20 MB
200 MB or more
10%