Sql-server – Troubleshooting TempDB for Performance Bottlenecks

^{(Note: this isn't a 100% certain answer, and I don't have references, so let me know if you can prove otherwise.)}

The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. That simply wouldn't work if "old" versions within one database were cleared out in the middle of another database's snapshot transaction.

So, if there's a very old open transaction, the version store will not be able to clear until that transaction either commits or rolls back.

_{Community wiki answer:}

Please ignore the sleep and queue waits. These should be filtered out as they are always going to be high, by definition. They collect a minute of wait every minute. PAGELATCH tells me it can't keep up. That said, I don't see that as your major problem here.

For latch contention, you might consider this whitepaper. However, I'd be much more concerned about the OLEDB and MSQL_DQ waits. Are you doing a ton of cross-server stuff? Can any of this be consolidated, or are these things communicating with non-SQL Server RDBMS platforms?

I don't think 2008 -> 2008 R2 should cause any severe issues, but you may want to be sure that you have updated statistics everywhere (with FULLSCAN, preferably during a maintenance window), as this is a common cause for plan/perf degradation after an upgrade or migration.

But I would also strongly consider getting on the most recent branch and installing the most recent CU available. You should also keep in mind that mainstream support for 2008R2 ends in two months.

And while I hesitate to advocate Thomas' line of thinking that a clustered GUID is always better than an IDENTITY column, given that you have Fusion IO and your latches are still occasionally exceeding 2 seconds, you may need to consider redesign, because obviously throwing fast I/O at the problem isn't solving anything.

See this post and this post for some edge case enlightenment (again, please don't consider this an endorsement of "heaps > clustered index" or "GUID > identity").