Microsoft KB Archive/156265

= FIX: Create Nonclustered Index Blocks Selects from Table =

Article ID: 156265

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q156265



BUG #: 15618 (6.50)



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.

Additional query words: create nonclustered index block

Keywords: kbbug kbfix kbnetwork KB156265

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.