Load/performance testing

Question

Load/performance testing

pshaship

SSCertifiable

Points: 6125
More actions
March 11, 2010 at 11:49 am

#156773

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

pshaship SSCertifiable Points: 6125 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

pshaship SSCertifiable Points: 6125 More actions · Answer 5

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

pshaship SSCertifiable Points: 6125 More actions · Answer 7

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

pshaship SSCertifiable Points: 6125 More actions · Answer 9

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

pshaship SSCertifiable Points: 6125 More actions · Answer 10

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 12

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

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

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 14

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.