SQL Clone performance experiences delays while running simple queries

Description

When using SQL Clone you may run into issues running simple queries on the clone where the performance becomes very slow. However, if a full restore is performed on the same backup the performance is fine.

A possible cause of this behavior is the stored procedures that are in the Procedure Cache are left over from the creation of the Image from the original backup taken. These stored procedures that are still in this cache should be cleared out for better performance. Freeing the procedure cache would cause an ad-hoc SQL statement to be recompiled rather than reused from the cache. This should be done after the new clone is added.

 

Steps

1. Login to newly added Cloned DB through SSMS

2. Run DBCC FREEPROCCACHE

 

DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Was this article helpful?

0 out of 0 found this helpful
Have more questions? Submit a request