How to find that a query could benefit from an index?

Performance is most required achievement in a multi-user environment, using SQL Server 2005 this can be achieved with a few statments execution. Firstly you need determine the issues by finding which query or queries are causing issues in achieving performance.

Using Estimated execution plan option in Query Analyzer in SQL 2000 or Display Esitmated execution plan in SQL 2005 Query editor. Also chooisng include Actual Execution Plan will also help to choose the right plan from cache when executing the query.

In order to see whether a new query could benefit from an index you can choose the following path:

• If you have only one or two databases then clear the server cache using DBCC FREEPROCCACHE (bear in mind do not execute this on a production server, as you might loose available better plans that are already stored in memory).
• If you only want to clear out the stored procedure cache for a single database (not the entire server) use DBCC FLUSHPROCINDB (undocumented DBCC statement).
• run SET  SHOWPLAN_XML ON and SET STATISTICS XML ON in order to display and store the statistics in XML format for a better analysis.

Once you have executed the query with the above options the sample stored procedure that creates an XML query plan indicating whether or not a query would benefit from an index. Because indexes are alternatives to table scans, the purpose of a proposed index is to avoid big I/Os. 

As you may aware  the SQL 2000 options listed above are limited and with the use of DMVs (DBA tool) will support reviewing the current query plan and the associated performance metrics: 

• sys.dm_exec_query_optimizer_info
• sys.dm_exec_query_plan
• sys.dm_exec_query_stats

Further to get complete view  of query plan metrics such as I/O, CPU usage and memory stats with XML format based use the following query against SQL 2005 instance:

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO

From this point you should go and analyze the available statistics to determine what best you can choose to obtain optimum performance on your SQL Server queries. As a best practice you might choose to open up 2 separate Management Studio sessions to compare the results of the original query and the query as it is being tuned.