Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 '11 at 5:10
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 at 16:56
    
Thank you very much –  Manjot Sep 6 '11 at 21:14
add comment

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.

share|improve this answer
    
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 '11 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 '11 at 7:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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