Performing Through Changes

Overview

Here is a not-so-uncommon scenario: a query is running slowly. Upon investigation, one determines the reason to be a full-table scan and a possible solution to be an index to help speed up the query. As part of this performance-improvement process, programmers may have to make several changes in the database, including using indexes and statistics, converting to different index types, changing optimizer compatibility, and modifying an initialization parameter. This paper demonstrates how to use SQL performance analyzer to gauge the overall impact of creating an index on the STORE_ID column in a table called TRANS and then refreshing the statistics on the table and the index.