I am going to start a new multi-part series on what is likely every DBA’s favorite topic, no matter which RDBMS it may be … indexing.
It is a topic that I talk a lot about with customers and it takes a lot of discussion that can be intricate and is very difficult to find common generalities that work well across all SQL Server workloads. There are a number of factors that affect the approach one takes to an indexing strategy including application type, database size, target workloads, system uptime requirements, among many others.
I found these 3 approaches that a DBA quoted in SSWUG by Stephen Wynkoop in a recent email he sent to SSWUG.org members very interesting (listed below). These are based on maintenance of a system that is a mix of OLTP & OLAP and I thought provide a decent starting point for database indexing in the real world with SQL Server:
Reorganize the indexes throughout the day when updates are occurring. The reorganize only updates the leaf nodes and does not lock the table allowing queries to continue without blocking.
Reindex fragmented tables daily during off hours. Originally we simply ran a maintenance plan that reindexed all tables. However, it got to the point it could not complete in the maintenance windows. I modified a process I posted to SSWUG years ago that reviews each table and it’s fragmentation. It performs an ALTER INDEX command on any table with a fragmentation percentage above my allowed level.
Separate certain tables onto dedicated drives. Even pre-allocating space in the database was not adequate. The need was for the database to allocate contiguous pages on a database file. So, placing certain tables on specific database files allowed them to expand and contract without interleaving with other tables of the same database.