There are times we need to modify a view in production, we can use tools like sp_who2 / sp_whoisactive or use the DMV’s and Extended Events ourselves to see who’s using the views at the moment. But what happens when we need to modify the view but it’s currently in use?
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.
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.
What happens:
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 the 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.