4) Scans: Scans (Table scans, Index scan, and Clustered Index scans) are usually bad unless the table has very few rows and the Query Optimizer determines that a table scan will outperform the use of an available index. Watch out for scans in your execution plans.
In Query Analyzer, go to pubs database and type the following query:
SELECT * FROM employee WHERE hire_date > ‘1992-08-01’
Highlight the query and press. (Cntl + L) or highlight the query and press F5. You will see the following in the “Estimated Execution Plan” tab:
Notice that in this case, a Clustered Index Scan was performed, which means that every row in the clustered index had to be examined to fulfill the requirements of the query.
Now that we understand some of the basics of how to read Query Execution Plans, lets take a look at some additional information that you will find useful when analyzing queries for proper index use:
- If you create multiple query or a stored procedure execution plans at the same time in Query Analyzer, you can compare the cost of each query or stored procedure to see which is more efficient. This is useful for comparing different versions of the same query or stored procedure.
- Primary Key constraints create clustered indexes automatically if no clustered index already exists on the table and a non-clustered index is not specified when you create the PRIMARY KEY constraint.
- Non-clustered indexes store clustered index keys as their row locators. This overhead can be used as a benefit by creating a covering index (explained later). Using covering indexes requires caution.
- A table’s size comprises both the table’s data and the size of any indexes on that table.
- Adding too many indexes on a table increases the total index size of atable and can often degrade performance.
- Always add a clustered index to every table, unless there is a valid reason not to, like the table has few rows.
- Seeks shown in Query/Execution plans for SELECT statements are good for performance, while scans should be avoided.
- A table’s size (number of rows) is also a major factor used up by Query Optimizer when determining best query plan.
- Index order plays an important role in many query plans. For example, in the authors table of the pubs database, a non-clustered index is defined in the order of au_lname, then au_fname.
Fine Query A
SELECT * FROM authors WHERE au_lname like ‘r%’
This uses a Bookmark Lookup and an Index seek.
Fine Query B
select * FROM authors WHERE au_lname LIKE ‘r%’ AND au_fname like ‘a’
This uses a Bookmark Lookup and an Index Seek.
Not so Fine Query C
SELECT * FROM authors WHERE au_fname LIKE ‘a’
This uses a Clustered Index Scan.
- SQL Server 2000 (not earlier versions) offers both ascending and descending sort orders to be specified in an index. This can be useful for queries, which uses the ORDER BY DESC clause.
- To find a particular word (for e.g. a table name) used in all stored procedure code, you can use the following code to identify it. For example, you can use this to find out a list of SP’s using a table.
SELECT DISTINCT a.name AS SPName FROM syscomments b, sysobjects a
WHERE b.text LIKE ‘%authors%’ AND a.id=b.id AND a.type=’P’
This query brings all SP’s having text “authors” in their code. Note that this query might bring extra SP’s, for example, if a stored procedure uses text in a comment.