White Papers

46 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
size and the percent growth are set to a reasonable size to avoid the frequent activation of the auto grow
feature. Microsoft’s recommendations are:
• Leave the auto grow feature on at database creation time to avoid running out of space and also
to let SQL Server automatically increase allocated resources when necessary without DBA
intervention provided there is physical disk space available.
• Set the original size of the database to a reasonable size to avoid the premature activation of the
auto grow feature.
• Set the auto grow increment to a reasonable size to avoid the frequent activation of the auto
grow feature.
7.3.5 Transaction log file growth
The transaction log is a serial record of all modifications and their executions that occurred in the
database. SQL Server uses the transaction log for each database to recover transactions. The log file size
depends on the recovery models and the frequency of the log backups. The most preferred recovery
model is
full to minimize downtime and data loss. Data warehouse workloads are primarily read intensive
when running user queries and typically generate minimal transaction log activity during read activity.
However, the log activity would be significant during the data warehouse load process. Microsoft’s
recommendations for the transaction log are:
• Place the log and the data files into separate volumes.
• Set the original size of the transaction log to a reasonable size to avoid constant activation of the
auto grow feature, which creates new virtual files and stops logging activity as space is added.
• Set the auto grow percent to a reasonable but small enough size to avoid frequent activation of
the auto grow feature and to prevent stopping the log activity for too long a duration
• Use manual shrinking rather than automatic shrinking.
7.3.6 Tempdb file growth
The tempdb database is a global resource that holds the temporary tables and stored procedures for all
users connected to the system. The tempdb database is recreated every time the SQL Server starts so that
the system starts with a clean copy of the database. The tempdb database can be I/O intensive in data
warehouse databases. Determining the appropriate size for tempdb in a production environment depends
on many factors, including the workload and SQL Server features that are used. To ensure the tempdb is
sized and working properly:
• Monitor the tempdb file periodically to check its growth and performance. Perfmon, SANHQ can
be used to monitor tempdb volumes.
• When a new database is created, start with allocating 10% of the total database size for all the
instances. Adjust the file size as the database grows.
• Pre-allocate space for the tempdb files by setting the tempdb file size to a value large enough to
accommodate a typical workload in the environment. If the space is not pre-allocated, set the
auto growth increment based on Microsoft’s recommendation (refer to Table 11). For the tests
conducted in this paper, the tempdb file size was pre-allocated to the largest table size