SP execution time varies with different environment

Question

Dev environment is restored with the latest Test.

When a SP with same set of parameter is executed against Test as well as Dev, the execution time is differing with both the environments.

In Test it takes 1.45 mins, whereas in Dev it is 3.16/3.08 mins.

Did check upon it execution throughout the day and that remains same that DEV is taking longer time than the Test.

How to trace the reason?

Answer 1

This is not unheard of, and there can many different reasons. The fact that you restore the database from one environment to another, means that there is no cached plan. When SQL Server builds a plan it "sniffs" the values of the input parameters this affects the plan. It the initial execution is for an untypical value, this can lead to a plan that less optimal for the typical values.

The hardware configuration also matters when building the plan, so if one environment has more RAM than the other, you can get a different plan for that reason.

In this case, the difference is not drastic, so it could simply be a case of that the Dev environment is slower than Test. For instance, it has fewer cores, so the total duration increases while the CPU is still the same.

As for troubleshooting. First thing is to check the hardware specs. (Or the VM specs, assuming that environments are virtualised.)

Next thing to do is to check the execution plans. Are they the same, or are they different. Which values were they sniffed for? You can see this, by selecting the XML view and the go the bottom of the XML.

Keep in mind that if the procedure invokes subprocedures or dynamic SQL, they have their own plans, with their own sniffed parameters.

Answer 2

Hi @Radhai Krish

Have you compared execution plans of the query on both servers. Save the execution plans and compare them by using SQL Server Management Studio Plan Comparison feature.

Besides, different environments may lead to query plan differences or direct differences in CPU usage. Environments include server versions, database or server configuration settings, trace flags, CPU count or clock speed, and Virtual Machine versus Physical Machine. See Diagnose query plan differences for details.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".