Minimal Logging

Question

Minimal Logging

harsha.majety

SSCommitted

Points: 1537
More actions
August 26, 2013 at 12:06 am

#395367

Comments posted to this topic are about the item Minimal Logging

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

TRUNCATE TABLE is not a minimally-logged operation. It's logged exactly the same way in all recovery models. It's fully logged, but very efficiently logged, including using the deferred-drop mechanism.

Minimal logging is a distinctly separate set of code paths in the Engine, that does things like setting the ML_MAP bits for the extents involved. That does not happen for TRUNCATE TABLE.

It's a common misconception that TRUNCATE is minimally-logged or non-logged.

And everything I say above also applies to DROP TABLE and DROP INDEX too.

I've explained this in several places online, including at http://sqlmag.com/blog/why-do-truncate-and-drop-table-complete-so-fast

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

harsha.majety SSCommitted Points: 1537 More actions · Answer 2

Thank you for clarifying Paul. Read your article on sqlmag.

sneumersky SSCertifiable Points: 7667 More actions · Answer 3

I beleive Microsoft refers to TRUNCATES as a metadata only operation. They LIST truncates in the "OTHER MINIMALLY LOGGED and METADATA ONLY operations" section of the document.

http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

Good article 🙂

Steven Neumersky, CBIP, MCITP

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

I know - it's very misleading because it doesn't actually do minimal logging for it.

If you watch the log records, it's a metadata-only operation to unhook the allocation unit and put it on the deferred-drop queue. Go back into the log a few seconds later and you'll see all the pages being deallocated. I demo'd it in my Pluralsight course on logging.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

SQLCharger SSCommitted Points: 1958 More actions · Answer 5

I'd also add the clarification that 'merge' is supported only when it comes to new extent allocations.

Cheers,

JohnA

MCM: SQL2008

kevaburg SSCoach Points: 18131 More actions · Answer 6

If I switch from FULL to the BULK-LOGGED recovery model, don't I lose what is currently in the transaction log?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

kevaburg (9/29/2013)
If I switch from FULL to the BULK-LOGGED recovery model, don't I lose what is currently in the transaction log?

I know this is a old post but the answer is "NO". It doesn't break the log chain at all. The ONLY thing that you need to be aware of is that if a minimally logged transaction (it only takes 1) is included in a particular log file backup, then you can't restore to a point in time within that log file backup. You must either stop the restore before that log file or use the whole log file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

I also state that the INSERT...SELECT minimal logging isn't worth a hoot. According to a source at MS, it does "double writing" in the MDF file (that would be just as bad or worse than writing to a log file) and it still blows the log file space out if it's not already allocated. I just completed some testing for another purpose where the MDFF and LDF files were both started at 100MB with a growth of 100MB and the heap table met all of the conditions. The DB is in the SIMPLE Recovery Model. Doing a given SELECT INTO of a nearly 300MB table cause the MDF to grow to 300MB (as expected) and the log file remained at 100MB (as expected).After resetting the database to it's starting point, the same datasource was used for INSERT... SELECT. First, that took 45 seconds instead of the 7.5 seconds the SELECT INTO did. Many MS articles have stated that this would happen because it was never designed performance improvement (Seriously???). It also cause the LDF to grow from 100MB to 1600MB, which MS also documented as a "safety", which is really a left over of reallocation (paraphrasing their words for simplicity).And yes... this occurred with the proper use of WITH (TABLOCK) and, even though it's not supposed to be needed for a heap, I tried it with TF-610. Same results.I've not tested INSERT...SELECT for minimal logging so I don't know if it causes the same non-minimal problems (or results if you rather not call documented functionality a problem) but, if it hasn't changed, it's a worthless feature to me. No performance improvement and unnecessary bloating of the log file don't sound like anything really useful. Yeah... it might help backups a bit in the Bulk Logged mode but that's overwhelmed by the problems this feature exudes.Oh... and upon further reading, some say that you can't do big stuff with it or will result in the problems I just spoke about. I'll try that latter, as well but REALLY? It can't handle a piddley 300MB transfer?Doesn't seem at all useful and, if it has similar problems in 2012 (just got there recently), I know I won't be using it there, either. SELECT INTO and some of the other methods are much more effective for the amount of data that I do end up loading. I'll gladly take the final log file hit of the transfer of data from staging tables to permanent tables compared to the hits INSERT...SELECT has even when I'm in a SIMPLE Recovery Model for staging databases.

UPDATE: SEE POST BELOW.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Heh... man, I'm glad I could find the post above so that I could make this update.

I couldn't, for the life of me, get minimal logging to work using INSERT/SELECT on an existing but empty table with a clustered index on it. The problem was, I was using a Tally function to generate the rows and, to control the number of rows, I was passing a variable to the Tally function, Finally remembering that a variable on something in the FROM or WHERE clause might cause a bad execution plan because the value of the variable isn't known at compile time, I added OPTION (RECOMPILE) to the table population query and, VOILA!, it finally worked as expect. Built a 7 million row table in under 3 minutes with the clustered index in-place on my laptop.

