November 12, 2016 at 5:31 am
Hi,
I've SQL 2012 SP2 and i trying to setup extended event for sqlserver.rpc_completed with duration > 2 sec
in the results it capture rpc_completed queries lower then 2 sec.
why is that?
THX
CREATE EVENT SESSION [Long_Running_Queries]
ON SERVER
ADD EVENT sqlserver.rpc_completed
(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.username,sqlserver.client_hostname)
WHERE duration>200000
)
ADD TARGET package0.asynchronous_file_target
(
SET filename=N'E:\rpc_completed\rpc_completed.xel',metadatafile = 'E:\msys_monitor_rpc_completed.xem',max_file_size=(250),max_rollover_files=(0)
)
WITH
(
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=4096 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON
)
found the problem....
should be duration>2000000
November 12, 2016 at 5:37 am
You missed a zero
with a thousand separator added your filter was: duration>200 000, so filtering for duration greater than 0.2 seconds
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2016 at 7:18 am
GilaMonster (11/12/2016)
You missed a zerowith a thousand separator added your filter was: duration>200 000, so filtering for duration greater than 0.2 seconds
Gail, in you experience, how accurate is the timing here? Is it a true microsecond or does it effectively work on the 300 microseconds granularity of the datetime data type?
😎
November 12, 2016 at 9:06 am
The times in XE are datetime2 and durations are bigints, so the properties of the datetime datatype are irrelevant. I wouldn't say it's accurate to the microsecond though, there's going to be a degree of error in measurments, haven't seen any documentation, can't be bothered looking into it myself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2016 at 12:46 pm
thanks for the help.
another question on the extended event.
i need to put this event on bunch of server but need to put the event files on different location for every server.
i've a table in every server with the location for the placeholder.
i try to run this query that replace the file with declared variable with no luck. how can i do it right?
THX.
DECLARE @FILE_LOCATION_XEL VARCHAR (2048),@FILE_LOCATION_XEM VARCHAR (2048)
SET @FILE_LOCATION_XEL = 'c:\rpc_completed.xel'
SET @FILE_LOCATION_XEM = 'c:\rpc_completed.xem'
CREATE EVENT SESSION [Long_Running_Queries]
ON SERVER
ADD EVENT sqlserver.rpc_completed
(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.username,sqlserver.client_hostname)
WHERE duration>3500000
),
ADD EVENT sqlserver.sp_statement_completed
(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.username,sqlserver.client_hostname)
WHERE duration>3500000
)
ADD TARGET package0.asynchronous_file_target
(
SET filename=@FILE_LOCATION_XEL,metadatafile =@FILE_LOCATION_XEM ,max_file_size=(250),max_rollover_files=(0)
)
WITH
(
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=4096 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON
)
November 12, 2016 at 1:21 pm
Dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply