March 18, 2016 at 4:21 am
Hi,
We have SQL ETL processes that are scheduled to run every 30 minutes in our OLAP environment that we have cubes as well.
The ETLS run just fine most times but sometimes end up taking 2 hours or more. We have captured longer running process, it is different each time. Are indices and stats are good.
What I have done so far to troubleshoot:
1) gather the long running processes, verified the execution plans and they seem fine.
2) verified the windows memory, cpu - as been good.
3) verified and put a monitor to see blocks -- none
4) verified the data to see if huge chunks come in at that time - confirmed it isnt
5) we have had slowness when no maintenance jobs are running on the server.
6) MAXDOP per m/s recommendation
The situation is pretty random! am a little lost.
Is there anything else i can look at?
Also, if i see the long running spid, and have its details like what is running, the i/o details, cpu, is there anything i can do to see if it is actually writing /reading or is it just hung?
March 18, 2016 at 4:31 am
sqllearner44 (3/18/2016)
Also, if i see the long running spid, and have its details like what is running, the i/o details, cpu, is there anything i can do to see if it is actually writing /reading or is it just hung?
Get sp_whoisactive off the internet (if you don't have it already) and execute it next time the process is running slowly.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply