SQL 2005- stored procedure performance troubleshooting

I am investigating performance issues for a stored procedure on SQL 2005 server.
This is how I intend to do it:

• Analyze the stored procedure for any bad TSQL practices (like count * etc)

• Run it through DTA

• Study the stored procedure's estimated query plan

-
Find the cached plan by running this code in the database:

SELECT  deqp.dbid ,
    deqp.objectid ,
    deqp.encrypted ,
    deqp.query_plan     
FROM sys.dm_exec_query_stats deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE objectid = OBJECT_ID('procedurename', 'p') ;

• Find the related indexes (not sure how) and check the usage

• Try to find any missing indexes

How else should I do it?
Comments are really appreciated.

Thanks in advance

Things like select count(*) are just fine as long as the where clause is indexed correctly.

DTA may or may not be of any use.

The actual and estimated plan are what you need to look at. It'll tell you if there are any indexes which are missing. The indexes which are used will be in the execution plan.

StackExchange Database Administrators Q#5426, answer score: 3