SQL Server & memory

Question

SQL Server & memory

ui ranejeb

SSC Enthusiast

Points: 148
More actions
February 28, 2007 at 1:16 pm

#175285

I have a 4G Ram computer with SQL Server 2000, Standard Edition.
Although, memory used by all application is around 2G, some counters suggests the memory is not well managed:
- SQL:BufferManager:FreePages sits down 500 pretty often.
- SQL Server : BufferManageage reads/sec goes over 200 constantly, it reaches 1000 from time to time.
- Memoryages/sec goes over 50 constantly.
- PhysicalDisk: %Disk Time goes over 55% constantly; it reaches 600-700% oftenly.
The same server is used for:
-few databases “small enough” 1-2G each
-file server
-printing some stuff
I’m suspecting file server is the one which harms the computer, but I don’t know how to get this proof.
What should I do next? Any idea is welcomed.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ewan Hampson SSCrazy Points: 2780 More actions · Answer 1

You don't say what OS, which is relevant to the amount of RAM the computer can use. But whichever OS it has, SQL Server 2000 Standard will only ever be able to use 2GB. I'd start by optimising memory usage as far as the OS will allow it.

This might help: http://support.microsoft.com/kb/274750

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

The /3GB switch in boot.ini will allow SQL Server 2000 Standard to use 3GB RAM on a 4GB server. This reduces the amount of RAM available for the operating system and may increase paging activity, but it is a good idea for a dedicated SQL Server.

Anything on the server that competes with SQL Server I/O is very detrimental to performance. Hopefully you can at least put the SQL Server files on separate drives. It's better if you can put SQL Server data files on separate drives from SQL Server log files (and both separate from OS, paging file, file shares, etc). It's even better if you can find yet another drive for the tempdb data file.

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 3

It depends of the number of user connecting to the server. Every open document keep an open connection (network), saving and reading large document keep the disk busy.

Printing cause last on memory and on network.

If possible move db's to another server.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

most of the counters you mention I'd probably say were fine, a free page is an unused page and although it's good to have them why concern yourself that most are being used?

disk time is interesting, as it reads over 100% it's not really very useful is it? Try using disk idle time and deduct it from 100%. I always use disk io completion times as this is the easiest method to tell if you disk performance is bad.

Other than that why do you think your server is performing badly ? Is it because you've read some perfmon counters or users are complaining? If it ain't broke don't fix it!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

ui ranejeb SSC Enthusiast Points: 148 More actions · Answer 5

Here more info about other counters.

http://m1.freeshare.us/view/?128fs3650028.gif

Any idea is really appreciated. Thanks

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 6

Colin is right. Check what about users are complain. When instead of graphs I suggest to check the report. Sometimes graphs and reports are not the same.

If users complain about poor file server performance, start the perfmon (from another pc or server) at that time and have a look to disk usage, network connections, memory and cpu. If you have a poor performance, probably is going to be a chain of more causes (for instance file server blocks db).

Ewan Hampson SSCrazy Points: 2780 More actions · Answer 7

"The /3GB switch in boot.ini will allow SQL Server 2000 Standard to use 3GB RAM on a 4GB server."

All the references I can find say that > 2GB support is available only to Enterprise and Developer editions. Can anyone confirm that they have 2000 Standard edition using over 2GB?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

I'm not in a position to check this one out. When sql server was on w2k I'm sure I tried this and it didn't work. As far as I can see with sql 2005 std edition now follows the o/s memory limits.

I don't think this is so for sql 2000 however.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

I apologize for the error, the /3GB switch does nothing for SQL 2000 Standard, only Enterprise or Developer. The only system I used it on happened to be SQL 2000 Enterprise, but it was basically the same type of server as all our other SQL 2000 Standard servers and I had forgotten that it was a different software edition.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 10

it can be a bit of a minefield with the different o/s versions and sql versions and sometimes sp's changing things too. I did note that somewhere in amongst the w2k3 releases sql2k ent can now use 64gb ram on w2k3 ent ( R2 ) I think , that was a change that went through very quietly !! and 64bit works differently - ah well it keeps us in work I guess.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/