Optimizing Indexes | ppk on JavaScript, 1/e

One way to keep performance at a good level is to constantly rebuild your indexes, especially clustered indexes. Over time, indexes become fragmented as data is added and deleted, and pages shift. Nothing on the database side slows down performance more than a badly maintained index.

Optimizing Index Fill Factors

When you create or rebuild an index, one of the decisions you must make is the size of the fill factor to use in your index. A fill factor shows you how much data SQL Server will try to fit on each data page. A fill factor of 98 percent, which is the default, fills the data page up to 98 percent full before creating another data page. You can configure this setting when you create or rebuild the index.

In most systems, you would never want to have 98 percent of your data page full. To determine how to set this figure correctly, you must consult your programmers. Find out how many updates, deletes, and inserts will occur in the table after the initial load. Will the data change 10 percent daily?

After you have this figure, create the fill factor to reflect the flux in your data. If your data is changing 10 percent between index rebuilds, consider making a fill factor of 90 percent. This means SQL Server won't have to create data pages very often between index rebuilds.

Don't make this fill factor setting too low. A setting that is too low increases the amount of space needed for your database. It also increases the amount of work SQL Server has to do to find your data, since it has to jump from one page to another more often. After you set the fill factor, you can look at the DBCC SHOWCONTIG information (discussed next) to determine how much space in each page is actually being used with the Avg. Page Density (full) result.

Using DBCC SHOWCONTIG

The DBCC SHOWCONTIG command shows whether you have fragmentation in your tables. You can execute the command against the Orders table in the Northwind database by using the following syntax:

DBCC SHOWCONTIG (Orders)

You can also use the WITH FAST option to quickly retrieve the vital fragmentation information only:

DBCC SHOWCONTIG (Orders) WITH FAST

In earlier versions of SQL Server, you had to use the object ID instead of the name of the object. You can obtain the object ID by using the object_id() function as shown here with the WebHits table:

SELECT Object_ID('WebHits')

You can also run the DBCC SHOWCONTIG command without any parameters to return the information for every table in the current database. The full results from looking at one table in the Northwind database look like this:

DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (357576312); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 20 - Extents Scanned..............................: 5 - Extent Switches..............................: 4 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 60.00% [3:5] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 40.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The Avg. Pages per Extent figure shows that this table is only using four pages per extent. This means that extent is only half full with pages, since extents can hold eight pages (64KB). The key fragmentation figures are the Logical Scan Fragmentation and the Extent Scan Fragmentation. As you can see, the Extent Scan Fragmentation figure is very high. Although this figure is a secondary indicator, behind the Logical Scan Fragmentation, it could still be responsible for a slowdown.

Once you determine that you have fragmentation in an object, you need to fix the problem before your application's performance suffers. You can perform either of the following actions to accomplish this:

Drop and re-create your clustered index. This is the process that occurs if you use the Database Maintenance Wizard to schedule a maintenance plan.
Use the DBCC INDEXDRAG command to reorder the leaf level pages in logical order.

The frequency with which you have to do this varies, depending on the activity
on the server. If you have many updates, inserts, and deletes occurring, rebuild the indexes more regularly. If this is a report database, and you're only selecting data from it, you may never have to rebuild the index unless you do a new data load.

To fix this fragmentation, rebuild the indexes using the DBCC DBREINDEX command. The syntax for the command is simple:

DBCC DBREINDEX (<table name>, '<index name>', <fill factor>)

If you specify a table, and leave a placeholder with two single-quotes for the index name, the command rebuilds all indexes in that table. You can also name a specific index if you want. The last parameter needed is the fill factor for the indexes:

DBCC DBREINDEX (Orders, '', 90)

After this command is issued, the DBCC SHOWCONTIG looks like this:

DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (357576312); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 22 - Extents Scanned..............................: 3 - Extent Switches..............................: 2 - Avg. Pages per Extent........................: 7.3 - Scan Density [Best Count:Actual Count].......: 100.00% [3:3] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 869.2 - Avg. Page Density (full).....................: 89.26% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Tip

In SQL Server 2000, you can optionally use the TABLERESULTS parameter to display the results in a rowset with added information.

As an example, we can look at a table with a few million records that also has some issues. This is a real table called WebHits, used by a Web site to track each hit on a Web page. As you can imagine, millions of records could potentially be written to this table in a month.

Over time, the Web pages began to load more and more slowly. Then it seemed as if something had reached a critical mass, because suddenly the Web pages were taking five minutes to load.

When I ran the DBCC SHOWCONTIG on the table, I saw the following results:

DBCC SHOWCONTIG scanning 'WebHits' table... Table: 'WebHits' (2092104187); index ID: 1, database ID: 8 TABLE level scan performed. - Pages Scanned................................: 12460 - Extents Scanned..............................: 1565 - Extent Switches..............................: 3351 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 46.48% [1558:3352] - Logical Scan Fragmentation ..................: 24.42% - Extent Scan Fragmentation ...................: 28.63% - Avg. Bytes Free per Page.....................: 162.0 - Avg. Page Density (full).....................: 98.00% DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As you can see, problems abound for this WebHits table and it's no wonder the site is having performance problems. The problems include the following:

