lock pages in memory and AWE

Hi,
I noticed that SQL Service account needs 'lock pages in memory' permission
to enable Address Windowing Extensions (AWE). Is there any risk in granting
this permission to SQL Server and SQL Agent service accounts?
ThanksOnly if your SQL server gets hacked/compromised. Then again, you are
usually hosed if that happens anyway.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alex" <alim@.rogers.com> wrote in message
news:uWfXe8lYGHA.2376@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I noticed that SQL Service account needs 'lock pages in memory' permission
> to enable Address Windowing Extensions (AWE). Is there any risk in
> granting this permission to SQL Server and SQL Agent service accounts?
> Thanks
>

Lock page in memory setting in SQl 64 system

Hi All,

We know that locking page in memory setting is recommended to set in SQL 64 system. The purpose is used to avoid memory pages in Sql address will not be OS paged out.

It is a little confused that if the memory pages of sql cannot be paged out after setting lock page in memory. Then the memory used by SqL will increase forever. If the physical memory is limited, it will obviously caused the memory pressure of the system eventually. How does the 64 bit sql manage his Buff pool in such scenario? Will it be paged out? Thanks in advance

SQL Server listens to memory notifications from windows. These notifications indicate the state of physical memory on the system, low (meaning that the system does not have enough memory to satisfy new allocations) or High (meaning there is an abundance of memory available).

When the low memory notification is set SQL Server reduces memory usage until the notification goes off, thereby avoiding the scenario you describe.

To answer your question about whether locked pages will ever be paged out - no, locked pages cannot be paged out by the OS. If no more physical memory is available new allocations will simply fail.

|||

Thanks. you mentioned "when the low memory notification is set SQL Server reduces memory usage." However, (in 64 bit environment), since the page is locked in memory at that time, how did SQL Server reduce the memory usage?

Do you mean that the SQL will report error if the sql server is in such scenario (low physical memory in OS but heavy workload in SQL requests more memory) ?

|||Locking a page does not mean you cannot deallocate it. The application that allocated and locked the page is free to deallocate it at any time.|||Thanks a lot !!|||You also can and should set the max memory setting in SQL Server 2005 to a level that reserves some memory for the OS. Slava Oks has blogged about the recommended settings for this. Hopefully, you are running SQL Server 2005 on a dedicated server, so its not competing for memory with any other applications.

local ip address

i've got a ms access mdb connected to a sql server on the internet.
whats the quickest way to get the ipaddress on the client machine from
the client machine? since there is a existing connection, shudn't there
be a simple method call that returns the ipaddress on the connection or
something?
the solutions i've seen r very ugly :(
riyazYou can get the MAC address of the client by doing this...
select net_address from master..sysprocesses where spid = @.@.spid
You then need to do processing outside of SQL Server in order to relate the
net_address to an IP address.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<rmanchu@.gmail.com> wrote in message
news:1137653314.617807.262180@.g43g2000cwa.googlegroups.com...
> i've got a ms access mdb connected to a sql server on the internet.
> whats the quickest way to get the ipaddress on the client machine from
> the client machine? since there is a existing connection, shudn't there
> be a simple method call that returns the ipaddress on the connection or
> something?
> the solutions i've seen r very ugly :(
> riyaz
>|||yup i'm already getting the mac address like that. but i can't find any
clean function that relates the mac to the ipaddr. the code on the
mvp.org site returns a collection which i don't think can be related to
the mac
how can this be done?
? possible bug?
!!! i noticed the following when using access2003 over the internet to
sqlserver2000 !!!
when using my laptop with wifi, the net_address returned by
sysprocesses is INCORRECT. it returns my LAN mac_address when it shud
return my wifi mac_address since i'm connected the internet thru wifi.
can anybody duplicate this?
riyaz|||>> yup i'm already getting the mac address like that. but i can't find any
All you have to do is to use the address resolution protocol mapping. On
your command prompt, simply type in ARP -a. It should give you a table of
mapping between all MAC & IP addresses.
It is not that hard to get this information from t-SQL ( script or within a
procedure )using master..xp_cmdshell & get the IP address.
Anith|||hi.

> You can get the MAC address of the client by doing this...
> select net_address from master..sysprocesses where spid = @.@.spid
am already doing this

> You then need to do processing outside of SQL Server in order to relate th
e
> net_address to an IP address.
am working in access2003. could u give me an idea as to how i might go
about doing this?
i have 2 ip-addresses both dynamically allocated. i wud prefer a
general solution
riyaz|||I wrote this here just for you:
CREATE PROCEDURE usp_getboundAddresses
AS
BEGIN
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#SomeTable') IS NULL
CREATE TABLE #SomeTable
(
ShellOutput VARCHAR(500)
)
INSERT INTO #SomeTable
EXEC xp_cmdshell 'nbtstat -a .'
SELECT
SUBSTRING( ShellOutput,
CHARINDEX('[',ShellOutput)+1,
LEN(ShellOutput) - CHARINDEX(']',ShellOutput) -2
) AS BoundAddress
from #SomeTable
WHERE ShellOutput LIKE '%Node IpAddress%' --the important lines
AND ShellOutput NOT LIKE '%0.0.0.0%' --Unassigned addresses
DROP TABLE #SomeTable
END
Returning the network adresses of the server.
HTH, Jens Suessmeyer.