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.
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?
Articles in this section
- Changes to SQL Clone In-Product Updates and Notifications
- Failed to clean up temporary user/login. The database principal owns a database role and cannot be dropped
- What resources are required for involved machines using SQL Clone
- Where is the log out button in SQL Clone?
- Only one Rule Controller can be present if the SIMPLELOGINSUB is used
- SQL Clone Download URLs
- SQL Clone performance experiences delays while running simple queries
- Changing the default location of SQL Clone files
- SQL Clone throws "Could not get list of clones" error due to Newtonsoft.Json.JsonSerializationException
- Silent Install Clone Agent