craigdev
asked on
Intermittent performance problems
I have a sql server database that is about 10gb, some table are in excess of 10million records. The server has 12gb of ram. There are queries that use indexes and indexed views and most of the time the server hums along nicely.
Occasionally something will happen and the server will lock up for a period of time. I have analysed all the queries that are running on the server and evaluated the execution plans on each - they are fine and usually run fast. However, sometimes the server locks up and I am not sure how to figure out why.
Please assist.
Occasionally something will happen and the server will lock up for a period of time. I have analysed all the queries that are running on the server and evaluated the execution plans on each - they are fine and usually run fast. However, sometimes the server locks up and I am not sure how to figure out why.
Please assist.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
waiting for your reply
ASKER
Hi thanks for the comments.
There are 7 databases on the server all of which are around 10-20gb. There is a DEV database, and the others are regional LIVE databases. All the regional databases are identical schematically and this allowed us to partition the data for a future scale-out.
The HDD capacity is 280GB of which 250GB is full. 12GB of RAM - I understand it is a 32bit system with 8GB expansion.
Am I hitting the limits for this server? Have looked at perfmon the red line sits at around 25%. Concidentally the server has 4 processors, does this suggest one of the processors is doing all the work?
There are 7 databases on the server all of which are around 10-20gb. There is a DEV database, and the others are regional LIVE databases. All the regional databases are identical schematically and this allowed us to partition the data for a future scale-out.
The HDD capacity is 280GB of which 250GB is full. 12GB of RAM - I understand it is a 32bit system with 8GB expansion.
Am I hitting the limits for this server? Have looked at perfmon the red line sits at around 25%. Concidentally the server has 4 processors, does this suggest one of the processors is doing all the work?
ASKER
...cant see the HDD led because I don't have physical access to the server.
ASKER
... average disk queue length can spike up to 10000 (not sure what this means?)
http://www.computerperformance.co.uk/HealthCheck/GettingStarted.htm