SQL 2000 server - Mem Usage

[micang]

SQL 2000 P4 3.4GHz, 1 gig ram - Win XP SP2

Hi All,

Hope someone could shed some light. I have SQL server 2000 installed, after running a few queries or even just leaving it dormant (unused) for a while (circa an hour or so), the mem usage of sqlservr.exe in task manager is around 800,000 K

At this point queries cannot be run as they do not finish and the whole pc is slow. I have to reboot the pc to get the performance back.

Could anyone maybe point me in the correct direction of why this is happening?

Many thanks in advance.

Michael

 

[onpnt]

Under the properties of the server --> memory tab what settings do you have?

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[micang]

Hi onpnt,

I am not sure where to find that info?

I have seen in task manager, under performance, the PF usage is 1.15 GB.

Thanks for you help so far.

Michael

 

[ptheriault]

Open Enterprise Manager on the Server
Connect to the local SQL instance.
Right click on the server name and select properties.
Select the memory tab.

What is selected?

If it is set to dynamically configure you'll want to change it to use a fixes ammount. SQL 2000 requires at least 512 MB. Is this just a test PC/Server? I hope so because you're going to get really poor performance with only 512MB assigned to SQL. But, if you only have 1 GB of physical memory you have no choice.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

It is selected to dynamically configure.

The PC/Server is used only to run SQL and nothing else. Could I therefore increase the fixed amount?

Many thanks for your input.

Michael

 

[ptheriault]

How much total memory is in the server? If you set the fixed amount try and leave 200 - 300 MB for the OS.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

The total memory is 1 gig.

Would setting it to fixed solve the problem of it slowing down so drastically after running a few queries?

The server is basically being used to cleanse data and query data from Access and Query Analyser. It's not used as a web server either.

Many thanks

Michael

 

[ptheriault]

I would set configure the memory to a fixed amount of 750 MB. I won't be the fastest server in the world but it should help clear up your problem.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Many thanks Paul, will give that a go.

Michael