SQL Server Performance

Optimizer making a bad choice?

Discussion in 'Performance Tuning for DBAs' started by JimmyJoe, Jan 27, 2003.

  1. JimmyJoe New Member

    I am an experienced Informix and Oracle DBA with little SQL Server tuning experience.

    The application is web-based OLTP and only calls stored procedures to access the database. The procedure in question sometimes takes 8 seconds to execute, it should be sub-second. If I execute the procedure using the 'with recompile' option, it now executes sub-second. I can see that the Execution Plan has changed. After a random period of time, the procedure is recompiled for some reason with the same bad Execution Plan as before and takes 8 seconds again. If I execute with the 'with recompile' option it goes back to sub-second for a random period of time.

    Any suggestions on where to look? How can I tell why the optimizer is making a bad choice? Is there any way to force the proc to use the good excecution plan?

    Thanks!
  2. royv New Member

    Is there any dynamic sql in your procedure?


    "How do you expect to beat me when I am forever?"
  3. JimmyJoe New Member

    quote:Is there any dynamic sql in your procedure?
    Thanks for the response. No, there is no dynamic sql.
  4. royv New Member

    Unfortunately, I'm at a loss now. If there was dynamic sql, then I can easily see why the with recompile would help, but since there is none, the execution plan should not become bad. No you cannot force the procedure to use a specific execution plan. Maybe try to check and see if your statistics are out of date. Anyone else have any ideas?


    "How do you expect to beat me when I am forever?"
  5. bradmcgehee New Member

    Bad statistics can be a potential cause of this problem, and because it is hard to manage statistics, I have sometimes decided to use optimizer hints to force the optimizer to use the "correct" execution plan. You might want to consider this option, or perhaps even consider leaving the "with compile" option on. Assuming the SP doesn't run thousands of times an hour, the little extra overhead of the "with compile" shouldn't be much of a performance hit.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. satya Moderator

    Also having regular DBCC and maintenance checks on the database will help to gain performance.

    Satya SKJ
  7. x002548 New Member

    Have you checked to see if there is any contention for resources? Is it possible you may be blocking yourself? ARe there cursors involved??? Could be a buch of reasons...Can you post the s/p?

    Brett

Share This Page