June 11, 2015 at 7:32 am
Hi Guys
I am wanting to get/filter on all queries and procs that take longer than 2 seconds to run (I'll balance real values later) but I'm not sure which Action out of the XE that I need.
I am using SQL Server 2014 and thought I had used sqlserver.sql_statement_completed.duration > 2000 in a previous version.
Any ideas?
Cheers
Alex
June 11, 2015 at 8:21 am
Resolved!
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='long_running_queries')
DROP EVENT SESSION long_running_queries ON SERVER
GO
-- Create Event
CREATE EVENT SESSION long_running_queries
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE duration > 2000 -- longer than 2 seconds
-- sqlserver.duration > 2000
AND sqlserver.database_id > 1 -- database filter placeholder for exanple
)
ADD TARGET package0.asynchronous_file_target
(
SET filename='c:\logs\long_running_queries.xet',
metadatafile='c:\logs\long_running_queries.xem',
max_file_size = 5,
max_rollover_files = 1000
);
-- Enable Event
ALTER EVENT SESSION long_running_queries ON SERVER
STATE=START
Originally I was referencing duration by sqlserver.duration rather than just duration.
Cheers for reading!
Alex
June 11, 2015 at 10:12 am
One point. I'm pretty sure duration in extended events is in microseconds. You might want to capture >2000000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2015 at 3:02 am
Cheers Grant! I am getting spurious results anyway, it seems waitfor delay doesnt count as a long running query!
June 12, 2015 at 3:07 am
It may be your filter on database id. I'm not sure for XE, but with Profiler, ad-hoc queries didn't get the database_id field populated.
Try removing that and, yes, as Grant mentioned, duration is in microseconds, so duration > 2000000
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
June 13, 2015 at 9:58 am
I agree about the filter on the database id... and disagree. As much as possible you should have it interrupt sooner rather than later to take advantage of the great filtering. Database id is a good interrupt, if it's working. If it's not, as Gail said, pull it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply