November 26, 2003 at 7:35 am
Dear All,
I have a server with 16 gig of memory and am not sure if this is all avaialble to SQL Server - the box runs as a dedicated server and I have set AWE to 1. The boot.ini file has also been changed to add the /3GB /PAE flag. We are running SQL Server 2000 Enterprise on Windows Server 2003.
The thing that concerns me is the value for Max Server Memory, as detailed below:
max server memory (MB)4214748364721474836472147483647
shouldn't this be 16384 ?
Regards,
Steve
the rest of sp_configure is below if needed:
affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)021474836471502615026
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0111
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Steve Brett
cheers
dbgeezer
November 26, 2003 at 3:44 pm
It should be 16384.
Using AWE Memory
To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.
SQL Server Setup will automatically grant the MSSQLServer service account permission to use the Lock Page in Memory option. If you are starting an instance of SQL Server 2000 from the command prompt using sqlservr.exe, you must manually assign this permission to the interactive user's account using the Windows 2000 Group Policy utility (gpedit.msc), or SQL Server will be unable to use AWE memory when not running as a service.
To enable the Lock Page in Memory option
Windows
How to enable the Lock Page in Memory option (Windows)
Note This functionality is available only if you are running the Microsoft® Windows® 2000 operating system.
To enable the Lock Page in Memory option
On the Start menu, click Run, and then in the Open box, type gpedit.msc.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the Users Rights Assignment check box.
The policies will be displayed in the details pane.
In the details pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
November 27, 2003 at 4:49 am
okay,
i did the following
1. created a user to run sql server under and chnaged the start up service name. sql server is now running under the new account.
2. edited the policy and made sure that the new user had Lock Page in Memory
3. rebooted the server
all went ok BUT sp_configure still gives me the wrong value (as shown at end of post).
also, sql server seems to refuse to use more than 200 meg of memory and hovers around 185 meg. on the old box (sql server 2000 on windows 2000) sql server used around 750 meg out of the 768 meg in the box.
an ideas ?
thanks
Steve
sp_configure details:
affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)021474836471502615026
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0111
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Steve Brett
cheers
dbgeezer
November 27, 2003 at 6:20 am
just done the perfmon thing described in another thread and the max server memory is reported as 15.8 gig ...
we are,however, getting a few reports saying the server is slower than before ....
Steve
Steve Brett
cheers
dbgeezer
April 12, 2004 at 1:19 pm
Steve,
Did you get anywhere with this? I've seen the same thing at my shop but everyone I talk to tells me we're configured correctly. Thanks.
Terry
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply