SQL Query Structure Analysis

After other performance-limiting factors have been considered, you should examine the structure of the SQL query and analyze if intelligent indexes are available for selection by the query optimizer. The following questions will help you fine-tune the query and available indexes.

Are there any unmatched data types?

Unmatched data types may indicate that a join clause is comparing values of different data types. A common problem is a join clause involving two character columns where one column is defined as char/not null and the other as char/null represented internally as varchar. The optimizer considers the two columns being joined as separate data types and therefore may not consider the clause an optimizable one.

Are there a lot of string operations or data conversions?

If there is a significant number of string operations or data conversions in the SQL statement, they may be more appropriate in the calling application. Extensive string manipulation and data conversions consume processor resources. On a heavily loaded SQL Server, this resource consumption may cause queries to perform slowly.

Are efficient indexes being used?

Look at the table(s) being accessed by the query being analyzed to determine if any indexes exist. You can access this information by using SQL Enterprise Manager or by executing the sp_help stored procedure. If indexes do exist, do they meet the index selection and efficient design criteria discussed in "Query and Index Design," earlier in this part? Remember, the index must be considered "useful" by the optimizer before it is selected.

Are the existing indexes useful?

Is the optimizer selecting any of the available indexes? To determine this, set the following Transact-SQL options ON:

SET SHOWPLAN ON—Generates a description of the query execution plan for the query.
SET NOEXEC ON—Compiles a query but does not execute it. This option must be turned off to execute queries.

After the query execution plan is available for viewing, check for references to Using Clustered Index or Index: index_name; the associated clustered or nonclustered index will be used for that portion of the query. If Table Scan is displayed, the query optimizer did a row-by-row sequential scan of the table due to lack of a useful index or because such a scan is cheaper in terms of I/O processing than using an index.

Why isn't the optimizer choosing an index?

If the optimizer has indexes to choose from but does not select them, check for the following causes:

The data in the table has changed significantly since the original indexes were created. Executing the UPDATE STATISTICS statement against the table(s) or index(es) will refresh the distribution statistics and result in a higher probability of index selection by the optimizer.
The available indexes were no better than a table scan with respect to the amount of I/O that would be incurred. This is because the indexes either had no relationship to the SELECT list and/or the WHERE clause, or the subsequent reading of the index plus the reading of the data is more expensive than just reading the data sequentially—the table itself is smaller than the combination of the table and the index.

Addressing these problems generally results in more efficient queries. Nonetheless, if the distribution statistics are current and it appears that an index was appropriate, further investigation of the optimizer selection process is warranted.

What is the optimizer thinking?

To obtain a detailed description of the index-selection criteria and join plan associated with a query, perform the following steps by using ISQL/w or SQL Enterprise Manager:

Execute DBCC TRACEON (-1)—Turns tracing on for all connections.

Execute DBCC TRACEON (3604)—Sends output to the screen.

Execute DBCC TRACEON (302)—Prints information about the optimizer's index selection.

Execute DBCC TRACEON (310)—Prints information about the optimizer's join selection.

Execute the query being analyzed.

Execute DBCC TRACEOFF (-1)—Turns tracing off for all connections.

The output from these steps reveals the selection criteria for indexes and joins and the number of access plans considered for the query.

What if the query is slow even with an index?

If the optimizer chooses an index but the query is still slow, the problem may be resource oriented. Use the memory-monitoring techniques discussed earlier to determine if excessive paging is occurring. If this is the case, you will need to review the size and makeup of the index. Remember, the smaller the index, the better. You may also want to change the FILLFACTOR to include more indexes on an index page. However, this can result in more frequent page splits, which decreases performance.