In recent weeks, I’ve received a handful of calls regarding diminishing performance on various reports. I was unable to reproduce the issues internally, and I could not find fault with the stored procedures themselves, so my conclusion was data-related. After scrambling the data and hosting the databases internally, I was still unable to reproduce the issues. After some thought, I realized that our scramble process also includes re-indexing the data! A ‘quick’ scramble without re-indexing allowed me to reproduce the slow reports. Similar to your workstation’s hard drive, fragmentation occurs on SQL Server tables as data is inserted, deleted, or updated. As the indexes are modified, the information is scattered across the data files, and the logical ordering of the data no longer matches the physical ordering; this can lead to a decline in query performance. Again, like your workstation’s hard drive, it is a good practice to defrag the data to keep things running in tip-top performance.
There are 3 ways to do this:
- Use the maintenance plan wizard in SQL Server Management Studio – Microsoft includes a simple wizard to automate this process, but it has its limitations; it rebuilds every index regardless of fragmentation level, some editions can’t rebuild indexes online and holds locks on tables, etc.
- Write your own T-SQL scripts to do more custom logic – Using T-SQL commands gives you the flexibility to see which indexes are fragmented and only fix those tables.
- Buy a third party index maintenance program
I prefer to use T-SQL to identify the level of fragmentation for each table and either rebuild the index, or simply reorganize them. Reorganizing an index defragments the data by reordering the pages to match the logical order (left to right) which improves index-scanning performance, releases additional available disk space, uses minimal system resources and does not hold long-term blocking locks. Rebuilding an index drops the index and creates a new one, removing all fragmentation, reclaiming disk space, and the index rows are reordered in contiguous pages.
A good rule of thumb is to reorganize indexes that are 5-30% fragmented, and rebuild indexes that are over 30% fragmented. The following website provides good information regarding Reorganizing and Rebuilding Indexes, including the advantages and disadvantages of each method. The fragmentation of your database should be checked weekly and, if needed, rebuilt/reorganized at a time when users will not be accessing the system.