Adding NOT NULL columns to existing table

Adding a column with NOT NULL feature to an existing table requires a default value to be inserted.  If the table is large, the operation can take more time and resources.  Importantly, this operation requires exclusive table lock. Before SQL Server 2005, this operation will prevent other users from accessing the table (unless NOLOCK query hint is specified or the session is in READ_UNCOMMITTED mode) until all the data pages were updated with the default value.

With SQL Server 2005,  two (some call it one and a half!)  new transaction isolation levels were introduced which allows the snapshot of the table to be copied into tempdb and accessed by other users. Users using READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation levels can access the data as at the column does not exists. However the cost involved with the operation is still high and It too a reason for page splits.

People generally prefer not to issue NOT NULL clause with the ADD COLUMN statement.  Adding a column with null value and then updating the table with the default value, chunk by chunk is one of the mechanism employed to prevent the issue to some extend.  This method will reduce the locks and increase the    availability of the table. However, it will not prevent the page splits.

With the introduction of SQL Server 2012, we do not have to worry much about it.

Page Splits Part 2: Identifying Page splits

Even though we know theoretically how page split occurs when the database system is on live identifying page splits is a different story altogether. We have little control over the data inserted or updated.  (There are mechanisms to control the clustered key inserted, but it is difficult to control the update)

There is no event in profiler to identify the page split.

Page splits could be identified through Performance Monitor (It is being called with different names:  Performance monitor, System Monitor and Performance – All refers to the same tool named PerfMon.exe ) It is part of control panel and placed under administrative tools.