locked
Help Me RRS feed

  • Question

  • Hello,
    I want to inquire about server problem.
    Sometimes, ms-sql 2005 server is slow down with high CPU,MEMORY share rate so I shut the service down to reset. Then it is working properly.
    Do you know why it happens?
    Look forward your reply. Thanks :-)
    Wednesday, January 9, 2013 2:01 AM

Answers

  • To start what specs are on the box and how is sql configured. You will ned to make sure that the memory is configured inside SQL to match the hardware spec of the box, making sure you leave enough memory for the Operating system and any other applications that are running on it.

    Remembering that SQL Server will start with a small amount of ram allocated, and as the system is used stored procedures and such are buffered as they are executed.

    Wednesday, January 9, 2013 9:09 PM
  • sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    sp_configure 'max degree of parallelism', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    

    Try running the one above, or

    DECLARE @SPID INT;
    IF OBJECT_ID('tempdb..#who2') IS NOT NULL DROP TABLE #who2;
    CREATE TABLE #who2 
    (
    SPID INT, 
    Status	VARCHAR(256),
    Login VARCHAR(256),	
    HostName VARCHAR(256),	
    BlkBy VARCHAR(256),	
    DBName VARCHAR(256),	
    Command	VARCHAR(256), 
    CPUTime	INT, 
    DiskIO INT,
    LastBatch VARCHAR(256),	
    ProgramName	VARCHAR(256),
    SPID2 INT, 
    REQUESTID INT)
    
    INSERT #who2 EXEC sp_who2;
    SET @SPID = (
    SELECT TOP 1 (SPID) FROM #who2 
    WHERE DBName NOT IN ('msdb','master')
    ORDER BY CPUTime DESC)
    
    EXEC ('KILL ' + @SPID)

    running this one repeatedly until it tells you you can't kill yourself. You might also need to add the name of your distributor database if you are using one. The first will prohibit parallelism on your instance. The second will kill the process with the most cpu time. Neither are ideal solutions but both are solutions.

    Msg 6104, Level 16, State 1, Line 1

    Cannot use KILL to kill your own process.


    If you're happy and you know it vote and mark.

    Thursday, January 10, 2013 2:38 AM

All replies

  • You must troubleshoot the system with DMVs and perfmon logs.

    You may check whats running next time when the issue occurs. If there is any query running which takes high resource utilization, you may tune the query. Also use Perfmon log to understand how your resources are being utilized over a period.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, January 9, 2013 4:41 AM
    Answerer
  • ---This first thing to check if CPU is at 100% is to look for parallel queries:

    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, January 9, 2013 6:03 AM
    Answerer
  • I will say check high CPU & memory consuming queries with therir occuring wait types to start analyzing the issue.

    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, January 9, 2013 3:20 PM
  • To start what specs are on the box and how is sql configured. You will ned to make sure that the memory is configured inside SQL to match the hardware spec of the box, making sure you leave enough memory for the Operating system and any other applications that are running on it.

    Remembering that SQL Server will start with a small amount of ram allocated, and as the system is used stored procedures and such are buffered as they are executed.

    Wednesday, January 9, 2013 9:09 PM
  • sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    sp_configure 'max degree of parallelism', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    

    Try running the one above, or

    DECLARE @SPID INT;
    IF OBJECT_ID('tempdb..#who2') IS NOT NULL DROP TABLE #who2;
    CREATE TABLE #who2 
    (
    SPID INT, 
    Status	VARCHAR(256),
    Login VARCHAR(256),	
    HostName VARCHAR(256),	
    BlkBy VARCHAR(256),	
    DBName VARCHAR(256),	
    Command	VARCHAR(256), 
    CPUTime	INT, 
    DiskIO INT,
    LastBatch VARCHAR(256),	
    ProgramName	VARCHAR(256),
    SPID2 INT, 
    REQUESTID INT)
    
    INSERT #who2 EXEC sp_who2;
    SET @SPID = (
    SELECT TOP 1 (SPID) FROM #who2 
    WHERE DBName NOT IN ('msdb','master')
    ORDER BY CPUTime DESC)
    
    EXEC ('KILL ' + @SPID)

    running this one repeatedly until it tells you you can't kill yourself. You might also need to add the name of your distributor database if you are using one. The first will prohibit parallelism on your instance. The second will kill the process with the most cpu time. Neither are ideal solutions but both are solutions.

    Msg 6104, Level 16, State 1, Line 1

    Cannot use KILL to kill your own process.


    If you're happy and you know it vote and mark.

    Thursday, January 10, 2013 2:38 AM
  • Thomas,

    Whilst i can understand the end result of your proposed solution(s) might be similar to what the op is doing with service stop and start, why would you suggest something like that. It is almost as saying, switch the machine off and leave it off.

    there is no auditing, no accountability, just kill everything that doesnt have master or msdb?

    I have seen many instances where development took place in the master db.

    Why would we try to kill processes that may or may not be running ?

    why should we attempt to kill processes within bellow the 50 range?

    etc...

    But perhaps more important, how would we find the problem and fix it?

    Perfmon, logs (both sql and eventlogs) , dmvs and other suggestions have already been mentioned correctly and are great starting points.

    Regards

    Friday, January 11, 2013 9:32 AM