Sql-server – SSIS slow performance to insert short rows

bcpoledbsql server 2014sql-server-2012ssis-2012

(Edit: I changed the post after some more explicative tests)

I'm experiencing a lack of performance between a SSIS server (name: Server1) and MSSQL server (name: 7551) when my ETL inserts rows of short length. The same server is much faster with longer rows.

Following are the resulting Elapsed vs Row length, testing with 1 million rows from flat file to table, with three configurations:

  1. From Server1 to 7551: the configuration with bad issue;
  2. From Server1 to 8221: server 8221 is another MSSQL Server, used as comparison;
  3. From my laptop to 7551: I've used my laptop as source alternative;
  4. From my laptop to 8221: final test is from laptop to comparison Db;

performance for configurations

As you can see, the configurations #2 (red) and #4 (violet) has the same pattern, expected: longer the row, longer the elapsed.

Configurations #1 (blue) and #3 (green) maintain the same pattern only for longer rows, but show an offset for the shorter row length. Watching at the data, the offset is like 85-90 seconds for 64 bytes row and decrease to quite zero for 1170 bytes rows.

Note that #1 takes more to load 64 bytes rows than 2048 bytes rows (61.1MB in 91s vs 2GB in 54.8s).

The table is:

create table dbo.ALMG_TEST_LOAD (
  N bigint primary key, 
  A nvarchar (4000) not null
)
Go

Finally, the ETL loads data with a Data Flow Task from a Flat File to an OLEDb destination:

screenshot of ETL

Do you know why this could happen?

Note that:

  1. Destination databases are similar: 7751 is backup/restored from 8221. Difference is that 7751 is SQL2014 and 8221 is SQL2012;
  2. I'm the only one who is using 7751 and 8221 during tests. I've got same results in different days and hours;
  3. SSIS version is 2012;
  4. I've test with heap destination table or indexed destination table, and time profiles are the same;
  5. ETL are performed with OLEDb connection and data loads are bulk.

Thanks

P.S.
I cannot access directly to destination server and it's not easy to talk to server admin to gather all kind of informations. Apparently they are not able to fix this issue and I have to help as much as I can. So any kind of suggestion is welcome.

Best Answer

There are a lot of things that come into play in regards to the performance difference.

1) is hardware\Instance configuration (RAM, Disk IO speed, Disk Size, Block Size, SQL-server memory configurations, Other software and tasks running)

2) Business of the server, If their are concurrent operations that it your tasks is awaiting resource access to.

3) Query Optimization. Perhaps you are doing a full table scan on the 58 bytes but the 2MB is fully contained with in an index. also perhaps there is more repetitive data in the 2MB. If you have built the Select Statements in T-SQL copy them into SSMS and look at the execution plans.

Perhaps there are differences between 2012 and 2014 but I haven't seen huge performance differences in moving my packages to 2014 yet.

Related Question