Sql-server – Extended Events, CPU_TIME

extended-eventssql-server-2012

I want to create a new session in Extended Events to track Queries that use lots of CPU_Time, but whats the Unit of CPU_TIME? ms? Microseconds?…

Duration seems to be Microseconds but I don't really know CPU_TIME and can't find any information regarding this…

Thanks,
Jimm

Best Answer

The sys.dm_xe_object_columns DMV shows metadata for each of the columns available for each event. Included is a "description" column, which is useful in answering your question.

select 
    name
    , object_name
    , type_name
    , description 
from
    sys.dm_xe_object_columns
where
    name = 'cpu_time' -- any XE column name here

This will show you all of the extended event objects that have a cpu_time column, and the description will tell you what unit of measure the column uses.

You can see here that units may vary depending on the object. For example:

sql_statement_completed: Indicates the CPU time (in microseconds) that is consumed by the statement.
sql_batch_completed: The CPU time (in milliseconds) used by the batch.

Related Solutions

Sql-server – Collecting parameter values from Extended event session

Someone mentioned "I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values." Please review answer below:

Capture Passed Parameter Values In Sql Server Extended Sessions

Sql-server – SQL Server 2012 extended event session to expose the network protocol

Found the answer.

The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(
    sqlserver.session_id
    ,sqlserver.username
    ))
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)
GO

And this would then populate options_text with something like: -

network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed

I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.

That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.

An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.

Best Answer

Related Solutions

Sql-server – Collecting parameter values from Extended event session

Sql-server – SQL Server 2012 extended event session to expose the network protocol

Related Question