Does anyone have experience upgrading from SQL Server 2008R2 to 2016?
Our system was running fine on 2008R2 but after upgrading to 2016 we are experiencing high tempdb contention.
We see a large amount of blocking on PAGELATCH_SH waits for tempdb objects. PLE is 99.98%. MAXDOP is 6 (machine has 8 cores/NUMA). MAXDOP threshold is 50.
Random queries are taking upwards of 30 seconds to run but when I try running the same queries from SSMS they run sub-second, which verifies blocking as the issue. We are currently using 16 temp file and increasing to 34 tonight.
We discover today that tempdb log has an io_stall_write_ms over 200,000 ms, we are moving it to a SAN tonight, but I do not believe it explains the long waits for page latches.
I’ve reached the end of my ideas. Does anyone have any suggestions?
We had a similar issue and it turned out to be the new cardinal estimator. Try changing the compatibility mode for the DB back to 2008 and compare.