Tricks for using the SQL Server Index Tuning Wizard

The SQL Server Index Tuning Wizard will recommend indexes for you to use based on a given workload, which you can specify. But remember that it is important to understand basic database indexing when designing and reviewing the Index Tuning Wizard's recommendations. The final answer in your environment will vary based on the database design, business processes, level of concurrency, data types, etc., and should be thoroughly tested. Do not be afraid to develop and test unconventional options based on the unique characteristics of your application to ensure the indexes you provide do not improve one area and cripple another.

The following list of tricks will help you use the Index Tuning Wizard more effectively to improve performance in SQL Server 2000.

1. Index Tuning Wizard is one of those hidden gems in Enterprise Manager. To access the tool, open Enterprise Manager, navigate to the Tools menu and select the Wizard option. Once the Select Wizard interface pops up, expand the Management heading and double click on the Index Tuning Wizard option. The Index Tuning Wizard has the following interfaces:

• Welcome Screen
• Select Server and Database
• Specify Workload
• Select Tables to Tune
• The analysis will be conducted once the OK button is pressed
• Index Recommendations
• Schedule Index Update Job
• Completing the Index Tuning Wizard

2. SQL Profiler is the key to Index Tuning Wizard's success. Running the wizard and getting recommendations on index design can be a big win for everyone. But, I have also seen great disappointment when the wizard doesn't recommend any indexes. A good run with SQL Profiler makes the difference. If the data provided to the Index Tuning Wizard is not representative of your issues, then the Index Tuning Wizard will not be able to provide valuable insight into the index selection. So, make sure that SQL Profiler captures the performance data you're ...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Administration Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Push vs. pull: Configuring SQL Server replication Setting up SQL Server Service Broker for secure communication Top load balancing methods for SQL Server Performance implications of transaction log autogrowth in SQL Server The keys to database backup protection for SQL Server Microsoft SQL Server Performance Monitoring and Tuning SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

interested in.

3. Capture Profiler sessions at different times during the day, and store the results of these sessions in separate tables. If you then move the data to a single table, you can see results from the Index Tuning Wizard as it analyzes the independent sets of data and the single set of data. The differences can offers insights that will help you decide which recommended indexes to implement.

4. Coordinate a single user's session with the business application in a controlled manner with SQL Profiler capturing data for this single session. Then use the Index Tuning Wizard to analyze and recommend transactions in an isolated manner. Next, check the query plans to determine which indexes are used and validate that none of the queries are table scanning as opposed to using an index.

5. You can call the Index Tuning Wizard directly from Query Analyzer by highlighting the query and pressing CTRL + I. Then follow the Wizard's steps to complete the analysis. The value of launching the Index Tuning Wizard directly from Query Analyzer is that you can analyze a single query independent of the remainder of the application code.

6. Review the recommendations that the Index Tuning Wizard offers to ensure they make sense for your environment. Do not just blindly add the indexes. Apply the indexes in a development environment and in a test environment and test with the same SQL Profiler data to see if the results are as expected.

7. Do not be afraid to add the indexes, but also revisit some basic database design and coding best practices. At times, an index can really do the trick. But, at other times, you must revisit the database design and coding for a final resolution.

ABOUT THE AUTHOR:   

[IMAGE]Jeremy Kadlec
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2006 TechTarget

More information from SearchSQLServer.com