Testuję minimalne wstawki rejestrowania w różnych scenariuszach i z tego, co przeczytałem INSERT INTO SELECT w stercie z indeksem nieklastrowanym za pomocą TABLOCK i SQL Server 2016+ powinien się minimalnie logować, jednak w moim przypadku robię to pełne logowanie. Moja baza danych jest w prostym modelu odzyskiwania i udało mi się uzyskać minimalnie zalogowane wstawki na stercie bez indeksów i TABLOCK.
Korzystam ze starej kopii zapasowej bazy danych przepełnienia stosu, aby przetestować i utworzyłem replikę tabeli Posty z następującym schematem ...
CREATE TABLE [dbo].[PostsDestination](
[Id] [int] NOT NULL,
[AcceptedAnswerId] [int] NULL,
[AnswerCount] [int] NULL,
[Body] [nvarchar](max) NOT NULL,
[ClosedDate] [datetime] NULL,
[CommentCount] [int] NULL,
[CommunityOwnedDate] [datetime] NULL,
[CreationDate] [datetime] NOT NULL,
[FavoriteCount] [int] NULL,
[LastActivityDate] [datetime] NOT NULL,
[LastEditDate] [datetime] NULL,
[LastEditorDisplayName] [nvarchar](40) NULL,
[LastEditorUserId] [int] NULL,
[OwnerUserId] [int] NULL,
[ParentId] [int] NULL,
[PostTypeId] [int] NOT NULL,
[Score] [int] NOT NULL,
[Tags] [nvarchar](150) NULL,
[Title] [nvarchar](250) NULL,
[ViewCount] [int] NOT NULL
)
CREATE NONCLUSTERED INDEX ndx_PostsDestination_Id ON PostsDestination(Id)Następnie próbuję skopiować tabelę postów do tej tabeli ...
INSERT INTO PostsDestination WITH(TABLOCK)
SELECT * FROM Posts ORDER BY Id Patrząc na fn_dblog i wykorzystanie pliku dziennika, widzę, że nie otrzymuję z tego minimalnego logowania. Czytałem, że wersje przed 2016 wymagają flagi śledzenia 610, aby minimalnie logować się do indeksowanych tabel, próbowałem też to ustawić, ale nadal nie mam radości.
Chyba coś tu brakuje?
EDYCJA - Więcej informacji
Aby dodać więcej informacji, korzystam z następującej procedury, którą napisałem, aby wykryć minimalne rejestrowanie, być może coś tu jest nie tak ...
/*
Example Usage...
EXEC sp_GetLogUseStats
@Sql = '
INSERT INTO PostsDestination
SELECT TOP 500000 * FROM Posts ORDER BY Id ',
@Schema = 'dbo',
@Table = 'PostsDestination',
@ClearData = 1
*/
CREATE PROCEDURE [dbo].[sp_GetLogUseStats]
(
@Sql NVARCHAR(400),
@Schema NVARCHAR(20),
@Table NVARCHAR(200),
@ClearData BIT = 0
)
AS
IF @ClearData = 1
BEGIN
TRUNCATE TABLE PostsDestination
END
/*Checkpoint to clear log (Assuming Simple/Bulk Recovery Model*/
CHECKPOINT
/*Snapshot of logsize before query*/
CREATE TABLE #BeforeLogUsed(
[Db] NVARCHAR(100),
LogSize NVARCHAR(30),
Used NVARCHAR(50),
Status INT
)
INSERT INTO #BeforeLogUsed
EXEC('DBCC SQLPERF(logspace)')
/*Run Query*/
EXECUTE sp_executesql @SQL
/*Snapshot of logsize after query*/
CREATE TABLE #AfterLLogUsed(
[Db] NVARCHAR(100),
LogSize NVARCHAR(30),
Used NVARCHAR(50),
Status INT
)
INSERT INTO #AfterLLogUsed
EXEC('DBCC SQLPERF(logspace)')
/*Return before and after log size*/
SELECT
CAST(#AfterLLogUsed.Used AS DECIMAL(12,4)) - CAST(#BeforeLogUsed.Used AS DECIMAL(12,4)) AS LogSpaceUsersByInsert
FROM
#BeforeLogUsed
LEFT JOIN #AfterLLogUsed ON #AfterLLogUsed.Db = #BeforeLogUsed.Db
WHERE
#BeforeLogUsed.Db = DB_NAME()
/*Get list of affected indexes from insert query*/
SELECT
@Schema + '.' + so.name + '.' + si.name AS IndexName
INTO
#IndexNames
FROM
sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
si.name IS NOT NULL
AND so.name = @Table
/*Insert Record For Heap*/
INSERT INTO #IndexNames VALUES(@Schema + '.' + @Table)
/*Get log recrod sizes for heap and/or any indexes*/
SELECT
AllocUnitName,
[operation],
AVG([log record length]) AvgLogLength,
SUM([log record length]) TotalLogLength,
COUNT(*) Count
INTO #LogBreakdown
FROM
fn_dblog(null, null) fn
INNER JOIN #IndexNames ON #IndexNames.IndexName = allocunitname
GROUP BY
[Operation], AllocUnitName
ORDER BY AllocUnitName, operation
SELECT * FROM #LogBreakdown
SELECT AllocUnitName, SUM(TotalLogLength) TotalLogRecordLength
FROM #LogBreakdown
GROUP BY AllocUnitNameWstawianie do sterty bez indeksów i TABLOCK przy użyciu następującego kodu ...
EXEC sp_GetLogUseStats
@Sql = '
INSERT INTO PostsDestination
SELECT * FROM Posts ORDER BY Id ',
@Schema = 'dbo',
@Table = 'PostsDestination',
@ClearData = 1Dostaję te wyniki
Przy 0,0024mb wzrostu pliku dziennika, bardzo małych rozmiarów rekordu dziennika i bardzo niewielu z nich cieszę się, że używa to minimalnego rejestrowania.
Jeśli następnie utworzę indeks nieklastrowany na id ...
CREATE INDEX ndx_PostsDestination_Id ON PostsDestination(Id)Następnie uruchom ponownie tę samą wkładkę ...
Nie tylko nie otrzymuję minimalnego logowania do indeksu nieklastrowanego, ale także zgubiłem go na stercie. Po wykonaniu kilku testów wydaje się, że jeśli utworzę klastrowany identyfikator, to loguje się on minimalnie, ale z tego, co przeczytałem, 2016+ powinien minimalnie logować się do sterty z indeksem nieklastrowanym, gdy używany jest tablock.
EDYCJA KOŃCOWA :
Zgłosiłem to zachowanie do Microsoft na SQL Server UserVoice i zaktualizuję, jeśli otrzymam odpowiedź. Napisałem również pełne szczegóły dotyczące minimalnych scenariuszy dziennika, których nie mogłem uruchomić na https://gavindraper.com/2018/05/29/SQL-Server-Minimal-Logging-Inserts/