locked
SQL Server Stored Proc Performance RRS feed

  • Question

  • Hi Experts, Last from 2 weeks I am facing performance problem on stored proc execution, we have tools which calls these stored proc in loop in thousand times to generate reports in excel add-ins.

    When tool get executed first time it ran in expected time and later i.e. 5 min if we run the tools then performance degraded by 10 times. When the actual SQL command called in SSMS then it get executed less than seconds and when its being called in loop by tools highly performance degrading.

    Some days these stored proc optimized to generate report for hierarchy level 99 and used MAXDOP hint to use parallel execution plan to speed the report but even it called for 0 level its taking longer time but when executed with RECOMPILE options it runs in expected time.

    Is this causing due to MaXDOP Hint? or something else. I can not remove hint otherwise proc takes ages to generate report for level 99.


    Shivraj Patil.

    Wednesday, September 11, 2013 5:29 AM

Answers

All replies

  • First of all it's better to think about remove loop and replace it with another option!

    But this is important that dose this tool create loop in server side or at client side?

    If it's at server side, Is it cursor or while?

    Is it possible to add code?


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page


    • Edited by Saeid Hasani Wednesday, September 11, 2013 5:51 AM
    Wednesday, September 11, 2013 5:47 AM
  • Thanks ,

    We cant remove that loop these tools are very complicated developed several years back and client is not ready for this they are aksing why it was running in expected time days back and now running slow.

    Its client side. Client tool is developed in VBA code , it has Array whihc stores hierarachy values and call this stored proc for each node in array and return result set to VBA Array again.


    Shivraj Patil.

    Wednesday, September 11, 2013 6:17 AM

  • Some days these stored proc optimized to generate report for hierarchy level 99 and used MAXDOP hint to use parallel execution plan to speed the report but even it called for 0 level its taking longer time but when executed with RECOMPILE options it runs in expected time.

    Is this causing due to MaXDOP Hint? or something else. I can not remove hint otherwise proc takes ages to generate report for level 99.


    Shivraj Patil.

    Tha above information is not so clear. Could you please elaborate why did you change the MAXDOP and what was the previous and current value for the same? Could you please share the execution plan for the same without RECOMPILE? It sounds like a parameter sniffing, but we need to look at the plan to help you better.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 11, 2013 6:22 AM

  • Its client side. Client tool is developed in VBA code , it has Array whihc stores hierarachy values and call this stored proc for each node in array and return result set to VBA Array again.


    As you said, this problem occurs at client side.

    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    Wednesday, September 11, 2013 6:45 AM
  • Yes this problem occures at client side


    Shivraj Patil.

    Wednesday, September 11, 2013 8:05 AM
  • http://www.sommarskog.se/query-plan-mysteries.html

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, September 11, 2013 8:06 AM
    Answerer