performance impact when renaming a column in 100M rows table

Question

We are running SQL Server 2012 Standard Edition.

Need to change the name of a column in a table with +100M rows.

This table is heavily used at all times.

I can't say at this moment if the column in question is part of an index (should I expect different behavior in case it is?)

What is the performance impact I should expect when running:

EXEC sp_rename 'myTable.oldColumnName', 'newColumnName','COLUMN'

EDIT: This column was created for future needs, we know is it not used in any part of the system. It has only null values. I'm offline right now, not sure if it is included in an index (by mistake)

EDIT 2: Most transactions on this table have with(nolock) hint. Do you think running sp_rename at isolation lever read uncommitted is the way to go?

score 10 · Accepted Answer · answered Nov 01 '17 at 00:37

The size of the table does not matter. Changing the column name just changes metadata in the table. However, there's no concept of a column metadata lock in SQL Server. Renaming a column requires a schema modification lock on the table. Here's what the lock request can look like:

<Database name="TEST">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="rename_col_test" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Under the default isolation level, after you issue the rename it will be blocked by any queries that were already running with any kind of lock on that object. Any queries kicked off after your rename request that require a lock on the table will be blocked by the rename.

Here's an example of how a blocking chain can form:

Session 52 is a long running SELECT that holds a lock on the table. Session 53 tries to rename the table. Session 54 is another SELECT query that is compatible with the locks taken by session 52, but it is blocked by the rename.

performance impact when renaming a column in 100M rows table

1 Answers1