Tuesday, January 08, 2013 10:08 PM
I have an application which executes a script. The script iterates thru a temp table and create indexes.
While running the script, the application got hung. In the database i see the same spid with several alter statements.
What can cause same spid to show multiple times and cause hang ?
55 0 suspended RAC\grobin Server001 0 TestDB ALTER TABLE
55 1 suspended Server001 55 TestDB ALTER TABLE
55 2 runnable Server001 0 TestDB ALTER TABLE
55 3 suspended Server001 55 TestDB ALTER TABLE
55 5 suspended Server001 55 TestDB ALTER TABLE
55 6 suspended Server001 55 TestDB ALTER TABLE
55 7 suspended Server001 55 TestDB ALTER TABLE
55 8 suspended Server001 55 TestDB ALTER TABLE
DECLARE @TABLENAME NVARCHAR(200)
DECLARE @DropSql NVARCHAR(4000)
DECLARE @CreateSql NVARCHAR(4000)
DECLARE @ID INT
SET NOCOUNT ON
WHILE(EXISTS(SELECT TOP 1 TableName FROM #TEMP))
SELECT TOP 1 @CreateSql = create_index_script, @ID=ID FROM #TEMP
EXEC SP_EXECUTESQL @CreateSql
DELETE FROM #temp WHERE ID = @ID
--Sample Index create statment
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [PK_Visit] PRIMARY KEY CLUSTERED ([VisitId])
WITH (SORT_IN_TEMPDB = OFF,ONLINE = OFF) ON [PRIMARY];
Tuesday, January 08, 2013 10:33 PM
You asking two questions here right .
Why multiple entry for same spid in sys.process.. ?
its called parallelism.. think like multiple people doing the same job at the very same time to complete the job in short period of time. index creation is one of the operation sql server uses parallelism. Pls read this link.
What causing the application to hung
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
- Marked As Answer by zzabvxx Tuesday, January 08, 2013 10:52 PM
Tuesday, January 08, 2013 11:12 PM