Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Unanswered: Performance problems

    I have seen this problem many times before, both in my dev environment and at client sites: Sometimes a stored procedure will run really slow and cause a timeout in the calling application.

    The weird part is that the query seems to run at a normal speed in QA. I am using ADO classic to access the database. I have tried many things on the stored procedure, such as WITH RECOMPILE, and have also tried to run dbcc freeproccache and dbcc dropcleanbuffers.

    The only solution to this problem seems to be to detach and reattach the database. Then everything works fine. Part of me thinks it could be an ADO problem since QA executes it quickly, but I can't figure out why the detach\reattach cycle fixes it. I imagine it is like a "reboot" for the database. I only noticed this fix when troubleshooting, I detached the database from the client server onto my dev server and it was fine, then I detached\attached on the client server and all was well.

    Obviously, this is not the best solution to my clients, some of them do not have a dedicated dba and others are so large it could take two days to find someone who has the authority to do this 10 sec procedure.

    I would like to find a cause\solution or a nice workaround. Any ideas? I'm sure someone has seen this before and can offer some suggestions?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sounds like parameter sniffing to me. google "SQL Server parameter sniffing" and for the love of god, never run those 2 DBCC statements in a production environment.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2004
    Posts
    9
    I've tried the WITH RECOMPILE option and other ways to avoid sniffing but have not fully solved the problem yet

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    post your code, ddl with indexes and sample data.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Have you confirmed that it's the performance of the stored procedure (meaning it executes slow), and not blocking? Run the profiler while the procedure is being executed, and include Showplan XML. While profiler is running, execute the same procedure from QA. Once you captured both executions (from ADO and from QA), you can view the plans and see if they are different. Ideally, if it is truly a parameter sniffing, you want to simulate 2 different plans using QA, and review the conditions that result in creation of inefficient plan. Also, check the contents of the procedure for any table/index hints.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by rdjabarov
    Have you confirmed that it's the performance of the stored procedure (meaning it executes slow), and not blocking?
    If it is blocking, and you are using SQL Server 2005, read about snapshot isolation. Turning this on will usually solve blocking issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •