I am trying to optimize one query which runs in SSIS package.
I ran db tuning advisor as well, it recommended 2 non clustered indexes and 7 create statistics for that query.
Issue: it runs sometime fine and gives results in 1.25 mins.
sometimes it runs for hours and hours. else I will kill the process and again re-run.
I see when db log backup or differential runs, it goes to suspended status. at that time we kill the process and wait until the job
completes, and rerun.
I also verified that this query was created 3 yrs ago and I am not much that familiar with tuning queries.
I see the CPU utilization increases high and DiskIO also increases high when this SSIS package runs.
Can anyone desperately help me in tuning the query.
Please find the attached.
"Dont Judge a Book by its Cover"