January 27, 2010 at 2:18 pm
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
January 27, 2010 at 2:55 pm
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
January 27, 2010 at 3:42 pm
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.
January 27, 2010 at 3:45 pm
hours to compile? literally? is it eight million lines of code? i'd like to see it, maybe it can be optimized.
Lowell
January 27, 2010 at 4:52 pm
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.
January 28, 2010 at 6:48 am
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
January 28, 2010 at 8:35 am
Are your developers running ALTER scripts or DROP and CREATE? Seem to remember having performance issues w/ 2000 using ALTER.
_____________________________________________________________________
- Nate
February 3, 2010 at 8:01 pm
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
February 4, 2010 at 6:52 pm
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