FIX: Create Nonclustered Index Blocks Selects from Table

• Microsoft SQL Server, version 6.5

SYMPTOMS

When creating a nonclustered index on a table with SQL Server 6.5, the indexing process will block all activity in the table, including selects, until the indexing is completed.

WORKAROUND

Generate nonclustered indexes in off-hours when users are not accessing the table.

MORE INFORMATION

When creating a nonclustered index, SQL Server takes an SH_TABLE lock on the underlying table to prevent changes to the data. This lock can be seen by looking at the output of the sp_lock stored procedure. SQL Server is also locking an internal data structure during the indexing process, which also prevents select operations from accessing the table. The selecting process may not be killed during this time, and will show as blocked with a waittype of 0x0012. The waittype can be seen by looking at the row in sysprocesses for the corresponding spid.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem does not occur in SQL Server 6.0, and has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.