Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Limit buffer pool memory amount by database Expand / Collapse
Posted Monday, May 3, 2010 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:59 PM
Points: 26, Visits: 59
We have a SQL Server 2005 SP3 on Windows Server 2008 x64 that has 14 different database on it. The server has 32GB of memory. One of the databases runs a job that goes for about 24 hours and during that time it does a lot of deleting from tables. This causes physical size on disk to grow as well as the logs. However, our concern is the amount of memory it consumes during this time. Currently, it's consuming 21.4 GB of our 32 GB. I'd like to limit the amount of memory in the buffer pool that it can use down to somewhere in the 8-10GB range. It isn't critical that the job runs quickly, but it is critical that the other databases use more memory and perform faster. When this job is running, everything else slows to a crawl and the other databases are starved for memory.

I've done some searching and haven't found that there is a way to do this. Does anyone have any recommendations?
Post #914751
Posted Friday, October 15, 2010 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 1:12 PM
Points: 1, Visits: 31
One possible solution would be to create multiple instances of SQL Server 2005 on the machine. You can control the max available memory to SQL Server, but this would affect all the databases running in that instance. If you create another instance, move the database over there and set the max available memory. Of course, the connections would have to be changed in the client apps.
Post #1005328
Posted Friday, October 15, 2010 10:14 AM



Group: Administrators
Last Login: Today @ 2:55 PM
Points: 34,371, Visits: 18,589
I'm not sure what you can do here, except perhaps limiting the deletes to smaller batches and then use less RAM.

You could use separate instances, but then you'll be balancing memory among instances.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1005337
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse