none
Performance increases after performing first stored procedure.

    Question

  • Hello,

    I have been testing my stored procedure on SQL Azure and it seems that when I first run it, the performance is lousy(on AVG 1000ms when I need it to be <20ms). When I continue running it using different parameters then the performance significantly improves(if i run it using the same params then the response is instant because it is cached). It should be noted that on my SQL Server 2005 this was not occurring.

    It is essential to my application that this procedure will always be responding at <~20ms (even on first hit after there have been no requests for over a minute). 

    My questions are as follows.

    1. Why does this happen(I am not using dynamic sql)?

    2. How can I keep the DB from "falling asleep"? Should I just send a dummy wakeup request every couple of seconds or is there a configuration that I can change?

    Thanks,

    Yehuda 

      

    Wednesday, February 29, 2012 8:49 AM

Answers

  • AFAIK, the stored procedures get compiled after the first run. In my understanding, Stored procedures execution plan is cached on the first run and next run onwards it boosts the performance.

    Some information here http://msdn.microsoft.com/en-us/library/ms190439.aspx see if that helps.

    -Sachin Sancheti

    Wednesday, February 29, 2012 7:07 PM
  • I'll try and answer your second question: How can I keep the DB from "falling asleep"? Should I just send a dummy wakeup request every couple of seconds or is there a configuration that I can change?

    - With SQL Azure, try embedding "re-try logic" in your code. So even if your DB falls asleep (and i believe you are referring to the current idle connection time out limit of 30 seconds) - Then it should get resolved when you retry connecting to the SQL Azure DB. And re-try logic would also help you in tackling other transient connection errors so it's always a good practice with SQL Azure to have retry logic in the code. More here: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Wednesday, February 29, 2012 8:22 PM
    Moderator

All replies

  • AFAIK, the stored procedures get compiled after the first run. In my understanding, Stored procedures execution plan is cached on the first run and next run onwards it boosts the performance.

    Some information here http://msdn.microsoft.com/en-us/library/ms190439.aspx see if that helps.

    -Sachin Sancheti

    Wednesday, February 29, 2012 7:07 PM
  • I'll try and answer your second question: How can I keep the DB from "falling asleep"? Should I just send a dummy wakeup request every couple of seconds or is there a configuration that I can change?

    - With SQL Azure, try embedding "re-try logic" in your code. So even if your DB falls asleep (and i believe you are referring to the current idle connection time out limit of 30 seconds) - Then it should get resolved when you retry connecting to the SQL Azure DB. And re-try logic would also help you in tackling other transient connection errors so it's always a good practice with SQL Azure to have retry logic in the code. More here: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Wednesday, February 29, 2012 8:22 PM
    Moderator