Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    1

    Unanswered: Performance Problem - Request for advice

    I am a DBA working for a large business in the North East of England.

    One of our main Database servers (SQL Server 2000 SP4) is configured as follows:

    Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
    - 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.

    Both 1 and 2 are on the same controller (SCSI).

    - 3. System Drive (C

    Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.

    Memory - 8GB of memory 6.5GB allocated to SQL Server
    Operating System: Windows server 2003 Enterprise Edition
    SQL Server: 2000 SP4


    We would appreciate your advice regarding the following problem that we have on busy times:

    Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
    Typical number of active processes can vary between 5 to 30 (all sorts of applications)

    The problem we have is that on some busy days a combination of processes grinding the server to a halt.
    When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
    (some values maybe large but not beyond expectation).


    However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.

    We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.

    Thank you,

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.

    IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants

    BTW, the number of users/connections seems pretty low.

    My guess is that there is some background batch job kibking off during that time which is a pig.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2004
    Posts
    46
    Hows your tempdb during the time of extremely slow?

  4. #4
    Join Date
    May 2006
    Posts
    5
    Other simple stuff,

    Are your statistics upto date on tables?
    Is your disk storage fragmented?
    Any bad disk warnings from the raid control?

    The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.

    ..and the killer... any virus checking going on?
    I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.

    Is someone starting up a games server on the box?

    Cheers
    Phil

    ---
    Special one just for you:
    Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
    They have described this tactic as "E by Gum"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •