Sometimes indexes need to be rebuilt because of changes in data that occurred after it was built. Rebuilding indexes helps in collecting the scattered information and bringing index data back to its original form. This increases the overall performance by making it easier for SQL Server to read pages to get data. You can re-create an index in three ways:
The first option is highly unfavorable for clustered indexes because other levels of indexes use the same index's cluster key as a pointer to data rows. When a clustered index is defined on a table, this dictates the physical order of the data. The clustered index key values are used as data pointers for all other nonclustered keys. Deleting a clustered index and then re-creating it means deleting and re-creating all nonclustered indexes. The second option is rebuilding the index. The method reduces unnecessary work and is advantageous both to clustered and nonclustered indexes in that it significantly reduces the overhead of rebuilding the index. The third option is the preferred out of the three, because it enables you to rebuild multiple indexes in a single shot, without rebuilding indexes individually. It is demonstrated in Step by Step 10.5. It also enables you to rebuild indexes on tables that use PRIMARY and UNIQUE constraints without requiring you to delete and rebuild each constraint. SQL Server enables you to rebuild one or more indexes on a table by using the DBCC DBREINDEX statement, without having to rebuild each index separately. DBCC DBREINDEX is also useful if you want to rebuild indexes that enforce PRIMARY KEY or UNIQUE constraints without having to delete and re-create the constraints. For example, you may want to rebuild an index on a PRIMARY KEY constraint to reestablish a given FILLFACTOR for the index. If simple defragmentation of index space is desired, use the INDEXDEFRAG option. NOTE Temporary Locking of Tables During the time indexes on a table are being rebuilt, the table becomes temporarily locked, disallowing users from accessing it. Unlike DBCC DBREINDEX , DBCC INDEXDEFRAG is an online operation. It does not hold locks long term , and thus does not block running queries or updates. A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild. In addition, the defragmentation is always fully logged, regardless of the database recovery model setting. The defragmentation of a very fragmented index can generate more log entries than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions, and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE . NOTE A Note on Syntax When specifying any of the optional parameters FILLFACTOR or index_name you have to specify all parameters preceding it. The syntax for DBCC DBREINDEX is as follows : DBCC DBREINDEX ( [ 'database.owner.table_name' [ , index_name [ , fillfactor ] ] ] ) [ WITH NO_INFOMSGS ] Where
Rebuilding indexes represents only one activity that should be considered in attempting to improve database performance. Indexing to Improve PerformanceYou should not always assume that index usage means improved performance, and vice versa. If using an index always produced the best performance, the job of the query optimizer would be simple. In reality, incorrect choice of indexed retrieval can result in less than optimal performance. Therefore, the task of the query optimizer is to select indexed retrieval only when it will improve performance and to avoid indexed retrieval when it will negatively affect performance. If you expect indexes to produce a performance gain, you need to be aware that a number of factors contribute to the performance achieved. You should write queries that update as many rows as possible in a single statement, rather than using multiple queries to update the same rows. By using only one statement, optimized index maintenance can be exploited. Of course, other aspects of the system can be affected by long-running transactions, and therefore some compromise is needed. When creating indexes, use integer keys where possible for clustered indexes. Additionally, clustered indexes benefit from being created on unique, non-null, or IDENTITY columns . Create nonclustered indexes on all columns frequently used in queries. This can maximize the use of covered queries. The time taken to physically create an index is largely dependent on the disk subsystem. Important factors to consider are:
Often, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. This is a fundamental problem with the data and query, and usually cannot be resolved without identifying this situation. Creating an index never makes a query slow; however, if the index is not useful, a table scan or use of another less useful index could result in a long-running query. Indexing and StatisticsThe SQL Server query optimizer chooses the index that accesses data fastest . To choose the right course of action, the query optimizer is dependent on indexes' statistical figures. SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Index statistics need to be up to date for the optimizer to decide upon the fastest route of access. As data is modified in rows of a table, or when indexes on tables change, index statistics are not automatically updated. When this happens, the statistics become somewhat out of date because now the optimizer can't properly decide on an optimum index. Index statistics need to be frequently updated so that the optimizer can have full power on its search. An index's statistics are first automatically created when the index is first created. NOTE Update with Backup You should always update index statistics before performing database backups. This will ensure the statistics are up to date and accurate in the event a restore operation is needed. After the index is created, it is up to an SQL Designer to frequently make use of the UPDATE STATISTICS to manually update index information. SQL Server 2000 provides two commands that can help you as a SQL Server designer maintain index statistics. These are
Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. But it is recommended that this be used with extreme caution and only by an experienced database administrator. The option Auto Create Statistics is on by default. SQL Server automatically creates statistics on columns if the statistics might be useful. Querying an Index's StatisticsAs noted above, an index's statistics can be viewed by the DBCC SHOW_STATISTICS command or the STATS_DATE function. The two major differences are that STATS_DATE is a function (it returns a datetime value that tells the last time an index was updated) and that STATS_DATE queries only the last time an index was updated. The syntaxes for both are as follows: STATS_DATE ( table_id , index_id ) Where
DBCC SHOW_STATISTICS ( table , target ) Where
Looking at statistics in this manner or through execution plans in the Query Analyzer will help you diagnose and catch potential problems before they can cause more serious problems. The UPDATE STATISTICS StatementA shortened syntax of the UPDATE STATISTICS statement is as follows: UPDATE STATISTICS table view Index_name [ ALL COLUMNS INDEX ] ] Where
If the system is already set to automatically update statistics through the database properties, then this will minimize the need for manual updates.
Dropping Unwanted IndexesIf an index isn't in use any longer, you may wish to delete it. Deleting an index means freeing all storage space the index was previously holding. It also results in more efficient INSERT , UPDATE , and DELETE operations. Only a table owner is granted permission to delete indexes. To delete an index or indexes, run the DROP INDEX statement. NOTE Deleting an Index Used by a Constraint To delete an index used by a PRIMARY or UNIQUE constraint, you must delete the constraint causing the associated index to then be deleted automatically. DROP INDEX 'table.index view.index' [ ,...n ] Where
Dropping an index may require the removal of constraints if those constraints are directly related to the index. In other instances, you may just want to remove an index that has outlived its usefulness . To help determine index validity, the Index Tuning Wizard is provided as an assistant in index analysis. You can't drop an index created with a PRIMARY KEY or UNIQUE constraint; the alter table command must be used. The Index Tuning WizardThe Index Tuning Wizard is a graphical tool that enables you to select and create powerful indexes and statistics for Microsoft SQL Server 2000 databases. The Index Tuning Wizard simplifies the task of choosing which indexes to create in a table. As you've seen previously, one of the most difficult tasks for a DBA is determining which columns get indexed. Fortunately, Microsoft has shipped a wizard that identifies the best clustered and nonclustered indexes for a table and the indexes that are most likely to improve query performance. The Index Tuning Wizard is used to find the most efficient path of execution in a set of input. This input is a table or file, namely a workload. To make workloads, use the SQL Profiler to set a trace. The SQL Profiler enables you to monitor and record activity events in a file or table. SQL Profiler traces enable the Index Tuning Wizard to make accurate decisions. Traces of the different events that happen while work is performed can be recorded for hours or even days, depending on the period of time desired to be monitored . After the events are recorded, the Index Tuning Wizard then works with the query processor to establish the viability of a configuration. Before you learn to use the powerful features of the Index Tuning Wizard, be sure to examine some guidelines, benefits, and limitations that are included in the following list:
Examining the Index Tuning WizardBefore creating indexes using Index Tuning Wizard, you have to create a load trace file to monitor average activity. Subsequently, you can start Index Tuning Wizard by selecting it from the Tools menu. Step by Step 10.7 guides you using Index Tuning Wizard to create indexes from scratch. The Index Tuning Wizard is an extremely powerful tool that makes automatic and efficient decisions on clustered and nonclustered table and view indexes. This Step by Step covers using this marvelous tool to create indexes. Before using Index Tuning Wizard, you have to open a trace file by creating and running a trace. (For more information on the SQL Profiler, see Chapter 12.)
Step by Step 10.8 shows you how to use Index Tuning Wizard to create indexes.
For the Index Tuning Wizard to really be useful, it must be executed during production use of the database, or at least under circumstances where a sufficient query load will produce meaningful results. REVIEW BREAK: Indexing ReviewHere are some points that recap the concepts you have come across in this chapter:
|