Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: SQL very slow after hard disk unavailable

    Hi everybody,

    I got a very interesting issue here on the customer site.
    I'm fetching data from ORACLE into MS SQL 2005 and doing a complete ETL process
    up to some cubes.
    Since last saturday the system is very slow.
    Especially update commands
    (I'm bringing data from customer master into some transactional data)
    are running very long in the night.
    I narrowed it down to e.g. one update sql which costs me 4 and half hours
    when running in the automated batch over the night.

    The server runs a service reboot to give the RAM to the IIS over the day.

    When I'm running the (4 hour) update sql manually it costs me about 2 Minutes!!!

    Hmmmm,
    that gives me the idea, that at night the update don't have the same resource like me
    over the day.

    Nevertheless,
    last saturday morning the network admin did some work on the NAS and updated
    Fileserver Software.
    I found errors in the eventlog.

    I did a DBCC CHECKDB on the Staging DB in which all the updates take place.
    Without any errors.

    So without the long running update the batch finish in time (slow but acceptable)

    What do you think?
    Could an unavailable harddisk lead to that kind of behavior?

    Any ideas are welcome.

    best regards

    Mike

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is the process clashing with anything else at the time it is scheduled e.g. other jobs, backups, maintenance?

    Can you run the same process manually, are you running the job or are you copying the SQL in to a query window and executing it that way?

    Might be good to see your code too
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36
    Hi George,

    not that I know.
    I'm totally alone on that BI-server .

    Yes I'm testing the SQL manually via script.
    Every script part is saved as an atomic sql-script.
    The update goes like this.

    update
    q4bis_JHAK_JHAP_JHAGA_STARTDATUM
    set q4bis_JHAK_JHAP_JHAGA_STARTDATUM.bran_pam =
    k.bran1
    from
    q4bis_JHAK_JHAP_JHAGA_STARTDATUM as ft
    ,q4bis_KNA1_kunden as k
    where
    ft.inserent = k.kunnr
    and
    ft.bran_pam = ''

    The last test took 8 Minutes.

    The script is bringing branches(lines,trades) from customermaster to transactional data.

    Thanks for your ideas.

    Mike

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you test it by executing the same job that is scheduled manually?
    See if there's a difference.
    George
    Home | Blog

Posting Permissions

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