Why does SQL Server Trace Flag 715 behave differently from the TABLOCK query hint?

Question

It's my understanding, based on numerous sources (including this one: https://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster/ba-p/384910) that SQL Server Trace Flag 715 should be the equivalent to the TABLOCK query hint--with the difference being that TF715 can be set at the session and global levels whereas TABLOCK can only be used as a query hint.

My issue is that I've tested both on SQL Servers 2016 and 2019 Development Editions in simple recovery model and I'm getting different results between TABLOCK and TF715 regardless of which server I use. TABLOCK is working as expected and TF715 isn't. Specifically, TABLOCK tells SQL Server to take out an entire table lock for bulk operations and minimally log the operation.

Consider the following code. This inserts 10,000 rows into a heap.

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

Now if you run this next block of code, you can see it logged 10,000 rows.

SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

Ok, so now if we do it all over again using the TABLOCK hint, you can see it becomes a minimally logged operation (i.e., the fully logged row count = 0).

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test WITH (TABLOCK) (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--0 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

However, when I try to enable TF715 (and take out the TABLOCK hint), I still get 10,000 fully logged rows when I would expect a minimally logged operation.

--Enable TF715 on both a session and global level to cover all bases...
DBCC TRACEON(715)
GO
DBCC TRACEON(715, -1)
GO
DBCC TRACESTATUS
GO

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--10000 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

DBCC TRACEOFF(715)
GO
DBCC TRACEOFF(715, -1)
GO
DBCC TRACESTATUS
GO

What am I overlooking? I shouldn't need to restart SQL Server for enabling TFs through DBCC, correct...?

Thank you in advance.

Answer 1

An important quote from the referenced article by Parikshit Savjani (emphasis added):

TF 715 can be set at the instance-level (global) or session level which enables TABLOCK for bulk load operations into heap with no non-clustered indexes . When this trace flag is enabled, bulk load operations acquires bulk update (BU) locks by default when bulk copying data into a heap with no non-clustered index. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table. The behavior is similar to when the user explicitly specifies TABLOCK hint while performing bulk load or when the sp_tableoption ‘table lock on bulk load’ is on for a given table however enabling this TF makes this behavior default without making any query changes or database changes .

Trace flag 715 enables BU locks for bulk load methods that support BU locks when TABLOCK is specified.

All bulk load methods support BU locks except INSERT...SELECT.

Examples of other bulk load methods include:

• SSIS SQL Server and OLEDB data destinations
• bcp
• BULK INSERT
• SQLBulkCopy in ADO.NET
• IRowsetFastload in OLE DB and the SQL Server Native Client ODBC library

It might be helpful to think of TF715 as pretending 'table lock on bulk load' is always set:

Value	Description
table lock on bulk load	When disabled (the default), it causes the bulk load process on user-defined tables to obtain row locks. When enabled, it causes the bulk load processes on user-defined tables to obtain a bulk update lock.

TF 715 has practical effects similar to specifying TABLOCK in many cases, but it is not exactly the same. In particular, BU locks ensure the bulk load process(es) have exclusive access to the table.

The bulk import optimizations are a set of features. You can get some of the benefits but not others in different situations.

In your example, you're not getting BU locks because INSERT...SELECT does not support them. TF715 only enables BU locks for bulk load methods that support that option. TF715 therefore has no effect in your example.

When you specify TABLOCK, the engine has an acceptable guarantee that the bulk load has exclusive access to the table, so minimal logging is allowed.

---

The above discussion applies only to heap table destinations. Loading a clustered table can benefit from the same underlying mechanism (RowsetBulk), and also FastLoadContext, which does not require TABLOCK or a BU lock to enable minimal logging.