Determining the amount of RAM needed for SQL Server
We recently had an outside IT firm come in and virtualize our MS SQL server. Since the virtualization the server appears to be half as fast as it was prior to the virtualization. They have limited the amount of RAM to 32GB and they want me to cap SQL at 24GB of RAM. We have 32 order processors banging on the server 12 hours per day. I would prefer they increase the amount of RAM allocated to this image to 64GB. I have not been able to get any information about how the drives are configured. From a memory standpoint what would be the suggested amount of memory needed for 32 users hitting a 100GB database?
ASKER
Currently there are 7 tempdb.ndf files along with the one tempdb.mdf. Each file is 74MB in size. The mdf and ldf files are in separate folders but I have not been able to obtain information on the configuration of the drives.
Virtualization on the same hardware must decrease the speed but the ratio may vary...
The highest impact should have other VMs actively running on the same hardware namely when the cumulative VMs configuration exceeds the available hardware resources.
If you have just one VM on the physical machine then you should be fine.
Disk access change from direct hardware access to virtual disks could also be critical...
You may read more e.g. here: https://serverfault.com/questions/135431/is-virtual-machine-slower-than-the-underlying-physical-machine
The highest impact should have other VMs actively running on the same hardware namely when the cumulative VMs configuration exceeds the available hardware resources.
If you have just one VM on the physical machine then you should be fine.
Disk access change from direct hardware access to virtual disks could also be critical...
You may read more e.g. here: https://serverfault.com/questions/135431/is-virtual-machine-slower-than-the-underlying-physical-machine
>> If you have just one VM on the physical machine then you should be fine. <<
But then why not just run it on a physical box? I wouldn't let them virtualize where I'm at (and our instances need way too much RAM anyway)
But then why not just run it on a physical box? I wouldn't let them virtualize where I'm at (and our instances need way too much RAM anyway)
ASKER
Virtualizing was not my idea. I believe the bigger picture is to move everything out to the cloud. I will try to find out how many other servers are being hosted inside this host server.
VM in a cloud is the trend Today... and the speed depends on your budget. Network throughput could be the main blocker but it is hard to say w/o any experience with the application, particular VM, and the provider.
Ideal solution would be to start with an oversized configuration and propose certain savings when the SQL Server loading is too low... Obvious way is to start with something smaller and let the configuration grow with increasing data size and number of users.
Anyway, start monitoring wait states and long running queries since beginning.
Ideal solution would be to start with an oversized configuration and propose certain savings when the SQL Server loading is too low... Obvious way is to start with something smaller and let the configuration grow with increasing data size and number of users.
Anyway, start monitoring wait states and long running queries since beginning.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Which virtualization tech, what else has been virtualized running on the same host and what is the prioritization?
24GB on a 100GB DB seems off, but prioritizing the DB server over everything else could reduce the appearance of slowness.
how is the DB being accessed, is it using a web based interface?
SQL the more memory the better.
if you look at the taskmanager/performance how much memory is the System using RAM plus paging.
a 100GB with 90GB archival with 10GB actively accessed, 24GB would seem fine, and as noted, equal weight prioritization would appear sluggish. an 80/20 split between the SQL server and everything else, should get the performance back to what it was with a physical server.
Network infrastructure?
The other question is what Network Feed does the sql server VM have and the systems accessing it. same host different host , etc.
24GB on a 100GB DB seems off, but prioritizing the DB server over everything else could reduce the appearance of slowness.
how is the DB being accessed, is it using a web based interface?
SQL the more memory the better.
if you look at the taskmanager/performance how much memory is the System using RAM plus paging.
a 100GB with 90GB archival with 10GB actively accessed, 24GB would seem fine, and as noted, equal weight prioritization would appear sluggish. an 80/20 split between the SQL server and everything else, should get the performance back to what it was with a physical server.
Network infrastructure?
The other question is what Network Feed does the sql server VM have and the systems accessing it. same host different host , etc.
ASKER
The database supports a 32-bit legacy ERP solution. Setting SQL RAM to unlimited and SQL takes up all 32GB. Since I set it to 24GB it went to24GB which no appreciable speed degradation. The slowness started as soon as they switched to virtual servers. I am trying to get the hardware specifications and virtual configuration but I am meeting with resistance. It takes SSMS almost 2 minutes to fire up so I do not think it is a SQL issue it is a hardware/virtualization configuration issue.
To change the VM configuration for a limited time should be possible and not so expensive. It should be the easiest way to determine the root cause of the slowness.
Did you check the Performance in Windows Task Manager? Does your VM have some reserves?
Also what recommends the ERP application vendor as an optimal configuration?
Did you check the Performance in Windows Task Manager? Does your VM have some reserves?
Also what recommends the ERP application vendor as an optimal configuration?
ASKER
This image was PTV'd from a physical server. It ran fine as a physical server. I am convinced the problem is with the host server. I am working wth the IT staff trying to get specfications and configuration of the host server.
You can query the VM configuration and CPU utilization in SSMS, e.g.:
Please let us know the result.
SELECT * FROM sys.dm_os_sys_info
SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'Please let us know the result.
Much depends on the basis on which the virtualization environment, specification. Based.
A dedicated SQL server is commonly 30% in use, a virtualize host is commonly, 70% in use.
Resource allocation/prioritization is important to the performance of the environment.
The virtualization remains in house?
Two changes, virtualization, and the setup moved offsite, cloud, VPS style setup?
A dedicated SQL server is commonly 30% in use, a virtualize host is commonly, 70% in use.
Resource allocation/prioritization is important to the performance of the environment.
The virtualization remains in house?
Two changes, virtualization, and the setup moved offsite, cloud, VPS style setup?
ASKER
I continue to get further information on how this host server is configured. There is only so much I can do. Thanks everyone for your expert tips.
But for only a 100GB db, 36GB might be enough (out of 48GB total on the server). I would be leery of less for performance reasons.
You can then still bump up SQL to 38GB or even, if you must, 40GB. You need some type of RAM cushion on the server, in case SQL performance suddenly falls off.
Make sure tempdb is also sufficiently large and that you have enough tempdb data files.