How long should I let this query run.. ALTER TABLE CPR.DBO.tblLineTable ADD ID [int] IDENTITY(1,1) NOT NULL It has over 800,000 rows.. and I see the following task state = suspended command= alter table wait type = pageiolatch_sh.. Thank in advance! I'm wondering if I should just let let run, or if something is wrong.
The pageiolatch_sh is the server waiting for I believe a latch on a page in the buffer pool. Updating 800K rows with a new column very well could take some time depending on your disk IO subsystem and how your table is structured. If time is a concern, you could think about creating a new table with the new column and then import the data into the new structure, drop the old structure and then rename the new table to the old name. I would highly recommend that you test this first to see if it would be a faster alternative. Or you could just let it wait it out. Hope this helps!