Using Extended events to find Long-running queries and

Sometimes you are encountered with mysteries on your database server. You have a beautiful alerting system that sends you emails about the Azure node when there are critical events so yo are well informed when some anomaly is going on on your Azure VM-machine.

 I received recently an email with a critical event. It was an alert about swapping the whole Sqlserver memory on the Azure node. I checked the Sqlserver Errorlogs, Windows eventviewer, sqlserver job history, query stats, windows scheduled task and did not find any cause.

I do not like when I can not find an answer so I decided to avoid this for a next time by creating extending events in SQL Server 2019.

An extended event is the equivalent what we used to do in the past with SQL Server 2019 Profiler.

There is one thing that an extended event does better than Profiler is that it runs always in the background and the performance load on the SQL Server 2019 is minimal.

This is an explanation how to set this up in SQL Server 2019 Management Studio. First lets check out where you can find Extended events in Management Studio.

• Step1:Click on Sessions with the right mouse button and choose “New Session Wizard”

Step2:Click on Next in the right down side of the page.

Step3:Fill in a Session name, FYI: LongRunningQueries.

Step4:Mark the box “Start the event session at server startup.

Step5:Mark: “Do not use a template”

Step6:Click on Next in the right down side of the page.

Step7: Select from the Event Library ( left windows of the screen) the events: 

1. sql_statement_completed 
2. sql_batch_completed 
3. rpc_completed 

Move each one from the left windows to the “selected events” window using the right arrow.

Step8:
In the next screen you can select the fields that should be captured.

Select:

• Client hostname
• Client app name
• Database name
• Sql text
• User name

Step9:In the next screen just click finish.
We did not set thresholds or filters on fields of event yet because

we can only choose from the complete lists of fields AFTER we have chosen FINISHED,  

Step10:Select you LongRunningQueries Event again in management studio by using your right mouse button.

Step 11:Create a Filter(predicate) for every selected event.

choose Field:”Duration”, Operator:”>” and Value:”60 000 000″

The unit for duration is Microseconds. 60,000,000 = 1 Minute. 

 You can find all the way down on this page a list with all the units for different events.

Step 11: Finally set the place where the target data will be stored by using you right mouse button on the event and selecting in the right side of the screen Data Storage. Most people pick  the ringbuffer option.

Step 12: Save your new created event

Step 13: Start your Event by selecting the EVEN in Management Studio, then use your right mouse button and click on start.

Step14: Watch the recorded event data. select the Event in Management Studio, Right mouse button and click on View Target data.

There is also the option avaliable to watch the recording of data in rela time by choosing “Watch Life Date”.

That’s all to it.

If you where  able to save the event and start it then you can skip the ERROR paragraph.

ERROR

When you try to save the even and you get an error:

Session Validation for the alter operation failed , The value specified for the event attribute or predicate source duration is invalid.

then you are using an outdated version of management studio. I encountered this problem on Azure and let  management Studio , Script the Even, instead of saving it. After having it scripted, then start a new query window and remove the quotes around the value of duration and execute the script.

The scriptwill look like this:

DROP EVENT SESSION [LongRunningQueries] ON SERVER; 

GO

CREATE EVENT SESSION [LongRunningQueries] ON SERVER ADD EVENT sqlos.wait_info(WHERE([duration] > (60000))), ADD EVENT sqlserver.lock_deadlock, ADD EVENT sqlserver.rpc_completed(SET collect_statement = (1) ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.sql_text, sqlserver.username) WHERE([duration] > (60000000))), ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text = (1) ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.sql_text, sqlserver.username) WHERE([duration] > (60000000))), ADD EVENT sqlserver.sql_statement_completed(ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.sql_text, sqlserver.username) WHERE([duration] > (60000000))) WITH(MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON);

GO

ALTER EVENT SESSION [LongRunningQueries] ON SERVER ADD TARGET package0.ring_buffer;

GO

ALTER EVENT SESSION [LongRunningQueries] ON SERVER

–ON DATABASE

STATE = START;   — STOP;

package_name	event_name	event_field	DurationUnit	field_type	column_type
sqlos	wait_info	duration	milliseconds	uint64	data
sqlos	wait_completed	duration	milliseconds	uint64	data
sqlos	wait_info_external	duration	milliseconds	uint64	data
sqlserver	sql_statement_completed	duration	microseconds	int64	data
sqlserver	sp_statement_completed	duration	microseconds	int64	data
sqlserver	rpc_completed	duration	microseconds	uint64	data
sqlserver	module_end	duration	microseconds	uint64	data
sqlserver	sql_batch_completed	duration	microseconds	uint64	data
sqlserver	logout	duration	microseconds	uint64	data
sqlserver	attention	duration	microseconds	uint64	data
sqlserver	existing_connection	duration	microseconds	uint64	data
sqlserver	sql_statement_post_compile	duration	milliseconds	uint64	data
sqlserver	lock_acquired	duration	microseconds	uint64	data
sqlserver	lock_deadlock	duration	microseconds	uint64	data
sqlserver	query_post_compilation_showplan	duration	microseconds	uint64	data
sqlserver	query_post_execution_showplan	duration	microseconds	uint64	data
sqlserver	query_plan_profile	duration	microseconds	uint64	data
sqlserver	query_post_execution_plan_profile	duration	microseconds	uint64	data
sqlserver	progress_report_online_index_operation	duration	microseconds	uint64	data
sqlserver	columnstore_tuple_mover_end_compress	duration	milliseconds	uint64	data
sqlserver	columnstore_tuple_mover_compression_stats	duration	milliseconds	uint64	data
sqlserver	sql_transaction	duration	microseconds	uint64	data
sqlserver	execution_warning	duration	microseconds	uint64	data
sqlserver	blocked_process_report	duration	microseconds	uint64	data
sqlserver	blocked_process_report_filtered	duration	microseconds	uint64	data
XtpEngine	xtp_ckptctrl_storage_array_grow	duration	microseconds	uint64	data