InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:23:34 GMT20http://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxAfter lot of testing, will go with this logic ...[code="plain"]DECLARE @i INT; DECLARE @chunk INT; DECLARE @maxNewTable INT; SET @i = ( SELECT MIN(ID) FROM OldTable WITH (NOLOCK) );SET @chunk = 5;SET @maxNewTable = ( SELECT MAX(ID) FROM NewTable )IF @maxNewTable IS NULL BEGIN begin try BEGIN TRANSACTION; set identity_insert dbo.NewTable on; insert into dbo.NewTable (ID, Col2, Col3, Col4, Col5) select ID, Col2, Col3, Col4, Col5 from dbo.OldTable WITH (NOLOCK) where ID BETWEEN @i AND @i+@chunk; COMMIT TRANSACTION; end try begin catch print error_message(); rollback transaction; end catch ENDELSE begin try BEGIN TRANSACTION; set identity_insert dbo.NewTable on; insert into dbo.NewTable (ID, Col2, Col3, Col4, Col5) select ID, Col2, Col3, Col4, Col5 from dbo.OldTable WITH (NOLOCK) where ID > @maxNewTable AND ID < @maxNewTable+@chunk; COMMIT TRANSACTION; end try begin catch print error_message(); rollback transaction; end catch set identity_insert dbo.NewTable off;[/code]Certainly not as clean like BT's, but that one is not grabbing lower ID values properly. I believe is something related to this line:[code="plain"]select @i = coalesce((select top (1) PrimaryKeyID from dbo.YourNewTable order by PrimaryKeyID desc), 0);[/code]But I really do not have time for more testing.I am still testing the dropping FK part. I do need to drop the FK from old and recreate on new table, after all rows are copied and during the short downtime window.I also need to rename the tables, after re-creating the FKs on new one.Mon, 08 Oct 2012 12:15:47 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]bteraberry (10/3/2012)[/b][hr]while 1 = 1 loops just fine. It will break out of the loop when applicable because of the break statement.My bad on @@ROWCOUNT ... that value should be assigned to a variable [i]before [/i]the commit, then checked against the block size [i]after[/i] the commit. I got lazy in my code for you and I forgot that after the commit, the @@ROWCOUNT will be 0, which will then cause the loop to break every time.A record size of 5000 and a wait of half a second is neither right nor wrong. Those weren't provided as such. They're examples. If someone is really concerned about their system sucking and they don't want to cause an undesirable strain on the system, then a lighter weight but longer running strain would be preferable to a shorter but more intense load. If it needs to be done in the next three weeks and the primary goal is to reduce impact, then 27 hours is irrelevant. Our servers are not struggling, so normally I would wait for five one-hundredths of a second in each loop. The record count in each batch will vary tremendously based on the size of each record.But in this case, it's all irrelevant if he's working with a heap and cannot get top (N) without a scan. That being the case, any chunk method would be a problem.[/quote]bt,I was almost ready to run your script, but found that min identity values do not match. In other words, the script is not grabbing or starting to copy from the min value. I am still checking why.Mon, 08 Oct 2012 07:52:07 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]sql-lover (10/3/2012)[/b][hr][quote][b]Jeff Moden (10/3/2012)[/b][hr]So.... no interest in trying the 65 millisecond method I proposed to see if it'll work? ;-)[/quote]Took a less elegant approach :-) ... and I may use bt's script for another table (same problem) so I can try live.Today, I copied the data into new table , with new data type. I will export the new table, which is a heap, via bcp. Move that file to live server. Create the new table there, then import via bcp.On testing server though, I still need to test the logic to break the FK from source table, recreate on new table and add missing rows not caught by bcp today via SELECT NOT IN or something similar.The advantage of copying the data into new table, different environment, is that I minimize production impact.If I can copy 90% or more outside the production environment, I will be a happy man. And I believe I accomplished that today. So after testing the FK and Index dropping logic, will be a matter of adding a few missing records, which of course I will do during the maintenance window, which hopefully will take a few min only.[/quote]Thanks for the feedback. Guess I'll try the method I suggested on some test tables. If it works, there wouldn't be any down time per se`.Wed, 03 Oct 2012 16:36:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]Jeff Moden (10/3/2012)[/b][hr]So.... no interest in trying the 65 millisecond method I proposed to see if it'll work? ;-)[/quote]Took a less elegant approach :-) ... and I may use bt's script for another table (same problem) so I can try live.Today, I copied the data into new table , with new data type. I will export the new table, which is a heap, via bcp. Move that file to live server. Create the new table there, then import via bcp.On testing server though, I still need to test the logic to break the FK from source table, recreate on new table and add missing rows not caught by bcp today via SELECT NOT IN or something similar.The advantage of copying the data into new table, different environment, is that I minimize production impact.If I can copy 90% or more outside the production environment, I will be a happy man. And I believe I accomplished that today. So after testing the FK and Index dropping logic, will be a matter of adding a few missing records, which of course I will do during the maintenance window, which hopefully will take a few min only.Wed, 03 Oct 2012 16:31:08 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]Eugene Elutin (10/3/2012)[/b][hr][quote][b]ScottPletcher (10/3/2012)[/b]...Why would the relative position of the column make the slightest bit of difference at all?[/quote]It may effect page splits and re-writes.[/quote]?? How is that??Fixed-length columns come first, then variable length columns. Increasing length is increasing the length, regardless of where in the row the column is, right??Wed, 03 Oct 2012 15:56:51 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxSo.... no interest in trying the 65 millisecond method I proposed to see if it'll work? ;-)Wed, 03 Oct 2012 15:51:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]GSquared (10/3/2012)[/b][hr]A WaitFor in such a process is commonly there to give the server a chance to finish other threads/processes in between loops.However, on a process like this, if you do batches of 5k, on 1B rows, just the half-second WaitFors add up to 27 hours, not including the time to actually process rows. So you may need to manage that.And definitely make it loop till it's done.An easy way to do that is:[code="sql"]select 1 as Col into #T;while @@rowcount > 0begin ... whatever you're looping ...end;[/code]That way, as long as the process has rows to run, it will keep going.If you have instructions after the Insert Select, you'll need to either move them before it (if possible) in the loop, or assign the @@rowcount value to a variable and loop on that instead. Otherwise, @@rowcount can easily return 0 every run.[/quote]Part of the goal in a successful chunking strategy is to prevent one humongous transaction that's going to destroy the system. The begin tran and commit tran need to be contained inside the loop. The most straight forward way I've seen to do this is simply do "while 1 = 1" and then calculate the logic within the loop of when it's time to break out of the loop. It's really no different though than simply assigning the @@ROWCOUNT to a variable immediately after the insert and use 'while @var > 0', but if you do that there is an extra step to make sure the initial value of @var is > 0 so it runs the first time. If it's left null the loop won't execute.Wed, 03 Oct 2012 15:05:15 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxwhile 1 = 1 loops just fine. It will break out of the loop when applicable because of the break statement.My bad on @@ROWCOUNT ... that value should be assigned to a variable [i]before [/i]the commit, then checked against the block size [i]after[/i] the commit. I got lazy in my code for you and I forgot that after the commit, the @@ROWCOUNT will be 0, which will then cause the loop to break every time.A record size of 5000 and a wait of half a second is neither right nor wrong. Those weren't provided as such. They're examples. If someone is really concerned about their system sucking and they don't want to cause an undesirable strain on the system, then a lighter weight but longer running strain would be preferable to a shorter but more intense load. If it needs to be done in the next three weeks and the primary goal is to reduce impact, then 27 hours is irrelevant. Our servers are not struggling, so normally I would wait for five one-hundredths of a second in each loop. The record count in each batch will vary tremendously based on the size of each record.But in this case, it's all irrelevant if he's working with a heap and cannot get top (N) without a scan. That being the case, any chunk method would be a problem.Wed, 03 Oct 2012 14:55:46 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxA WaitFor in such a process is commonly there to give the server a chance to finish other threads/processes in between loops.However, on a process like this, if you do batches of 5k, on 1B rows, just the half-second WaitFors add up to 27 hours, not including the time to actually process rows. So you may need to manage that.And definitely make it loop till it's done.An easy way to do that is:[code="sql"]select 1 as Col into #T;while @@rowcount > 0begin ... whatever you're looping ...end;[/code]That way, as long as the process has rows to run, it will keep going.If you have instructions after the Insert Select, you'll need to either move them before it (if possible) in the loop, or assign the @@rowcount value to a variable and loop on that instead. Otherwise, @@rowcount can easily return 0 every run.Wed, 03 Oct 2012 13:24:42 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]bteraberry (10/3/2012)[/b][hr]Wow, they built a billion record heap? Ouch. Sorry man.[/quote]I've seen a few similar tables. I am in the process of fixing those issues.Anyway ... tested your script. But it looks does not loop automatically, am I right? I mean, it processes whatever is on the batch and then you need to invoke it again for next batch? I was confused because the WAITFOR. I guess that's for MS-SQL to "breath" and log changes ... and allow people to use the table in between batches... right?Wed, 03 Oct 2012 13:19:27 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]ScottPletcher (10/3/2012)[/b]...Why would the relative position of the column make the slightest bit of difference at all?[/quote]It may effect page splits and re-writes.Wed, 03 Oct 2012 13:04:01 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxWow, they built a billion record heap? Ouch. Sorry man.Wed, 03 Oct 2012 12:53:39 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxI wanna die ... :crying:I just discovered there is no CI on that table, just a NCI. So bt ... your script is doing an ugly table scan (checked the execution plan on my test environment, real data).I guess I will have to incorporate that on new one, after moving the records.Wed, 03 Oct 2012 12:43:48 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]sql-lover (10/3/2012)[/b][hr]bt,That's a very nice piece of code, thank you so much. However, I wonder how many locks (if one) it will create. Like I said, the production server has serious IO issues and very bad drive layout. I'm actually in the process of migrate to a new environment I designed.But I may try and test on my ITG box. Also, how do I know when complete? The live table gets incremented sporadically. I guess the scripts will surpass the insert rate.Still I wonder how I can make BCP work via XML.[/quote]You can add a with (nolock) on your select if it makes you feel better, but since it's just reading records that aren't being updated that nolock will have virtually zero impact, but it won't hurt. As it is (sans nolock), it's just going to put a non-exclusive lock on the pages as it's reading them to verify that no other process has an exclusive lock on those same pages (which would mean that an update is in the works, ergo, it would wait until that update is done.) The non-exclusive lock is released as soon as the pages are read.You'll know it's done when the script is complete. You could add a print statement to the end as well if you want more clarity on that. Also, with this process, you can kill it at any time (just be sure to manually execute the rollback statement if you do so) with very little impact since it is only dealing with the N records at a given time. If your box is getting bogged down, simply kill it and rollback. The script will then pick up wherever it left off when you're ready to start it up again.Wed, 03 Oct 2012 12:36:04 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxbt,That's a very nice piece of code, thank you so much. However, I wonder how many locks (if one) it will create. Like I said, the production server has serious IO issues and very bad drive layout. I'm actually in the process of migrate to a new environment I designed.But I may try and test on my ITG box. Also, how do I know when complete? The live table gets incremented sporadically. I guess the scripts will surpass the insert rate.Still I wonder how I can make BCP work via XML.Wed, 03 Oct 2012 12:18:02 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxThis is the kind of code I was referring to if you choose to go with a chunking process rather than BCP. We've had similar projects as yours and this is how we normally do it.[code="sql"]declare @i int, @cnt int, @blockSize int = 5000;set identity_insert dbo.YourNewTable on;while 1 = 1begin begin try begin transaction; -- recalculate each time (3-4 reads so lightweight) since there may be gaps in ID's ... -- otherwise, just calculate before loop and add the @blockSize to @i each time select @i = coalesce((select top (1) PrimaryKeyID from dbo.YourNewTable order by PrimaryKeyID desc), 0); insert into dbo.YourNewTable (PrimaryKeyID, Col2, Col3, Col4) select top (@blockSize) PrimaryKeyID, Col2, Col3, Col4 from dbo.YourOldTable where PrimaryKeyID > @i option (maxdop 4); -- I'm pretending there are 16 processor cores set @cnt = @@ROWCOUNT; commit transaction; if @cnt < @blockSize break; waitfor delay '00:00:00.500'; end try begin catch print error_message(); rollback transaction; end catchendset identity_insert dbo.YourNewTable off;[/code]The block size here is 5000. What will work in your situation really depends, but on critical systems I try to error on the low side. Each iteration is wrapped explicitly in its own transaction to prevent the whole thing from being the same transaction which would kill your log. And then as I mentioned before, I'm using both a maxdop and a delay of a half second to make 100% sure the query cannot interfere with normal activities on the server.I would use the same script one last time after your old table has been renamed. I would write a final script to do the old table rename, then use this same script above getting the data from the renamed table to do the final population, then rename the new table. Your downtime should be a matter of seconds or less.I'm not trying to say that you shouldn't use BCP, I'm just saying how we have handled similar issues in the past.** edit: fixed an error of when @@rowcount was referenced within the script **Wed, 03 Oct 2012 10:59:51 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxMichael,Good catch!Here's the simple schema...[code="plain"]CREATE TABLE [dbo].[MyTargetTable]( [col1] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [col2] [int] NOT NULL, [col3] [smallint] NOT NULL, [col4] [smalldatetime] NOT NULL, [col5] [real] NULL) ON [FG2][/code]col1 is INT on source ... I also discovered I can use BCP and XML file to specify data type, but still having some weird errors.Wed, 03 Oct 2012 10:56:41 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]sql-lover (10/3/2012)[/b][hr]Faced 1st challenge (dumb error)I can't use "n" option with BCP or re-inserted from source table. Source has "int" on that column. Target table already has the new "bigint" data type. I am getting this error:[code="plain"]Starting copy...SQLState = 42000, NativeError = 7339Error = [Microsoft][SQL Native Client][SQL Server]OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].DOB'.[/code]Can someone refresh my mind and tell me what BCP option do I need to use? I already have the flat file from source. I just need to import the information into the new table, which already has "bigint" there. This is on my testing environment, of course.The remaining ones or discrepancy, is something I was planning to insert during the short offline period, using pure SELECT and INSERT commands and IDENTITY INSERT.Anyone? ...*** EDIT ***I honestly don't remember if I can import data via BCP and change or specify datatype somewhere. But I can actually alter or change the data type now on my testing environment on that table, and export again via BCP. Still I wonder if I can skip that extra step.[/quote]Since you didn't post the table structure or the BCP commands you used, it is a little hard to troubleshoot.On the assumption, that you are trying to export and then import in native mode, you will need to modify the export BCP to export the identity column as BIGINT, instead of INT. One way to do that is by exporting a query where you cast the ID column to BIGINT, instead of exporting the table.I recommend that you test with a small number of rows until you get everything working.Wed, 03 Oct 2012 10:51:29 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxFaced 1st challenge (dumb error)I can't use "n" option with BCP or re-inserted from source table. Source has "int" on that column. Target table already has the new "bigint" data type. I am getting this error:[code="plain"]Starting copy...SQLState = 42000, NativeError = 7339Error = [Microsoft][SQL Native Client][SQL Server]OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].DOB'.[/code]Can someone refresh my mind and tell me what BCP option do I need to use? I already have the flat file from source. I just need to import the information into the new table, which already has "bigint" there. This is on my testing environment, of course.The remaining ones or discrepancy, is something I was planning to insert during the short offline period, using pure SELECT and INSERT commands and IDENTITY INSERT.Anyone? ...*** EDIT ***I honestly don't remember if I can import data via BCP and change or specify datatype somewhere. But I can actually alter or change the data type now on my testing environment on that table, and export again via BCP. Still I wonder if I can skip that extra step.Wed, 03 Oct 2012 09:47:34 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]ScottPletcher (10/2/2012)[/b][hr][quote][b]sql-lover (10/2/2012)[/b][hr][quote][b]bteraberry (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.[/quote]You nail it down! ... that is correct ...[/quote]Can you show the results that demonstrate that claim? You only need an additional 4 bytes per row. Did you pack the table to 99-100% rather than 98%?Now, you might have done something dopey and put the identity in your clustered key, in which case you cannot just ALTER it. And dropping and recreating the clus index would indeed be much more overhead than a simple ALTER column.[/quote]Probably won't matter what the fill factor is. First, the fill factor only affects build/rebuild on indexes. It doesn't affect subsequent inserts and updates.If the Identity column is the leading edge of the clustered index, and that index was originally built or has been rebuilt (recently) with a fill factor that allows for average row-size to increase by 4 bytes, then fill factor will matter. But, if the cluster is on an ID value, and the table isn't subject to a lot of size-increasing updates, you would normally have the fill factor at 100, because inserts will be at the end of the index anyway, and a lower fill factor just means you're using disk space to hold nothing at all in that case.If the ID column isn't the leading edge of the clustered index, but is the leading edge of other critical indexes, then you'll still have a problem with page-splits, just not in the clustered index. That means I/O, CPU, RAM, page-locks (and/or heavy tempdb use), possibly extent/index locks, etc. So you may still have problems with this even if it's not the leading edge.Even if it's not the leading edge, but it's anywhere in the b-tree except the leaf level, you'll still have to face potentially running page-splits due to that. If it's only at the leaf level (rare for an ID, since there's not generally much use for one in that position), it might potentially be possible to avoid splits, theoretically. I doubt it's a realistic scenario, but I concede it could happen.Then there's the fact that, at the page level, fixed-width columns, like Int and BigInt, are stored first, and variable width (varchar, varbinary, sparse [if I'm not mistaken]) ones are stored after. That means, depending on table structure and how the engine decided to do the actual physical storage, you're likely going to get page-rewrites and splits anyway, just because of the size increase. It's not like increasing the maximum size of a varchar column, which can often be done simply through a metadata change with a DDL command, without having to actually do anything at the storage level of the data. The only way you'd avoid that, theoretically, on an Int to BigInt modification, is if the column happens to be stored last, and row-size is such that it's not an even 8k page division, so there might be room after each row for the 4-byte increase. You'd still get rewrites of all the pages, but not splits. The rewrites aren't likely to take as much time as inserts, but are likely to lock the table for whatever duration they do take, while inserting into a separate table via BCP is unlikely to result in that kind of lock escalation.There are people who know the storage internals better than I do, and if any of them want to correct what I've written here, please do. I've tested the Alter Table version, and it's not what you'd expect if you're used to doing that on varchar or varbinary columns. Try it yourself, you'll see what I mean.Wed, 03 Oct 2012 09:42:32 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxSo I guess there's no issue with rows being UPDATEd / DELETEd while the new table is being created?Or with the potential lock outs/delays to current modifications during the export of the table?Wed, 03 Oct 2012 09:10:13 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxWe recently encountered the same issue in multiple production db's.When re-inserting the data back in use BULK INSERT and remove the indexes, it is significantly faster.Wed, 03 Oct 2012 08:57:59 GMTHomersdonuthttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]aaron.reese (10/3/2012)[/b][hr]another thing, if you are going to create a new table and manually insert the records into it, dont forget to turn identity insert on; otherwise you will potentially destroy the key sequence. Not good if it is used as a FK on other tables ;-)[/quote]True! Thought about that, already implemented on my script. Thanks for the advice.Regarding your other comment, I can't use partitioning (would be awesome, assuming I have the proper Indexes) because this is not Enterprise edition..Here's what I've done so far ..-Exported the tables via BCP and moved the flat files to a different server.-I created a dummy database there with empty schema and tables and now importing flat files using bcp in. It is taking a while ...old server is even older, but same SQL flavor.Once second step is done, I will duplicate the source table (created another one already) and insert rows via bcp on second table. So I can create script to drop FKs, etc, on source... let's see ...Wed, 03 Oct 2012 08:54:24 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]foxxo (10/3/2012)[/b][hr]What about adding a new BIGINT column with NULL default so theres no locking, populate with values via script in batches. Then have a short outage while the new column is marked as IDENTITY(1billion,1), then rename old and new Id columns. OldId will remain for reference, or you could try dropping the column during the outage, but it will take some time.[/quote]You can't change a column to identity after you create it.Wed, 03 Oct 2012 08:53:10 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]Eugene Elutin (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr][quote][b]sql-lover (10/2/2012)[/b][hr][quote][b]bteraberry (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.[/quote]You nail it down! ... that is correct ...[/quote]Can you show the results that demonstrate that claim? You only need an additional 4 bytes per row. Did you pack the table to 99-100% rather than 98%?Now, you might have done something dopey and put the identity in your clustered key, in which case you cannot just ALTER it. And dropping and recreating the clus index would indeed be much more overhead than a simple ALTER column.[/quote]OP did mentioned that this column does participate in relationship, so I do think it is at least PK. Could you please clarify why putting the identity into clustered key is "dopey"? Do you, somehow, know what this table holds? I want the same crystal ball ;-).Actually, I can easily believe that even without index on this column, it may be much faster to re-insert into new table than ALTER the existing one. [b]It may depend on position of this column (let me guess it's a first one)[/b] and wideness of the table. Also, OP cannot allow too long down-time which will be required in case of using ALTER. I guess the best way would be the one suggested by OP. May be it needs to be batched.[/quote]Why would the relative position of the column make the slightest bit of difference at all?Wed, 03 Oct 2012 08:29:26 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxanother thing, if you are going to create a new table and manually insert the records into it, dont forget to turn identity insert on; otherwise you will potentially destroy the key sequence. Not good if it is used as a FK on other tables ;-)Wed, 03 Oct 2012 08:15:04 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxalso, if you are running on an old server with I/O bottlenecks, are you running SQL2008. An earlier versin may affect the answer.Its not an area I am familiar with, but would partitioning the table help him out here?Wed, 03 Oct 2012 08:13:16 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]foxxo (10/3/2012)[/b][hr]What about adding a new BIGINT column with NULL default so theres no locking, populate with values via script in batches. Then have a short outage while the new column is marked as IDENTITY(1billion,1), then rename old and new Id columns. OldId will remain for reference, or you could try dropping the column during the outage, but it will take some time.[/quote]I believe that would cause massive page splitting to make room for the new column. Dropping the clustered index probably wouldn't help here either because the resulting heap would still need to expand the rows.I'd have to do some testing to make sure it would work correctly but I would try making the new table as an empty table with the IDENTITY seed on the BIGINT column larger than the largest value in the old table. Then, combine the two tables using a partitioned view. This new view would be named the same as the old table and, of course, the old table would be renamed. Then, create an INSTEAD OF trigger to intercept new inserts to force the new inserts into the new table rather than the old. Correctly done, the partitioned view would work for UPDATEs, DELETEs, and SELECTs without further complication.Except for a possibly new constraint on the old and new tables, the whole shootin' match could be done online in about 65 milliseconds.Again, this all is just a thought and should be tested prior to actually trying to implement it. And, yeah.... it'll take a bit of planning to do it right the first time.Wed, 03 Oct 2012 06:10:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]ScottPletcher (10/2/2012)[/b][hr]Even turning on certain options / features lengthens rows in SQL Server.[/quote]I've never heard of such a thing, Scott. Do you have an example of this?Wed, 03 Oct 2012 05:57:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxWhat about adding a new BIGINT column with NULL default so theres no locking, populate with values via script in batches. Then have a short outage while the new column is marked as IDENTITY(1billion,1), then rename old and new Id columns. OldId will remain for reference, or you could try dropping the column during the outage, but it will take some time.Wed, 03 Oct 2012 05:56:00 GMTfoxxohttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]bteraberry (10/2/2012)[/b][hr]Depending on your timeline, you could do this with virtually zero downtime. Rather than doing a bulk copy, you could easily chunk this up since you're not looking for updates but only inserts (if I understand you correctly.) Just select top (1) id from the new table and then get the next N records from the old table after that ID to do the insert. Wrap each iteration in its own transaction, add a maxdop of whatever makes you comfortable (for something like this I would typically use 25% of my processors on a busy machine) and include a short WAITFOR DELAY after each iteration. With such a strategy you can easily plow through the copy without adversely affecting your server. You will still need to have a very short period of downtime to rename the old and then make sure you didn't miss any new records coming in before you rename the new, but it will be a matter of seconds instead of an hour.If you're not worried about the downtime so much, I believe the plan you have established will work fine.[/quote]bt,,Can you elaborate more? Are you saying, inserting the remaining records (not copied via bcp) from old to new using SELECT INTO or something like that? Do you mind putting the T-SQL? Yes, that would be one of my steps, the final one before renaming the table and during the short offline, if I understand you correctly.@Eugene,Thanks for the suggestion. Quick question.Not at work right now, but we do have several FK and Indexes on the source table. So I do need to drop and recreate Indexes on the new one after moving all the info.Wed, 03 Oct 2012 05:18:38 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]ScottPletcher (10/2/2012)[/b][hr][quote][b]sql-lover (10/2/2012)[/b][hr][quote][b]bteraberry (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.[/quote]You nail it down! ... that is correct ...[/quote]Can you show the results that demonstrate that claim? You only need an additional 4 bytes per row. Did you pack the table to 99-100% rather than 98%?Now, you might have done something dopey and put the identity in your clustered key, in which case you cannot just ALTER it. And dropping and recreating the clus index would indeed be much more overhead than a simple ALTER column.[/quote]OP did mentioned that this column does participate in relationship, so I do think it is at least PK. Could you please clarify why putting the identity into clustered key is "dopey"? Do you, somehow, know what this table holds? I want the same crystal ball ;-).Actually, I can easily believe that even without index on this column, it may be much faster to re-insert into new table than ALTER the existing one. It may depend on position of this column (let me guess it's a first one) and wideness of the table. Also, OP cannot allow too long down-time which will be required in case of using ALTER. I guess the best way would be the one suggested by OP. May be it needs to be batched.Tue, 02 Oct 2012 19:02:11 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxDepending on your timeline, you could do this with virtually zero downtime. Rather than doing a bulk copy, you could easily chunk this up since you're not looking for updates but only inserts (if I understand you correctly.) Just select top (1) id from the new table and then get the next N records from the old table after that ID to do the insert. Wrap each iteration in its own transaction, add a maxdop of whatever makes you comfortable (for something like this I would typically use 25% of my processors on a busy machine) and include a short WAITFOR DELAY after each iteration. With such a strategy you can easily plow through the copy without adversely affecting your server. You will still need to have a very short period of downtime to rename the old and then make sure you didn't miss any new records coming in before you rename the new, but it will be a matter of seconds instead of an hour.If you're not worried about the downtime so much, I believe the plan you have established will work fine.Tue, 02 Oct 2012 17:32:45 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]sql-lover (10/2/2012)[/b][hr][quote][b]bteraberry (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.[/quote]You nail it down! ... that is correct ...[/quote]Can you show the results that demonstrate that claim? You only need an additional 4 bytes per row. Did you pack the table to 99-100% rather than 98%?Now, you might have done something dopey and put the identity in your clustered key, in which case you cannot just ALTER it. And dropping and recreating the clus index would indeed be much more overhead than a simple ALTER column.Tue, 02 Oct 2012 17:24:25 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]bteraberry (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.[/quote]You nail it down! ... that is correct ...Tue, 02 Oct 2012 17:18:45 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]sql-lover (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]NO way! Because downtime! Why do you think that I am taking all those extra measures or steps? The table has over 1 billion records? :cool:I did not design this table / database, I am the new DBA. It is my responsibility to take care of the system though, that's what I am doing.I pretty much know what I need to do. I was just asking to validate the overall steps.The database is a MC one. I can't touch it! Plus is a very old hardware with serious IO bottleneck. Any activity on that table will make the table useless and trigger a downtime.[/quote]Yes, I read the 1B rows.[b]Do you really think that with adequate freespace in each block to begin with, it will be faster to insert the rows twice than to change the length of one column??[/b] I don't see how that's even remotely possible.Tue, 02 Oct 2012 17:18:11 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]NO way! Because downtime! Why do you think that I am taking all those extra measures or steps? The table has over 1 billion records :cool:I did not design this table / database, I am the new DBA. It is my responsibility to take care of the system though, that's what I am doing.I pretty much know what I need to do. I was just asking to validate the overall steps.The database is a MC one. I can't touch it! Plus is a very old hardware with serious IO bottleneck. Any activity on that table will make the table useless and trigger a downtime.Tue, 02 Oct 2012 17:13:47 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspxSome other thoughts/possibilities:Rather than export and then import, it should be faster to just directly INSERT INTO the new table, naturally doing a table lock on the new table.Pre-allocate enough log space to handle the activity (you'd want to do this whatever method you use).Tue, 02 Oct 2012 17:02:14 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]bteraberry (10/2/2012)[/b][hr][quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.[/quote]He packed a huge table 100% full? Yeah, that's a mistake for a number of reasons, particularly in SQL Server. Even turning on certain options / features lengthens rows in SQL Server.Tue, 02 Oct 2012 16:47:52 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367161-392-1.aspx[quote][b]ScottPletcher (10/2/2012)[/b][hr]Maybe I'm missing something.Why not just ALTER the column to be a bigint instead of an int?[/quote]He said these are very big tables. Altering the column means that every single records needs more storage space to accommodate the larger data type. Tons of downtime is likely to result because in most environments the extra space won't be available without shuffling everything around.Tue, 02 Oct 2012 16:29:43 GMTbteraberry