Using the SQL Server Profiler tool, you can gather important information about server activity in the form of a captured trace. Implemented in conjunction with the Index Tuning Wizard (ITW) and proper general indexing information, this trace can help you debug your SQL Server applications and tweak performance. This article will step through how you can take your trace data and actually use it in the ITW.

SQL Server performance tweaks

Catch up on how to tweak the performance of your SQL Server applications with the previous articles in this series:

Index Tuning Wizard
The ITW can be used with any trace captured from the SQL Server Profiler. Once you have collected your trace (either to a file or to a database table), you can run the ITW utilizing the data that you have captured.

Figure A
Select a Wizard

To initialize the ITW (see Figure A), select the Run Wizard icon, then navigate to Management | ITW. (Alternately, you can run this from the command prompt using the itwiz.exe, which is especially useful for remotely tuning SQL Server.)

Next (see Figure B), you can change the previously selected server, which should be grayed out, and select the database that you wish to tune from the drop-down list.

Figure B

Figure B also shows that you can deselect/select the Keep All Existing Indexes check box. Deselecting this check box allows SQL Server to have the greatest latitude in its efforts to suggest the best possible indexing schema. But doing so can also increase the amount of time it takes to complete the operation, and it places the heaviest workload on the database.

You can also allow SQL Server to create indexed views for you as part of the wizard’s output. There are many instances where indexed views can be useful. The last portion of the form shown in Figure B contains the tuning mode selections. Note that when you select the tuning mode, the process could take a considerable amount of time to complete, depending on the size of the work file, the number of records, and the number of indexes you are analyzing.

There can also be a great performance hit to your SQL Server during this process, so carefully consider the environment in which you are operating when working with these options. For instance, if you have a production OLTP database and perform log shipping to an OLAP server, you could run the wizard against the OLAP database and apply the recommended changes to both systems.

Figure C

As you can see in Figure C, you can utilize either a file or database trace to initialize the wizard. The third choice, which is grayed out in Figure C, is available if you run ITW from query analyzer. Clearly, the query analyzer invocation can allow you to target a smaller problem set than a more encompassing profiler trace.

Figure D

Figure D shows the advanced options. Here, you can set three parameters. First, you can limit the number of queries in the trace that the ITW will use. With large traces, this can be especially helpful in reducing the time that the ITW takes to complete its work, but this option is less precise because, if the number specified is below the total count of queries, then the queries that the ITW uses are randomly chosen. Next, you have the ability to specify how many megabytes of space the indexes may consume. Lastly, you can set the maximum number of columns that the ITW will attempt to implement, the default and maximum is 16.

Figure E

Figure E shows how you can choose which tables you would like the wizard to review. Obviously, your trace would need to have captured representative activity against these tables for the wizard to do its job properly. Again, the more tables that you select, the longer the optimization can potentially take.

Figure F

Figure F shows the recommendations that the wizard has made, and you can select the actions, if any, which you would like the wizard to take.

Figure G

You may also view the analysis shown in Figure G to better understand the recommendations that are being made and implemented by the wizard.

Figure H

Next, you can choose to have SQL Server apply the changes immediately (as shown in Figure H) or schedule it to be completed at a later time. Also, you can save a script of the recommended changes.

Figure I

Finally, Figure I shows that you can go back and review the previous screens, finish, or cancel the process.