You say you enabled AWE on SQL Server but did you activate /PAE in the boot.ini file?
PAE enables Large Memory support.
Activate PAE in Windows 32Bit Enterprise
c:\boot.ini
[boot loader]
timeout=30
default=multi(0)disk(2)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
http://support.microsoft.com/kb/283037
Lock Pages in Memory
As part of enabling AWE, you would also need to give the SQL Server Service user the right to Lock Pages in Memory. Enabling this option in the Group Policy dialog (gpedit.msc), prevents Windows from paging memory out of the SQL Server working set for its own needs, especially if memory starts to run low on Windows.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Monitoring AWE Memory Usage
To test AWE usage, I setup a Windows 2003 Enterprise SP2 with PAE enabled in boot.ini. I used SQL Server 2005 32bit Standard SP4.
The total physical memory is 8192Mb. The max memory setting for SQL Server is configured at 8192 and the minimum is at 2048.
Does SQL Server see AWE on startup.
2012-02-09 07:27:51.35 Server Address Windowing Extensions is
enabled. This is an informational message only; no user action is
required.
Is SQL Server using Locked Pages for the Buffer Pool
According to some blogs, I should be seeing this message, but I'm not.
Using locked pages for buffer pool.
This is an informational message only; no user action is required.
Memory being allocated through AWE
SELECT SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM sys.dm_os_memory_clerks ;
[3648]
Memory allocated to Multi-Page memory.
select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks
[14]
Multi-page memory can not use AWE allocated memory
Memory being used outside the buffer pool
SELECT sum(multi_pages_kb
+ virtual_memory_committed_kb + shared_memory_committed_kb) AS
[Memory used outside BPool, mb]
FROM sys.dm_os_memory_clerks
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL'
[24]
Overall memory allocation by component
SELECT type, (single_pages_kb)/1024 as Single_Pages_MB, (multi_pages_kb)/1024
AS Multi_Pages_MB, (awe_allocated_kb)/1024 as AWE_allocated_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC
This will show you how single-page and multi-page memory is being used by each component. It will also tell you which one is able to benefit from AWE.
Mutli-page : when request exceeds 8Kb
Single-page : when request is less than or equal to 8Kb
As a side note, I have found sp_whoisactive very helpful in finding slow queries, memory hogs and just about everything that's going on in SQL Server. Here is a link that Brent Ozar provides on how to set it up and use it.
http://www.brentozar.com/archive/tag/sp_whoisactive/
There is nothing inherently wrong with the current configuration however you have not included info that would be relavent, such as whether the host is overcommitted on RAM/CPUs. What problem are you trying to solve - performance and/or HA/DR? Assuming you are not overcommitted:
- Make sure the SQL Server instance(s) have sensible max memory settings. Ensure resource is allocated to accomodate that. If money is no object, use resource pools.
- VMware (in my opinion) is a better choice than HyperV, but it isn't a big deal which you choose.
- If you have enough memory to hold all the databases on the instance(s) which are virtualized, your main concern after that is CPU pressure, which is easy to measure. If you don't (which seems to be the case from your description), you're relying on storage for IO much more, so using the SSDs is obviously a good approach.
- I'd suggest the contractor DBA you are working with is coming up with ideas designed to keep him/her in a job as opposed to solving a problem. Either that or they have no experience of VMware. Or both. You can still allocate resources in VMware in much the same way as with HyperV. Striping tables is not necessarily a performance improvement, depending on the nature of your storage array and the IO requirements of the other VMs.
- If your SQL instance is Enterprise edition, you ought to use a physical box.
- WHen you say "6 CPUs" are you talking about an 8 socket box with unpopulated sockets or a single socket populated with 6 cores? If it is the latter, your performance issues will almost certainly be caused by processor availability, as VMware will only use all cores allocated to a VM once they all become available.
Bottom line - it seems that the host is under resourced for memory and CPU cores, which won't go away with HyperV. If you have need for more than 128GB of memory (based on amount of data in all databases) a physical box for the SQL server would be a good choice.
I wouldn't bother with anything fancy like table partitioning until you have sorted your resource issues.
Best Answer
There is no clear best practice on this. It all depends on your VMWare configuration and failover requirements.
The idea behind this is the fact that it should be preventing SQL Server from releasing memory when the VMWare balloon driver thinks it needs this.
There are several possible problems with that. One of them being the fact that the balloon driver may be requesting memory because a physical host is failing and the VM needs to be moved elsewhere (or other VM's need to be moved to the host the SQL VM resides on).
In case of disaster, what do you prefer? A slow SQL Server or no SQL Server?
The balloon driver will just request memory from other processes on the server if SQL doesn't release it (SSRS, OS, whatever) and if the OS starts paging SQL will suffer anyway.
If you have this configuration because of you are overcommitting memory it might be a better option to reserve memory for the server in VMWare to make sure SQL actually has available what has been allocated.
This setting could be a good configuration, but setting it as a default seems wrong to me. You need to thoroughly understand what it does and what the effects on VMWare are to decide if it should be configured in your environment. Have a good chat with the datacenter/VMWare folks to understand how they have things set up too.