3 gig switch

Question

3 gig switch

devereauxj

Hall of Fame

Points: 3263
More actions
May 16, 2008 at 7:17 am

#71272

I am running SQL Server 2000 Standard Edition sp 4 on a Windows 2003 Enterprise server with 8 gig ram. SQL Server is using 1.7gig Ram and 1.7 VM. With tracing and Profiler, I have determined SQL Server needs more memory.
I had the net work admin set the 3 gig switch, reboot, but SQL Server is not using more memory.
From what I have read, this is not an AWE situation.
Any one have hints/checks/things to look at, to find out why SQL Server is not allocating more memory? I have the memory set to 128m Min and 2.8g max in the properties.
Thanks,
Joseph

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Joseph

SQL Server 2000 Standard will only use 2GB whatever you do. That's the theoretical maximum - more often you see something like the 1.7GB you are witnessing.

John

devereauxj Hall of Fame Points: 3263 More actions · Answer 2

My understanding is that the /3GB switch is NOT the same as AWE.

AWE need SQL Enterprise.

/3GB is a Windows parameter to allow any 32big application the ability to consume UPTO /3gig memory instead of 2gig, IF you are running a valid version of Windows (which Windows 2003 enterprise is). Applications to not have to AWE enabled to extend pas 2 gig.

Like I said, not saying I am write, just my understanding at this point in time.

Joseph

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Joseph

You're right about what the /3GB switch does, but if your application can't use all the memory available to it then it won't. SQL 2000 Standard can only use 2GB.

John

devereauxj Hall of Fame Points: 3263 More actions · Answer 4

Does this hold for SQL Server 2005? Does it have to be Enteprise to take advantage of /3gb switch?

I am a new and only DBA at medium sized company. The have 4-5 production boxes running Std that ll are bumping into the 1.7/1.7 memory max and need more. Each box is running enterprise 2003, but sql 2000 std.

Managemnt is NOT going to like it when I tell them to get the performance the expect, the will have to spend another 240K - 300K for SQL Enterprise.

Thanks,

Joseph

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Joseph

No, the same limits don't apply to SQL 2005. Have a look on the Microsft website to see what the limits are. Have you checked that you have the correct indexes, that they're regularly rebuilt and your statistics are up to date? Also, you may want to review your code to check that it's optimal, if you are able to change it.

John

devereauxj Hall of Fame Points: 3263 More actions · Answer 6

I am in the fight the largest fire mode.

Right now, these are vendor applications. I have changed some of their code and added indexes and got my hand slapped (improved specific query performance by 80%). I am not limited by cpu and my I/0, when busy, is moderate. Each application will reach the 1.7/1.7 in a matter of an hour when end users start running.

I have profiles showing the queries with full scans and perfmon showing full scan activity. I am letting the vendor try to fix their own queries now, but none of them even knew that you can add hints to queries.

I will push managment and vendor's toward SQL Server 2005, and put a upgrade plan in for all servers. Yearly budgeting start in July.

Thank you for your help and advice.

Joseph