[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

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.
0
craigdev
Asked:
craigdev
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Smart_ManCommented:
you probably want to make sure it is because of the SQL processes and not any other software like anti-virus or whatever. also , keep an eye on the hard disk access led. the red hard disk light. so you can figure out where and when the slow down occurs.

if you can eleminate the hard disk , any other programs/process then it is time to check for performance optimization for the server.

please write down your monitor notes and we are waiting for your reply

0
 
bradleys40Commented:
are you familiar with perfmon?
0
 
bradleys40Commented:
There are several reference sites relating to using perfmon to find performance issues but heres an easy one to get started on
http://www.computerperformance.co.uk/HealthCheck/GettingStarted.htm
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Faiga DiegelSr Database EngineerCommented:
Check also event logs.
0
 
Smart_ManCommented:
waiting for your reply
0
 
craigdevAuthor Commented:
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?
0
 
craigdevAuthor Commented:
...cant see the HDD led because I don't have physical access  to the server.
0
 
craigdevAuthor Commented:
... average disk queue length can spike up to 10000 (not sure what this means?)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now