locked
SQL consuming CPU amd memory RRS feed

  • Question

  • Hi.. Seems the SQL is taking most of the memory and the cpu. When checked in the task manager, it shows 95+ values for both cpu amd memory with respect to sql process.

    Need to check what is the culprit in the SQL that causes so much high utiliztion in cpu ad memory.

    IS tat anything you cud help to find the culprit causing this ..?

    Friday, June 25, 2010 10:41 AM

Answers

  • Run the below query and find the culprit SPID causing the CPU and memory acitivity

    ***************************************************************************

    USE master


    GO

    BEGIN
        -- Do not lock anything, and do not get held up by any locks.
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

        -- What SQL Statements Are Currently Running?
        SELECT [Spid] = session_Id
     , [Database] = DB_NAME(sp.dbid)
     , [Status] = er.status
     , [Wait] = wait_type
     , [Individual Query] = SUBSTRING (qt.text,
                 er.statement_start_offset/2,
     (CASE WHEN er.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE er.statement_end_offset END -
                                    er.statement_start_offset)/2)
     ,[Parent Query] = qt.text
     , Program = program_name
     , Hostname
     , nt_domain
     , start_time
    , ecid
    , [User] = nt_username
        FROM sys.dm_exec_requests er
        INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
        CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) as qt
        WHERE session_Id > 50              -- Ignore system spids.
        AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
        ORDER BY 1, 2
    END

    ***************************************************************************

    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 5:46 AM
    • Marked as answer by Tom Li - MSFT Sunday, July 4, 2010 11:39 AM
    Friday, June 25, 2010 12:26 PM
  • The high memory usage is normal:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    The high CPU usage generally is not.  The primary cause of high CPU usage that I see is a lack of appropriate indexing in the database causing the system to perform a lot of table scan operations.  The first thing I would look at is whether or not you have indexes on your foreign key columns used in joining tables together. 

    What version of SQL Server is this?  (SELECT @@VERSION)

     


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 5:46 AM
    • Marked as answer by Tom Li - MSFT Sunday, July 4, 2010 11:39 AM
    Friday, June 25, 2010 1:49 PM

All replies

  • Run the below query and find the culprit SPID causing the CPU and memory acitivity

    ***************************************************************************

    USE master


    GO

    BEGIN
        -- Do not lock anything, and do not get held up by any locks.
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

        -- What SQL Statements Are Currently Running?
        SELECT [Spid] = session_Id
     , [Database] = DB_NAME(sp.dbid)
     , [Status] = er.status
     , [Wait] = wait_type
     , [Individual Query] = SUBSTRING (qt.text,
                 er.statement_start_offset/2,
     (CASE WHEN er.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE er.statement_end_offset END -
                                    er.statement_start_offset)/2)
     ,[Parent Query] = qt.text
     , Program = program_name
     , Hostname
     , nt_domain
     , start_time
    , ecid
    , [User] = nt_username
        FROM sys.dm_exec_requests er
        INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
        CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) as qt
        WHERE session_Id > 50              -- Ignore system spids.
        AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
        ORDER BY 1, 2
    END

    ***************************************************************************

    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 5:46 AM
    • Marked as answer by Tom Li - MSFT Sunday, July 4, 2010 11:39 AM
    Friday, June 25, 2010 12:26 PM
  • The high memory usage is normal:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    The high CPU usage generally is not.  The primary cause of high CPU usage that I see is a lack of appropriate indexing in the database causing the system to perform a lot of table scan operations.  The first thing I would look at is whether or not you have indexes on your foreign key columns used in joining tables together. 

    What version of SQL Server is this?  (SELECT @@VERSION)

     


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 5:46 AM
    • Marked as answer by Tom Li - MSFT Sunday, July 4, 2010 11:39 AM
    Friday, June 25, 2010 1:49 PM
  • its SQL 2005 running on WIn 2008 64 bit...

    Also for memory, i seem to see w3wp service that is realtion to IIS(Reporting Services) is hugely taking more memory...

    Friday, June 25, 2010 5:00 PM
  • Hey.. Thanks a lot Murali for your script... It would be still great if you could help me which column in that we need to check for confirming that spid is doing the culpirt job as i am not able to specifically check in for cpu nor memory.... Thanks in advance...
    Friday, June 25, 2010 5:02 PM