MS SQL Basics Tutorial: Index Tuning Wizard

8.5.1 Index and system performance

Indexing speeds up data retrieval, but it slows the insertion, deletion, and updating of data. In particular, the cluster index, the data is stored in a logical order in a certain physical location, when changing data, according to the new data sequence, the need to move a lot of data physical location, which will increase the burden of the system. For non-clustered indexes, the index pages need to be updated when the data is updated, which also takes up the system time. Therefore, using too many indexes in one table can affect the performance of the database. For a table that is often changed, you should limit the table to using only one cluster index and no more than 3~4 indexes. A table with a particularly heavy handle on transactions should be indexed as few as 3.

8.5.2 Indexing Wizard (Index tuning Wizard)

The Index Tuning Wizard helps you select and create an optimized collection of indexes to improve the performance of your database.

A workload record (workload) is required to use the Index Tuning Wizard. A workload record consists of traces created by SQL scripts or SQL Server Profiler that are stored in a file or table. If you do not have an existing workload record for the database or table that you want to index, you can create one through SQL Server Profiler (see the use of SQL Server Profiler in chapter 19th for a specific method). You can use sample 1–tsql trace definitions to create or build a new trace. The Index Tuning Wizard can use the query optimizer to analyze the performance of indexes based on workload records and make appropriate adjustments recommendations. You can immediately have the system modify the index as recommended, or you can create the task after it has been scheduled.

8.5.3 using the Index Tuning Wizard:

The steps for using the Index Tuning Wizard are as follows

(1) Select the "Wizards" option from the "Tools" menu and show the Selection wizard interface as shown in Figure 8-15.

(2) Select the "Index Tuning Wizard" option under "Management" from the tree directory, and the Index Adjustment wizard interface as shown in Figure 8-16 appears.

(3) Click the Next button to appear in the Select Server and Database dialog box as shown in Figure 8-17. Select the database to be indexed for tuning here. The options have the following meanings:

Keep all existing indexes

Preserves an already existing index. If you do not select this option, some indexes may be deleted when indexing is optimized.

Analysis of perform thorough

Perform a thorough analysis of the workload records.