question

aimyee avatar image
aimyee asked

adding column. taking over hour..

I have about 800,000 rows in a table and I need to add an identity column and it is taking over an hour. I end up cancelling the query because I'm not sure if something is wrong. ALTER TABLE PQT.DBO.TBLINFO ADD ID [int] IDENTITY(1,1) NOT NULL How long should this take? I also want to make this a primary key, but not sure if I should do that after I add the column or at the same time? Please advise. Thank in advance!
sql serveridentity
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aimyee avatar image aimyee commented ·
The wait type I believe said something with pageiolatch... I can't remember. I will look tonight again. Any suggestions for when I try again tonight? Thanks again!!
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Maybe you oculd also supply us with a few more details on the table (creation script and any indexes too).
0 Likes 0 ·
aimyee avatar image aimyee commented ·
53 1 DS25699\Lisa CPR SUSPENDED ALTER TABLE Microsoft SQL Server Management Studio - Query 182 PAGEIOLATCH_SH 7:1:162156 16 DS25699 internal This is what is taking to long.
0 Likes 0 ·
aimyee avatar image aimyee commented ·
What is PageIOLAtch_SH?
0 Likes 0 ·
aimyee avatar image aimyee commented ·
on another note... I added another column to the same table, just a simple int and it took about 3 seconds.. any suggestions?
0 Likes 0 ·
Show more comments

4 Answers

·
Kev Riley avatar image
Kev Riley answered
This could take a while. Alternatively this could take no time at all. On my very slow, extremely disk-bound desktop machine, it took 1.5 minutes to add this column to a 800,000 row table (albeit a very simple table with one integer column). You could be experiencing blocking - attempting to add a column will require an exclusive lock - is the system in use at the same time? Try running sp_who2, or the Activity Monitor in SSMS to see what your alter query is waiting on.
8 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
I concur on the blocking part - also don't forget that there could well be massive page splitting going on too. As @Kev Riley writes, take a look at what is going on while this command is running to get an idea of what is causing the delay.
6 Likes 6 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Good point on the page splits - not knowing the table schema I hadn't considered this
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
the majority of time on my PC was spent growing the log file..... so coupled with page splits, you could also see data file growth
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
very slow. Doesn't help that I'm running 7 SQL instances but only have 2Gb ram :( oh and 2 copies of VS open, SSMS, and forced to use IE. With the amount of paging going on, I'd probably be better with 0 ram and write straight to disk!
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
Don't want that dirty contractor breaking into their network!
1 Like 1 ·
Show more comments
Dave Morrison avatar image
Dave Morrison answered
Isn't tempDB used for this operation too? It's quite possible that tempDB is chugging. Might be worth looking at some articals for tidying up tempDB
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aimyee avatar image
aimyee answered
Thanks. So I did that last night when there where no other connections to the database. Just me when I looked at the activity monitor. This is our production server, so it concerns me. What can I look at to find the issue. Should I look into the tempDB first?
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
To prevent you running around looking at all sorts of possible reasons, it is best to get to the root cause - see what the query was waiting on - that will then point you in the right direction either blocking, tempdb i/o, log growth, etc.
2 Likes 2 ·
aimyee avatar image
aimyee answered
Thanks everyone! I just decided to let it run... Finally finished..
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.