[SQL] Online Index Rebuild while you r doing SELECT

This address of MSDN descript detail theory. HERE. And I did a test on it

MSDN said  when the concurrent user do a “DML” option, the table will be locked at “Preparation” and “Final” phase term
Source Structure Activities:

Phase	Source activity	Source locks
Preparation Very short phase	System metadata preparation to create the new empty index structure. A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency. Concurrent user write operations on the source are blocked for a very short period. No concurrent DDL operations are allowed except creating multiple nonclustered indexes.	S (Shared) on the table* IS (Intent Shared) INDEX_BUILD_INTERNAL_RESOURCE**
Build Main phase	The data is scanned, sorted, merged, and inserted into the target in bulk load operations. Concurrent user select, insert, update, and delete operations are applied to both the preexisting indexes and any new indexes being built.	IS INDEX_BUILD_INTERNAL_RESOURCE**
Final Very short phase	All uncommitted update transactions must complete before this phase starts. Depending on the acquired lock, all new user read or write transactions are blocked for a very short period until this phase is completed. System metadata is updated to replace the source with the target. The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.	INDEX_BUILD_INTERNAL_RESOURCE** S on the table if creating a nonclustered index.* SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

SO, Let’s do a test
 
--Before build a “Clustered INDEX”
Table: [AdventureWorks2012].[Sales].[SalesOrderDetail]

SELECT [SalesOrderID]

      ,[SalesOrderDetailID]

      ,[CarrierTrackingNumber]

      ,[OrderQty]

      ,[ProductID]

      ,[SpecialOfferID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

      ,[LineTotal]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderID =43661

And the time result





--Gonna rebuild Clustered Index and put option is “Online”

Alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

on sales.SalesOrderDetail

REBUILD WITH (ONLINE = ON);

GO

SELECT [SalesOrderID]      ,[SalesOrderDetailID]

      ,[CarrierTrackingNumber]      ,[OrderQty]

      ,[ProductID]      ,[SpecialOfferID]

      ,[UnitPrice]      ,[UnitPriceDiscount]

      ,[LineTotal]      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderID =43661

And the time result



See the difference?? WHY? Because just like the MSDN said.

“No concurrent DDL operations are allowed except creating multiple nonclustered indexes.”

And if you look at the “Execution Plan”, the SQL engine will do this part below



“New empty INDEX created” and “Source Drop” then “Meta data updated”