Index Maintenance


  • Improve index use by using the Index Tuning Wizard, index placement, and statistics.

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:

  • Deleting the original index and then re-creating it

  • Specifying DROP_EXISTING in the CREATE INDEX clause

  • Using the Database Console Command (DBCC), DBCC DBREINDEX

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

  • 'Table_name' is the name of the table for which to rebuild indexes.

  • 'Index_name' is the name of the desired index to rebuild. If 'index_name' is not specified, all indexes on 'table_name' are rebuilt.

  • 'Fillfactor' specifies the percentage of space on each index page to be used for storing data when the index is rebuilt. The new fillfactor overwrites the original. However, if the new fillfactor is 0, the old fillfactor is used.

  • WITH NO INFOMSGS , as the name suggests, disallows printing of informational messages.

STEP BY STEP

10.5 Rebuilding All Indexes Using DBCC DBREINDEX

  1. Open the Query Analyzer by selecting Query Analyzer from the Start menu.

  2. To rebuild all indexes on the previously created IndexPractice table with a FILLFACTOR of 54, execute the following query:

     DBCC DBREINDEX (IndexPractice,' ', 54) 

Rebuilding indexes represents only one activity that should be considered in attempting to improve database performance.

Indexing to Improve Performance

You 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:

  • RAID level used to store the database and transaction log files.

  • Number of disks in the RAID disk array.

  • Size of each data row and the number of rows per page. This determines the number of data pages that must be read to create the index.

  • The columns in the index and the data types used. This determines the number of index pages that have to be written.

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 Statistics

The 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

  • The STATS_DATE or DBCC SHOW_STATISTICS command, used to show the last time statistics were updated for a particular index.

  • The UPDATE STATISTICS command, which updates statistical information for an index.

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 Statistics

As 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

  • Table_id is the ID of the table.

  • Index_id is the index's ID.

 DBCC SHOW_STATISTICS ( table , target ) 

Where

  • 'table' is the name of the table from which to display statistical information.

  • 'target' is the name of the index to which to show the statistical information.

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 Statement

A shortened syntax of the UPDATE STATISTICS statement is as follows:

 UPDATE STATISTICS table  view         Index_name  [ ALL  COLUMNS  INDEX ]     ] 

Where

  • 'Table view' is the name of the table or view on which statistical updating needs to be performed.

  • 'Index_name' is the name of the index for which statistics are being updated. If index_name is not specified, all indexes in the table or view are updated.

  • 'ALL COLUMN INDEX' specifies whether all indexes are updated, only column indexes are updated, or only index statistics are updated. The default value is ALL .

STEP BY STEP

10.6 Querying and Updating Index Statistics

  1. Open the Query Analyzer from the Start menu.

  2. To see the statistics for the titleind index in the Titles table, use the following:

     USE pubs GO DBCC SHOW_STATISTICS (titles, titleind) 
  3. To update its statistics, run the following:

     UPDATE STATISTICS titles titleind 

If the system is already set to automatically update statistics through the database properties, then this will minimize the need for manual updates.

IN THE FIELD: SCHEDULING STATISTIC UPDATES

Normally an UPDATE STATISTICS operation would be scheduled to occur on a regular basis. In most environments this is common database maintenance procedure. This scheduled job would be present in most environments, even those who "update automatically."

Dropping Unwanted Indexes

If 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

  • 'table view' is the name of a table or view in which the index resides.

  • 'Index' is the name of the index being dropped.

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 Wizard

The 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:

  • Make sure there are enough rows of data in the sample tables you use for the SQL Profiler. The Index Tuning Wizard may not make accurate decisions or decisions at all if there is not enough data in the tables being sampled.

  • In SQL Server 2000, index tuning can also be invoked from a command-line utility called itwiz .

  • The Index Tuning wizard may not offer suggestions. This could be because indexes would most likely not increase performance.

  • The Index Tuning Wizard consumes excessive CPU overhead. Decreasing the size of the workload can assist this. So, you shouldn't capture an excessive workload in order to do index tuning.

  • The Index Tuning Wizard does not support backward compatibility with creating and selecting indexes on databases made in SQL Server 6.5 and earlier.

Examining the Index Tuning Wizard

Before 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.7 Creating a Trace

  1. Open the profiler by selecting SQL Enterprise Manager (found in Programs, SQL Server 2000) from the Start menu, and then selecting Tools, Profiler.

  2. Click on File, New Trace, provide login information if necessary, and then type MyTrace in the Trace Name text box. Check Save to File; this saves the workload file onto disk for future use. Save the trace as C:\MyTrace.trc, and then execute the trace by selecting Run in the Trace Properties dialog box, as shown in Figure 10.6.

    Figure 10.6. Creating a trace in the SQL Profiler.

    graphics/10fig06.jpg

  3. Now you have to manually make SQL Server process queries so that the trace you just set up may be able to monitor the events configured. To do this, switch to the SQL Query Analyzer and execute the following query:

     SELECT * FROM Authors WHERE au_fname LIKE 'C%' GO SELECT * FROM titles 
  4. Now end the trace by going back to the Profiler and clicking the Stop button or selecting File, Stop Trace from the menu.

Step by Step 10.8 shows you how to use Index Tuning Wizard to create indexes.

STEP BY STEP

10.8 Creating an Index with Index Tuning Wizard

  1. Open the Index Tuning Wizard by selecting it from the menu, Tools, Wizards, Management, Index Tuning Wizard.

  2. When you start the Index Tuning Wizard, a screen displays the goals and aims that you can accomplish through using the Index Tuning Wizard. Click Next.

  3. This will lead to the opening of the second screen, which is used to select the server and database. Type in the name of your server and then select Pubs as the database (this is the database you are interested in tuning). Select Thorough for the tuning mode. Choosing Thorough processes a thorough, but time-taking analysis of the current situation and provides best results (see Figure 10.7). Click Next to proceed.

    Figure 10.7. Selecting a database and tuning mode.

    graphics/10fig07.jpg

  4. This screen is where you choose workload-specific information. As the workload file, choose C:\MyTrace.trc.

  5. Now you have a selection screen that asks which tables you want to tune. Select Dbo.Authors as the table and click Next.

  6. This screen processes workload information and gives advice as to which indexes should be placed on which columns. Click Next to continue.

  7. This is the last screen of Index Tuning Wizard. Click Finish.

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 Review

Here are some points that recap the concepts you have come across in this chapter:

  • Information concerning indexes can be viewed by using the sp_helpindex stored procedure. To rename or delete an index, use the sp_rename stored procedure and DROP INDEX statement, respectively.

  • When information that affects data is modified, index information can go loose throughout the database. Rebuilding indexes helps in collecting the scattered information and bringing data back to its original form. Use the DBCC DBREINDEX to re-index a single or all indexes in a table.

  • Defining clustered indexes on views enables you to store the resultsets of the view in the database, thereby reducing the overhead of dynamically building the resultset.

  • Index statistics need to be up to date for the optimizer to decide upon the fastest route of access. You can use the UPDATE STATISTICS command, which updates statistical information for an index.

  • The Index Tuning Wizard enables you to select and create powerful indexes and statistics for a Microsoft SQL Server 2000 database without prior knowledge of index and database structural designs.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net