12

I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).

Using

ALTER TABLE [table] ALTER COLUMN [col] NVARCHAR(260) NULL

results in SQL Server rewriting the entire table (and using 2x table size in log space), which is billions of rows, only to change nothing, isn't an option. Increasing the column width doesn't have this problem, but decreasing it does.

I have tried creating a constraint CHECK (DATALENGTH([col]) <= 520) or CHECK (LEN([col]) <= 260) and SQL Server still decides to re-write the entire table.

Is there any way to alter the column data type as a metadata-only operation? Without the expense of rewriting the entire table? I'm using SQL Server 2017 (14.0.2027.2 and 14.0.3192.2).

Here is a sample DDL table to use to reproduce:

CREATE TABLE [table](
    id INT IDENTITY(1,1) NOT NULL,
    [col] NVARCHAR(4000) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

And then run the ALTER.

16

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data:

  1. CAST the column as NVARCHAR(260) in all codes that uses it. The query optimizer will calculate the memory grant using the casted data type instead of the raw one.
  2. Rename the table and create a view that does the cast instead. This accomplishes the same thing as option 1 but may limit the amount of code you need to update.
  3. Create a non-persisted computed column with the right data type and have all of your queries select from that column instead of the original one.
  4. Rename existing column and add computed column with the original name. Then adjust all of your queries making updates or inserts to the original column to use new column name instead.
|improve this answer|||||
15
+100

Is there any way to alter the column data type as a metadata-only operation?

I don't think so, this is how the product works right now. There are some really great workarounds to this limitation proposed in Joe's answer.

...results in SQL Server rewriting the entire table (and using 2x table size in log space)

I'm going to respond to the two parts of that statement separately.

Rewriting the Table

As I mentioned before, there's not really any way to avoid this. That seems to be the reality of the situation, even if it doesn't make complete sense from our perspective as customers.

Looking at DBCC PAGE before and after changing the column from 4000 to 260 shows that all of the data is duplicated on the data page (my test table had 'A' 260 times in the row):

Screenshot of data portion of dbcc page before and after

At this point, there are two copies of the exact same data on the page. The "old" column is essentially deleted (the id is changed from id=2 to id=67108865), and the "new" version of the column is updated to point to the new offset of the data on the page:

Screenshot of column metadata portions of dbcc page before and after

Using 2x Table Size in Log Space

Adding WITH (ONLINE = ON) to the end of the ALTER statement reduces the logging activity by about half, so this is one improvement you could make to reduce the amount of writes to disk / disk space needed.

I used this test harness to try it out:

USE [master];
GO
DROP DATABASE IF EXISTS [248749];
GO
CREATE DATABASE [248749] 
ON PRIMARY 
(
    NAME = N'248749', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\248749.mdf', 
    SIZE = 2048000KB, 
    FILEGROWTH = 65536KB
)
LOG ON 
(
    NAME = N'248749_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\248749_log.ldf', 
    SIZE = 2048000KB, 
    FILEGROWTH = 65536KB
);
GO
USE [248749];
GO

CREATE TABLE dbo.[table]
(
    id int IDENTITY(1,1) NOT NULL,
    [col] nvarchar (4000) NULL,

    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

INSERT INTO dbo.[table]
SELECT TOP (1000000)
    REPLICATE(N'A', 260)
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;
GO

I checked sys.dm_io_virtual_file_stats(DB_ID(N'248749'), DEFAULT) before and after running the ALTER statement, and here are the differences:

Default (Offline) ALTER

  • Data file writes / bytes written: 34,809 / 2,193,801,216
  • Log file writes / bytes written: 40,953 / 1,484,910,080

Online ALTER

  • Data file writes / bytes written: 36,874 / 1,693,745,152 (22.8 % drop)
  • Log file writes / bytes written: 24,680 / 866,166,272 (41 % drop)

As you can see, there was a slight drop in the data file writes, and a major drop in the log file writes.

|improve this answer|||||
2

I have been in a similar situation many times.

Steps :

Add a new col of desired width

Use a cursor, with a few thousand iterations (perhaps ten or twenty thousand) per commit to copy data from old column to new column

Drop old column

Rename new column to name of old column

Tada!

|improve this answer|||||
  • 3
    What if some records you've already copied end up being updated, or deleted? – George.Palacios Sep 18 '19 at 14:15
  • 1
    It’s very easy to do one final update table set new_col = old_col where new_col <> old_col; before dropping old_col. – Colin 't Hart Sep 18 '19 at 17:29
  • 1
    @Colin'tHart that approach will not work with millions of rows... the transaction gets huge, and it blocks.... – Jonesome Reinstate Monica Sep 19 '19 at 0:10
  • @samsmith First you do what you describe above. Then, before dropping the original column, if there have been any updates to the original data in the meantime, run that update statement. It should only affect the few rows that have been modified. Or am I missing something? – Colin 't Hart Sep 19 '19 at 7:24
  • To cover rows updated during the process, trying to avoid the full scan that where new_col <> old_col with no other filtering clauses will result in, you could add a trigger to carry these changes over as they happen and remove that at the end of the process. Still a potential performance hit, but many small amounts over the length of the process instead of one huge hit at the end, probably (depending on your app's update pattern for the table) adding up to far far less in total than that one huge hit. – David Spillett Sep 20 '19 at 15:14
1

Well there is an alternative depending on available space in your database.

  1. Create an exact copy of your table (e.g. new_table), except for the column where you will be shortening from NVARCHAR(4000) to NVARCHAR(260):

    CREATE TABLE [new_table](
        id INT IDENTITY(1,1) NOT NULL,
        [col] NVARCHAR(260) NULL,
        CONSTRAINT [PK_test_new] PRIMARY KEY CLUSTERED (id ASC)
    );
    
  2. In a maintenance window copy the data from the "broken" table (table) to the "fixed" table (new_table) with a simple INSERT ... INTO ... SELECT ....:

    SET IDENTITY_INSERT [new_table] ON
    GO
    INSERT id, col INTO [new_table] SELECT id, col from [table]
    GO
    SET IDENTITY_INSERT [new_table] OFF
    GO
    
  3. Rename the "broken" table table to something else:

    EXEC sp_rename 'table', 'old_table';  
    
  4. Rename the "fixed" table new_table to table:

    EXEC sp_rename 'new_table', 'table';  
    
  5. If everything is fine, drop the "broken" renamed table:

     DROP TABLE [old_table]
     GO
    

There you go.

Answering Your Questions

Is there any way to alter the column data type as a metadata-only operation?

No. Currently not possible

Without the expense of rewriting the entire table?

No.
(See my solution and others.)

|improve this answer|||||
  • Your "insert into select from" will result, on a large table (millions or billions of rows) in an ENORMOUS transaction, that may bring the DB to a halt for tens or hundreds of minutes. (As well as making the ldf enormous and possibly breaking log shipping, if in use) – Jonesome Reinstate Monica Sep 22 '19 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.