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.* |
--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”
No comments
Post a Comment