locked
Database wise CPU usage and Index fragmention Checking RRS feed

  • Question

  • Hi all

    I s it possible to check CPU usage and Index fragmentation Database wise in SQL Server 2005,

    I know how to use performance Dash board, but here I am getting TOP SQL which is using more CPU.

    I want compare usage with other environment, Please some help me to find out

    CPU usages database wise and Index fragmentation Database wise.

     

    Thanks in advance

     


    SNIVAS
    Monday, March 7, 2011 5:36 PM

Answers

  • SNIVAS

    For CPU I use Adam's usage

    http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx

    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;

     

    For fragmentation

    SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
    (
      DB_ID('testdb'),
      OBJECT_ID('dbo.T1'),
      1,
      NULL,
      NULL
    );

    I got the result 96.5217391304348, meaning that there are over 96
    percents of out-of-order pages. If you need further proof that the
    pages do not necessarily reside in the file in the same order as in the
    linked list, use DBCC IND to show the actual pointers between the
    pages in the linked list:


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by WeiLin Qiao Sunday, March 13, 2011 6:42 AM
    • Marked as answer by WeiLin Qiao Tuesday, March 15, 2011 11:33 AM
    Tuesday, March 8, 2011 6:50 AM
    Answerer