The Logical Scan Fragmentation and Extent Scan Fragmentation results are high. This means there is so much fragmentation in the table that SQL Server has to work harder than it should.
The Avg. Page Density (full) result shows a fill factor of 98 percent. This is causing SQL Server to create more pages when data is inserted.
The Scan Density result is too low (46 percent). It should be as close to 100 percent as possible. This figure represents how contiguous the index is. If this figure is less than 100 percent, fragmentation exists.
The table's indexes need to be rebuilt at an 85-90 percent fill factor. The lower fill factor should accommodate the large amounts of inserts in the table. In the future, this table's indexes should be rebuilt at least weekly, as a scheduled job.

Tip

Complete rebuilds of indexes take a lot of CPU horsepower and also shift pages. This causes the table to slow down tremendously during the rebuild.

After rebuilding the index, the DBCC SHOWCONTIG outputs the following results:

DBCC SHOWCONTIG scanning 'WebHits' table... Table: 'WebHits' (2092104187);; index ID: 1, database ID: 11 TABLE level scan performed. - Pages Scanned................................: 13505 - Extents Scanned..............................: 1690 - Extent Switches..............................: 1689 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 99.94% [1689:1690] - Logical Scan Fragmentation ..................: 7.39% - Extent Scan Fragmentation ...................: 9.64% - Avg. Bytes Free per Page.....................: 775.7 - Avg. Page Density (full).....................: 90.42% DBCC execution completed. If DBCC printed error messages,  contact your system administrator.

Tip

If you're loading data into a warehouse, consider dropping the indexes before you perform the data load. You can rebuild the indexes after the data load. This speeds up the load procedure significantly. It took me an hour and a half to load data into a database with indexes and less than 20 minutes without indexes, including the time it took to rebuild the index.

Defragging Indexes

Rebuilding indexes is a costly operation, and the process slows down operations while it is running. One online operation you can do instead is to defrag your index. When you defrag your index, leaf pages are shuffled so that the physical order of the pages matches the logical order. Since the pages are shuffled in place, this can be accomplished with little impact on performance. Use the following syntax to defrag an index:

DBCC INDEXDEFRAG (<database name>, <table name>, <index name>)

The database name can also be 0, which would perform the command on the current database. After you execute the command, you are notified about the percentage complete every five minutes. If your database has multiple database files, only one file is done at a time, and pages do not shift between the two files. As an example, to execute the command against the PK_Orders index in Orders table of the Northwind database, use the following syntax:

DBCC INDEXDEFRAG (Northwind, Orders, PK_Orders)

The results look something like this (depending on the fragmentation and the amount of data):

Pages Scanned Pages Moved Pages Removed  ------------- ----------- -------------  6             3           2 (1 row(s) affected) DBCC execution completed. If DBCC printed error messages,  contact your system administrator.

As you can see by the results, not only does this command fix your fragmentation, it also frees up any empty pages. Rebuilding your indexes is the best and most thorough method of defragmenting your index, but when you want to give your index a little boost, use the DBCC INDEXDEFRAG command.

Using the Index Tuning Wizard

You can use the Index Tuning Wizard from within Enterprise Manager and Query Analyzer to find potential index candidates and to find indexes that may be unnecessary. This feature first appeared in SQL Server 7.0 and was strengthened in SQL Server 2000.

To use the feature, use SQL Server profiler to capture queries over a period of time. Save the trace file, which you can import as a Workload file in the Index Tuning Wizard.

I prefer to execute the wizard from Query Analyzer, because it provides the added ability to look at queries you have selected.

To access the Index Tuning Wizard in Query Analyzer, choose Query | Index Tuning Wizard. You can also access the wizard in Enterprise Manager by selecting Tools | Wizards.

When you're collecting your data in Profiler, capture data that best represents the activity you want to tune. Also, filter the Profiler data for the database you want to index. Here are a few ideas about where to place your indexes:

Place clustered indexes on data that holds a range. For example, if your queries are gathering all data between given dates, this is an ideal candidate for a clustered index. Clustered indexes best help BETWEEN, <, >, GROUP BY, and ORDER BY queries.

In the Trenches

The Index Tuning Wizard looks at whatever load you give it. It may also suggest that you delete current indexes. The second screen in the wizard allows you to select the option to Keep Existing Indexes to prevent this from happening.

If your sample time was not long enough, the indexes it wants you to delete may not be good selections.

Be very careful before accepting any of the indexes. It's not uncommon for the Index Tuning Wizard to create a nonclustered index on every column in the table. This would do you no good.

Don't place a clustered index on sequential data such as an identity column.
Try not to place a clustered index on a column that is updated often. Any nonclustered indexes will have to be updated also if you update the clustered data.
Try to add nonclustered indexes on columns that you search on that are at least 95 percent unique. A region column for some companies would be a poor choice for a nonclustered index, since there are only four potential choices in some companies.