May 23, 2002 at 4:05 am
Hello all,
I have a Production server which has 2gb of memory. SQL server memory is set to configure dynamically. The Server also has a third party application on it. The third party application was running poorly, and the software vendors recommended running the following sql on a job every hour:-
exec sp_configue 'max server memory(MB)',500
reconfigure
exec sp_configure 'max server memory(MB)'1400
reconfigure
The hourly job has the effect of flushing out sql server memory every hour and setting the max amount down to 500mb and back up to 1400mb. SQL server still manages memory dynamically, so within the hour before the next time the job runs, sql server could use more than 1400mb.
I' feel that this job will degrade sql server performance because it will empty the buffer cache and procedure cache. My approach would be to select the 'use a fixed memory' option and set it to 1400mb, and not use their recommendation.
Does anybody have experience of this, and perhaps explain the consequence of running the recommended job every hour.
Any advice would be great.
Pargat
May 23, 2002 at 4:39 am
The cache is your primary speed augementor as it stores execution plans of queries and some datasets that are called often or have been recently called. If you adjust the size of the memory on the fly it will force a flush of these and bascially your queries start at square one and thus will cause performance lags. It would be better for you to change to 500 and let stand there than doing this but then that too can cause a large performance hit based on the amount of transactions done and how much data comes into play at any given time. My suggestion is look to move the app to another box or write something to take it's place that is not so memory intensive (if this is possible). Many software vendors do have people who do a great job in programming but a lot program generically and have not clue how to properly handle the work which can bite you. I would suggest to them they offer you a better solution, look for a better provider, or seperate this process to it's own box.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 6:10 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply