SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Granting the Lock Pages in Memory user right to the SQL Server service?


Granting the Lock Pages in Memory user right to the SQL Server service?

Author
Message
SkyBox
SkyBox
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 737
I am researching this memory setting and trying to gather as much info as possible. I want to be sure that my sql min/max memory settings are adjusted appropriately for this change. Also, how will this play out with my large paging file that has a dedicated physical disk?

My total used memory on the (SQL only) box is at 99% and SQL uses 92%.

SQL BOX:
Win Server 2008 2008 R2 Enterprise
Memory - 144GB
Pagefile - 300gb
Pagefile local disk - 418gb

SQL Server 2008 Enterprise
SQL min server memory 0 MB
SQL max server memory 143872 MB
Use AWE = no

Lock pages in memory
This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).

http://support.microsoft.com/KB/918483/EN-US

Want to be sure that I am getting the most out of my hardware - Would appreciate any advice...
Sqlchicken
Sqlchicken
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 669
For all things hardware regarding SQL Server, you can't do much better than Glen Berry. Check out his min/max memory settings post: http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

Don't set the max SQL Server can use more than the server has otherwise a runaway query/process could choke the box out by starving the OS of memory which would possibly not let you remote in and fix the issue (while I'm at it, make sure you enable DAC connections on your servers as safeguard).

Another great resource is Brent Ozar, he has tons of performance tuning tips/articles you should read through: http://www.brentozar.com/sql/sql-server-performance-tuning/

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
SkyBox
SkyBox
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 737
Sqlchicken (6/17/2011)
For all things hardware regarding SQL Server, you can't do much better than Glen Berry. Check out his min/max memory settings post: http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

Don't set the max SQL Server can use more than the server has otherwise a runaway query/process could choke the box out by starving the OS of memory which would possibly not let you remote in and fix the issue (while I'm at it, make sure you enable DAC connections on your servers as safeguard).

Another great resource is Brent Ozar, he has tons of performance tuning tips/articles you should read through: http://www.brentozar.com/sql/sql-server-performance-tuning/


I have 2GB allocated for the OS and the rest to SQL. I will read up through your links. Thanks for the info...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search