What is SPID in SQL Server

The main issue with updating millions of rows in a single statement is locking, since too many rows have to be updated, lock escalation can cause a table level blocking; There  are actually two ways to handle it.

1. Disable lock escalation at the table level using ALTER TABLE SET LOCK_ESCALATION
2. Disable lock escalation using Trace Flag

In order to disable lock escalation, simply run

DBCC TRACEON(1211,-1)
GO

This can still generate some large number of page locks, but now a days servers with 64 bit SQL and lots of memory space, the consequences are negligible
3. Smaller batches

Running statement on smaller batches will work in all environments, below is a sample code for doing the update

 

WHILE (1 > 0)
  BEGIN
    BEGIN TRANSACTION
    UPDATE TOP ( 10000 ) TableName
    SET    Col1 = 0,
           Col2 = 0
    WHERE  Col1 IS NULL AND col2 IS NULL
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRAN
         BREAK
      END
    COMMIT TRAN 
    WAITFOR DELAY '00:00:01'  --wait for a second before the next update
  END -- WHILE
GO

Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

• A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.

• A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.

• The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

Escalation Threshold for a Transact-SQL Statement

When the Database Engine checks for possible escalations at every 1250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table. Lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table. For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.

Lock escalation only occurs for tables that have been accessed at the time the escalation is triggered. Assume that a single SELECT statement is a join that accesses three tables in this sequence: TableA, TableB, and TableC. The statement acquires 3,000 row locks in the clustered index for TableA and at least 5,000 row locks in the clustered index for TableB, but has not yet accessed TableC. When the Database Engine detects that the statement has acquired at least 5,000 row locks in TableB, it attempts to escalate all locks held by the current transaction on TableB. It also attempts to escalate all locks held by the current transaction on TableA, but since the number of locks on TableA is < 5000, the escalation will not succeed. No lock escalation is attempted for TableC because it had not yet been accessed when the escalation occurred.

Escalation Threshold for an Instance of the Database Engine

Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:

• If the locks option is set to its default setting of 0, then the lock escalation threshold is reached when the memory used by lock objects is 24 percent of the memory used by the Database Engine, excluding AWE memory. The data structure used to represent a lock is approximately 100 bytes long. This threshold is dynamic because the Database Engine dynamically acquires and frees memory to adjust for varying workloads.

• If the locks option is a value other than 0, then the lock escalation threshold is 40 percent (or less if there is a memory pressure) of the value of the locks option.

The Database Engine can choose any active statement from any session for escalation, and for every 1,250 new locks it will choose statements for escalation as long as the lock memory used in the instance remains above the threshold.

reference : http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx