DNN platform executes the UpdateUser stored procedure in every login. In my experience, this procedure timeouts first when site is under heavy load. I have not seen timeouts from other DNN stored procedures. UpdateUser timeouts occur regularly and causes trouble to end users, essentially preventing them from logging in.
Users table is also used in practically all modules (for read queries), which may or may not increase locking contention with updates, depending on the queries. I have not found any way to optimize the Users table indexes from their defaults. I have tried the READ COMMITTED SNAPSHOT isolation level, arguably with some benefit, but not with a satisfactory result.
Before submitting a improvement request to JIRA, I'm curious to know if others have had the same issue. This problem is not easy to reproduce, and any fixes are complicated. Fixes would require splitting the Users table to multiple tables and splitting the UpdateUser procedure to more specific procedures, using inserts for audit data instead of updates etc. I don't think the procedure or the tables have changed much in DNN major versions.
For now, I don't know any other practical fix than increasing the database server performance. Using the SQL 2014 In-Memory OLTP sounds promising too. Right now, I'm having these issues with a 2-core, 32GB RAM server running SQL 2012 Standard. Processor load runs pretty high, so adding more cores would be the next step.