SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Limit buffer pool memory amount by database


Limit buffer pool memory amount by database

Author
Message
amrinea
amrinea
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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?
akrishnamoorthi
akrishnamoorthi
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: Administrators
Points: 109341 Visits: 19356
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search