Other SQL Server Performance Considerations

Earlier, it was mentioned that many opportunities exist for SQL Server performance tuning in the area of disk usage. The classic server-level configuration typically will try to separate certain SQL Server items across different hard drives , RAID controllers, and PCI channels. This results in a physical I/O segregation with minimal confusion and maximum value. The main items to try to segregate are transaction logs, Temp DB, databases, certain tables, and even nonclustered indexes.

Transaction logs are easily segregated (isolated) away from the data files simply by specifying this different location during database creation. Don't have transaction logs located on the same physical device as the data files.

Temp DB is a bit harder in that you must use an Alter DB command to change the physical file location of the SQL Server logical filename associated with Temp DB. However, by isolating tempdb away from the data files of your other databases, you will almost achieve minimal disk arm contention for one of the most heavily used databases in SQL Server.

Database partitioning can be accomplished using files and file groups. In general, you can employ a concept of segregating databases with high volatility away from other databases with high volatility by defining the files/file groups on physically separate devices and not sharing a single device.

For tables and nonclustered indexes, you can reference the file groups from within their create statements (the ON statement) to physically segregate these objects away from others. This can be extremely powerful for heavily used tables and indexes.