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