Limit buffer pool memory amount by database

  • 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?

  • 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.

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply