1

What happens if the Data Storage is never configured?

enter image description here

0

If you specify no target, but choose to use an Extended Events wizard template for the the session, it is created with a single histogram target.

You can see this by clicking through to the last page of the wizard, then hitting the "script" button.

This is a sample I created using the "script" button. Below this is one where I specified a ring buffer as a target:

CREATE EVENT SESSION [test] ON SERVER 
ADD EVENT sqlserver.additional_memory_grant,
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.query_hash)
    WHERE (([package0].[greater_than_uint64]([database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash')
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=ON,STARTUP_STATE=OFF)
GO

This is the one with the ring-buffer:

CREATE EVENT SESSION [asdf] ON SERVER 
ADD EVENT sqlserver.additional_memory_grant,
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.query_hash)
    WHERE (([package0].[greater_than_uint64]([database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash'),
ADD TARGET package0.ring_buffer
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=ON,STARTUP_STATE=OFF)
GO

As you can see above, the only difference is the lack of a ring-buffer target in the first result, even though they bot have a histogram target.

However, if you do not use a template when building the Extended Events session, the session is created like so (I just added a single event and action):

CREATE EVENT SESSION [test] ON SERVER 
ADD EVENT sqlserver.additional_memory_grant(
    ACTION(sqlserver.client_pid))
WITH (STARTUP_STATE=OFF)
GO

This results in the session only providing live data, which is viewable via the "Watch Live Data" context-menu of the XE Session:

enter image description here

You can obtain a live stream of the rows returned by the Extended Event by looking at the very lightly documented system function, fn_MSxe_read_event_stream function, as in:

DECLARE @source nvarchar(256);
DECLARE @sourceopt int;
SET @source = N'test';
SET @sourceopt = 0;

SELECT xes.type
    , CONVERT(xml, CONVERT(varbinary(max), xes.data))
FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt) xes

However, the format of the data returned is image, and apparently "not customer readable".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.