Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2010-01-28 : 04:45:01
|
| 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 helpP |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-28 : 06:22:21
|
| Things to check1) 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-01-28 : 06:58:56
|
| Thanks Charlie for the tipsI 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 againPaul |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 07:06:38
|
| 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 ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 07:19:06
|
| "It's very easy to write very badly performing queries"Aint that the unfortunate truth?! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 07:27:25
|
"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!!) |
|
|
|
|
|
|
|
|
SQL Server grinding to a hault