Load/performance testing

Question

Post reply

Load/performance testing

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

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

pshaship SSCertifiable Points: 6125 More actions · Answer 1

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

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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

BlackHawk-17 SSChasing Mays Points: 628 More actions · Answer 4

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.