locked
CPU Usage ON DB level RRS feed

  • Question

  • Hello Scripting Guys,

    Is it possible to obtain expensive query results on Database level? The top expensive queries I am getting are server level, which is for each database. I have used sys.dm_exec_query_stats, sys.dm_exec_sql_text(sql_handle) and sys.dm_exec_query_plan(plan_handle). Is it possible to get the top expensive queries in a Database rather than in an instance? 

    Thanks in advance,

    D. 


    • Moved by Bill_Stewart Monday, April 1, 2013 2:09 PM Move to more appropriate forum
    • Edited by Kalman Toth Thursday, April 4, 2013 8:46 AM clarity
    Sunday, March 31, 2013 8:27 PM

Answers

  • You can use the following DMV query to filter on database. Ad-hoc queries do not have database reference.

    SELECT SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
        ((CASE statement_end_offset 
            WHEN -1 THEN DATALENGTH(ST.text)
            ELSE QS.statement_end_offset END 
                - QS.statement_start_offset)/2) + 1) AS statement_text,
    	 DB_NAME(dbid) AS db_name, 
    	 last_execution_time AS last_execution,
    	 QS.*, ST.*
         FROM sys.dm_exec_query_stats AS QS
         CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
    	 WHERE DB_NAME(dbid) = 'AdventureWorks2012'
    	 ORDER BY last_execution DESC;


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Edited by Kalman Toth Tuesday, April 2, 2013 7:25 AM
    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:32 PM
    Tuesday, April 2, 2013 7:25 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;

    --------------------------------------------------------
    SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
    cpu_count/hyperthread_ratio AS [Physical CPU Count], 
    physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
    FROM sys.dm_os_sys_info OPTION (RECOMPILE);

    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms], 
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);


    -- HIGH CPU ************
          -- Get Top 100 executed SP's ordered by execution count
          SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
          qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
          qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
          qs.total_worker_time AS 'TotalWorkerTime',
          qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
          qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
          DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
          FROM sys.dm_exec_query_stats AS qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
          WHERE qt.dbid = db_id() -- Filter by current database
          ORDER BY qs.execution_count DESC

    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

    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:32 PM
    Tuesday, April 2, 2013 9:10 AM

All replies

  • You should post this kind of question in teh SQS2008R2 forum as it is a database specific issue.

    The query is specifi to the current database;

    This will work:
    use <dbname>
    select * from sys.dm_exec_query_stats

    OR this:
    select * from <dbname>.sys.dm_exec_query_stats


    ¯\_(ツ)_/¯

    • Proposed as answer by Kalman Toth Monday, April 1, 2013 7:09 PM
    • Unproposed as answer by Kalman Toth Tuesday, April 2, 2013 5:56 AM
    Sunday, March 31, 2013 9:38 PM
  • You can use the following DMV query to filter on database. Ad-hoc queries do not have database reference.

    SELECT SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
        ((CASE statement_end_offset 
            WHEN -1 THEN DATALENGTH(ST.text)
            ELSE QS.statement_end_offset END 
                - QS.statement_start_offset)/2) + 1) AS statement_text,
    	 DB_NAME(dbid) AS db_name, 
    	 last_execution_time AS last_execution,
    	 QS.*, ST.*
         FROM sys.dm_exec_query_stats AS QS
         CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
    	 WHERE DB_NAME(dbid) = 'AdventureWorks2012'
    	 ORDER BY last_execution DESC;


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Edited by Kalman Toth Tuesday, April 2, 2013 7:25 AM
    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:32 PM
    Tuesday, April 2, 2013 7:25 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;

    --------------------------------------------------------
    SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
    cpu_count/hyperthread_ratio AS [Physical CPU Count], 
    physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
    FROM sys.dm_os_sys_info OPTION (RECOMPILE);

    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms], 
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);


    -- HIGH CPU ************
          -- Get Top 100 executed SP's ordered by execution count
          SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
          qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
          qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
          qs.total_worker_time AS 'TotalWorkerTime',
          qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
          qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
          DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
          FROM sys.dm_exec_query_stats AS qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
          WHERE qt.dbid = db_id() -- Filter by current database
          ORDER BY qs.execution_count DESC

    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

    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:32 PM
    Tuesday, April 2, 2013 9:10 AM