logiczne odczyty z globalnej tabeli temp, ale nie z tabeli temp na poziomie sesji

Rozważ następujące proste MCVE:

SET STATISTICS IO, TIME OFF;
USE tempdb;

IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'tempdb..##t1', N'U') IS NOT NULL DROP TABLE ##t1;
CREATE TABLE ##t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'dbo.s1', N'U') IS NOT NULL DROP TABLE dbo.s1;
CREATE TABLE dbo.s1 
(
    r int NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.s1 (r)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;
GO

Kiedy uruchamiam następujące wstawki, wstawianie do #t1nie pokazuje żadnych statystyk we / wy dla tabeli temp. Jednak wstawienie do ##t1 robi Pokaż statystyki I / O dla tabeli temp.

SET STATISTICS IO, TIME ON;
GO

INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;

Dane wyjściowe statystyk:

Czas analizy i kompilacji programu SQL Server: 
   Czas procesora = 0 ms, czas, który upłynął = 1 ms.
Tabela „s1”. Liczba skanów 1, logiczne odczyty 19, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

 Czasy wykonania programu SQL Server:
   Czas procesora = 16 ms, czas, który upłynął = 9 ms.

(Dotyczy 10000 wierszy)

INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;

Czas analizy i kompilacji programu SQL Server: 
   Czas procesora = 0 ms, czas, który upłynął = 1 ms.
Tabela „## t1”. Liczba skanów 0, logiczne odczyty 10016, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „s1”. Liczba skanów 1, logiczne odczyty 19, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

 Czasy wykonania programu SQL Server:
   Czas procesora = 47 ms, czas, który upłynął = 45 ms.

(Dotyczy 10000 wierszy)

Dlaczego w tabeli temp ## jest tak wiele odczytów, gdy tylko się do niej wstawiam?

Podczas korzystania z INSERT INTOglobalnych tabel tymczasowych nie jest używane minimalne rejestrowanie

Wstawianie miliona wierszy w globalnej tabeli temp za pomocą INSERT INTO

INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

Podczas działania SELECT * FROM fn_dblog(NULL, NULL)podczas wykonywania powyższego zapytania zwracane są ~ 1 mln wierszy.

Jedna LOP_INSERT_ROWoperacja dla każdego wiersza + inne dane dziennika.

---

Ta sama wstawka w lokalnej tabeli temp

INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

Zwracane tylko do 700 wierszy SELECT * FROM fn_dblog(NULL, NULL)

Minimalne logowanie

---

Wstawianie miliona wierszy w globalnej tabeli temp za pomocą SELECT INTO

SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

SELECT INTO globalna tabela temp. z 10 000 rekordów

SELECT s1.r
INTO ##t2
FROM dbo.s1;

Statystyki czasu i IO

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 10 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

---

Na podstawie tego postu możemy dodać, TABLOCKaby zainicjować minimalne logowanie do tabeli sterty

INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT   s1.r
FROM dbo.s1

Niski odczyt logiczny

Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000 rows affected)

---

Część odpowiedzi @PaulWhite na temat tego, jak osiągnąć minimalne logowanie do tabel tymczasowych

Nie. Lokalne tabele tymczasowe (#temp) są prywatne dla sesji tworzenia, więc wskazówka dotycząca blokady tabeli nie jest wymagana. Wskazówka dotycząca blokady tabeli byłaby wymagana dla globalnej tabeli tymczasowej (## temp) lub zwykłej tabeli (dbo.temp) utworzonej w tempdb, ponieważ dostęp do nich można uzyskać z wielu sesji.

Tworzenie regularnej tabeli w celu przetestowania:

CREATE TABLE dbo.bla
(
    r int NOT NULL 
);

Wypełnianie go rekordami 1 mln

INSERT INTO bla 
SELECT   top(1000000)s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

> 1M odczytów logicznych w tej tabeli

Table 's1'. Scan count 17, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bla'. Scan count 0, logical reads 1001607, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

---

Paula White'a odpowiedź wyjaśniający logicznym czyta zgłaszane na globalnej tabeli temp

Ogólnie odczyty logiczne są zgłaszane dla tabeli docelowej, gdy wstawka nie jest minimalnie rejestrowana.

Te logiczne odczyty związane są ze znalezieniem miejsca w istniejącej strukturze w celu dodania nowych wierszy. Minimalnie zalogowane wstawki używają mechanizmu ładowania zbiorczego, który przydziela całe nowe strony / zakresy (a więc nie musi czytać docelowej struktury w ten sam sposób).

---

Wniosek

Wniosek jest taki, że INSERT INTOnie jest w stanie korzystać z minimalnego rejestrowania, co powoduje rejestrowanie każdego wstawionego wiersza indywidualnie w pliku dziennika tempdb, gdy jest używany w połączeniu z globalną tabelą temp / tabelą normalną. Podczas gdy lokalna tabela temp / SELECT INTO/ INSERT INTO ... WITH(TABLOCK)może korzystać z minimalnego rejestrowania.