ALTER TABLE

Question

Hi

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    

Script
=========

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))

BEGIN

SELECT TOP 1 @CreateSql = create_index_script, @ID=ID FROM #TEMP

EXEC SP_EXECUTESQL @CreateSql

DELETE FROM #temp WHERE ID = @ID

END

--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];

 

TIA

Answer 1

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.

http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

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

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Who says it its hung? You are just impatient! If this is a big table, and
there is limited resources on the machine, the operation may take some time.

---

Erland Sommarskog, SQL Server MVP, [e-mail]