One of the advantages of SQL Server Enterprise Edition is the ability to build indexes online. During an online index build, the table on which the index is being created will still be available for queries and data modifications. This feature can be extremely useful when a database needs to be accessed and maintenance windows are short to nonexistent.
A common myth with online index rebuilds is that they don’t cause any blocking. Of course, like many myths, this one is false. When using an online index operation, there is an intent shared lock held on the table for the main portion of the build. At the finish, either a shared lock, for a nonclustered index, or a schema modification lock, for a clustered index, is held for a short time while the operation moves in the updated index. This differs from an offline index build where the shared or schema modification lock is held for the duration of the index build.
Of course, you will want to see this in action; to accomplish this, you will create a table and use Extended Events to monitor the locks that are applied to the table while creating indexes with and without the ONLINE options. To start this demo, execute the code in Listing 6. This script creates the table dbo.MythThree and populates it with ten million records. The last item it returns is the object_id for the table, which is needed for the subsequent parts of the demo. For this example, the object_id for dbo.MythThree is 1319675749.
Note The demos for this myth all require SQL Server Enterprise or Developer Edition.
Listing 6. MythThree Table Create Script
USE AdventureWorks2014 GO CREATE TABLE dbo.MythThree ( RowID int NOT NULL,Column1 uniqueidentifier ); WITH L1(z) AS (SELECT 0 UNION ALL SELECT 0), L2(z) AS (SELECT 0 FROM L1 a CROSS JOIN L1 b), L3(z) AS (SELECT 0 FROM L2 a CROSS JOIN L2 b), L4(z) AS (SELECT 0 FROM L3 a CROSS JOIN L3 b), L5(z) AS (SELECT 0 FROM L4 a CROSS JOIN L4 b), L6(z) AS (SELECT TOP 10000000 0 FROM L5 a CROSS JOIN L5 b) INSERT INTO dbo.MythThree SELECT ROW_NUMBER() OVER (ORDER BY z) AS RowID, NEWID() FROM L6; GO SELECT OBJECT_ID('dbo.MythThree') GO
To monitor those events in this scenario, you’ll use Extended Events to capture the lock_acquired and lock_released events fired during index creation. Open sessions in SSMS for the code in Listing 7 and Listing 8. Use the session_id from Listing 8 for the session_id in Listing 7; for this scenario, the session_id is 42. After the Extended Events session is running, you can use the live view to monitor the locks as they occur.
Listing 7. Extended Events Session for Lock Acquired and Released
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'MythThreeXevents') DROP EVENT SESSION [MythThreeXevents] ON SERVER GO CREATE EVENT SESSION [MythThreeXevents] ON SERVER ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1) WHERE [sqlserver].[session_id]=(42) AND [object_id]=(1319675749)), ADD EVENT sqlserver.lock_released(WHERE [sqlserver].[session_id]=(42) AND [object_id]=(1319675749) ADD TARGET package0.ring_buffer GO ALTER EVENT SESSION [MythThreeXevents] ON SERVER STATE = START GO
In the example from Listing 8, creating the index with the ONLINE option causes the lock acquired and the released events shown in Figure 2. In the output, the SCH_S (Schema_Shared) lock is held from the beginning of the build to the end. The S (Shared) locks are held only for a few milliseconds at the beginning and ending of the index build. For the time between the S locks, the indexes are fully available and ready for use.
Listing 8. Online Index Operations on Nonclustered Index Creation
USE AdventureWorks2014 GO CREATE INDEX IX_MythThree_ONLINE ON MythThree (Column1) WITH (ONLINE = ON); GO CREATE INDEX IX_MythThree ON MythThree (Column1); GO
Figure 2. Index create with ONLINE option
By default, only the name and timestamp appear in the live viewer. The live viewer allows for customizing the columns that are displayed. In Figure 2, the columns object_it, mode, resource_type, and sql_text have been added to the defaults of name and timestamp. To add additional columns, right-click a column header and select “Choose columns”.
With the default index creation, which does not use the ONLINE option, S locks are held for the entirety of the index build. Shown in Figure 3, the S lock is taken before the SCH_S lock and isn’t released until after the index is build. The result is that the index is unavailable during the index build.
Figure 3. Index create without ONLINE option
For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)
Alternatively click through the link if you found this article interesting. (This will help the companies Search engine rankings)