SQL Server Performance

SQL Server restarting itself and closing connections;

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jr81, Apr 9, 2008.

  1. jr81 New Member

    Hi, We have a 2 node active/passive 32 bit sql 2005 9.0.3200 cluster SERVER A. The total memory on the box is 8 GB, sql is allocated 6 GB. AWE is turned on. We have a monthly job that runs every month. The past weekend the job didn't finish. Coz every time the job started the SERVER A had 100% CPU, and it was in a hung state. After few minutes the sql will restart and the connections got killed and the server will be normal. This job has been running for months. Suddenly it failed last week. We added more memory from 4 GB to 8GB and turned AWE just before the job ran. After some trouble shooting and adding some indexes the job completed. I am really not sure whats the root cause of the problem, since the job has been running for months with a just 4 GB memory. would Turning AWE on cause the problem? coz we have a same copy of this database in another server (SERVER B) we ran the job in that server and it ran without a problem. The SERVER B had 4 GB total memory. we didn't add the index on SERVER B that we added on SERVER A.
    Also there were no messages that said the sql was running out of memory anywhere.
    These are the erros in the event log:
    Event Type: Error
    Event Source: MSSQLSERVER
    Event Category: (3)
    Event ID: 19019
    Date: 04/04/2008
    Time: 07:14:32 PM
    User: N/A
    Computer: XXXX
    Description:
    [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
    ..
    Event Type: Error
    Event Source: MSSQLSERVER
    Event Category: (3)
    Event ID: 19019
    Date: 04/04/2008
    Time: 07:14:32 PM
    User: N/A
    Computer: XXXX
    Description:
    [sqsrvres] OnlineThread: QP is not online.
    ..
    Event Type: Error
    Event Source: MSSQLSERVER
    Event Category: (3)
    Event ID: 19019
    Date: 04/04/2008
    Time: 07:14:32 PM
    User: N/A
    Computer: XXXX
    Description:
    [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]Communication link failure ..
    Event Type: Error
    Event Source: MSSQLSERVER
    Event Category: (3)
    Event ID: 19019
    Date: 04/04/2008
    Time: 07:14:32 PM
    User: N/A
    Computer: XXXX
    Description:
    [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.
    ..
  2. Luis Martin Moderator

    Someone had the same problem like you in MSDN and no answer yet.
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1490941&SiteID=1
    I think you have to follow from there. Just a thought.
  3. eleung New Member

    My understanding is that this is a sign that the server is under stress.
    Update the statistics on all the databases using sp_updatestats before running that job.
    I had this problem with our Windows 2003 64-bit cluster server with one instance of SQL Server 2000 32-bit and two instances of SQL Server 2005 64-bit installed. I was able to isolate the problem to the SQL Server 2000 32-bit and configure it with AWE with 6 GB RAM. Also created maintenance jobs to optimize the databases once a week and update stats daily.
  4. jr81 New Member

    The server is under stress,is that the reason why the sql is forced to restart ? i have never seen this behaviour before.
  5. eleung New Member

    It is the cluster server that force the restart of its resources, in this case the sql server.
    Did you run sp_UpdateStats and able to solve the problem?
  6. SQL2000DBA New Member

    In my opinion if sql server(even on cluster) is under stress and gets restart automatically will write some error event on Windows event viewer. Please try to monitor following memory counter to identify whether problem is related to memory or not.
    SQL Server: Memory Manager: Total Server Memory(KB)Amount of RAM SQL Server is consuming
    SQL Server: Memory Manager: Target Server Memory(KB)Amount of RAM SQL Server is willing to consumeNote: SQLServer:Memory Manager: Total Server Memory (KB) counter is less than the SQLServer:Memory Manager: Target Server Memory (KB) counter, then this means that SQL Server has enough memory to run efficiently. On the other hand, if the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory
  7. jr81 New Member

    i added few indexes and ran update stats and the server is performing at an optimal level. I ran the perfmon and collected the counters, everything looks normal.
    thanks
    jay
  8. satya Moderator

    What kind of scheduled job is running, which is causing 100% cpu in this case?
    Also check what other processes are running at the same time, as you have collected the stats from SYSMON(PERFMON) it may not report the internal issues of databases, in this case run SERVER SIDE TRACE to see what kind of connections are performing proceses.
    Also see relevant BLOG posts here http://sqlserver-qa.net/search/SearchResults.aspx?q=high cpu that will give you lead to resolve the issue.
  9. jr81 New Member

    The scheduled job is a big select statement, nothing very unusual. during the time the job ran there was no other process running at the same time. Also the windows server was NOT restarted at the same time i think only the sql server was restarted.
    thanks
  10. satya Moderator

    Ok, why not put that SELECT statement as a stored procedure, compile and execute via scheduled job?
    This is something I see as a performance issue causing HIGH CPU and other issues, also do you have any tools such as Anti Virus or Anti spyware on this machine?
  11. jr81 New Member

    The select statement is inside a stored procedure, and the scheduled job is executing the sp. This has been running for months as i have mentioned. There's no virus scan running during the time. only change that was done to the box was turn AWE on and after adding more memory and suddenly we started seeing the problems.
  12. ghemant Moderator

    Hi,
    I think you have to check Windows Event Viewer carefully for the unknow shutdown, their must be something written in it for this sudden shutdown/restart. I recall that some times back their was a patch released which is a work around for Buffer Overrun problem which leads to restart server, what I mean to say it could be your Windows server which got restarted and not SQL Server!! Is this server is exposed to internet!!

Share This Page