Why does my query run fast in Environment A, but slow in Environment B?

I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!

Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?

There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.

Server

• Is the hardware across environments the same (disks, memory, CPU, etc.) ?

• If VMs are being used, could noisy neighbors affect overall VM performance?

• If in the cloud, do autoscaling and other configurations have parity?

• Are environments mixed between physical/virtual/cloud?

• Do the OS versions match?

• Are environments in different datacenters?

Instance

• Are the SQL Server versions the same?

• A CU or SP can make a world of a difference even if the major versions are the same.

• Is the active workload during the query execution(s) comparable?

• Are the same volume of queries present in all environments?

• Is the nature of the workload the same across all environments?

• Do all environments participate in the same HA/DR setup?

• Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.

• Do the same maintenance jobs get run on the same schedules in all environments?

• Are trace flags equivalent across all environments?

• Are the same backup jobs running in all environments?

• Impact from backups should be minimal, but often they are not run at all on lower environments.

• Are the sys configurations the same?

Database

• Are the schemas / indexes / statistics / objects all equal across environments?

• Even SELECT queries can cause statistics updates

• Does the exact same data exist across environments?

• Amount of data

• Distribution of data

• Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)

• Are database level configs the same?

• Are compatibility levels the same?

With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies between environments. When developing a new query, additional tuning is often needed as it moves towards production.

Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.

Final note: lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.

More Resources:

• Different Plans for "Identical" Servers by Aaron Bertrand

StackExchange Database Administrators Q#216919, answer score: 13