Looks like today is going to be another rubbish one. We have recently updated our sql box with a complete monster, with loads of cores and ram, however we are stuck with our old DB schema which is crapola.
Our old sql box had problems but nothing like what we are experiencing with the new one, although on the day of rolling out it was running super fast, within a week it's a complete mess...
Our .net app used by a couple of hundred people or so is generating a huge amount of deadlocks and timeouts on the SQL box and we are struggling to work out why. We have checked all the indexes and they are as good as they can be right now. Some of the major tables are way too wide and have a stupid amount of triggers on, but there is nothing we can do about this now.
A lot of the pids seem to be the same for the same users who are trying multiple times.
So for instance...
User: user1
Time: 09:21
Error Message: Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
User: user1
Time: 09:22
Error Message: Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
etc....
When we moved the db to the new box it was backed up from the old and restored to the new.
If anyone has any suggestions as to something we can do I will buy them multiple pints.