Similar queries running differently in servers which have similar setup

Question

Having same setup on test and pre-prod servers.

Running same queries on test and it is taking very less time, however on pre-prod it is running more than 5minutes.

Note: Application and test server are in same data center, Application and pre-prod server are in different data centers.

Observed Deadlocks when executing queries from application in pre-prod but now on test. The same/similar queries run on Test environment even with larger number of parts without any locks. The SPID which are causing deadlocks belongs to transactions which are running from application.
The database has Read committed isolation level on both servers and having same indices and other settings.

Can someone suggest why the deadlocks are occurring in pre-prod but not on test and these are taking more time for execution.

Answer 1

Since we don't know anything about your system, we can only offer speculations and tips for further troubleshooting.

Observed Deadlocks when executing queries from application in pre-prod but now on test.

I take that "now" is a typo for "not"?

One thing to check is to run SELECT * FROM sys.databases WHERE name = 'dbname' in both environments and check if there are any differences. Particularly, check is_read_committed_snapshot_on.

Apart from that, I would assume that query plans are different in the two environments. Slower queries are generally also more prone to cause deadlocks.

Assuming that you are on SQL 2016 or later, you should enable Query Store and re-run your tests, to see which are the top-resource consuming queries in the respective environments. It is also interesting to compare plans for the same query in different environment, to get a better understanding of what could be done to always be fast.