160G DB Preformance issues

[jeffmoore64]

Hi,
I have a several databases that total 738 GB. This is sitting on a box that has 8 proccessors and 8 gig of ram. We are having performance issues with the box. What would help our performance on this box??\
TIA
Jeff

 

[SQLDenis]

First look at RAM and CPU utilization which one is maxed out?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jeffmoore64]

The odd thing is that the VM on the task manager shows only 128MB used and the sqlsrver process is only uning 117mb of memory. This seems VERY low .. we have 8 gb of ram and plenty of HD space ... Can you describe some performance monitor indicators that I could watch
TIA
JEFF

 

[MDXer]

What is the OS? is the Server OS configured to utilize the amount of RAM on board for Example 2000 Advanced server will use 8GB but ou must go in and enable the PAE and 3GB switches as startup params. How much RAM do you have dedicated to SQL? If SQL is the only thing running then cofigure it's mim memory to be between 6 and 7GB.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jeffmoore64]

SQL SVR 2000 ru8nning on srv 2003 box..
the PAE and 3gb switches are set for startup ...
I just increased the vm from 8gb to 16gb ... it was recomending about 12 gb.
preformance monitor indicates that sqlserver is using about 6 gb mem.

 

[mrdenny]

First keep in mind that SQL Server won't use VM ever.

Task manager won't show SQL using the actual amount of RAM that it's using. It's a known issue that it shows a low amount (somewhere around 100 Megs) when it's actually using several gigs of RAM.

How much RAM is SQL configured to use as the Max? How much RAM does the Task Manager or perfmon show is actually in use total?

How are your physical disks laid out (including controllers and controller channels)?
What % are your CPUs running at?
What are you min and max memory settings for SQL?
Do you have queries that aren't using indexes?
Does performance improve after a reboot?
Did you configure SQL for AWE via sp_configure?
How are your data files laid out on the disks?
Are you seeing disk contention at the logical or physical level?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hmckillop]

I just run into similiar performance issues with a very similiar spec of machine.
Our issue was the SAN configuration, and that only one datafile existed for the database.

You can try running a series of tests, things such as SQLIO test or IOSTRESSTest etc are good for testing disc operations.
But it would be wise to follow through MrDenny's list as this pretty much lists the tasks (though I wouldnt necessarily agree with rebooting the box.)

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mrdenny]

I didn't recommend rebooting the box, I was only asking if performance had been corrected after a reboot. That can lead to a memory leak somewhere or tempdb performance issues as both are cleared out with a reboot/restart.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hmckillop]

Very true, I should read things more closely!


"I'm living so far beyond my income that we may almost be said to be living apart