| Author |
Topic |
|
tkizer
Almighty SQL Goddess
38200 Posts |
 Posted - 2003-06-03 : 19:11:56
|
| Tomorrow night during an application upgrade, we are going to drop a column that is defined as VARCHAR(3000). This column is no longer needed in the next version of the application. The table is currently over 5GB with most of the data being in this column.Any time that I have previously had to drop a column, it has happened immediately. But I'm not sure what is going to happen on this table. We are only allowed 60 minutes to perform this maintenance (we have a 99.999% availability requirement according to the SLA). Do you think that the ALTER TABLE DROP COLUMN statement will be able to complete in under 30 minutes (the other 30 minutes will be used for other stuff)? Is there anything I can do to speed this up such as changing the recovery model to SIMPLE during the maintenance?Tara |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-03 : 20:04:05
|
Tara,BOL has some bad news for you...quote:
ALTER TABLE acquires a schema modify lock on the table to ensure no other connections reference even the meta data for the table during the change. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records.
Setting the recovery model won't help performance wise (I think) as the operation has to be logged during the statement and only after it has finished will the log be checkpointed...And Sql-Performance.com it appears there is more work to do.....quote:
If you run the ALTER TABLE DROP COLUMN statement to drop a variable length or text column, did you know that SQL Server will not automatically reclaim this space after performing this action. To reclaim this space, which will help to reduce unnecessary I/O due to the wasted space, you can run the following command, which is new to SQL Server 2000. DBCC CLEANTABLE (database_name, table_name)
I would recommend a couple of courses of action....1) Try it on a backup set first! Restore to dev box and give it a whirl... That should give you a good indicator...2) Talk to the parties involved in the SLA, tell them the problem straight away and inform them that it will take X minutes to complete.Good luck and tell us how it goes....DavidM"SQL-3 is an abomination.." |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 20:42:20
|
| If most data is in that column you may find it faster to:backup database (of course)set recovery model to simplecreate a new table by select col1, col2, ... into newtbl from oldtblmissing out the columndrop the old tablerename the new tableset the recovery model to whatever you wantbackup the database==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 21:20:40
|
| I would have liked to have tested the performance first but we just don't have the time for it due to production being unreachable to our other environments. To copy production, we usually zip up the backup file, copy it onto a Jaz disk, then copy it to the other environment(s). We can not zip up the backup file for this database because it is over 4GB (limitation of Winzip). We can not just copy the file to the Jaz disk because it only supports 2GB. So we don't have a current quick method (we do have a slow method though) to copy the databases that are over 4GB. I might end up testing this out on the same server if I can get the approval. We have plenty of disk space available so it probably won't be an issue.Considering that this will not be checkpointed until after it completes and that the SIMPLE recovery model will probably not help me out with the ALTER, I am going to go with Nigel's recommendation. It was one that I was considering but wanted to hear other people's recommendations.I was not aware that you had to run DBCC CLEANTABLE after ALTER TABLE DROP COLUMN to reclaim the disk space so thanks for the info David.I'll let you know how this goes.Thanks both of you!Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-03 : 21:27:35
|
| Tara-If you're interested in another archiving tool:http://www.rarlab.com/I've been using RAR for several weeks now, it handles HUUUUUGE files and handily compacts 10GB backup files down to 1 GB or less. It's almost as fast as WinZip and is MUCH better on compression on certain files...as I just discovered today. It was able to compress 800 MB of files down to 36MB, where WinZip only brought it down to 180 MB. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-03 : 22:53:19
|
| Tara,Use BCP to output the table, truncate the table and BCP back in.. It will be magnitudes faster than a SELECT INTO for a table that size...Don't forget the TABLOCK hint....Edited by - byrmol on 06/03/2003 22:55:47 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 22:55:07
|
| Cool, thanks Rob! I was asked today to look into other tools, so you saved me some work. Winzip 9.0 (it's in beta right now) actually doesn't have this limitation anymore, but I'm not sure if we get to upgrade for free or not and I don't even know what hoops I would have to go through in order to get either purchased. I was able to test this ALTER TABLE DROP COLUMN thing out today. The drop took less than 1 second. The DBCC CLEANTABLE took 5 minutes and made my transaction log grow to over 4GB! It also did not clean up the disk space as advertised. I had to then run DBCC DBREINDEX (took another 5 minutes (but still under the 30 minute mark that I am trying to hit) on that table to clean it up. So I then ran another test. I recreated the test database, ran the DROP, then ran DBCC DBREINDEX. And guess what, the disk space was cleaned up without having to run DBCC CLEANTABLE. So this time it only took 5 minutes instead of 10 (5 minutes for each DBCC). Can anybody explain why DBCC CLEANTABLE didn't work and why DBCC DBREINDEX needed to be run instead? The column really is VARCHAR so DBCC CLEANTABLE should have worked according to BOL.TaraEdited by - tduggan on 06/03/2003 22:58:14 |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-04 : 02:38:20
|
quote:
Can anybody explain why DBCC CLEANTABLE didn't work and why DBCC DBREINDEX needed to be run instead? The column really is VARCHAR so DBCC CLEANTABLE should have worked according to BOL.
I'm guessing here, but maybe you have a clustered index on this particular table. I think the DBCC CLEANTABLE is used on tables w/o clustered indexes on them.Again, just a guess.btw, here is an interesting technet article on extent lock timeouts when using DBREINDEX on large tables. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q329069&gssnb=1-ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-04 : 11:48:22
|
| Yes I do have a clustered index on that table. BOL should state that DBCC CLEANTABLE does not work on tables with clustered indexes if that is the case. Oh well.Tara |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-06 : 09:44:38
|
| Tara,Was the column that you dropped the "last" column as defined by the ordinal position in the system tables?DavidM"SQL-3 is an abomination.." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-06 : 12:35:16
|
The column that was removed, called detail, was in the middle of the table.Here's the DDL:CREATE TABLE [QDC].[MT_LOG_ENTRY] ( [customer_domain_id] [int] NOT NULL , [message_id] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [entry_sequence] [int] NOT NULL , [component] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [destination] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [detail] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [log_level] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [metadata] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [time_stamp] [numeric](19, 0) NULL , [type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GOALTER TABLE [QDC].[MT_LOG_ENTRY] WITH NOCHECK ADD CONSTRAINT [PK__MT_LOG_ENTRY__2BFE89A6] PRIMARY KEY CLUSTERED ( [message_id], [customer_domain_id], [entry_sequence] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO Tara |
|
|
|
|
ALTER TABLE DROP COLUMN performance on large table