SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL Server grinding to a hault
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
206 Posts

Posted - 01/28/2010 :  04:45:01  Show Profile  Reply with Quote
Hi there,

Recently our SQL Server started slowing down significanly and the jobs, which are many, were taking hours rather than minutes to run.
Now, we're getting timeout errors. We've checked the physical disk and it seems to be running normally.

On checking task manager we can see the bottleneck is definitely SQL Server as it's using far to much CPU etc.

I would like to have advice on where else to look, or if there's a systematic way of checking things to resolve this?

Many thanks for any help

P

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/28/2010 :  06:22:21  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Things to check

1) Recovery model -- are you using SIMPLE recovery model? If not are you raking regular log backups?

2) Blocking processes -- try running EXEC sp_Who2 to get an idea of how many processes are running / if there are blocking processes.

3) Index usage of your queries. If your database is growing and your queries are taking longer and longer then the queries / indices are probably not adequate / fragmented.

4) The queries themselves. It's very easy to write very badly performing queries that work fine with small datasets but take exponentially longer with increasing data sets. Check the highest cost queries and have a look at them. Could be they are just badly written.

There are tons of examples which give sql to expose top n longest running processes / highest cost processes etc.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 01/28/2010 06:25:34
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
206 Posts

Posted - 01/28/2010 :  06:58:56  Show Profile  Reply with Quote
Thanks Charlie for the tips

I recently looked at the error logs and event logs and found the error 17883. This seems to be a scheduling error caused by non-yeilding threads. On looking at the logs, it appears there was a number of instances where Autogrow of a file took place.

On researching the error, this kind of links with the problems we're having.

I'm going to keep your tips for future so thanks again

Paul
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/28/2010 :  07:06:38  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Monitor your database file sizes and grow them manually at a convenient time when they're getting close to full. Autogrow should be on just in case you miss something, it shouldn't be primarily responsible for growing the DB files

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/28/2010 :  07:19:06  Show Profile  Reply with Quote
"It's very easy to write very badly performing queries"

Aint that the unfortunate truth?!
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/28/2010 :  07:27:25  Show Profile  Reply with Quote
"On looking at the logs, it appears there was a number of instances where Autogrow of a file took place.
On researching the error, this kind of links with the problems we're having.
"

Hmmm ... I thought that was a thing-of-the-SQL2000-past, solved in SQL 2005 . Used to be dreadful, a busy SQL2000 server could "die" for 20 or 30 minutes whilst it was extending the files.

We fixed that (in SQL 2000) by changing the AutoGrow size to something fixed (rather than Percentage) and making it a realistic, but modest, amount.

I still recommend Fixed size auto-increments, and a modest size (enough for a few weeks, not a decade!!)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000