Well, to start off, it's the function "UpdateHitCounter" that's proximally causing the problem, but you don't know whether this is a cause or an effect. Your query is exceeding the configured time allotted to complete.
Whenever you have a poorly performing database, a broad stroke approach you can run on it is to attach Sql Profiler to it, and then execute the Index Tuning Wizard against the results.
If you want to take a more measured, triage approach, you can restrict the profiler to log only queries that take longer than x seconds to complete, whatever you think x should be. I usually start out at 5 and work down from there if nothing shows up. Here is a primer on that topic.
Once you identify the long running queries, execute them in a local copy and examine the execution plans. Here is a primer for that, but to start out look for "table scan".
Ultimately, either your database is suboptimal, or your hardware isn't up to the traffic. It's almost certainly the first, and these two approaches should get you on your way.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…