Database - How is index tuning used to improve query performance?

How is index tuning used to improve query performance?

The Index tuning wizard can be used to improve the performance of queries and databases. It uses the following measures to do so:

- It uses the query optimizer to perform the analysis of queries with respect to the workload and based on this knowledge, it recommends the best usage of indexes.
- The changes in the usage of index, query distribution and their performance are analysed for checking the effect.
- It also recommends ways of tuning the database for a small set of problem queries.

How is index tuning used to improve query performance?

- Index tuning improves query performance by using Index Tuning Wizard. SQL profiler is used for capturing a trace of the activity, for the optimizing performance. The trace can be extended for a period of time for the purpose of capturing a wide range of activity.

- Subsequently, Enterprise manager is used for starting the Index Tuning Wizard and instructs to recommended indexes which are based on the trace that is captured. An estimation of increased performance after making changes is provided apart from appropriate columns suggestion.
Database - Reasons of poor performance of query
No indexes, excess recompilations of stored procedures.....
We shouldn't allow input from users during a transaction, we shouldn't open transactions while browsing through data...
Database - Explain Execution Plan
SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call.....
Post your comment