January 14, 2008 at 9:11 am
Users are complain off poor performance and getting chucked out of the application (due to dead locks). This problem has just come up in the last few days. Dead locks are occurring regularly less than every minute. Have looked into it created a few indexs and it has reduced dead looks to every few minutes.
Using perfmon CPU usage is very low 20-30% memory is not being hit I/O reads looks okay. Everything looks good!!
Any ideas as what could be coursing the problem where do I go from here? What could course it to suddenly go wrong.
January 14, 2008 at 11:19 am
After executing DBCC DBREINDEX command also try executing
Update Statistics 'Table name' with fullscan.
This would definitely reduce your dead locking issue.
SQL DBA.
January 14, 2008 at 12:47 pm
Can you use SQL Profiler to find out why the dead lock occurred?
January 14, 2008 at 1:31 pm
Couple suggestions.
1. Make your transactions as short as possible.
2. Include NO_LOCK clause in your SELECT statement (or change your isolation level in the SELECT statement).
January 15, 2008 at 12:01 am
Firstly, see if anything's changed in tha last few days. Any code changes, large data loads, hardware changes, etc, etc
Rebuild the indexes if they're fragmented. If you do a rebuild, you don't need to update the stats afterwards, as stats are updated during an index rebuild.
To find the slow procs and queries, run sQL profiler for an hour or so. I would suggest you capture the RPC:Completed and SQL:BatchCompleted events and filter for duration >1000. That will give you all the queries taking more than a second.
Re the deadlocks, switch traceflag 1204 on (DBCC TRACEON(1204,-1)). With the traceflag on, SQL will write a deadlock graph into the error log any time it encounteres a deadlock. The graph will tell you what the two (or more) processes were that were involved in the deadlock, what statement each was running and the resources that they were deadlocked over.
Once you've found the poor queries, then it's a matter of optimising them either by rewriting the queries, if they're written badly, or tweaking indexes.
Does that help at all?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2008 at 2:16 am
HI thanks for that I have turned on the trace flags that’s how I found the tables to reindex. I am having problems finding out the tables involved in the RPC processes. The rate at which the dead locks occur is much less now but still every few minutes. What I don’t understand is why this has suddenly become an issue, the server CPU is very low at 30-40%(8 cpu and 8 memory). We have the same application running on the same type of server with no problems with the cpu around 70%-80%. What I don’t understand is why this server suddenly has problems. By the way I regularly update the indexes.
January 15, 2008 at 2:36 am
RPC Process? Did you identify that from profiler or from the deadlock graph?
Could you post the deatils that you have?
PRC calls are usually stored procedures. Means you'll have to dig into the proc to find what it does.
As for the sudden onset..
Has anything changed? Hardware, procedure changes, data volumes, index changes?
Has the database recently been shrink (or have auto shrink enabled)
If you have lots of locking (which you probably do, seeing you have deadlocks) then the CPU will be lower than usual, because most processes are waiting for a lock
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2008 at 2:14 pm
The presence of a deadlock does not imply your server is overworked - in fact they're not even correlated.
You said you had your database running on another server which does not exhibit the problem. Do the users there make use of the application in a different way? Presumably they do. If so, how is their usage pattern different?
Do you have users running lengthy reports whilst other users are performing transactions?
January 16, 2008 at 10:24 am
thanks for that I have done all usual checks and have failed over to a different node and the problem has gone away. The serves are meant to be the same. Obviously not. So far no one has been able to identify any differences. Some thing weird has happened.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply