May 13, 2008 at 6:16 am
Post Type: Question
My Experience: 1 year full time asp/sql server 00/05 developer
SQL Version: 9.0.3042
OS Version: 2003 R2 Sp2 (Dell PE2850) 4GB RAM (as recognised by windows)
Problem:
Hello all, Our database hosted as detailed above is having a few performance issues, after addressing a few index problems and normalisation problems performance has increased slightly. However one thing we have noticed is SQL server is only eating 1.7GB. Baring in mind some tables have upwards of 5,000,000 rows, some queires are BASHING the page file to death. Hard Disks are reading and writing their nuts of however SQL server STILL only eats 1.7GB of ram. Now i have enabled AWE and set the Max server memory in MB to 3500 with no effect. I have seen random things like adding /3 into boot files etc, but before i go restarting and editing the boot files is there anythying more obvious that i am missing?
Any tips would be helpfull 😀
Best Regards
Jordon.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 13, 2008 at 10:53 am
Jordan,
Are you running 32-bit or 64-bit?
You mentioned that the server recognizes 4GB of RAM -- What does it actually have? Does it really have more than 4GB of RAM available?
Run sp_configure and look at the "awe enabled" row. What is the run_value?
May 13, 2008 at 10:58 am
hello there, its a 32bit system, with 4GB installed, windows has detected the full 4 GB in system information etc.
sp_configure returns
minimum: 0
maximum: 1
config_value: 1
run_value: 0
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 13, 2008 at 11:24 am
jordonpilling (5/13/2008)
hello there, its a 32bit system, with 4GB installed, windows has detected the full 4 GB in system information etc.sp_configure returns
minimum: 0
maximum: 1
config_value: 1
run_value: 0
The run_value of 0 means that AWE is actually not enable. Here are possible causes (these are both necessary)...
1. The account that runs the SQL Server 2005 Database Engine has not been assigned the Lock Pages in Memory option
2. sql server service was not re-booted
3.The Maximize data throughput for network application option of Network Connection in Control Panel is not selected.
http://msdn.microsoft.com/en-us/library/ms179301.aspx"> http://msdn.microsoft.com/en-us/library/ms179301.aspx
"SQL Server supports Address Windowing Extensions (AWE) allowing use of physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems." http://msdn.microsoft.com/en-us/library/ms187499.aspx
I don't believe you actually need to enable AWE if you only have 4GB of RAM. I believe you want to look at the /3GB switch..
http://msdn.microsoft.com/en-us/library/ms178067.aspx
"The 32-bit operating systems such as Windows 2000 and Windows Server 2003 provide access to 4-gigabyte (GB) of virtual address space. The lower 2 GB of virtual memory is private per process and available for application use. The upper 2 GB is reserved for operating system use. All operating system editions, starting with Microsoft Windows XP Professional and later, including Windows Server 2003, include a boot.ini switch that can provide applications with access to 3 GB of virtual memory, limiting the operating system to 1 GB. See your Windows documentation for more information on using the /3GB switch memory configuration."
Hope this helps.
May 13, 2008 at 12:43 pm
Are both the operating system and SQL Server Enterprise Edition?
K. Brian Kelley
@kbriankelley
May 27, 2008 at 2:04 am
Hello everyone, sorry for the delay in my reply, just a quick note, i found the issue, it was as you suggested 'Lock pages is memory' permission.
I did already know about this and our Network Admin insisted he had sorted the permission via group policy, however after digging about I realised it was a local-permission rather than from the domain. I set the permission locally via admin tools, restarted the server and its working fine now.
K. Brian Kelley Wrote
Are both the operating system and SQL Server Enterprise Edition?
No, it was Standard Edition 32bit on a 32bit operating system.
Thanks again.
Regards
Jordon.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply