Post reply

Runs in Production but not QA

  • October 12, 2007 at 12:26 pm

    #119038

    We have an interesting problem, we have restored a database from production to QA. There is a specific stored proc that runs in 4 minutes in production, however running that same stored proc (with the same parms) never seems to finish in QA. By never seems to finish I mean, after 60+ minutes we have killed the process.

    We have used Quest's PA tool to find the specific SQL Statement within the stored proc where it's getting "hung". Now, to say that this specific statement is ugly is an understatement. There are 29 joins in the from clause and each column in the select statement is a case statement that involves mathematical equations.

    Our Specs (for both boxes):

    Windows 2003 Enterprise Ed., sp2, 64-bit

    SQL Server 2005 Enterprise Ed., sp2, 64-bit

    8 proc

    32GB of RAM for entire server

    28GB of RAM allocated to SQL Server

    What we have done:

    compared SQL configuration using sp_configure - they match

    rebuilt all indexes in QA

    recompiled all stored procs in QA

    Used Quest's PA tool to see that CPU usage never rises above 40%, Disk I/O is almost non existent

    Rebooted server

    The whole purpose for running this in QA is to get a baseline for performance before we make changes to the stored proc, so we can show performance improvement. So we could modify the proc in QA to get it to run, but then we would be comparing apples to oranges.

    Any ideas on where to look next? Or anything we might have missed checking?

    TIA,

    -A.

  • October 12, 2007 at 12:30 pm

    #741704

    Have you updated statistics with a fullscan (i.e. index and column stats)?

    Tommy

    Follow @sqlscribe

  • October 13, 2007 at 2:30 pm

    #741806

    Compare execution plans on both servers.

    Then, execution time - is it for the 'cold' execution or a 'hot' execution (2nd time execution)?

  • October 14, 2007 at 10:08 am

    #741841

    What modification might make it run? In addition to the items above.

  • October 14, 2007 at 1:36 pm

    #741853

    it's sql 2005, wright ?

    Compare the XML-plans. They provide more detail ...

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • October 15, 2007 at 7:07 am

    #741961

    I agree with Tommy. Run an Update Statistics. I had a similar problem (granted it was SQL 2000) some time back and it boiled down to updating statistics and everything ran as expected.

  • October 15, 2007 at 4:19 pm

    #742166

    Back when I was having a similar problem with a query in production taking an inordinate amount of time for the web site but not from the SSMS. (still searching for why on that one...)

    But did discover that

    create procedure usp_yourNameHere

    WITH RECOMPILE

    AS

    did allow the procedure to run more efficiently then previous.

    Good Luck

    Daryl

  • October 16, 2007 at 4:28 am

    #742270

    Check that you're connecting using the same network library (Named Pipes or TCP/IP) on both servers. I've seen this make a significant difference in the past.

    John

  • October 16, 2007 at 4:35 am

    #742271

    Hi,

    Have you reviewed execution plans on both servers?

  • October 16, 2007 at 6:08 am

    #742301

    It looks like the execution plan that the stored procedure is taking into consideration is different. May be giving even more time to stored procedure to be able to create the new execution plan on the QA server would help it run successfully. Give it a chance giving it more and keeping more patience. Lets seee...

  • October 16, 2007 at 6:24 am

    #742310

    ok, this is great information.

    If possible, could you show execution plans or advise on the differences and update me?

    Thanks,

    Phillip Cox

  • October 16, 2007 at 9:25 am

    #742420

    I had a similar issue between script execution and sproc execution. Noticed the difference in the execution plans (query execution plan and profiler w/xml). Tweaked a few indexes.

    http://www.sqlservercentral.com/Forums/Topic344707-338-1.aspx

    Note: I have had this issue with sprocs in other areas and added WITH RECOMPILE that seems to find the more efficient execution.

    I still have not discovered how the web site executes an sproc different then a manual execute of the same parameters sproc in SSMS (same credentials.).

    Daryl

  • October 16, 2007 at 11:06 pm

    #742590

    Hi, In my last post on this topic the basic idea that I intended to share was that when you run that stored procedure include the actual execution plan by activating the appropriate button on the toolbar. Remember it is different from the Estimated Execution plan button. Perform this activity on production server and on QA server and than compare the execution plans to see the differences in the indexes and other resources being used by them.

  • October 17, 2007 at 2:42 am

    #742609

    Hi

    Has there been any addition/modification/deletion of data ?

    "Keep Trying"

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply