You may already be working with the usual suspects for performance tuning tools, but are you using all the features available in each tool? The following is one of several must-have free tools, or go to the complete list of freebies.
|3. Enterprise Manager's Index Tuning Wizard||Return to Complete List|
Another hidden gem in Enterprise Manager is the Index Tuning Wizard. Now I can't say that this tool is bulletproof, but it has provided me with some valuable nuggets of information with respect to indexes needed. The valuable nuggets were from Profiler sessions that were saved to tables. If you are going to run SQL Server Profiler on your production system for any amount of time, you might as well take it to the next logical step and run the same information through the Index Tuning Wizard to see if an index or two will benefit your database.
To launch the Index Tuning Wizard, open Enterprise Manager, click on the Tools menu, then Wizards option and a Select Wizard interface will appear. Open the Management tree, double click on Index Tuning Wizard and follow the interface prompts.
Here is one of the final screens of the wizard with index recommendations. It shows if the indexes are added, a 23% improvement is expected based on the sample workload.
Click for the next featured freebie: System Monitor
About the author: 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.
This was first published in July 2005