Tips for Performance Tuning SQL Server OLTP Databases

Some tables that are accessed frequently may have columns that are accessed very frequently, but other columns that are rarely accessed. If the size of this table is causing performance problems, one option is to split the table into two tables. The first table will have the columns that are frequently accessed, and the second table will have the columns that are rarely accessed. When the columns in the second table are needed along with the frequently accessed columns, then the two tables can be joined. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

OLTP database applications should typically be fully normalized to remove redundant data. Redundant data causes SQL Server to perform unnecessary I/O, reducing performance. There are of course exceptions to this rule of thumb, which are discussed elsewhere on this web site. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

Don't over index your OLTP tables, as every index you add increases the time in takes to perform INSERTS, UPDATES, and DELETES. There is a fine line between having the ideal number of indexes (for SELECTs) and the ideal number for data modifications. [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

If you have a very active OLTP server application with many INSERTS, UPDATES, and DELETES, it is possible that the default "recovery interval" of 0 (which means that SQL Server determines the appropriate recovery interval) may not be appropriate. If you are watching the performance of your server with the Performance Monitor and notice that you have regular periods of 100% disk-write activity (occurring during the checkpoint process), you may want to set the "recovery interval" to a higher number, such as 5. You will have to experiment to find the ideal number for your particular circumstances  [6.5, 7.0, 2000, 2005] Updated 5-3-2005

*****

On tables that experience high levels of updates, consider adding a timestamp column to the table, if there isn't one already. A timestamp column helps SQL Server perform concurrency control more efficiently. If a timestamp column is not available, SQL Server must compare the values of all columns in a row to all of the current values in the row at the time of the update. [7.0, 2000, 2005] Updated 10-4-2005

*****

On tables that experience high levels of updates on indexed columns, consider creating those indexes using the PAD_INDEX option of the CREATE INDEX command. The PAD_INDEX option tells SQL Server how much space to leave open on each page in the intermediate levels of the index. If PAD_INDEX is not used, then the default behavior is that each intermediate index page is only given enough empty space to hold at least one row of the maximum size the index can have. For tables experiencing heavy index updates, this can cause the intermediate index pages to split often, causing unnecessary overhead.

To help prevent this intermediate index page splitting, indexes should be created with both the FILLFACTOR and the PAD_INDEX options. The PAD_INDEX option will take the value provided in the FILLFACTOR option, and apply it to the intermediate index pages. For example, if a FILLFACTOR of 80 is used in the CREATE INDEX command, then the PAD_INDEX will use this value, and only fill 80% of the intermediate index page space, instead of leaving enough space for just one new row.

But if your tables don't have much UPDATE activity, then don't use the PAD_INDEX option. If you set the PAD_INDEX value as to leave lots of free space, and it is not needed, this only causes unnecessary disk I/O when SQL Server reads these pages, reducing performance. [6.5, 7.0, 2000, 2005] Updated 10-4-2005