SQL Server Performance

SQL Processes and SQLDMO

Discussion in 'T-SQL Performance Tuning for Developers' started by kerroppa, Jan 9, 2003.

  1. kerroppa New Member

    Hi all,

    Our ASP scripts send SQL statements (as a stored procedure) to SQL Agent to process the requested jobs in the background. After the job execution, the Process ID stays in the server and waits for next command with sleeping status. Since this process does not go away, next job execution adds another process and eventually, the server is overloaded with these processes and dies. (Under Process Info of SQL Svr Enterprise Management, there are hundreds of process IDs with sleeping status and awaiting command status. Most of these process are executed by SQLDMO because Application column indicates as SQLDMO_xxx (xxx denotes for digits).)

    We make sure of opening and closing the connectinos from the scripts, but this problem does not go away. Does anyone have any ideas how to solve this problem? How come those processes don't go away after the job execution? Any comments would be greatly appreciated.

    BTW, we use SQL Server 2000.

    Thanks in advance,
    kerroppa
  2. satya Moderator

    First of all you need to control the transaction from the ASP script and capture the trace using PERFMON and PROFILER which gives better information where its lacking.

    BTW, what are the memory settings on the server, any information from SQL error log during this process.


    Satya SKJ
  3. bradmcgehee New Member

    I agree with satya, you need to do a Profiler trace to help identify what is going on, as it is not normal.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. kerroppa New Member

    thanks. We'll start by tracing the problem with PROFILER...

    kerroppa
  5. pal123 New Member

    Hi,
    I have the exact same problem.
    I have a VB application that runs on a client machine and inserts records to the SQL server db running on another machine.

    When I run this VB application, new process ids are spawned continously until the client application dies. All these processes are in sleeping status and awaiting command. when I kill the client application, all these spids go away and start spawning again as soon as the client application is restarted.

    In the VB client application, I open the connection, insert a claim information (1 or more records to several tables) and then close the connection. I gather the next claim information, open the connection to the db, insert the claim into the db and then close the connection again. I do this for about 2000 claims in an hour.

    I welcome any suggestions that would fix this problem.
    Thanks
    Mahipal
  6. bradmcgehee New Member

Share This Page