When I drop a view or stored procedure, is cache clear automatically ??

Question

When I drop a view or stored procedure, is cache clear automatically ??

Ather M

SSChasing Mays

Points: 607
More actions
July 27, 2009 at 8:31 am

#212818

When I drop a view or stored procedure, is cache clear automatically ??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

I don't think either cache is cleared. The procedure cache, because that will likely be cleared as space is needed. Same for execution plans.

For the data cache, that's data, not a view or a proc. It is also cleared as new space is needed and that data is stale.

Ather M SSChasing Mays Points: 607 More actions · Answer 2

Thanks for your reply.

We have snashot replication we are dropping all tables,views,stored procedures,functions,indexes,foreign keys etc and recreating them. Will they impact in any way to cache and execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The entire of cache won't be cleared (that would be a bad thing). Just the plan of the object dropped will be marked as invalid and discarded.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ather M SSChasing Mays Points: 607 More actions · Answer 4

Did you mean there is no impact on cache but execution plan will be recreated.