Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Index Tuning Toolbox
SQL Server has a couple of great tools that can help you analyze the effectiveness of your indexes. First of all, you can examine the estimated execution plan in the query analyzer. If you plan to optimize your application, you must learn how to read the execution plan. The query execution plan tells you how SQL Server plans to access data for a particular query. There could be thousands of ways in which SQL Server could execute a query, but it chooses the most efficient plan given the existing indexes. The graphical execution plan brings back a very colorful presentation of what SQL Server has to do to get the results of your query. If you'd rather have a verbal presentation of the same idea, use SET SHOWPLAN_TEXT ON.
Suppose you have the following query:
SELECT au_lname,
au_fname,
title
FROM authors a INNER JOIN titleauthor b ON a.au_id = b.au_id
INNER JOIN titles c ON c.title_id = b.title_id
WHERE title LIKE '%computer%' AND state = 'CA'
ORDER BY title
Pressing Ctrl+L within the Query Analyzer displays the execution plan shown in Figure 1.
)
Figure 1 Graphical execution plan in Query Analyzer.
If you move your mouse over each icon included in the graphical execution plan, you'll get additional details. Alternatively, if you use SET SHOWPLAN_TEXT ON option, you'll get the following text version of the same execution plan:
|--Nested Loops(Inner Join, OUTER REFERENCES:([b].[au_id]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([c].[title_id]))
| |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]
AS [c]),
WHERE:(like([c].[title], '%computer%', NULL)) ORDERED FORWARD)
| |--Index Seek(OBJECT:
([pubs].[dbo].[titleauthor].[titleidind] AS [b]),
SEEK:([b].[title_id]=[c].[title_id]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:
([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]),
SEEK:([a].[au_id]=[b].[au_id]), WHERE:([a].[state]='CA')
ORDERED FORWARD)
Both versions of the execution plan tell you that your query involves two joins, both executed with the LOOP algorithm. SQL Server has to scan a non-clustered index on the titles table, seek for a particular set of values in a non-clustered index on titleauthor table, and seek through a clustered index in the authors table.
The execution plans of the complex stored procedures can be rather difficult to decipher. Unfortunately, there's no way to print an execution plan except by printing the entire screen. If you have difficulty reading the execution plan, split your procedure into multiple queries and analyze each of them separately.
Another tool that you might find helpful is the Index Tuning Wizard (ITW). In SQL Server 7.0, this tool was accessible through the Query Analyzer. Such functionality was useful for tuning a single query. In SQL Server 2000, the ITW is available only through the Profiler.
To access the ITW, open SQL Server Profiler and select Tools, Index Tuning Wizard.
Specify the server and the database in which you want to tune indexes.
Specify whether to perform a fast, medium, or thorough examination of queries. The thorough examination takes more time but is likely to produce better results.
Supply a set of queries (workload file) or a SQL Server table that contains the output of a trace. You also have an option to limit the number of queries to be considered, as well as the storage space limit for the suggested indexes.
Select the tables that the ITW should optimize—you can select all tables in the database or only those that you think need some tuning.
That's all you have to do—the ITW will examine the current table structure, number of rows, types of joins, and WHERE clauses that your queries contain and come up with recommendations.
Unfortunately, the ITW doesn't take into consideration any queries outside the workload; the recommendations that ITW makes are useful, but don't try to follow them blindly. Think about what else could affect performance and make your own decisions.