Post reply

Memory Allocation

  • April 3, 2008 at 6:21 am

    #128404

    Hi all,

    Was wondering if someone could help me out.

    Have a server running Windows Server 2003 Enterprise edition, then have SQL 2005 Standard installed. The box has 8gb of RAM, we want to allocate a minimum of 4gb to the SQL service. At present all the memory is dynamic, and it's using around 1.6gb, so in theory if i set the minimum to 2gb and the maximum to 4gb i would expect to see the amount used by the service rise from 1.6gb to 2.0gb but this does not happen. Can anyone tell me what i am missing here.

    Oh, AWE is not enabled, there is no 3gb switch or PAE in the boot.ini as yet.

    Thanks in advance.

  • April 3, 2008 at 6:37 am

    #797750

    You answered the question yourself. Set the /PAE switch in boot.ini and configure SQL Server to use AWE.

    John

  • April 3, 2008 at 6:41 am

    #797753

    Thought as much, just wanted someone to confirm, thanks John.

    Regards,

    Nic

  • April 3, 2008 at 8:06 am

    #797828

    One other thing tho, i have now configured all the switches, if i set the min memory to 2gb, should i see this reflected in task manager or will sql only take the 2gb when it actually needs it?

    Thanks,

    Nic

  • April 3, 2008 at 8:11 am

    #797832

    Nic

    I think with AWE you can't use dynamic memory, therefore you have to fix it at 4BG, say, and you should see it using that as soon as you've restarted or whatever it is you need to do to invoke it.

    By the way, does your server only have SQL Server on it? If so, you should be using a lot more than just half the memory for SQL Server.

    John

  • April 3, 2008 at 8:15 am

    #797837

    thanks for the quick response John.

    Yes it only has SQL on it, at present it's a sandpit, hence the 4gb, i was thinking of allocating 7gb of the 8gb on the box.

    But for some reason it wont take the memory, in task manager it's still showing as less than 100mb used when i have set up all the AWE etc at 4gb min.

    Nic

  • April 3, 2008 at 8:20 am

    #797845

    Nic

    Yes, Task Manager isn't a reliable indicator of AWE memory. Try using the counters in Perfmon, or you should also find the same information in one of the dynamic management views (sys.dm_...) in SQL Server 2005.

    John

  • April 3, 2008 at 8:40 am

    #797862

    Thanks John, i owe you one.

    Had a look round the performance monitor seems to confirm that the full 7gb of memory is being used.

    Thanks again, much appreciated.

    Nic

  • April 3, 2008 at 9:06 am

    #797888

    Some corrections to this thread:

    (a) AWE is indeed dynamic under SQL 2005 (it wasn't under SQL 2000)

    (b) It's not true to say that Taskman is inaccurate for AWE - it simply concentrates on Virtual Memory measurements & doesn't report AWE at all. Keep in mind that when SQL Server is configured to use AWE, it still also uses VM & TaskMan is only reporting the VM component of the SQL Server process' memory. This makes sense when you consider the fact that VM is managed by the OS whilst AWE is managed by the application / process. Hence, you shouldn't expect to see process level AWE counters in an O/S tool like TaskMan. To measure AWE, you need to look at the process publised perfmon counters under SQL Server Buffer Manager: AWE counters, or SQL Server's DBCC / DMVs

    (c) When configuring AWE, you also need to make sure you have set the "Lock Pages in Memory" permission for the SQL Server service account, or your other configurations won't be enough.

    HTH

    Cheers,

    Greg Linwood


    Regards,
    Greg Linwood

  • April 3, 2008 at 9:40 am

    #797916

    Hi,

    thanks for the additional info, yeah the 'lock pages' did come up but got that one sorted, it all seems to be functioning as expected now.

    Thanks,

    Nic

Viewing 10 posts - 1 through 9 (of 9 total)

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