That little nuance isn't in BOL for minimal logging anywhere. To be on the safe side, I recommend using OPTION(RECOMPILE) all the time so that you don't have to remember to add it in in certain cases. The recompile time is trivial compared to the huge savings that minimal logging can give you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 10

"(3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged."

I need clarification of above statement. What "dependency" I can fix for minimal-logging? I have trace flag 610 On, and trying to load table with non-clustered indexes - it is performing full-logging.

Thanks

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

hello mahesh - Monday, October 30, 2017 7:44 PM
"(3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged."
I need clarification of above statement. What "dependency" I can fix for minimal-logging? I have trace flag 610 On, and trying to load table with non-clustered indexes - it is performing full-logging.
Thanks

Post your code and let's have a look. It should also include the CREATE TABLE and all related indexes, keys, constraints, etc for both the source and target tables.

Also, please verify that the database of the target table is NOT in the FULL Recovery Model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 12

Jeff Moden - Tuesday, October 31, 2017 8:27 AM
hello mahesh - Monday, October 30, 2017 7:44 PM
"(3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged."
I need clarification of above statement. What "dependency" I can fix for minimal-logging? I have trace flag 610 On, and trying to load table with non-clustered indexes - it is performing full-logging.
Thanks
Post your code and let's have a look. It should also include the CREATE TABLE and all related indexes, keys, constraints, etc for both the source and target tables.
Also, please verify that the database of the target table is NOT in the FULL Recovery Model.

I have bunch of cases, I just want to know if dependency rules documented anywhere..

Thanks

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

hello mahesh - Tuesday, October 31, 2017 8:34 AM
Jeff Moden - Tuesday, October 31, 2017 8:27 AM
hello mahesh - Monday, October 30, 2017 7:44 PM
"(3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged."
I need clarification of above statement. What "dependency" I can fix for minimal-logging? I have trace flag 610 On, and trying to load table with non-clustered indexes - it is performing full-logging.
Thanks
Post your code and let's have a look. It should also include the CREATE TABLE and all related indexes, keys, constraints, etc for both the source and target tables.
Also, please verify that the database of the target table is NOT in the FULL Recovery Model.
I have bunch of cases, I just want to know if dependency rules documented anywhere..
Thanks

Yes. Although written back in 2008, it's still the "bible" in this area. See the following white paper article.

https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

There is a fault in that document in the form of an omission. If you have variables in the code that's doing the INSERTs, you might need to use OPTION(RECOMPILE) so to "materialize" the contents of the variable so that SQL Server can figure out that it can, in fact, do minimal logging.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ti 93734 Grasshopper Points: 21 More actions · Answer 14

Like some previous posters I have trouble getting minimal logging to work. Any suggestions would be very much appreciated.

My case is INSERT INTO .. SELECT, where I am inserting into a table with a clustered index and data already in the table. I am getting full logging no matter what I do.

I am on SQL Server 2017 and both the source and destination databases use simple recovery model.

I do not use TF610 as I understand that this is automatic on SQL Server 2016/17. I did try it just to be sure. No luck.

The source and destination tables are identical and have identical indexes (a few fields renamed):

CREATE TABLE [dbo].[InsertDest](
[OriginID] [int] NULL,
[DestinationID] [int] NULL,
[Stuff] [numeric](38, 8) NULL,
[IsX] [int] NULL,
[RunID] [int] NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20190318-175354] ON [dbo].[InsertDest]
(
[RunID] ASC,
[OriginID] ASC,
[DestinationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE [dbo].[InsertSource](
[OriginID] [int] NULL,
[DestinationID] [int] NULL,
[Stuff] [numeric](38, 8) NULL,
[IsX] [int] NULL,
[RunID] [int] NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20190318-175354] ON [dbo].[InsertSource]
(
[RunID] ASC,
[OriginID] ASC,
[DestinationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The source table has 80372250 rows, which are completely evenly distributed across RunID with 8037225 row for each of the values 0..9.

There is a second InsertSource2 table, which is identical to InsertSource, but where the values of RunID are 10..19.

Everything is on my 4 core 16 GB SSD laptop.

First I do this:

truncate table [InsertDest]
GO

checkpoint
GO

Then I shrink the log file to 0 on the destination database.

Then:

INSERT INTO [dbo].[InsertDest] WITH (TABLOCK) ([OriginID],[DestinationID],[Stuff],[IsX],[RunID])
select [OriginID],[DestinationID],[Stuff],[IsX],[RunID]
from SourceDB.[dbo].[InsertSource]
order by [RunID] ASC, [OriginID] ASC, [DestinationID] ASC
OPTION (RECOMPILE)

Works great. Runs in less than a minute and only creates 30-40 MB logfile content.

Then this:

INSERT INTO [dbo].[InsertDest] WITH (TABLOCK) ([OriginID],[DestinationID],[Stuff],[IsX],[RunID])
select [OriginID],[DestinationID],[Stuff],[IsX],[RunID]
from SourceDB.[dbo].[InsertSource2]
order by [RunID] ASC, [OriginID] ASC, [DestinationID] ASC
OPTION (RECOMPILE)

Takes about 220 seconds and creates about 22 GB of logfile.

WHY?? It should be minimally logged according to this: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in-bulk-load-operations/