Share via

SP execution time varies with different environment

Radhai Krish 191 Reputation points
2024-06-22T12:51:19.8666667+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,157 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
0 comments
Accepted answer
  1. Erland Sommarskog 104.5K Reputation points MVP
    2024-06-22T15:01:59.4966667+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHongMSFT-4306 25,246 Reputation points
    2024-06-24T01:44:04.8466667+00:00

    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".