July 5, 2007 at 4:05 am
I've got a SQL Server 2000 running on my development desktop, with databases I use for testing, and I would be happy to limit the amount of memory it can use, so that it (together with other greedy applications) doesn't force the system into paging. (MSSQL starts low (< 100MB) but over days it can reach 1GB and more, seemingly without doing much work that would justify the growth.) The current alternative is to shutdown and restart SQL when it grows too big, and I'm happy enough with that. Yet, exploring the SQL Server Properties dialog, it seemed I could do better... But the option "dynamically configure SQL Server memory" (SQL Server Properties|Memory) doesn't seem to work. I can change the "maximum" value from the default 2GB (= the amount of physical memory) to 1GB and click OK, but when I reopen the dialog window, "maximum" is back to 2GB. 1) What happens ? Ways to circumvent this problem ? 2) is this a good idea to limit memory MSSQL uses ? When MSSQL reaches, say, 1GB after days of intermittent use, does it really need all that memory, or is it safe enough to forbid it go beyond ? (i don't think any single query of mine requires that.) Thank you.
July 5, 2007 at 5:15 am
Its not always a good idea to limit the memory used by SQL server with the amount of RAM that you have in the server(2GB). Allow SQL to handle memory dynamically so that let i decide what it needs you can limit the memory using the configure sttic memory option but then SQL will hang if it needs more memory that what you have allocated.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 5, 2007 at 10:53 am
Don't do the dynamic thing. Allocate a static amount. If things run slow when you need it on the dev machine, raise the max for the day and then reset it the next day.
July 5, 2007 at 11:08 pm
Since you say this to be a dev machine one idea is to schedule a job to restart sql services so that memory will be cleared and will start from scratch.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 6, 2007 at 8:46 am
Thank you all for your advice. I've set up a job to restart the MSSQLSERVER service every night. Great idea !
(with the help of http://support.microsoft.com/kb/162294 to know how to do that)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply