First have a look on the following things based on the query:
- Check indexes
- Check 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 Profile
- Windows NT /2000 Performance monitor
- Graphical execution plan in Query Analyzer
- Index tuning Wizard
