performance impact when renaming a column in 100M rows table
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:

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


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.