PAGELATCH_EX on 2:1:128
Apparently we were able to solve the problem. There is one particular UDF that is being called thousands of times per second, and it declares 3 table variables. For most of the calls, the UDF returns before declaring the tables, but I did not realize that SQLServer creates them anyway. We separated the second part of the query to a new UDF that is called only when necessary. This way, most of the times, the table variables are not created (obtained from cache to be more precise). By doing this, we were able to significantly reduce the perf index Active Temp Tables, and SQL Server returned to normal behavior.

For those that did not know this: if an ad-hoc query, PROC or UDF declare a table variable (or a temporary table), SQL Server creates the tables, before starting execution. So, an early return does not prevent temp tables from being created. This completely fooled this accidental DBA. I was trying to find the culprit by changing queries one by one, putting a return in the beginning. Because of this behavior (which is understandable), I was failing to find the root cause of the contention on the DB. Live and learn every day!

For those who are now saying that it does not make sense to have a query called so many times, you are right! We are already changing the application to cache the results outside SQL Server.

Thank you Aaron and RTL for your help.

Renato Ferreira