When performance is slow, first isolate the query or queries that are slow. Often it will appear that an entire application is slow, when only a few of the queries are slow. For a development tool that transparently generates SQL, use any available diagnostic or debug mode of this tool to capture the generated SQL. For tools that use embedded SQL, this is much easier. If your development tool does not provide this, you can use the 4032 trace flag to capture SQL statements sent to SQL Server. For more information about trace flags, see the Microsoft SQL Server Administrator's Companion and Trace Flags in the Microsoft SQL Server Transact-SQL Reference.
After the slow query is isolated, run the suspected slow query in isolation, using a query tool such as isql, and verify it is in fact slow. It is often best to run the query on the server computer using isql and local pipes, and to redirect the output to a file. This helps eliminate complicating factors such as network and screen I/O and application result buffering.
Use SET STATISTICS IO ON to examine the I/O consumed by the query. Give attention to the count of logical page I/Os. The optimizer's goal is to minimize I/O count. Make a record of the logical I/O count. This forms a baseline against which to measure improvement. It is often more effective to focus exclusively on the STATISTICS IO output and experiment with different query and index types than to use SET SHOWPLAN ON. Interpreting and effectively applying the output of SHOWPLAN can require some study, and it can consume time that can be more effectively spent on empirical tests. If your performance problem isn't fixed by these simple recommendations, then you can use SHOWPLAN to investigate the optimizer behavior more deeply. For more information about SHOWPLAN, see the Microsoft SQL Server Administrator's Companion.
If the query involves a view or stored procedures, extract the query from the view or stored procedures and run it separately. This allows the access plan to change as you experiment with different indexes. It also helps localize the problem to the query itself, versus how the optimizer handles views or stored procedures. If the problem is not in the query itself but only when it is run as part of a view or stored procedure, running the query by itself will help determine this.
Be aware of possible triggers or FOREIGN KEY constraints (referencing other tables or referenced by other tables) on the involved tables that can transparently generate I/O as the trigger runs or the constraint is checked. It is best to remove any triggers involved in a slow query. This will help determine if the problem is in the query itself or the trigger or view, and it will help direct your focus.
Examine the indexes of the tables used by the slow query. Use the techniques described in this chapter to determine if these are good indexes, and change them if necessary. For testing, consider an index on each column in your WHERE clause. Often performance problems are caused by having no index or no useful index on a column in the WHERE clause.
Run the query again after making the index change and observe any change in I/O count. After noting improvement, run the main application with the improved query to see if overall performance is better.
In a case in which you want to force the optimizer, you can use the SET FORCEPLAN ON statement or use optimizer hints to persuade the optimizer to use a specific index, locking strategy, or whether to optimize for "first row response time" or overall throughput (that is, minimizing physical I/Os).