Clone for performance testing

abhishek813

Is it recommended to use use a clone generated using sql clone for performance testing?

Dan B

Hi abhishek813,

We would not recommend this as the clones image will be stored on a network location which could affect performance compared to a local database.

Grant

It does depend on what kind of performance testing we're talking about. If you want to measure specific query timing metrics, or your disk I/O timings for the server or database, then absolutely not. The overhead introduced by SQL Clone will skew those results.

However, if you're looking at query tuning by using execution plans, then yes, absolutely. The plans generated are going to be the same, using Clone or not. If you're looking to see the volume of I/O of a query, how many pages it reads, then you can also use Clone as part of your performance tuning.

For me, it's whether or not you're attempting to measure system metrics or more granular information. The system metrics, especially I/O are going to be somewhat skewed because of Clone. Most of the other measures are going to be good enough for you to understand what's going on in terms of performance tuning. Just go into it with the knowledge that I/O speed is not something you can accurately measure because of Clone. The rest should be OK with that knowledge.

TheSQLGuru

I agree with Grant, but with a rather large caveat. The query plan you get is drastically influenced by the "server" capabilities. In the vast majority of cases the machine you mount a clone on will be a fraction of the hardware (especially the optimizer-affecting ones of CPU and RAM) that the production box is. So you could wind up chasing a red herring or worse. Another potential gotcha is the edition/version/build of SQL Server the clone is mounted on.

TheSQLGuru

You do not move the data multiple times when you use SQL Clone. One move to create the image (from a backup or directly from a live database). Then it is just "mounting" that image as a new read/write database on any SQL Server. Each time you do this it takes 10-15 seconds and ~50MB of local disk space.

Now, if you actually ACCESS data (say for tuning some query or running some report) then you move the data to the local box. Thus the network becomes much more important piece of the infrastructure pie (as does the storage you create the image on).