Hey,
Just a quick post based on some questions on performance boosts I received at the presentation the other night. There are so many ways to tune your SQL Instance/application, but for someone who wouldn’t be too comfortable going in deep, the biggest bang for the buck, for me, comes from loading your server with more RAM and enabling AWE.
There are limitations inherent to 32 bit addressing where it can only support an upper limit of 4 GB of RAM natively, so even if you load up your host machine with 32GB of RAM, the most SQL will be able to take is 4GB (but will actually top out around 1.7 – 2GB in most cases I have seen).
This is where AWE(Address Windowing Extensions) comes in. It hands the management of your memory over to the OS, which can bypass the 32 bit restriction and donate more RAM to your SQL instance. There are still limitations over using 64 bit, even if both instances are granted the same amount of RAM (Data cache can only use RAM above 4GB on 32 bit, etc.), have a look at THIS internals video if you’re feeling brave. Still, it’s well worth doing if you’re seeing any memory problems, or even certain disk I/O problems. Recently I had a server that was doing a lot of paging in and out of the buffer pool due to a bad query, I couldn’t fix the query as it was 3rd party, but by enabling AWE the buffer pool took the extra 150MB it needed (That’s all!) and the swapping stopped.
Here’s the technet article on AWE for a deeper insight, but for the moment, to enable AWE you need to do the following:
- Go into Local Security Settings (click Start, Run, type SECPOL.MSC and hit enter) and click Local Policies, User Rights Assignment.
- In the permission “Lock pages in memory”, add your SQL Server service account or domain group. This lets SQL Server use additional physical memory instead of swapping it out to disk if we go over roughly 2GB. Essential to enable AWE on 32 bit systems
- Go to Management Studio -> Right click instance -> Properties -> Memory -> Click box AWE enabled
- Set an upper limit on the memory SQL is allowed to use, as now it will take everything in the box if it is allowed. Reserve about 2GB for the OS, plus whatever RAM your other apps on the box will need, and subtract that from the total RAM installed. This should be your SQL upper limit (EG. 16GB installed, minus 2 for the OS, minus 3 for IIS, Max SQL memory should be 11GB)