Load/performance testing

  • Hello,

    We have SQL Server 2005 Enterprise Edition with SP3 and have MOSS 2007 SP1 databases.

    We have 16 GB physical RAM and Max server memory is set to 12 GB & left 4 GB to OS and lock pages in memory is NOT set. Page file size is default 2046 MB and it’s located in C drive. And all the storage is from SAN.

    We did load testing for Share point application for 1 hour and we collected the values for different counters from the perfmon for this duration of 1 hour on SQL Server.

    I have couple of questions regarding these perfmon readings:

    Memory\Available Mbytes

    Threshold: A consistent value of less than 20 to 25 percent of installed RAM is an indication

    of insufficient memory

    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?

    Memory\Page Reads/sec

    Threshold: Sustained values of more than five indicate a large number of page faults for read requests

    Here, in 1 hour, we noticed one value is more than 5 and it went to ~9.5

    2.Is this acceptable?

    Memory\Pages/sec

    Threshold: Sustained values higher than five indicate a bottleneck

    Here, in 1 hour, we noticed that the value went to >5 three times as below

    9.5, 190 & 5.5 and it’s the same time when the Page Reads/Sec value went to 9.5.

    Is that normal to have some spikes of higher values or Pages/sec should always has to have >5 ?

    Disk Sec/Write for data file

    This value went to >10 ms twice 50 ms & 75 ms respectively.

    Disk Sec/Read for data file

    Is <10 ms thought out the test (for 1 hr)

    Disk Sec/Write for log file

    This value went to >10 ms twice 50 ms & 75 ms respectively.

    Disk Sec/Read for log file

    Is <10 ms thought out the test (for 1 hr)

    Target Server Memory & Total Server memory is always same i.e 12582912 KB (12 GB)

    Please give me some inputs based on above results

  • Is SQL Server the only thing installed on the server, or is it an application server as well?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Grant the lock pages permission. Leaving that unset is asking the OS to swap SQL's memory out for no good reason.

    Were the disk counters spiking to 'unacceptable' values or was it sustained? Spikes aren't in general a concern. Sustained unacceptable values are.

    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
  • Is SQL Server the only thing installed on the server, or is it an application server as well?

    It's a separate Sevres for SQL Server. No other applications installed except Anti virus software

    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 %

    I'm more concerned about the above available bytes. Because it's always 2200 MB. Is this the problem with having insufficient memory?

    many thanks

  • Are you seeing deteriorating performance?

    Your memory available could be a result of the AV and max memory setting on your SQL instance.

    I suggest you go ahead and do the Grant the lock pages permission suggestion that Gail made.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • A

    re you seeing deteriorating performance?

    As

    Your memory available could be a result of the AV and max memory setting on your SQL instance.

    I suggest you go ahead and do the Grant the lock pages permission suggestion that Gail made.

    We are NOT seeing any performance degradation. As the application is load testing stage, I cannot see any problem now. But I'm concerned about this less available memory as it may effect the performance once we go live. Having 2200 MB available memory out of 16 GB RAM is a problem?

    How much available memory should be left as per Best practices?

    If I enable lock pages in memory, then the available memory (bytes) will be increased? How can I make sure that enabling lock pages in memory, will improve the performance?

    thanks again

  • pshaship (3/11/2010)


    ...But I'm concerned about this less available memory as it may effect the performance once we go live. Having 2200 MB available memory out of 16 GB RAM is a problem?

    Not necessarily. That is plenty for the OS.

    How much available memory should be left as per Best practices?

    It depends. Each server and environment is different. This is where monitoring your server comes into play.

    If I enable lock pages in memory, then the available memory (bytes) will be increased? How can I make sure that enabling lock pages in memory, will improve the performance?

    thanks again

    Lock pages in memory will help to prevent swapping to the pagefile on disk. Typically, if you decrease the amount of swapping, you decrease the amount of IO, and thus you decrease latency related to IO, and thus you increase performance.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I went through the link http://support.microsoft.com/kb/139609 and it says:

    PerfMon: High Number of Pages/Sec Not Necessarily Low Memory

    Is this applicable to Windows 2003 R2 X64 with SP2 too (It did not mention in the "Applies to") but I'm curious to know

    We have another SQL Server 2005 Enterprise edition with SP3 and we have 4 SQL Instances on the same server.

    We have 16 GB RAM and we set Max memory as 3 GB for each and left 4 GB for OS.

    Question: How to set "lock pages in memory" option, when we have 4 instances on same server?

    thanks

  • It's a local security policy setting. Grant the SQL Server Service account rights.

    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
  • Hi Gail,

    I have followed the below link to enable lock pages in memory (Is the right method to enable lock pages in memory?)

    http://msdn.microsoft.com/en-us/library/ms190730%28SQL.90%29.aspx

    We have 3 node a/a/p cluster setup. We have 1 sql server instance on node1 & 4 instances on node2 and node3 is passive.

    I have set lock pages in memory on node1.

    Now I'm planning to set lock pages in memory on node2. So I just go & set it and it will apply to all 4 instances on node2 right?

    And do I need to enable lock pages in memory on node3 (passive) too?

    thanks

  • Now I'm planning to set lock pages in memory on node2.

    Hi Gail,

    Do we need to enable lock pages in memory option on Passive node too?

    please advice

    thanks in advance

  • I have a lot of clients that would be very happy to have your numbers on their machines. 😀

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

  • If the passive node is going to be used for failover then "YES" you would need to enable "lock pages in memory" for this node.

    Also, if your service account is different for each instance you would need to grant this permission (lock pages) for each service account.

  • Steve-357 (3/16/2010)


    If the passive node is going to be used for failover then "YES" you would need to enable "lock pages in memory" for this node.

    Also, if your service account is different for each instance you would need to grant this permission (lock pages) for each service account.

    Agreed on both points.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • This may or may not be relevant here but we did load testing on our MOSS 2007 website and we were able to triple the user count by enabling BLOB CACHING where the first call pulls the file from the SQL Server and stores it on the file system of the web server (MOSS) This feature is enabled in the web.config for common css, and js files that are downloaded from the SharePoint content database everytime a user opens a page. There are other caches like the OUTPUT CACHE for each site collection too. Obviously, caching isn't right for all sites and your the only judge of that, but thought I'd share what we found with you, since we found MOSS makes lots of calls to the SQL Server since "most" everything is stored in the Content database for SharePoint sites. It was talking up lots of resources for files that were static.

Viewing 15 posts - 1 through 15 (of 18 total)

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