locked
How to know CPU usage by all the process(SPID)? RRS feed

  • Question

  • How to know CPU usage by all the process(SPID)?

    One of our SQL Server using high CPU. Is there any whay to list all the SPIDs with the CPU usage?

    Friday, August 3, 2012 6:24 PM

Answers

  • http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

    /*CPU*/
    EXEC dbo.sp_WhoIsActive
    @get_transaction_info=0,
    @output_column_list ='[session_id][start_time]
                      [cpu][status][context_switches][wait_info][program_name]
                     [database_name][sql_text][host_name][open_tran_count]', 
    @sort_order='[CPU]DESC'

    /*delta*/
    EXEC dbo.sp_WhoIsActive
    @delta_interval=5, @get_task_info = 2,
    @output_column_list ='[session_id][start_time][context switches]
                      [CPU_delta][reads_delta][writes_delta][tempdb_writes_delta]
                      [tempdb_reads_delta][tempdb_current_delta]
                      [database_name][host_name][login_name]', 
    @sort_order='[CPU_delta]DESC'


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

    • Proposed as answer by Chintak Chhapia Tuesday, August 7, 2012 12:20 PM
    • Marked as answer by amber zhang Monday, August 13, 2012 1:30 AM
    Tuesday, August 7, 2012 11:16 AM
    Answerer

All replies

  • Hi,

    I have recently come across this site, Hope it will help you to meet the requirement regarding CPU usuage.


    Thanks & Regards, Pramilarani.R

    Friday, August 3, 2012 6:34 PM
  • How to know CPU usage by all the process(SPID)?

    One of our SQL Server using high CPU. Is there any whay to list all the SPIDs with the CPU usage?

    The exact way is to use Profiler to record stuff as it happens.

    You can probably use various DMVs to get counts while they are active, but if you want by SPID rather than by statement, overall I don't know ... well, actually, sp_who2 gives you a session total, so look at the code it uses to compute the number.  Or just run sp_who2 without looking at the code!

    The link Pramilarani gives is interesting, but seems to provide only overall stats, not by SPID or query.

    Josh

    ps - actually sp_who2 is old code, and there is a simple dmv that might help:

    select 
      session_id, cpu_time
    from sys.dm_exec_sessions
    where session_id>50;


    • Edited by JRStern Friday, August 3, 2012 7:34 PM
    Friday, August 3, 2012 7:15 PM
  • Thanks. You also replied my other question about Log space usage. Actually I’m trying to capture resource usage by all the SPIDs at one place. Like below.

    Transaction log file space usage by all SPIDs

    Tempdb space usage by all SPIDs

    Memory usage by all SPIDs

    Disk I\O by all SPIDs

    Start time and elapsed time of a SPID

    CPU usage by all SPIDs

    This looks more like a monitoring tool but wanted to create and keep handy these scripts to avoid installing any tool.

    Thanks

    HYD DBA

    Friday, August 3, 2012 7:48 PM
  •  Hello HyDBA, If you are using SQL server 2008, there are standard reports  which you could leverage from monitoring perspective.

     the path is: - right click on the instance name in the management studio and click on reports and then standard reports.

    HTH!

     

     


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Tuesday, August 7, 2012 9:29 AM
  • Ever heard of PROCESS EXPLORER? Its a sysinternals tool (or was, rather, now it's microsoft => download :  http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx.) 

    You can lookup your sql server process there, double click it, then select Tabpage Threads and voila, Processor usage per Kpid:

    And then these querys should get you all you need to know: 

    Get spid for kpid (enter kpid at the back)

    SELECT spid, kpid,cpu, memusage FROM sysprocesses WHERE kpid=<ProcessexplorersTID>

    Get running sql ..  :

    DBCC inputbuffer(SPID)

    Hope this helps !


    Www.visionsofnight.com

    Tuesday, August 7, 2012 11:06 AM
  • http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

    /*CPU*/
    EXEC dbo.sp_WhoIsActive
    @get_transaction_info=0,
    @output_column_list ='[session_id][start_time]
                      [cpu][status][context_switches][wait_info][program_name]
                     [database_name][sql_text][host_name][open_tran_count]', 
    @sort_order='[CPU]DESC'

    /*delta*/
    EXEC dbo.sp_WhoIsActive
    @delta_interval=5, @get_task_info = 2,
    @output_column_list ='[session_id][start_time][context switches]
                      [CPU_delta][reads_delta][writes_delta][tempdb_writes_delta]
                      [tempdb_reads_delta][tempdb_current_delta]
                      [database_name][host_name][login_name]', 
    @sort_order='[CPU_delta]DESC'


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

    • Proposed as answer by Chintak Chhapia Tuesday, August 7, 2012 12:20 PM
    • Marked as answer by amber zhang Monday, August 13, 2012 1:30 AM
    Tuesday, August 7, 2012 11:16 AM
    Answerer
  • Hi HYDBA,

    If you are running Multiple SQL instances in a single server then there may be multiple sqlsrvr.exe's in your task manager and these each PID will indicate an individual SQl instance.

    Now if you are running using a SQL service account (different accounts to different SQL's hosted) the users through which these processes will be running will be different..So through that you will be able to identify each SQL instance and each the process and Memory it is taking from the task Manager..

    If you want to analyze more...and want to have a detailed analysis... You can go to the process object in the Perfomance monitor..where you can store and analyze the logs..afterwards...

    And for analyzing the Memory and Disk I\O i guess you must be having Max Memory option and separate disks for each instance as you are hosting multiple SQLs over the same server.. So the analysis will simplified...

    Thanks

    Hemanth..

    Tuesday, August 7, 2012 11:50 AM