none
Find High CPU Query

    Question

  • My production SQL Server 2008R2 nearly always 100% CPU usage. How can I find which query makes this? Any SQL scripts or 3rd party tools can be used to troubleshoot this problem?
    Thursday, March 7, 2013 11:39 AM

Answers

  • In SQL Server Management Studio run the "Performance - Top Queries By Total CPU Time" report.


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by nonno Thursday, March 7, 2013 11:57 AM
    Thursday, March 7, 2013 11:53 AM
  • I got this from the internet:

    SELECT TOP 50
    	[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
    	[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
    	[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
    	[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
    	qs.execution_count,
    	[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
    	[Total I/O] = total_logical_reads + total_logical_writes,
    	Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
    		(
    			(
    				CASE qs.statement_end_offset
    					WHEN -1 THEN DATALENGTH(qt.[text])
    					ELSE qs.statement_end_offset
    				END - qs.statement_start_offset
    			) / 2
    		) + 1
    	),
    	Batch = qt.[text],
    	[DB] = DB_NAME(qt.[dbid]),
    	qs.last_execution_time,
    	qp.query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    where qs.execution_count > 5	--more than 5 occurences
    ORDER BY [Total MultiCore/CPU time(sec)] DESC

    • Marked as answer by nonno Friday, March 8, 2013 3:13 AM
    Friday, March 8, 2013 3:13 AM
  • Check this URL:

    http://www.dbrnd.com/2015/06/sql-server-cpu-usage-per-different-objects/

    You can find one script to find CPU Usages per different SQL objects. 


    • Edited by Anvesh M. Patel Monday, August 3, 2015 9:57 AM
    • Marked as answer by nonno Wednesday, August 5, 2015 6:44 AM
    Monday, August 3, 2015 9:37 AM
  • ---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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by nonno Wednesday, August 5, 2015 6:44 AM
    Monday, August 3, 2015 9:48 AM
    Answerer

All replies

  • Hi,

    Please find below the script

    --CPU Pressure 
    SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
    AS [%signal (cpu) waits],'If % Signal Wait >25% then we have some CPY Pressure' as [%signal (cpu) waits Optimal Value],
    CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
    AS [%resource waits]
    FROM sys.dm_os_wait_stats OPTION (RECOMPILE);

    Thanks

    Deepak

    Thursday, March 7, 2013 11:40 AM
  • Also enable MAXDOP query hint if you know the query.
    Thursday, March 7, 2013 11:42 AM
  • In SQL Server Management Studio run the "Performance - Top Queries By Total CPU Time" report.


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by nonno Thursday, March 7, 2013 11:57 AM
    Thursday, March 7, 2013 11:53 AM
  • I got this from the internet:

    SELECT TOP 50
    	[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
    	[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
    	[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
    	[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
    	qs.execution_count,
    	[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
    	[Total I/O] = total_logical_reads + total_logical_writes,
    	Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
    		(
    			(
    				CASE qs.statement_end_offset
    					WHEN -1 THEN DATALENGTH(qt.[text])
    					ELSE qs.statement_end_offset
    				END - qs.statement_start_offset
    			) / 2
    		) + 1
    	),
    	Batch = qt.[text],
    	[DB] = DB_NAME(qt.[dbid]),
    	qs.last_execution_time,
    	qp.query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    where qs.execution_count > 5	--more than 5 occurences
    ORDER BY [Total MultiCore/CPU time(sec)] DESC

    • Marked as answer by nonno Friday, March 8, 2013 3:13 AM
    Friday, March 8, 2013 3:13 AM
  • Check this URL:

    http://www.dbrnd.com/2015/06/sql-server-cpu-usage-per-different-objects/

    You can find one script to find CPU Usages per different SQL objects. 


    • Edited by Anvesh M. Patel Monday, August 3, 2015 9:57 AM
    • Marked as answer by nonno Wednesday, August 5, 2015 6:44 AM
    Monday, August 3, 2015 9:37 AM
  • ---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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by nonno Wednesday, August 5, 2015 6:44 AM
    Monday, August 3, 2015 9:48 AM
    Answerer
  • God bless you! I was searching for this last 3 days!
    Monday, April 9, 2018 11:15 AM