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.