Post reply

Monitor CPU with Extended Events

  • February 25, 2013 at 10:07 am

    #394251

    Hello,

    We are undergoing a consolidation effort and desire to monitor CPU usage to help evaluate which workloads to consolidate and monitor as consolidation continues. We're looking at extended events as a way to capture queries that are CPU intensive.

    From what I've seen, you can capture ms of CPU usage, but is there any exposure to CPU %utilization, or any way to break down how a query execution maps to CPU usage across the CPUs on a server? From what I've read so far, it seems that it would be possible to correlate system performance data with the extended events, but I haven't seen too many examples of that yet.

    Any help or direction would be appreciated.

    Thanks!

    Jason

  • February 25, 2013 at 10:55 am

    #1590891

    You should start reading Paul's article first:

    http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx

    Then from SSC Extended Events QA:

    http://www.sqlservercentral.com/blogs/vinaythakur/2011/12/23/basics-qa-on-extended-event/[/url]

    Then try some examples from SSC:

    http://www.sqlservercentral.com/articles/deadlock/65658/[/url]

    BOL:

    http://msdn.microsoft.com/en-US/library/bb630355%28v=sql.100%29

    http://msdn.microsoft.com/en-US/library/bb677357%28v=sql.100%29

    Then download the add-in and create your own.

    http://extendedeventmanager.codeplex.com/releases/view/35325

    Alex S

  • February 25, 2013 at 11:32 am

    #1590899

    Thanks, Alex. I've seen many of those resources, as well as Jonathan Kehayias's excellent material. But I was looking for some resources about cpu usage in particular. If one workload takes 50ms of cpu and another takes 90ms, it's difficult to say if should they be consolidated. If they take 25% of similar processors each, that seems more actionable. But, the only CPU data I've seen in extended events is ms of CPU.

  • February 25, 2013 at 1:15 pm

    #1590931

    i think (not 100% sure) the closest you'll ever come with extended events is using the sqlos.wait with sqlos.cpu_id and sqlos.node_affinity.

    below code was not tested:

    CREATE EVENT SESSION [Check CPUusage] ON SERVER

    ADD EVENT sqlos.wait_info

    (

    ACTION (sqlos.cpu_id,

    sqlos.node_affinity,

    sqlserver.database_name,

    sqlserver.request_id,

    sqlserver.session_id,

    sqlserver.session_nt_username,

    sqlserver.sql_text,

    sqlserver.transaction_id,

    sqlserver.username)

    Alex S

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply