logical reads on global temp table, but not on session-level temp table The Next CEO of Stack OverflowGet minimal logging when loading data into temporary tablesCheck existence with EXISTS outperform COUNT! … Not?Which of these queries is best for performance?SQL Server - Logical Reads lowered, Execution time remained the sameMulti-statement TVF vs Inline TVF PerformanceLogical reads different when accessing the same LOB dataOPTION (RECOMPILE) is Always Faster; Why?Does IMAGE column affect query performance even if it's not included in the query?Helpful nonclustered index improved the query but raised logical readsAggregation in Outer Apply vs Left Join vs Derived tableHigh processor utilization when running a stored procedure
Which acid/base does a strong base/acid react when added to a buffer solution?
Why do we say “un seul M” and not “une seule M” even though M is a “consonne”?
Find the majority element, which appears more than half the time
Does Germany produce more waste than the US?
Why does freezing point matter when picking cooler ice packs?
How do I keep Mac Emacs from trapping M-`?
Compilation of a 2d array and a 1d array
What does it mean 'exit 1' for a job status after rclone sync
Shortening a title without changing its meaning
What day is it again?
MT "will strike" & LXX "will watch carefully" (Gen 3:15)?
How can a day be of 24 hours?
Why did the Drakh emissary look so blurred in S04:E11 "Lines of Communication"?
How do I secure a TV wall mount?
Could a dragon use its wings to swim?
Is it okay to majorly distort historical facts while writing a fiction story?
Planeswalker Ability and Death Timing
What did the word "leisure" mean in late 18th Century usage?
Finitely generated matrix groups whose eigenvalues are all algebraic
Is it OK to decorate a log book cover?
Incomplete cube
Simplify trigonometric expression using trigonometric identities
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Strange use of "whether ... than ..." in official text
logical reads on global temp table, but not on session-level temp table
The Next CEO of Stack OverflowGet minimal logging when loading data into temporary tablesCheck existence with EXISTS outperform COUNT! … Not?Which of these queries is best for performance?SQL Server - Logical Reads lowered, Execution time remained the sameMulti-statement TVF vs Inline TVF PerformanceLogical reads different when accessing the same LOB dataOPTION (RECOMPILE) is Always Faster; Why?Does IMAGE column affect query performance even if it's not included in the query?Helpful nonclustered index improved the query but raised logical readsAggregation in Outer Apply vs Left Join vs Derived tableHigh processor utilization when running a stored procedure
Consider the following simple 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
When I run the following inserts, inserting into #t1
shows no stats I/O for the temp table. However, inserting into ##t1
does show stats I/O for the temp table.
SET STATISTICS IO, TIME ON;
GO
INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;
The stats output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 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 = 9 ms.
(10000 rows affected)
INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '##t1'. Scan count 0, logical reads 10016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 = 47 ms, elapsed time = 45 ms.
(10000 rows affected)
Why are there so many reads on the ##temp table when I'm only inserting into it?
sql-server sql-server-2016 temporary-tables
add a comment |
Consider the following simple 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
When I run the following inserts, inserting into #t1
shows no stats I/O for the temp table. However, inserting into ##t1
does show stats I/O for the temp table.
SET STATISTICS IO, TIME ON;
GO
INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;
The stats output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 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 = 9 ms.
(10000 rows affected)
INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '##t1'. Scan count 0, logical reads 10016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 = 47 ms, elapsed time = 45 ms.
(10000 rows affected)
Why are there so many reads on the ##temp table when I'm only inserting into it?
sql-server sql-server-2016 temporary-tables
add a comment |
Consider the following simple 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
When I run the following inserts, inserting into #t1
shows no stats I/O for the temp table. However, inserting into ##t1
does show stats I/O for the temp table.
SET STATISTICS IO, TIME ON;
GO
INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;
The stats output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 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 = 9 ms.
(10000 rows affected)
INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '##t1'. Scan count 0, logical reads 10016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 = 47 ms, elapsed time = 45 ms.
(10000 rows affected)
Why are there so many reads on the ##temp table when I'm only inserting into it?
sql-server sql-server-2016 temporary-tables
Consider the following simple 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
When I run the following inserts, inserting into #t1
shows no stats I/O for the temp table. However, inserting into ##t1
does show stats I/O for the temp table.
SET STATISTICS IO, TIME ON;
GO
INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;
The stats output:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 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 = 9 ms.
(10000 rows affected)
INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '##t1'. Scan count 0, logical reads 10016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 = 47 ms, elapsed time = 45 ms.
(10000 rows affected)
Why are there so many reads on the ##temp table when I'm only inserting into it?
sql-server sql-server-2016 temporary-tables
sql-server sql-server-2016 temporary-tables
asked 2 hours ago
Max VernonMax Vernon
52k13114230
52k13114230
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Minimal logging is not being used when using INSERT INTO
and global temp tables
Inserting one million rows in a global temp table by using INSERT INTO
INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
When running SELECT * FROM fn_dblog(NULL, NULL)
while the above query is executing, ~1M rows are returned.
One LOP_INSERT_ROW
operation for each row + other
log data.
The same insert on a local temp table
INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
Only going up to 700 rows returned by SELECT * FROM fn_dblog(NULL, NULL)
Minimal logging
Inserting one million rows in a global temp table by using SELECT INTO
SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
SELECT INTO
a global temp table with 10k records
SELECT s1.r
INTO ##t2
FROM dbo.s1;
Time and IO Statistics
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.
Based on this blogpost we can add TABLOCK
to initiate minimal logging on a heap table
INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT s1.r
FROM dbo.s1
Low logical reads
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)
Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables
No. Local temporary tables (#temp) are private to the creating
session, so a table lock hint is not required. A table lock hint would
be required for a global temporary table (##temp) or a regular table
(dbo.temp) created in tempdb, because these can be accessed from
multiple sessions.
Creating a regular table to test this:
CREATE TABLE dbo.bla
(
r int NOT NULL
);
Filling it up with 1M records
INSERT INTO bla
SELECT top(1000000)s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
>1M logical reads on this table
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.
Paul White's answer explaining the logical reads reported on the global temp table
Generally, logical reads are reported for the target table when the
insert is not minimally logged.
These logical reads are associated with finding a place in the
existing structure to add the new rows. Minimally-logged inserts use
the bulk-loading mechanism, which allocates whole new pages/extents
(and so does not need to read the target structure in the same way).
Conclusion
The conclusion being that the INSERT INTO
is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO
/ INSERT INTO ... WITH(TABLOCK)
is able to use minimal logging.
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
add a comment |
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233689%2flogical-reads-on-global-temp-table-but-not-on-session-level-temp-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Minimal logging is not being used when using INSERT INTO
and global temp tables
Inserting one million rows in a global temp table by using INSERT INTO
INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
When running SELECT * FROM fn_dblog(NULL, NULL)
while the above query is executing, ~1M rows are returned.
One LOP_INSERT_ROW
operation for each row + other
log data.
The same insert on a local temp table
INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
Only going up to 700 rows returned by SELECT * FROM fn_dblog(NULL, NULL)
Minimal logging
Inserting one million rows in a global temp table by using SELECT INTO
SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
SELECT INTO
a global temp table with 10k records
SELECT s1.r
INTO ##t2
FROM dbo.s1;
Time and IO Statistics
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.
Based on this blogpost we can add TABLOCK
to initiate minimal logging on a heap table
INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT s1.r
FROM dbo.s1
Low logical reads
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)
Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables
No. Local temporary tables (#temp) are private to the creating
session, so a table lock hint is not required. A table lock hint would
be required for a global temporary table (##temp) or a regular table
(dbo.temp) created in tempdb, because these can be accessed from
multiple sessions.
Creating a regular table to test this:
CREATE TABLE dbo.bla
(
r int NOT NULL
);
Filling it up with 1M records
INSERT INTO bla
SELECT top(1000000)s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
>1M logical reads on this table
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.
Paul White's answer explaining the logical reads reported on the global temp table
Generally, logical reads are reported for the target table when the
insert is not minimally logged.
These logical reads are associated with finding a place in the
existing structure to add the new rows. Minimally-logged inserts use
the bulk-loading mechanism, which allocates whole new pages/extents
(and so does not need to read the target structure in the same way).
Conclusion
The conclusion being that the INSERT INTO
is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO
/ INSERT INTO ... WITH(TABLOCK)
is able to use minimal logging.
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
add a comment |
Minimal logging is not being used when using INSERT INTO
and global temp tables
Inserting one million rows in a global temp table by using INSERT INTO
INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
When running SELECT * FROM fn_dblog(NULL, NULL)
while the above query is executing, ~1M rows are returned.
One LOP_INSERT_ROW
operation for each row + other
log data.
The same insert on a local temp table
INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
Only going up to 700 rows returned by SELECT * FROM fn_dblog(NULL, NULL)
Minimal logging
Inserting one million rows in a global temp table by using SELECT INTO
SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
SELECT INTO
a global temp table with 10k records
SELECT s1.r
INTO ##t2
FROM dbo.s1;
Time and IO Statistics
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.
Based on this blogpost we can add TABLOCK
to initiate minimal logging on a heap table
INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT s1.r
FROM dbo.s1
Low logical reads
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)
Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables
No. Local temporary tables (#temp) are private to the creating
session, so a table lock hint is not required. A table lock hint would
be required for a global temporary table (##temp) or a regular table
(dbo.temp) created in tempdb, because these can be accessed from
multiple sessions.
Creating a regular table to test this:
CREATE TABLE dbo.bla
(
r int NOT NULL
);
Filling it up with 1M records
INSERT INTO bla
SELECT top(1000000)s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
>1M logical reads on this table
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.
Paul White's answer explaining the logical reads reported on the global temp table
Generally, logical reads are reported for the target table when the
insert is not minimally logged.
These logical reads are associated with finding a place in the
existing structure to add the new rows. Minimally-logged inserts use
the bulk-loading mechanism, which allocates whole new pages/extents
(and so does not need to read the target structure in the same way).
Conclusion
The conclusion being that the INSERT INTO
is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO
/ INSERT INTO ... WITH(TABLOCK)
is able to use minimal logging.
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
add a comment |
Minimal logging is not being used when using INSERT INTO
and global temp tables
Inserting one million rows in a global temp table by using INSERT INTO
INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
When running SELECT * FROM fn_dblog(NULL, NULL)
while the above query is executing, ~1M rows are returned.
One LOP_INSERT_ROW
operation for each row + other
log data.
The same insert on a local temp table
INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
Only going up to 700 rows returned by SELECT * FROM fn_dblog(NULL, NULL)
Minimal logging
Inserting one million rows in a global temp table by using SELECT INTO
SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
SELECT INTO
a global temp table with 10k records
SELECT s1.r
INTO ##t2
FROM dbo.s1;
Time and IO Statistics
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.
Based on this blogpost we can add TABLOCK
to initiate minimal logging on a heap table
INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT s1.r
FROM dbo.s1
Low logical reads
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)
Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables
No. Local temporary tables (#temp) are private to the creating
session, so a table lock hint is not required. A table lock hint would
be required for a global temporary table (##temp) or a regular table
(dbo.temp) created in tempdb, because these can be accessed from
multiple sessions.
Creating a regular table to test this:
CREATE TABLE dbo.bla
(
r int NOT NULL
);
Filling it up with 1M records
INSERT INTO bla
SELECT top(1000000)s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
>1M logical reads on this table
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.
Paul White's answer explaining the logical reads reported on the global temp table
Generally, logical reads are reported for the target table when the
insert is not minimally logged.
These logical reads are associated with finding a place in the
existing structure to add the new rows. Minimally-logged inserts use
the bulk-loading mechanism, which allocates whole new pages/extents
(and so does not need to read the target structure in the same way).
Conclusion
The conclusion being that the INSERT INTO
is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO
/ INSERT INTO ... WITH(TABLOCK)
is able to use minimal logging.
Minimal logging is not being used when using INSERT INTO
and global temp tables
Inserting one million rows in a global temp table by using INSERT INTO
INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
When running SELECT * FROM fn_dblog(NULL, NULL)
while the above query is executing, ~1M rows are returned.
One LOP_INSERT_ROW
operation for each row + other
log data.
The same insert on a local temp table
INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
Only going up to 700 rows returned by SELECT * FROM fn_dblog(NULL, NULL)
Minimal logging
Inserting one million rows in a global temp table by using SELECT INTO
SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
SELECT INTO
a global temp table with 10k records
SELECT s1.r
INTO ##t2
FROM dbo.s1;
Time and IO Statistics
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.
Based on this blogpost we can add TABLOCK
to initiate minimal logging on a heap table
INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT s1.r
FROM dbo.s1
Low logical reads
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)
Part of an answer by @PaulWhite on how to achieve minimal logging on temporary tables
No. Local temporary tables (#temp) are private to the creating
session, so a table lock hint is not required. A table lock hint would
be required for a global temporary table (##temp) or a regular table
(dbo.temp) created in tempdb, because these can be accessed from
multiple sessions.
Creating a regular table to test this:
CREATE TABLE dbo.bla
(
r int NOT NULL
);
Filling it up with 1M records
INSERT INTO bla
SELECT top(1000000)s1.r
FROM dbo.s1
CROSS APPLY dbo.s1 S2;
>1M logical reads on this table
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.
Paul White's answer explaining the logical reads reported on the global temp table
Generally, logical reads are reported for the target table when the
insert is not minimally logged.
These logical reads are associated with finding a place in the
existing structure to add the new rows. Minimally-logged inserts use
the bulk-loading mechanism, which allocates whole new pages/extents
(and so does not need to read the target structure in the same way).
Conclusion
The conclusion being that the INSERT INTO
is not able to use minimal logging, resulting in logging every inserted row individually in the log file of tempdb when used in combination with a global temp table / normal table.
Whereas the local temp table/ SELECT INTO
/ INSERT INTO ... WITH(TABLOCK)
is able to use minimal logging.
edited 13 mins ago
answered 1 hour ago
Randi VertongenRandi Vertongen
4,191924
4,191924
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
add a comment |
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
@PaulWhite I did not know that that was the explanation for it! Thanks for the feedback, will definitely add that to the answer, thanks!
– Randi Vertongen
11 mins ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233689%2flogical-reads-on-global-temp-table-but-not-on-session-level-temp-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown