Load/performance testing

  • Hi,

    I went through the link http://msdn.microsoft.com/en-us/library/ms190730(SQL.90).aspx

    where it says "Locking pages in memory is not required on 64-bit operating systems"

    Now I'm confused what to do. Because, I already enabled Lock pages in memory in all cluster nodes which are 64-bit operating systems

    Please advice whether I can keep lock pages in memory enabled or disable it (remove sql server service account from lock pages in memory right)?

    thanks

  • That's complete garbage. It's AWE that's not required on 64 bit operating systems with 64 bit SQL.

    If you look at the annotations (Click the 'annotations link, top-right of the page), you'll see that it's been replaced and will be updated in a future version and should read

    On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance.Locking pages in memory is not normally required on 64-bit operating systems. You will need to enable this right on 64-bit operating systems only when using Large Page Memory support or to configure SQL Server such that the Buffer Pool memory does not get paged out.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Also note that Microsoft recommends you do some homework to evaluate the system BEFORE you enable lock pages in memory because it can cause or exacerbate problems.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Here we have 16 GB RAM and Memory\Available Mbytes is always 2200 MB (~14% of 16 GB). Ideally we should have 20 to 25 % (3.2 GB to 4 GB) but we have ~14 %

    1.So is this the indication of insufficient memory?

    You have to understand that PerfMon's explanations are generic in nature. On a standard file and print server, an application server, even a Domain Controller, less than 25% available RAM may be an indication of an overworked system.

    SQL lives in RAM and will gladly consume all you provide, in fact if it doesn't then you have too much RAM 🙂

    Looking at your numbers: you had told the O/S that 75% of RAM would be committed to SQL out of the gate. That leaves you with 25% of RAM left. Everything else on your server is consuming the remainder - including the O/S! Of course you will have less than 25% remaining. You guaranteed that outcome when you configured SQL Server. That being said, it is not a problem as it stands as your paging is low. If you do start running CLR processes you may find the amount of RAM lacking but your tests will have to determine that.

    My conclusions at this point are that you don't have any memory issues per se but keep an eye on the available RAM. At present you know your system functions with that overhead remaining.

    As for the disk/sec counters, you say that you had a couple of larger transfers. What you don't state is how many total transfers you had. 2 out of 4 is bad. 2 out of 4,000 is not bad.

Viewing 4 posts - 16 through 18 (of 18 total)

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