June 11, 2006 at 5:48 pm
Hello everyone,
I am trying to add an identity column with a unique constraint to a table and it is taking forever. Granted I am adding it to a table that is about 400GB, but I have run it for up to 18 hours before having to kill it because the database cannot be down longer than that. The server is decent and should perform admirably (4 processor compaq with plenty of ram). I just can't understand what it is doing for all that time. In performance monitor it seems to be working, but not as hard as I think it should. The processors barely run, but I can see that the disk is being written to.
It may be that it just will take longer than that to complete, but is there any way I can track the progress of such a thing so that I can calculate when it will be done? Also, can anyone give me an idea of if there is any performance tuning that can be done to speed up the process?
Background info:
Server: 4 processor(2.8GHZ) Compaq proliant, 8 gb memory using AWE. Windows 2000 sp4, SQL Server 2000 Sp3a, EMC clarion disk. No performance related errors of any kind.
Database: Configured to simple recovery for this process, otherwise full recovery normally. data and log files set to unlimited growth, plenty of disk space for data files, logs, and tempdb.
Thanks in advance,
Ethan
June 13, 2006 at 1:56 am
It's most likely poor disk performance. Raid 5 by any chance?
run the perfmon counters to check i/o completion time, if this more than say 5 or 6 ms then this may be your problem. Check out disk idle time , if this is near 0% this indicates disk overload. ( with SAN's some of the disk counters don't display meaningful data - these two counters do )
I agree that you should be able to do this, however you may get better performance by creating a new table ( which essentially is what sql server is porobably doing ) and batching the data inputs. Try creating a new table and inserting the first few million rows , I assume it is a largish table at 400Gb, and see what time you get.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 13, 2006 at 5:37 am
I have found that, if you copy the schema, make the alteration, and then difference the two schemas with something like ApexSQL Diff, you will end up with a better create. I say that because I have seen EM drop and recreate an entire table when it could have performed two alters. Adding and removing of columns can be very quick, but changing the order will mean creating a whole new table and 2 copies of your data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply