Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2011-06-02 : 20:07:06
|
| We run every most frequently with long SP's driven through the SQL Agent Job. While it take almost more than a day We check its buffer memory allocations and it happens the FREE PAGES gets diminished as the time progresses and remove them through DBCC for Procedures and buffers.The reason is it is taking long hours to finish the job hence we adopt this way.Is this good practise while a long running job, if not what is the best way of releasing the buffer and its other memory allocations?Many Thanks in advance. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 03:33:14
|
| Usually you should let sql server take care of it.Your issue is the design and implementation - sort that out to speed up the processes - probably tackle a few of the more importatnt things then you will have more time to look at the rest as you won't have to babysit the system.I take it you are gathering history of how long everything takes and the rowcounts so you know what to look at?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-06-06 : 19:57:04
|
| Many Thanks NigelRivett,Yeah you are right in the perspective of Design and Implementation.The Design Rules were apt and Implementation changes as the data is migrating from time to time and no doubt it is on the increase.As the Rule goes by, the data is drawn through the pipes of Link Server and is taking more time to finish the SQL Agent Job.This is ever increasing rather being stagnant in execution time, when I encroach and liberating manually the ree Pages and then it speeds up.I just wonder what else could be done to expedite the Agent Job such that it will keep the execution time in reducing time apart from increasing the Free Pages and Buffer Memory parameters.Thanks again. |
|
|
|
|
|
|
Memory Cache