3

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

Improve this question
1
  • It's a pretty broad question, as the performance may or may not have anything to do with indexes, and it may or may not show up as something obvious in the plans. Some other things are statistics, parameter sniffing, runtime set options, inefficient query semantics, unnecessary looping... If you show the procedure and explain the schema involved, maybe folks would spot something to help you narrow your focus.
    – Aaron Bertrand
    Sep 6, 2011 at 5:10

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • Thanks ! I did study the estimated plan but it didn't come up with any missing index. I can run it during day as it is a production server so, i was going to find the execution plan from cached plans. will the missing index show up there? Because i didn't see any. What else would you do? P.S. I am regular reader of your blog :-)
    – Manjot
    Sep 6, 2011 at 1:02
  • If you are getting the plan from the cache and the optimizer has identified missing indexes they will be shown in the plan. If there are things that will be fixed by covering indexes the optimizer may not show those as needing to be created so you have to find those by hand.
    – mrdenny
    Sep 6, 2011 at 16:56
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:

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.

Improve this answer
2
  • We have a baseline for the server for more than an year. this collects SQL counters, available memory and % processor time. But the developer says that it has been happening forever
    – Manjot
    Sep 6, 2011 at 20:38
  • If it has been happening for ever than at least you have a baseline to compare the improved version with (I'm speaking mostly about stored procedure duration, IO.. it's hard to see a change in global server picture only from a procedure improvement).
    – Marian
    Sep 8, 2011 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.