Sql-server – Efficient method to update character length of columns in large table

sql serversql-server-2012

I have a table with 80+ million records. There are 2 columns which are currently varchar(MAX), which I would like to update to varchar(255) to save space. It is not an issue if data will be truncated. This table has 3 non-clustered indexes on it.

SQL Server version: 2012 Web Edition

A solution that would require table locking or taking the database offline would be acceptable.

What I have tried:

ALTER COLUMN: SSMS does not let me use the ALTER COLUMN statement because there is an index that uses that column.
Updating in batches: does not work efficiently (or at all) since the unique identifier (or date field) that would be needed to keep track of which records were updated is not indexed and therefore the queries just hang. Also I read that updates would generate a lot of logs.
Adding a new table and inserting in batches: does not work because of same reason above.
SSMS Export Wizard: Creating a new table with the correct column data types and exporting from original works within a few hours ONLY IF I don't create the indexes on the table. If I first create the indexes and then run the export, the process gets stuck at about 25/30 million. The first few million imports fairly quickly but gets slower as it goes on. I can confirm this after I had it run for 12 hours. In the former case, I am unsure how to build these indexes as I am concerned about how much memory/cpu/space it will end up taking on my server
Do work on test server and re-attach: This last point wasn't tried yet but is next on list if I don't get another idea: Transfer/copy database (via file transfer) to test server, try applying the change there without having to worry about performance costs and then re-attaching to original server. (I can also see how long and how much space an index rebuild would take, but then again that wouldn't offer me accurate information for the production environment.)

Any ideas or comments on how to accomplish this?

Best Answer

I would suggest the following steps

When possible, change your database recovery mode to Simple Recovery if otherwise
Drop your index(es) on the two varchar(max) columns (as you will need to rebuild it anyway)
Run the following

update MyTable Set MyCol_X=substring(MyCol_X, 1, 255), MyCol_Y=substring(MyCol_Y, 1, 255);
Alter table to change the column data type

alter table MyTable alter column MyCol_X varchar(255);

alter table MyTable alter column MyCol_Y varchar(255);
Rebuild your indexes that were dropped
Set recovery mode to previous state before step 1 is executed

Related Solutions

SQL Server 2012 – Updating Large Table and Indexes

You say that the UPDATE is running in a loop and was still running after 2.5 hours. Did that loop update any rows at all? Was it the loop itself that was taking a long time or a single UPDATE statement? That is a somewhat important distinction and is currently ambiguous in the given information. However, here are some things to note based on things said in the question:

No, SQL Server should not be updating indexes that do not use the new column
Disabling indexes is not simply a matter of turning them off. It actually drops all of the index pages and keeps only the structure so that you don't need to run the full CREATE statement again. But re-enabling an index will have to rebuild it.

And here are some things to look for:

Are there any UPDATE triggers on the Child table?
What is the auto-growth setting on the transaction log? If set to a very low number it could be that the UPDATEs are impeded by a large number of auto-growth operations.
Of those 5 indexes on the Child table, do any of them have FKId as the leading column?
Have you tried lower batch sizes, or just 20k? I have seen several people randomly recommend large (10k - 50k) batch sizes for these types of operations without considering that not only does it limit the operation to that number, but it also hints to the Query Optimizer that the number is possible to hit in the first place. Hence, if there are only 5000 rows to find then it might keep looking past those to see if any others meet the c.NewColumn IS NULL criteria. And considering that the field is not indexed, all the Q.O. has to go on are the auto-generated statistics which don't tell it where to find the remaining NULL rows. Assuming that the first several iterations of the loop did succeed, SQL Server still has to find 20,000 rows that are NULL, out of the 6.5 million, by scanning as many as it takes, in no particular order, before it finds them. Hence the first few iterations probably happened quickly, but this type of operation slows down quickly as each successive pass needs to scan more and more of the records before it can find the 20,000 that meet the IS NULL criteria.

So, two things to consider:
- Start out with a small batch size, maybe 10 rows, and run that statement by itself (i.e. not in the loop). Does it come back? If not then you need to answer @Aaron's question about what the WAIT_RESOURCE is. If it does come back, increase the batch size to 100, then 500, 1000, 2000, 5000, and then 10000. It will take more loops to complete, but finding the required number of records to UPDATE will be easier to accomplish. Once you find a size that seems responsive, then you can run the loop again (I am guessing that somewhere in the 2000 - 5000 range is gonna work :)
- Rather than get rid of indexes, you might actually want to create an index, temporarily, to support this particular UPDATE operation. A filtered index (which was introduced in SQL Server 2008 so the compatibility mode shouldn't pose a problem) will allow you to target the remaining rows to update. Something like:
```
CREATE NONCLUSTERED INDEX [IX_Child_FKId_temp]
  ON dbo.Child ([FKId] ASC) -- you might need to include [NewColumn] ASC after [FKId]
  WHERE [NewColumn] IS NULL
  WITH ( FILLFACTOR = 100 );
```

Sql-server – Shrink database or DB files not reducing size even after dropping biggest table

Exactly how did you run the SHRINK commands? My understanding is that by default it just releases unused space at the end of the file, but you can also have the database compact itself first to make more space available. This is a slow and expensive operation, though; around an hour per 10 GB in my recent experience.

That said, I suspect your database is using more space than it ought to due to fragmentation. If you run a query like this:

SELECT TOP 10
    '[' + S.name + '].[' + O.name + ']' AS TableName,
    CASE WHEN I.type_desc = 'HEAP' THEN '(heap)' ELSE '[' + I.name + ']' END AS IndexName,
    Stats.avg_fragmentation_in_percent, Stats.fragment_count
FROM
    sys.indexes AS I
    INNER JOIN sys.objects AS O ON I.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), O.object_id, I.index_id, NULL, NULL) AS Stats
WHERE
    S.name NOT IN ('sys')
ORDER BY
    Stats.avg_fragmentation_in_percent * Stats.fragment_count DESC

...you'll see the tables and indices which are both large and badly fragmented. Running ALTER INDEX MyIndx ON MyTBl REBUILD on a few of those should recover some space. As Aaron Bertrand said, you probably don't want to actually free this space with SHRINK; your database will need it sooner or later.

Heap tables are prone to wasted space; using ALTER TABLE MyTbl REBUILD on your bigger tables may help too.

Best Answer

Related Solutions

SQL Server 2012 – Updating Large Table and Indexes

Sql-server – Shrink database or DB files not reducing size even after dropping biggest table

Related Question