December 18, 2008 at 8:02 am
I'd like to add a persisted column to our database, but when I do it locks the entire table and blocking ensues. We have lower usage times, but even still I'm guessing a 5 minutes worth of blocking will occur.
Is there anyway to avoid locking the entire table when adding a persisted column?
I'm using a script similar to the code below.
ALTER TABLE dbo.tblAddress ALTER COLUMN
AddressOnly AS Column1+Column2 PERSISTED
Thanks in advance!
December 19, 2008 at 5:23 am
I guess you could use the ONLINE hint, but it requires the Enterprise edition to work.
The ONLINE hint determines whether concurrent user access to the underlying table during operation.
See http://msdn.microsoft.com/en-us/library/ms186241(SQL.90).aspx
and specifically the "with index options" here http://msdn.microsoft.com/en-us/library/ms186869(SQL.90).aspx
N 56°04'39.16"
E 12°55'05.25"
December 19, 2008 at 5:30 am
ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
Note:
Unique nonclustered indexes cannot be created online. This includes indexes that are created due to a UNIQUE or PRIMARY KEY constraint.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
N 56°04'39.16"
E 12°55'05.25"
December 19, 2008 at 8:39 am
Thanks for the suggestion, although I don't quite see that working. That allows me to add index with Online=On, but has no effect on the computed column. I need to add the computed column only (no index), but blocking occurs from the locking of the entire table.
December 23, 2008 at 7:01 pm
Hi Bradley,
How big is the table? Are there indexes on these two columns? I wonder if they can obstruct the lock acquisition during the alter table.
Maybe if you don't need the index on the column, using view that would return this column could be an option?
Regards
Piotr
...and your only reply is slàinte mhath
December 24, 2008 at 7:43 am
Hi Bradley,
How big is the table? Are there indexes on these two columns? I wonder if they can obstruct the lock acquisition during the alter table.
Maybe if you don't need the index on the column, using view that would return this column could be an option?
The table is 14 million rows spanning 2.5 gb worth of data. There are 3 columns that are varchar(35) that are being combined into a single persisted column that will be Full Text Indexed (to reduce the number of keys returned by ContainsTable). I did think of using a view, but you cannot Full Text Index a computed column. So it'd have to be an indexed view, which I did not try.
In the end, I just locked everybody out of the database for 7 minutes (called it database "maintence") and moved on. Although, I still find it odd that a persisted column locks the entire table.
December 24, 2008 at 10:07 am
Hm, I imagine that such alter of table updates all the rows in it - according to BOL the Sch-M is acquired. There is a remark that "Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records." I think that adding persisted computed column would be such an operation.
I wonder if switching database to bulk logged mode would help?
...and your only reply is slàinte mhath
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply