SQL 2005- stored procedure performance troubleshooting

Question

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

Answer 1

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.

Answer 2

First things first: if you're investigating performance issues for a stored procedure you have to have a baseline of procedure calls and also a list of calls that surpass your desired performance (also some statistics that you need: how many calls per day, how many are OK vs how many are slow...etc).

This can be done most easily with traces. So before starting investigation, I'd suggest you have/make traces of that specific procedure calls and not only, also a picture of the complete load on the system, because it's pretty natural to have a procedure go wild when there's a batch job that's killing the system.

If you don't already have all that info, go read the following questions related to Profiler and server traces:

• Using SQL Profiler on a database that's in production and
• SQL Profiler tutorials for newbie

After that you should start investing time in tuning, reading the execution plan (can also be saved in traces) and trying to rewrite your code.