1

What risks do I need to consider if I want to run ALTER VIEW in a live SQL Server production environment? Assume lots of long-running queries could be going on, both SELECTS and INSERT/UPDATE/DELETEs, but no ALTER TABLES or other schematic changes. Is there any risk of disrupting users in the following cases?

  1. Nobody is selecting from the view I'm updating, or anything that depends on it, but they may be updating data in tables the view uses.

  2. Users may be selecting the from the view being updated immediately before or after the statement is run. For example, if a user starts (A) a long-running SELECT on the view, I run (B) ALTER VIEW and it has to wait for (A) to finish, then a different user starts (C) another SELECT on the view. Does (B) block (C) until (A) is completed?

If you need to know, the environment is SQL Server 2005 SP4 (9.0.5000), but please indicate if your answer is different for newer versions.

4

Modifying the underlying table for the view should not make an impact when running alter view. This is not true to users trying to access the view.

If a view currently used is modified by using ALTER VIEW, the Database Engine takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, the Database Engine deletes all copies of the view from the procedure cache. Existing plans referencing the view remain in the cache but are recompiled when invoked.

Source

Exclusive locks:

Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Schema locks:

Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

What it does:

During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

So it sounds like this:

    User 1 runs a query.

    User 2 alters the view.

    User 3 tries to query the view.

    User 1 finishes query, User 3 finishes query, User 2 now establishes a schema lock 
preventing any other transactions until no active users are using the view.

I ran a test on my database with three concurrent windows. One would select * from the view 10,000 times, another would insert into the view 10,000 times, and the other would update the base table 1000 times. While these ran, I would run the alter view script. It behaved in a manner as I described with the scenario above.

None of these procedures were overly heavy or time consuming, basically thousands of micro transactions. The alter wedged itself in as fast as it could and altered the view between transactions when it could establish a lock. Due to this view and tables being small, I could not see the difference. Your environment will vary based on data sets.

I think your primary risk is raising the time it could take to run or complete transactions on the view. The other risk is modifying a view can break other things if they were not written to specify columns and were maintained with * instead. Other thoughts, you could be removing columns in the view in use elsewhere.

1

Nobody is selecting from the view I'm updating, or anything that depends on it, but they may be updating data in tables the view uses.

Should be OK.

Users may be selecting the from the view being updated immediately before or after the statement is run. For example, if a user starts (A) a long-running SELECT on the view, I run (B) ALTER VIEW and it has to wait for (A) to finish, then a different user starts (C) another SELECT on the view. Does (B) block (C) until (A) is completed?

I would suggest the schema switch approach. At the end to refresh metadata (if the view is not created using schema binding), just run sp_refreshview view_name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.