Unanswered: SQL very slow after hard disk unavailable
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!!!
that gives me the idea, that at night the update don't have the same resource like me
over the day.
last saturday morning the network admin did some work on the NAS and updated
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?
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.
set q4bis_JHAK_JHAP_JHAGA_STARTDATUM.bran_pam =
q4bis_JHAK_JHAP_JHAGA_STARTDATUM as ft
,q4bis_KNA1_kunden as k
ft.inserent = k.kunnr
ft.bran_pam = ''
The last test took 8 Minutes.
The script is bringing branches(lines,trades) from customermaster to transactional data.