8.5.1 Index and system performance
The
index 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 Index Tuning 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.
you need a workload record (workload) 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:
Use the Index Tuning Wizard as follows
(1) Select the "Wizards" option from the "Tools" menu, and the Selection wizard interface shown in Figure 8-15 appears.
(2) selects the Index tuning Wizard option under "Management" from the tree directory, and appears as shown in Figure 8-16.
(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.
(4) Click the Next button to see the specified Workload record file dialog box as shown in Figure 8-18. This selects the workload record file for the database to be indexed for adjustment. Click the Advanced Options ... button to see the Modify Default Adjustment Parameters dialog box, shown in Figure 8-19, to view or modify the parameter settings for the adjustment index, the maximum number of columns in the index, the maximum space for the recommended index, and the maximum number of tuning query tests.
(5) Click the Next button to see a table dialog box with the specified index adjustment as shown in Figure 8-20. Select one or more tables in the database to be indexed for adjustment here
(6) Click the "Next" button to appear as shown in Figure 8-21, the Recommended Index Adjustment dialog box, where you select the system's recommended index to index adjustments to be included in the adjustment plan.
(7) Click the Next button and the End Index Adjustment dialog box appears as shown in Figure 8-22. At this point, the Index Tuning Wizard finishes executing. Click the Finish button to finish the wizard.
