| Lesson 13 || Indexing strategies |
| Objective || Find out how your queries will be indexed. |
Indexing Query Strategies in SQL-Server
Indexes and queries
You should know by now that indexes are commonly used to speed up queries. At the same time, indexes do not automatically improve the
performance of queries. In fact, the wrong combination of query and index type can negatively impact your system's performance. When writing an insert query, you should first investigate the indexing that will be used for that query.
Once you have this information, you can decide whether you should adjust either your query or the index.
An alternative to an index is a table scan, which simply reads all the records from the table. A table scan is usually used with small tables, and when most of a table's rows will be accessed.
Role of Query Optimizer
The execution of a query is determined by Query Optimizer, a SQL utility that determines whether an index will be used to perform a query. The result of Query Optimizer's analysis is detailed in the query plan.
You can view the query plan either through a Transact-SQL statement or graphically, using Query Analyzer.
View query plan with Transact-SQL
Non-graphical statistics can be displayed by issuing this Transact-SQL statement before issuing a query statement:
SET SHOWPLAN_TEXT ON
If you use the SET SHOWPLAN_TEXT ON Transact-SQL statement, SQL Server will not actually run your query.
It will only show the execution plan of the query.
Index query plan
The index plan below indicates that the Query Optimizer used the clustered index idx in the IndexTest table. This table is located in the DT database.
|--Clustered Index Insert(OBJECT:
Table scan query plan
The query plan below indicates that an index will not be used for this query.
Using Query Analyzer
You can use the SQL Server Query Analyzer by selecting the Query->Show Execution Plan menu. The graphical plan displays the same
information as the non-graphical plan, but in much more detail. The graphical plan also displays information about how much disk access SQL Server had to do (indicated by I/O Cost) to achieve the results, and much more.
In the next lesson, we'll look at how to use the information we have accumulated.