Minimal Logging

  • Comments posted to this topic are about the item Minimal Logging

  • 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

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

  • 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

  • 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

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

    Cheers,

    JohnA

    MCM: SQL2008

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • "(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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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/

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

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