100% on single cpu in SMP host

ตอบทั้งหมด

• 26 กุมภาพันธ์ 2555 13:22

  ผู้ตอบ

   

   
  

  0
  ---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://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • ตอบ
  • อ้างอิง

   
• 26 กุมภาพันธ์ 2555 14:23

  ผู้ดูแล

   

   
  

  0
  What does your load look like? Perhaps it s one session, submitting query or queries which doesn't lend itself to parallelims?

  ---

  Tibor Karaszi, SQL Server MVP | web | blog

  • ตอบ
  • อ้างอิง

   
• 28 กุมภาพันธ์ 2555 2:52

   

   
  

  0

  It does appear to be a single query which is not lending itself to parallel execution. Looking at task manager performance of the different processors can see only 1 of the 16 at 100% rest being idle.

  Is there a query within sql server for instance dmv which will show cpu usage per processor, i.e what I see as usage on task manager performance screen?

  Reason for this is whilst we usually deem performance based on averages across multiple processors, a single cpu hitting 100% is also problematic. Want to be able to monitor and alert for these intances.

  Thanks

  • ตอบ
  • อ้างอิง

   
• 28 กุมภาพันธ์ 2555 7:38

  ผู้ดูแล

   

   
  

  1

  Hi SQL5,

  It may be that one processor is active for a particular operation. Without knowing more about what's actually running and what it’s doing, it's hard to say whether that is the case.

  Suggest you to see these two blogs written by Mat Stephen:

  Microsoft SQL Server Performance Top Tip: Multi Processor (SMP) Box Doesn’t Use All It’s Processors
  Microsoft SQL Server Performance Top Tip: Multi Processor (SMP) sudden death syndrome.

  At last, please check the article: Maximum Number of Processors Supported by the Editions of SQL Server. If your SQL Server edition is not Enterprise or developer, the SQL Server can at most support 4 processors.

  ---

  Best Regards,
  Iric
  Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 7 มีนาคม 2555 6:35
  •  
  • ตอบ
  • อ้างอิง

   
• 28 กุมภาพันธ์ 2555 10:09

   

   
  

  1
  In addition to the other recommendations, you should check that your server is not running under the balanced Power Plan. You want it to be running under the High Performance power plan.

  ---

  John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

  • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 7 มีนาคม 2555 6:35
  •  
  • ตอบ
  • อ้างอิง

   
• 28 กุมภาพันธ์ 2555 12:14

   

   
  

  0

  Thanks for the suggestions.

  Is there a query to see cpu utilisation per processor rather than overall cpu usage within sql server?

  Thanks

  • ตอบ
  • อ้างอิง

   
• 6 มีนาคม 2555 8:09

  ผู้ดูแล

   

   
  

  0

  SQL5,

  Please see this blog: How to find out the CPU usage information for the SQL Server process using Ring Buffers

  ---

  Best Regards,
  Iric
  Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 7 มีนาคม 2555 6:35
  •  
  • ตอบ
  • อ้างอิง