SQL Server Index Optimization Wizard for tracking data

With the SQL Server event probe tool, you can use a captured trace to collect important information about the Server. With the Index Tuning Wizard, ITW, Index OPTIMIZATION Wizard, and correct general Index information, this trail can help you debug SQL Server applications and optimize performance. This article will guide you step by step to obtain the tracking data and use it in ITW.

SQL Server Performance Optimization

Refer to the following articles to learn how to optimize the performance of SQL Server applications:

Tips for optimizing SQL Server Indexes;

Use the index adjustment Wizard to adjust the application performance;

Use the query analyzer to adjust the SQL server script;

Index optimization wizard.

Any trace captured from the SQL Server event probe can be used for ITW. Once your tracking is collected to a file or database table, you can run ITW and use the data you just captured.

Figure

Select a wizard

To initialize ITW, see figure A), click the run wizard icon and choose manage> index optimization wizard ]. In addition, you can execute itwiz.exe directly in the command line, which is particularly useful when you remotely adjust SQL Server.

See figure B). You can change the server selected in advance. It should be dimmed and select the database you want to optimize from the drop-down list.

Figure B

You can also select or select to retain all existing indexes in Fig B. If you withdraw the database, SQL Server will do its best to recommend the best index architecture, but it will also increase the operation time and bring the maximum load to the database.

SQL Server can also create an index view for you, which will be provided as part of the wizard output. The index view can be used in multiple scenarios. The last part of Figure B contains the optimization mode options. Note that if you select thorough optimization, the entire process will take a considerable amount of time, depending on the size of the work file, the number of records, and the number of indexes you want to analyze.

In this process, the performance of SQL Server will be seriously affected, so when operating these options, pay special attention to your work environment. For example, if you have a production OLTP database but logs are recorded on an OLAP server, you should run the wizard for the OLAP database and then apply the suggested changes to the two systems.

Figure C

C. You can use a file or database trace to initialize the wizard. The 3rd options are gray unavailable in Figure C. They can be used only when you run ITW from the query analyzer. Obviously, the query analyzer allows you to locate a small problem set without having to face a wider range of event probe tracking.

Figure D

Figure D shows the advanced options. You can set three parameters here. First, you can limit the number of queries that ITW will use in the trail. For large tracking, this helps to shorten the ITW's working time. However, the precision of this option is not high enough, because once the specified number is lower than the total number of queries, the queries used by ITW will be randomly selected. Second, you can specify the maximum amount of MB space consumed by the index. Finally, you can set the maximum number of columns that ITW tries to implement. The default value is 16 columns.

Figure E

Figure E shows how to select the table you want the Wizard to check. Obviously, your trail needs to capture the most representative activities for these tables so that the wizard can play its best role. Similarly, the more tables selected, the longer the optimization time.

Figure F

Figure F shows the wizard's suggestions. You can choose the actions to be taken by the wizard if any ).

Figure G

You can also view the analysis shown in G to better understand the suggestions provided by the wizard.

Figure H

Next, you can choose whether to enable SQL Server to apply changes immediately, as shown in H), or schedule changes at a later time. In addition, you can save a script that contains the recommended changes.

Figure I

Finally, in the screen shown in I, you can choose to return to view the previous screen, select finish, or select cancel wizard.

(