Queries not Running in Prod

Question

I've an update statement which uses 2-3 tables. It runs in less than a second in QA but it takes forever in Prod. The number of records are exactly same and all the indexes are same too. In fact Prod server is an stand alone box while QA is a VM and have Dev,QA and Stage SQL Server instances on one server. So in that way Prod is better equipped than QA or Stage as it has more memory and more h/w.

And it's not just one update statement which has this issue. There are some other selects too which runs in lower environments but they don't move at all in Prod. I tried these codes to run when no one is on Prod.

My dba restored the prod copy to another instance. where no user has any access. And then he tried to run the same query. Again, it runs in QA but not in prod and not in the new instance. What could be the possible things which We should look for?

The update I'm running is -

UPDATE d Set Status = 'Active' 
FROM DimesionTable D 
INNER JOIN StageDB.dbo.StageTable a on d.PublicID = a.PublicID 
WHERE EXISTS(SELECT 1 FROM StageDB.dbo.Table1 p 
     INNER JOIN Table2 d ON p.PublicID = d.PublicID 
     WHERE p.RetroFlag = '' 
     AND p.AccountID = a.RowNumber 
     AND d.IsActive = 1 
     AND (p.CancellationDate is NULL 
     OR p.CancellationDate > GETDATE() 
     OR p.CancelFlag = 'Cancelled') 
AND GETDATE() BETWEEN p.StartDate AND p.EndDate) AND d.AccountID > 0

I'm ruling out blocking as I tried in off hours in Prod as well as in the new environments where no one has any access. I looked in sys.configurations, and the only differences I found are:

max degree of parallelism - Prod - 8      | QA - 0
max server memory (MB)    - Prod - 472064 | QA - 256000

Both servers have 8 CPUs. I double checked all the involved tables and all have the same indexes. Still the query plans are different. And the suggested index by SQL Server is different too. My understanding is - if the tables has exact same structure, then query plan and suggested indexes should be same.

Indexes and statistics are getting updated nightly. And data distribution is also exactly same. No difference at all. We restored the prod database on a separate instance and there too we have the same issue. In fact, it's not just this query, there are other queries too which is having the same problem. I'm more concerned about something wrong with the server rather than the query. All these queries run in just few secs or a min or two in QA but they won't finish at all in prod or the new test environment restored from Prod.

Tempdb configuration:

QA   - Size - 122880 MB, Available - 97277 MB
Prod - Size - 174080 MB, Available - 148476 MB

Total 8 files in each server.

• QA - 1st one's initial size is 25600 and rest 10240, auto growth - 5120 MB unlimited
• Prod - 1st one's initial size is 76800 and rest 10240, auto growth - 15% unlimited

Initially STG was created from QA and it used to run on STG. But when we created STG from PROD, it's not running there either. The compatibility is set to 120 on both the instances.

Answer 1

Two likely possibilities.

Blocking

Your production machine probably has a lot more going on than your test/dev machines. You could simply be seeing blocking. Run your query in one window and this in another.

SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0

If you are being blocked you may need to look at what's blocking you and how long it's taking to run.

Configuration differences

This one covers a LOT of possibilities. It could be a difference in the max memory as Kin suggested or any number of other possibilities. See:

difference in execution plans on UAT and PROD server

For example if the max degree of parallelism is 1 or the cost threshold for parallelism isn't the same on both servers you could be getting a single threaded plan instead of getting a parallel plan. It could even be the reverse. I had one case where the parallel plan was quite a bit slower than the single threaded plan.

Compare the results of this query between the two servers:

SELECT * FROM sys.configurations

Check any differences and see if they are affecting your query.