Store Procedure takes lot of time to execute at 1st

  • OK, here's my doubt

    Do I really need DBCC DROPCLEANBUFFERS to force a store procedure runs faster or validates new changes on it?

    It is my understanding that sp_recompile and/or "WITH RECOMPILE" option should provide that, am I wrong? Instead, got these developers that want to get the ability to run DBCC DROPCLEANBUFFERS just because 1st time, store procedure (after some changes) takes hours to complete. 2nd, 3rd and subsequent execution times takes just seconds of course.

    They said sp_recompile did not achieve same results, still takes hours to execute for 1st time.

    Suggestion, opinions?

    Thanks in advance,

    EDIT: after reading my own post, i believe that what they are trying to do is flush the data cache, not the procedure cache. But still, my question remains, how can I avoid the use of DBCC DROPCLEANBUFFERS via T-SQL or any other way. Our policies do not allow DBCC commands to developers, so ... no way to give that

  • Are you saying that if you clear the buffers, the proc takes seconds to run the first time, and then keeps going that way?

    I've never seen that behavior. But I've also never seen a proc take hours to run the first time and seconds on subsequent runs, unless it's working on much smaller data sets after the first run (which doesn't have anything to do with clearing or filling buffers).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (1/27/2010)


    Are you saying that if you clear the buffers, the proc takes seconds to run the first time, and then keeps going that way?

    I've never seen that behavior. But I've also never seen a proc take hours to run the first time and seconds on subsequent runs, unless it's working on much smaller data sets after the first run (which doesn't have anything to do with clearing or filling buffers).

    No, you are correct.

    1st time, takes an eternity. After that, seconds, really quick, unless you use DBCC DROPCLEANBUFFERS. I am trying to find a workaround for the DBCC command, a way to force MS-SQL to flush the data buffer. We do not grant DBCC DROPCLEANBUFFERS to developers.

  • hours to compile? literally? is it eight million lines of code? i'd like to see it, maybe it can be optimized.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Like an 1 hr or so.

    Unfortunately , I can not post the code here (data privacy and all that stuff) and honestly, I do not want to dig in the code itself. Maybe it sounds like the root cause of the problem or right approach, but what I need right now or I'm focus to is in providing a good replacement or workaround, for the DBCC DROPCLEANBUFFERS command.

  • iamthemanx (1/27/2010)


    Like an 1 hr or so.

    Unfortunately , I can not post the code here (data privacy and all that stuff) and honestly, I do not want to dig in the code itself. Maybe it sounds like the root cause of the problem or right approach, but what I need right now or I'm focus to is in providing a good replacement or workaround, for the DBCC DROPCLEANBUFFERS command.

    Tell the devs to stop writing junk code is the only workaround I can think of.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Are your developers running ALTER scripts or DROP and CREATE? Seem to remember having performance issues w/ 2000 using ALTER.

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • Quite confusing what exactly is the problem, I would say you need to explain it better to get right help...

    Are you saying that after cleaning buffers first time procedure run fast and then runs slow in next execution? possible due to data buffer is cleared out, though next runs should be faster since procedure plan is in cache as well as data...

    SP with recompile do not clear data buffer just clear execution plan so as exepcted data will be there in buffer

    How much data you are accesing in procedure, try to quantify on mb or rows number?

    Whats the memory configuration?

    Any set statements are used in procedure?

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • See my comments just posted on a different (but similar) thread.

    http://www.sqlservercentral.com/Forums/Topic859371-360-1.aspx

    I think there is confusion between DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE. If you are wanting to clear cached Execution Plans then you should be using DBCC FREEPROCCACHE. DBCC DROPCLEANBUFFERS will clear cached data pages (and not cached Execution Plans).

    Second, I think you need to understand if the elapsed time is due to compiling the Execution Plan or actually running the code/SQL in the SPROC. Have a look at the script I posted in that other thread. It may give you some insights. If the elapsed time is due to actually running the code/SQL, then it is time to assess the SQL, assess the Execution Plans, and potentially assess the Indexes.

    Peter

    http://seattleworks.com including my blog The SQL Janitor

Viewing 9 posts - 1 through 8 (of 8 total)

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