question

CirqueDeSQLeil avatar image
CirqueDeSQLeil asked

CPU Performance Information

What are three different methods of collecting cpu metrics for SQL Server?

seeder-questionperformance-metricsfor-fun
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLMaster avatar image
SQLMaster answered

If you are using SQL Server 2008 then Management Data Warehouse (MDW) framework aka Performance Studio will do work for you! FYI http://blogs.technet.com/rob/archive/2008/06/20/sql-server-2008-performance-studio.aspx & http://msdn.microsoft.com/en-us/library/dd939169.aspx links.

As you have asked 3 different methods:

  • first and foremost one is PERFMON (SYSMON) for CPU related counters.
  • secondly DMVs (DBA friend) for instance: sys.dm_os_sys_info & sys.dm_os_ring_buffers

SELECT TOP 10 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,* FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)

  • third one using SMO.

I would recommend Baseline & Benchmarking of such monitoring process http://sqlserver-qa.net/blogs/perftune/archive/2007/12/19/2981.aspx link.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

I listen to the fan noise, watch the blinking lights, and feel the cables for warmth. ;-)

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 because it was worth the laugh :)
0 Likes 0 ·
Finally a solution that makes sense. I'm off to the server room to institute the new methodology. Thanks Jeff!
0 Likes 0 ·
As funny as it is, I actually used to do that. Much like walking through the equipment room on the "boats", I'd walk through the server/comm room before my "watch". Fans would fail frequently back then and I was always the first to catch it. I had to check lights on the new "San thingy" we got. If the whacko UPS wasn't working right, I-squared-R would go up and the cables would actually get warm. I could usually guess what Hz was being pumped out by the warmth of the cables and the fan noise. We didn't have network monitoring so I did a visual for network collision lights, etc, ect. ;-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.