Monitoring SQL Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 51

DataandLogFilePlacement
Separating data and log files has been a long-standing recommendation for SQL Server administrators.
The typical OLTP platform will generally consist of fairly random read/write patterns across the data
file, while the transaction log is usually written to sequentially and will see very little read traffic.
Best Practice
Consider grouping data files onto one LUN (Logical Unit Number, a unit of presenta-
tion from a SAN) and log files onto a separate LUN. This will allow the disk controller
to better manage the two I/O patterns and service each type from the resources it has
available.
DataandLogFileAutogrowth
Data and log file autogrowth is enabled by default on all new databases. This operation will automati-
cally grow these files in the background with no user intervention required. As you are aware, disk I/O
activity is expensive in respect that it's often time consuming. The default growth increment for a data
file is 1 MB increments with 10 percent growth rate for transaction log file. If these files aren't properly
managed, imagine attempting to load data into a database that is inadequately sized, and the database is
expanding in 1 MB increments. This will significantly impair performance.
Best Practice
Pro-actively manage data and log file growth. This means pre-empt any auto-grows by
manually controlling them. This way you can be sure they happen in a controlled way
during periods of low activity and at sensible growth increments. You should consider
data and log file autogrowth very much as a safety net.
TempdbConfiguration
SQL Server uses tempdb as a holding area during join, sort, and calculation operations as well
as by the version store. Depending on the type of applications and user activity within SQL Server, the
performance of tempdb can directly affect user experience. Under workloads that make extensive use of
tempdb, it's worth planning the placement and size of tempdb to ensure optimal performance.
Best Practice
Size tempdb sufficiently to ensure no autogrowth will be required. It's important that
the initial file size is correctly specified as well, otherwise a service restart can result in
tempdb being sized at the default size.