Sql-server – alter table which is under the load (sql server)

alter-tablesql-server-2012upgrade

we have table that being used for sending sms and other notification for confirmation as second factor.

Some changes in business logic requires us to alter table to make some columns NOT NULL.

Our stumbling-stone is that table should be accessed during 24/7, application writes to and reads from it pretty frequently and table has tens of millions rows. And when command like

alter table NOTIFICATION_TABLE 
alter column C1 int null

starting to execute it obviously tries to lock table and seemingly cannot do it because table is being used by other requests. That situation end up with that alter command hangs out and nothing happened during 5 minutes. After that timeout we stop it because do not want occasionally crush our system.

Any thoughts about how we can implement this modification without stopping our system?

Best Answer

Announce that over the weekend there will be a planned maintenance event where new notifications will be delayed for X hours.
Make a copy of the table. Redirect all reads there.
Disable any writes from the application or queue them up if that's unacceptable.
Alter the table.
Apply any queued up writes.
Set everything back.

Related Solutions

Can a view affect ALTER TABLE commands

No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:

sql> select name from dba_objects where object_type = 'VIEW' and status = 'INVALID';

You should fix the problems before the view can be made VALID. The view can be compiled manually:

sql> alter view foo compile;

Or you can allow the system automatically compile it when someone attempts to query the view. However manual compilation is the recommended way because it reduces the execution time of the first query. Usually views and other invalid objects (procedures, triggers etc.) are recompiled in bulk using a supplied script utlrp and this is a routine task for a DBA.

If you want to create the view but the base table doesn't yet exist, you can use the option FORCE:

sql> create force view foo as select * from moo;

Sql-server – Troubleshooting SOS_SCHEDULER_YIELD wait

So I resolved this, turns out that power management features were enabled on our SQL server that were scaling the CPU frequency up and down, but not fast enough to keep up with the small demand and introduced the SOS_Scheduler_Yield wait. After changing it to run always in high performance the issue went away and now the waits are more normal (LatchIO type stuff).

Best Answer

Related Solutions

Can a view affect ALTER TABLE commands

Sql-server – Troubleshooting SOS_SCHEDULER_YIELD wait

Related Question