June 1, 2009 at 10:12 am
I have a base table where in text file loads every day and once in a month data is exported from the base table to 10 real tables which is designed in SSIS pkgs which takes about 16hrs to load into all 10 tables, during this process 100% of CPU is utilized. I assume this is because my pkg pushes data into 10 tables at one time and i am planing to run them in sequence, like one table after another to improve performance, does it make any sense?
Does it also take too long if i load each table at one time versus all tables at once.
thanks
June 1, 2009 at 11:30 am
It really depends. It's possible you could do 10 at once, but are there relationships between these tables? That will certainly affect data loading. Maybe there's an issue with indexes or an incorrect clustered index on the tables. It's hard to say based on this information. Maybe you're experiencing contention, where the processes are forced to wait while other connections read from the tables.
Personally, I suggest monitoring the wait states of the server to see what's causing things to slow down, what the processes are waiting on. There's a great white paper from Microsoft on this at this link.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2009 at 12:35 pm
all of the tables are not related, they are completely independent.
June 1, 2009 at 1:00 pm
That answers some of the questions. You still need to know what's causing the slowdown in the first place. If one or more tables are maxing out the CPU on each individual load, it's not going to get better running more than one load simultaneously.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2009 at 1:19 pm
Do you mean to say running them simultaneously will take long time comapred running them one by one?
wait time on the total process is 30526656 CXPACKET and i see 20 sessions in the activity monitor for the same job, 10 under RUNNABLE and 10 under SUSPENDED.
June 2, 2009 at 5:50 am
It's possible it could take longer. It really depends on what the contention is. Did you look at the link on wait state monitoring? That should let you know what's going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply