Case of Missing RAM from SQL server and AWE

Recently I came across any issue with Windows 2008 R2 server with high memory utilization. This server was hosting a custom monitoring tool and it was not servicing runtime reporting request to do performance hit.

The Server had 8 CPU cores and 12 GB ram. CPU utilization was in check however RAM utilization was above 95% consistently. Server owner informed that they are usually forced to reboot the box to get memory utilization under control and generally after 2-3 days uptime memory spikes again to 95-100% and never goes down. First look at  the task manager revealed that memory utilization above 95% however, total memory consumed by processes under process tab was approx 1.5 GB which is less than 13%.

So we started with case of a missing ram. Looking at installed products list, it mentioned SQL 2008 R2 SP2. Off course the usual suspect was SQL. Databases largely  follow Linux memory policy, “Free memory is wasted memory”. Like any other database product SQL has the tendency to occupy free memory as required.

For performance issues there are multiple tools available out there but for Advanced memory utilization analysis RAMMAP from Sysinternals is best choice. RAMMAP revealed almost 10 GB was occupied by AWE????

Address Windowing Extensions or AWE is windows memory management functions which is used to allow more than 3GB memory to standard 32 bit application. Using AWE for SQL was great option on 32 bit OS with high amount of RAM. But we are running windows x64, where AWE should not have been used.

SQL memory utilization settings showed a different picture all together,

image

Although SQL is set to default value of allow maximum available memory to be utilized “Use AWE to allocate memory” was unchecked. SQL was still our primary suspect and to isolate SQL we took the downtime for application and stopped SQL service. Indeed SQL was the culprit, as immediately AWE utilization was clear and total memory utilized on server was less than 20%. Remember the saying, Things are not always what they look like!

image

We changed the maximum server memory settings for SQL to be 8 GB and started up the services. This time SQL had its max 8 GB and our monitoring APP had sufficient breathing space for all the data collection and reporting 🙂

However, I was wondering on why did i never saw this issue on my test servers? It turns out that AWE cannot be used by any account. Its control by GPO setting “Lock pages in Memory” option.

Capture

If you configure a user account to run SQL services, by default no user has rights for Lock Pages in memory settings and SQL wont be able to use AWE settings. In our case SQL service was running under Local System account which by default has the rights for using AWE.

Advertisements