What are the steps you will take to improve performance of a poor performing query?

Posted by

To view Verified answers click on the button below.

This is a very open ended question and there could be a lot of reasons behind the poor
performance of a query. But some general issues that you could talk about would be: No
indexes, table scans, missing or out of date statistics, blocking, excess recompilations of
stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written
query with unnecessarily complicated joins, too much normalization, excess usage of
cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET
SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server
Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query
Analyzer.