Virtual Memory Manager

Question

Virtual Memory Manager

steve-212144

SSC-Addicted

Points: 426
More actions
April 12, 2010 at 2:54 pm

#220382

I'm running Win2008 Standard server and 6 licensed instances of SQL Server 2008 Standard. I have the maximum allowable RAM of 32GB for my configuration. Max Server Memory is set to 4GB per instance and currently no performance issues.
Should I try to manage Virtual memory or should I continue to let Windows manage it for me? What's the recommendation on this and is there any documentation that I can review.
There is not a lot of drive space left on this server; about 10GB free. But database growth from day-to-day is minute. That 10GB may last me for the next 12 months. But when I go into VMM, which is currently set to automatically manage virtual mem, it is allocating 26GB to virtual memory, even though the server does not have that much free drive space.
Should I be concerned about the amount of VM that is being automatically managed by Windows? What are the recommendations?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Toby White SSCertifiable Points: 6187 More actions · Answer 1

I would not do what you are doing with your multiple instances. I would generally opt for one or two instances and share resources. However, I assume you have good business reasons for splitting up the instances like you are.

I would not set virtual to larger than available. So in your case I would trim that down to 10GB. Also, I would set min/max at something like 2GB/8GB for each instance and just allow the instances memory to be trimmed when other instances need the memory. You currently have a total of 24GB for SQL which leaves 8GB for the O/S which is more than you need unless you have 4 or more cores.

I usually don't allow memory trimming and grant the account that runs SQL Server "Lock Pages In Memory", which I believe makes a difference on SQL 2008 Standard (it didn't in 2005 standard), but in your case I would allow cannibalization of memory between instances assuming you have some databases that are over roughly 10GB.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Toby White (4/13/2010)
(it didn't in 2005 standard)

This was enabled for SP3 CU4 on SQL Server 2005 Standard and SP1 CU2 for SQL Server 2008 Standard

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

steve-212144 SSC-Addicted Points: 426 More actions · Answer 3

Toby

Thanks for the advice. I will apply the suggestions where I can.

Thanks

Toby White SSCertifiable Points: 6187 More actions · Answer 4

I hope it helps your environemnt Steve. And thanks for the clarification on the versions that allowed the lock pages Perry. I always felt resentful that the SQL engine would ignore this privelege whether it was granted or not on Std Edition.

steve-212144 SSC-Addicted Points: 426 More actions · Answer 5

I don't have much say in this environment, even though I'm the only SQL guy in the IT dept. It was my Sup who decided that there should be a separate SQL Server instance for each of the 6 regional offices. Each regional office has its own database, but they are all hosted by the same Win2008 server, with a separate SQL install for each office. They are small offices (4 or 5 employees per office). Each office does a remote connect to the home office in order to access their respective databases. My preference would have been simply the Default instance of SQL managing all 6 databases. The Sup said (this was nearly 2 years ago) that to be legal, each regional office had to be running their own licensed copy of the front-end app. Maybe so, but when I looked into it, I found that each licensed copy of the front-end app could access the same instance of SQL Server, but my Sup insisted on 6 instances of SQL as well. What ya gonna do. It sure make it a challenge for developing things like SQL reports that have to pull data from all 6 databases.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

it sounds like they may have been separated for adminstrative reasons. That's perfectly valid if that is what you want to do. As the server is licensed from the install of the first instance, you may install as many instances as required up to the maximum of 16

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