Need to automate raising and lowering of SQL Memory 1

[jeromaqui]

I have a problem with SQL Server slowing down. It seems that when I raise and lower the sql memory, the problem is fixed. I plead ignorance on why this is so, but it works. I was wondering if anyone knows how to automate lowering and raising sql memory or has a script that will do this. Thanks in advance.

 

[ClaireHCM]

Go to enterprise manager -- Right click on properties of the server which you want to set up memory configuration -- Go to memory tab where you can set up the mininum memory you want sql server to use

 

[NoCoolHandle]

This should be done automaticly if you have SQL manage the memory..

In Enterprise Manager you would look at servername right click -> properties -> Memory -> check Dynamically configure.

I know for sure that if you preallocate memory and give it too much you will likley cause MAJOR problems. As when SQL assigns pages to memory it will be placeing cache data on the disk (page faulting)

Not good.

You could possibly set a perfmon counter on SQLSErver Page Faults and if you see more than 20 use sp_configure to modify your memory (most likley not recomended).

In the old days (sql 6.5 and before) it was often better to under guestimate the amount of memory and set it low for the above reasons..

Rob

 

[NoCoolHandle]

Oh yeah if you want to do this in tsql

sp_configure 'max server memory (MB)',256
reconfigure

would set memory to 256 MB of ram

then

sp_configure 'max server memory (MB)',128
reconfigure

would set it to 128 mb

HTH

Rob

 

[jeromaqui]

Thanks for your help !

 

[NoCoolHandle]

Had a thought this morning... (won't have any effect if your memory is set too high, but it could clear old clean pages from memory leaving more space for read ahead opps)

DBCC DROPCLEANBUFFERS

You could run this everys often and see if it helps.

Rob