What are the consequences of using the table lock option with a fast load destination?

Question

I'm trying to speed up a fact table load as part of an overall performance project. The table is just about 120 million rows about 100k are added each evening. The table is pretty heavily indexed.

Currently I'm using an SSIS Fast Load OLE DB destination and loading the 100,000 rows takes about 15 minutes. This seemed really high to me to insert 100k rows, so I altered the package to dump it's results into a staging table, then did an T-SQL insert into the fact table from that staging table. The insert now runs in less than 1 minute.

I found it quite odd that a plain old T-SQL insert would be faster than SSIS Fast Load, so I started looking at which boxes were checked on the OLEDB destination. It turns out Table Lock was NOT checked. When I checked this option, the SSIS load is now under 1 minute. My questions are:

• What are the implications of leaving Table Lock checked?
• Does the T-SQL insert statement issue a table lock by default and that's why it was initially faster?

Answer 1

Well, I think the explanation is straightforward (see a more detailed reference here):

For your first question:

Table Lock – By default this setting is checked and the recommendation is to let it be checked unless the same table is being used by some other process at same time. It specifies a table lock will be acquired on the destination table instead of acquiring multiple row level locks, which could turn into lock escalation problems.

As for the insert statement, considering the rather large number of rows that need to be inserted, then SQL Server will most likely choose to make a table lock.

To confirm this you can check what kind of locks are held on the table by using the sys.dm_tran_locks DMV. Here are few good samples on how to interpret the results (and also good reading on lock escalation): http://aboutsqlserver.com/2012/01/11/locking-in-microsoft-sql-server-part-12-lock-escalation/.