SQL2000 slow response

Question

SQL2000 slow response

Charlie-120533

SSCrazy

Points: 2187
More actions
May 2, 2006 at 9:05 am

#169249

Hello, Gurus. I need a little advise. We have a clustered environment with two active clusters. Recently, the delevopment side of the cluster began to slow down. I have observed two problems:
1. When trying to perform a restore to a database, it takes about one minute before the splash screed shows. After that, everything runs fine.
2. When trying to go to Current Activities, the server will 'hang' sometimes for more than 5 minutes. When it comes back, everything works quickly. But if I refresh, it will hang for another 5 minutes or more.
Has anyone experienced a similar situation?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Use sp_who from your query analyzer and check if any processes are getting block and see what process are creating any bottlenecks.

Prasad Bhogadi
www.inforaise.com

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 2

SP_WHO showed no blocks. Most users were either waiting or inserting. We have bounced the server which seemed to clear it up for a while, but now it's back again.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

with the restore it could be one of a few things. more than likely it's the I/O speed when trying to create the files (how big is your database?)

or perhaps the backup and restore history is very large - you may want to use the stored proc msdb.dbo.sp_delete_backuphistory '01-may-2006' (substitute date as required)

i'm guessing since it's a cluster that you have a SAN. how is the san configured?

MVDBA

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 4

SANs are a couple of years old - FC4700 I believe. About 10,000 disk speed, connected via fiber. Drives are 73 gig each in RAID 5 configuration with 5 drives per instance. LOG and DATA are in different RAID groups.

Both instances are configured identically.The production side works properly. This just started happening about one month ago. As mentioned before when we bounced the node, it was working okay for a few hours then reverted back to it's devilish ways.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

First I would start looking in the Windows event log searching for IO-errors (cards or disks) if you can perform a checkdisk,..

Then check if there are memory errors.

Then perform dbcc memoryusage (yes, this will also take a while).

Did someone drop any indexes on your dev-db's

did someone drop any clustering indexes

Is you load CPU / IO / Memory-based ?

Is your sqlserver pageing ? (huge performancehit !)

Did someone user dbcc pintable with a very big table, consuming all your available ram ?

(or sp_tableoption with the equivalent)

select *

from sysobjects O

where ObjectProperty(O.Id, 'TableIsPinned') = 1

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

you might be able to find a lot of this information (and more) by downloading sqldiagnostic manager from idera.com

there's a 28 day free trial and it's about £600 ($1000) per server if you want to carry on using it.

MVDBA

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

about the same goes for "Spotlight on SQLServer" from Quest software.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brian Munier SSCrazy Points: 2875 More actions · Answer 8

I would also check your database fragmentation and current statistics.

For getting the restore splash screen, there are other posts on using: msdb.dbo.sp_delete_backuphistory '01-may-2006' (substitute date as required)

Please note that there is a missing index in msdb which makes this operation run ridiculously slow, so index: msdb.dbo.backupset the column media_set_id with a non-clustered non-unique index, otherwise the SP can take hours or days to do a small delete.

Brian

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 9

Thanks, folks. Prasad Bhogadi nailed it. I overlooked a coulpe of blocks. I reran SP_WHO and discovered that I had a BizTalk application blocking itself. Am working on finding out why, but the server is responding properly now.

Thanks a lot to all who replyed.