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.
LVL 1
craigdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.