In the previous example http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx, I described minimal logging while moving data from a source table into a heap. You may recall that it requires a X table lock to get minimal logging. Now, I will show you what happens when you move data from a source table into a btree. In order to get minimal logging with btree, the only requirements are
Here is one example:
Insert into empty BTREE without TABLOCK is minimally logged except for the rows inserted on the first page.
-- create the source table
create table t_source (c1 int, c2 int, c3 char (100), c4 char(1000))
declare @i int
select @i = 1
while (@i < 10000)
insert into t_source values (@i, @i+10000, 'indexkey', 'hello')
select @i= @i + 1
-- create a table with clustered index
create table t_ci (c1 int, c2 int, c3 char(100), c4 char(1000))
create clustered index ci on t_ci(c1)
-- minimally logged except for the first page worth of rows
-- Order BY is not necessary. You will only get minimal logging if
-- Optimizer chose to sort the rows before inserting.
-- LOCK: IX at table level
insert into t_ci select * from t_source order by c1
Here is the query plan. Please note that SQL Server chose a SORT operator
Here are logged records. You will notice, that first 7 are fully logged but after that it is minimally logged. When inserting rows into the btree, the page where the first row is to be inserted may have some rows so for this reason each insert is fully logged. Only once it is done with this page, a new page needs to be allocated, it is minimally logged
When I insert data into new range into a non-empty btree from the previous example, I get minimal logging except for the last page. Again, this works with/without TABLOCK
-- create a table with an non-overlapping range
create table t_newrange (c1 int, c2 int, c3 char(100), c4 char(1000))
select @i = 10001
while (@i < 20000)
insert into t_newrange values (@i, @i+10000, 'indexkey', 'hello')
-- minimally logged except for the rows inserted on to the last page.
-- Note, this inserting the data in a new data range.
-- LOCK: X lock on the table.
-- Same logging behavior with/without TABLOCK
insert into t_ci with (TABLOCK) select * from t_newrange order by c1
Here are the log records. You will notice that the first 2 inserts are fully logged. Again, there are fully logged because the two rows go to the existing page and from that point onwards, the rows go to new page and hence minimally logged
In fact, I can load data into this btree concurrently as follows
Now execute the following in two different sessions
-- connection 1
-- connection 2
insert into t_ci select * from t_newrange order by c1
Both of these inserts are minimally logged and they will insert the data in concurrently.
PingBack from http://msdnrss.thecoderblogs.com/2008/03/23/
So far we had discussed ( http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx
Given some of the "larger" features being introduced with Sql 2008 which are getting lots of
Katmai (Sql 2008) - Transaction Logging Enhancements
Based on the customer feedback, we have decided to make minimal logging functionality into a btree available
Do we need to ｈａｖｅ ａ SORT ｏｐｅｒａｔｉｏｎ ｉｎ ｔｈｅ ｐｌａｎ ｂｅｆｏｒｅ ｔｈｅ ｉｎｓｅｒｔ. What if the source table is already sorted based on a index, so if sql server read the index sequentially , the data will be already ordered. We still need to sort it?
Is the minimal logging using INSERT..SELECT available in all versions of SQL Server 2008 or did it get included in one of the Cumulative Updates.
We are on Microsoft SQL Server 2008 (SP1) - 10.0.2740.0 (X64), but it seems the minimal logging is not working for us.
We satisfy all the pre-requisites,
a) The table is a heap or an empty B-Tree,
b) TABLOCK is being used,
c) The database is in SIMPLE recovery model,
d) The target table is not used in replication.
There weren't any backups running at the time of testing.
I would really like to make use of this new feature in SQL Server 2008. Any ideas please.
Yusuf, did you use TF-610?