Limit buffer pool memory amount by database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Limit buffer pool memory amount by database

Limit buffer pool memory amount by database

Rate Topic

Display Mode

Topic Options

Author

Message

amrinea

Posted Monday, May 03, 2010 9:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 30, 2012 9:58 AM
Points: 26, Visits: 56

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

akrishnamoorthi

Posted Friday, October 15, 2010 10:03 AM

Forum 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

Steve Jones - SSC Editor

Posted Friday, October 15, 2010 10:14 AM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

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 »

Permissions