Max Amount of RAM(Memory) the SQL server 2000 can use?

Question

Max Amount of RAM(Memory) the SQL server 2000 can use?

THolley

SSCommitted

Points: 1506
More actions
January 12, 2010 at 12:43 pm

#96374

We have SQL Server 2000 SP4 running on Window 2003 Enterprise Edition SP2.
There is currently 16GB of RAM on the server.
Windows 2003 Enterprise Edition suports up to 32GB of ram.
I thought I found somewhere once that SQL Server 2000 can use up to the max that the server can hold.
We have plans to update to 2008 in the second half of this year, but our CPU is running very high in the afternoon hours.
Before I suggest to my boss we should get more memory, wanted to get other's input.
Will the SQL Server 2000 use the extra 16GB(aka 32GB) of RAM if added?

Viewing 15 posts - 1 through 15 (of 17 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

SQL server 2000 standard edition is limited to 2Gb, but I presume you have enterprise edition, in which case it can use the extra memory with /PAE in the boot.ini and SQL configured to use AWE. You will need to increase the max memory setting in SQL but leave enough for the OS. Also as you are going above 16GB remove the /3Gb switch from the boot.ini if you have it set.

That said what makes you think you have a memory bottleneck as opposed to CPU? Likelihood is you have some non-performant queries that need tuning.

---------------------------------------------------------------------

THolley SSCommitted Points: 1506 More actions · Answer 2

Yes, it is Enterprise SQLServer 2000 version.

I want the profiler and such and nothing is standing out that points to any one server.

Our DB is over 750GB at this point.

So memory is the cheaps and easies thing to try first.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Use task manager just to confirm it is SQL chewing up the CPU.

You already know CPU spikes in the afternoon, does that give any clues to a possible process causing it.

Run profiler during this time including CPU as a column. after you have saved the profiler trace you can sort by CPU.

Also do a select * from sysprocesses where spid > 50 order by CPU desc to get processes using most CPU. Take login time into account when viewing the results.

---------------------------------------------------------------------

THolley SSCommitted Points: 1506 More actions · Answer 4

SSCrazy, thanks for you quick replies.

I ran you suggested query, is the CPU time accumulative? Is that why you want to take into account the login time?

george sibbald SSC Guru Points: 104210 More actions · Answer 5

yes its cumulative. The last_batch time is also of interest as this is when the current process executing kicked off.

---------------------------------------------------------------------

THolley SSCommitted Points: 1506 More actions · Answer 6

These are the top 20 CPU usage numbers between 11am - 2pm today from the profiler.

The top 12 are the same query which I have tuned to the best of my ability. (it runs every 15 minutes)

356000

317754

307856

306637

306306

302320

294829

288306

276420

275719

231069

225223

100190

73594

62453

57438

40531

37672

36157

35719

george sibbald SSC Guru Points: 104210 More actions · Answer 7

At this point identify the exact query which consumes this CPU (if say you only have a stored proc execution at the moment). Check the health (ie fragmentation levels) of the tables(s) it works against and that the stats are up to date.

See if the index tuning advisor suggests any index changes.

If no go at that point, start a new thread and ask if any one has any advice, post the query, the query plan and table definitions.

If this is a third party app get the vendors looking at it.

SQL does love memory so it can't hurt to add more but there are no guarantees it will give you the results you hope for. seeing it is CPU usage that is high perhaps you should look to that hardware first.

---------------------------------------------------------------------

THolley SSCommitted Points: 1506 More actions · Answer 8

SSCrazy,

Thanks so much for your replies.

We have Memory on it's way.

I just remembered this point, we are adding lots of new webusers, the "User Connections" count went up from low 300 around 400. I noticed that when it gets close to the 400 user counts that when the CPU gets the highest.

So I'm hoping that the memory will put a patch on, so we can get to 2008.

Tim

- Win. SSCertifiable Points: 6790 More actions · Answer 9

Hi,

I also faced the same in the past of CPU 100%

Try this query to get the top most executed queries in you server. Monitor the same for a day and try to fix the issues by fine tuning the processes (procedures, queries what ever they are which chews your CPU)

_______________________________________________________________________________

select top 50

(total_logical_reads + total_logical_writes) as total_logical_io,

(total_logical_reads/execution_count) as avg_logical_reads,

(total_logical_writes/execution_count) as avg_logical_writes,

(total_physical_reads/execution_count) as avg_phys_reads,

substring(st.text, (qs.statement_start_offset/2)+1,

((case qs.statement_end_offset

when -1 then datalength(st.text)

else qs.statement_end_offset

end - qs.statement_start_offset)/2) + 1) as statement_text,

*

from

sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

order by

total_logical_io desc

________________________________________________________________________

Hope this helps you.

Cheers,

WIN

Cheers,
- Win.

" Have a great day "

THolley SSCommitted Points: 1506 More actions · Answer 10

Win,

Thanks for your reply, but it appears your solution is for a newer edition of SQL Server.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 11

Is the OS you are running now and are going to be running on the upgrade 32-bit or 64-bit? If it is 32-bit, then you will need the /PAE switch, otherwise you will see all the memory installed. Also, if you are running 32-bit SQL, you will need to enable AWE to use over 4GB of memory. If you are running 64-bit SQL, you will see and be able to use all available physical memory.

Joie Andrew
"Since 1982"

THolley SSCommitted Points: 1506 More actions · Answer 12

The OS is 32-bit(Windows 2003 Enterprise). /PAE switch is enable. AWE is enable.

The extra memory was installed last night, but it doesn't appear that the SQL Server is seeing the newly added memory.

We have a clustered environment. So we shut down the passive server, added memory re-started and then failed it over and did the same with the active server before switching it back.

The Active server is still only seeing 15Gb of memory, I wondering if we have to shut both servers down and restart them before they will see the newly added memory? Or once the SQL Server is under a heavy load will it start seeing it?

Tim

george sibbald SSC Guru Points: 104210 More actions · Answer 13

If you mean perfmon is showing SQL is using just 15GB then yes SQL won't use more memory unless it needs it.

check the settings you have for min and max memory are not limiting SQL to 15GB

---------------------------------------------------------------------

THolley SSCommitted Points: 1506 More actions · Answer 14

Thanks for you quick reply,

That was it, it is set to only use 15GB in the property windows.

If I change that will it effect anything? Or will it just start using more?