SQLTeam.com | Weblogs | Forums

Data Load into SQL Stutters


#1

Loading data (500 million rows) interactively using SSDT from source (Teradata) into (SQL2014) destination table.

Notice load process is not continuous since SPID in sp_whoisactive appears in short burst when INSERTING, then same SPID viewed via sp_who2 alternates between (SLEEPING, DORMANT, INSERTING).

Shouldn't the E2E process be continuous, no breaks, just continuous INSERT? Could it be SSDT loading data piece-meal/or the source doing it/or for some reason I have not encountered before sql server is loading the data in batches?

Any ideas of what I'm experiencing would be greatly appreciated.


#2

Using SQL Profiler I noticed multiple entries for the same load (INSERT) SPID with identical cpu, reads, writes and duration results. So the load process is loading data in batches (very small batches). Is this attributed to how SSDT loads data? Know there is a max number of records commit value but Profile states only 36 writes per row and we are loading a 500 million row table - so will be slow if what I'm reading is valid and only 36 writes per batch.


#3

What have you specified for Rows per batch and Maximum insert commit size in your OLEDB destination (assuming that's what you're using)