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