SQL Server Performance

SQL SERVER 2000 Deadlock issues

Discussion in 'Performance Tuning for DBAs' started by auppal80, Oct 16, 2007.

  1. auppal80 New Member

    We are using SQL Server 2000. In production we are facing deadlock problems.
    If we try to run a trace then users starts complaining that there are not
    able to get into the application. What do you think are the options that we
    have to trace down deadlock issues?
    We have hundreds of stored procedures, triggers.
    So, we do not know from where it is coming and why?
    If we use the flags:
    DBCC TRACEON (3604)
    DBCC TRACEON (1204
    Will that impact performance of SQL Server 2000? Is using flags same
    As using trace in profiler.
    I would really appreciate your help.
  2. thomas New Member

    Running those trace flags (as startup parameters), will not impact your users or your application. This will print deadlock information in the sql server error log. This information will be very valuable for you in troubleshooting the deadlocks.

    Deadlocks are very difficult to sort out, I am grappling with some of my own right at the moment.

    Useful links:-
    http://www.sqlservercentral.com/articles/Performance Tuning and Scaling/tracingdeadlocks/1324/

    http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx
  3. auppal80 New Member

    thanks for the help. So turning the trace files on will not have any impact on performance.
    I read some where that it may slow down database.
  4. Greg Larsen New Member

    There is some overhead, but it is minimal. Do not monitor for every possible event, be selective in what you monitor.
  5. rohit2900 Member

    Hi,
    Today our production server was hanged cos.....the number of current users was increasing and it was around 300+, but in normal case the number of users are around 30-35, I open the server through VNC and then tried to connect it through enterpirse manager and query analyzer but it gave network error it was something like
    "Microsoft SQL server network error : check your network document"
    I was physically working on the production server. I checked the services all were running fine I tried it for 10 mins but when I failed. I restarted both the Sql server and Sql server agent services and after that everything was fine and when I connected through enterprise manager their were around 10 process in the current activity that were showing as blocked by some other process, I manually killed those processes and everything was fine but my question is like how can I know the reason for this, so that I can take preventive measures to avoid it in future. This thing happened last months also and it people restarted the server and everything was fine.
    Can anybody help in in this regard?
    Thank You,
    Rohit Paliwal
  6. dilan New Member

    Please post this as a separate topic.
    Sounds like the SQL server was not listening on the expected ports. One thing you can try in this situation is to telnet to <sqlserver> port 1433 (or whatever port is configured in SQL server) and see if you can make a successful connection and work you way up such as telnet from within your VNC connection to port 1433 etc..
    Dilan
  7. venkatesanj@hcl.in New Member

    Have you tried by tracing on the flags with numbers 1204 and 3605.
    dbcc traceon(1204,1)
    dbcc traceon(3605,1)
    After placing the trace, restart the server and check for error log to idenctify the server process id to kill it.
    Dont kill all the resources or process. Instead, killing specific process may resolve your problem.
    Regards,
    Venkatesan Prabu .J
  8. satya Moderator

Share This Page