Post reply

SQL2000 slow response

  • 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?

     

  • May 2, 2006 at 9:13 am

    #635342

    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

  • May 2, 2006 at 9:22 am

    #635348

    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.

     

  • May 2, 2006 at 10:11 am

    #635361

    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

  • May 2, 2006 at 12:03 pm

    #635417

    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.

     

     

  • May 2, 2006 at 1:38 pm

    #635446

    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

  • May 3, 2006 at 2:12 am

    #635510

    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

  • May 3, 2006 at 5:00 am

    #635532

    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

  • May 3, 2006 at 5:15 am

    #635540

    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

  • May 3, 2006 at 6:03 am

    #635548

    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.

     

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

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