2

Extended event session can be created without targets. In such situation you can watch collected events in SQL Server Management Studio in the Live Data view. For such session a LiveStreamTarget is created whenever Live Data view is opened. If no one is monitoring such session, there is no target created for it.

Does such XEvent session affects server performance when no one is monitoring events with Live Data tab?

3

The overhead of an active event session with no targets depends on the frequency of events. Costs will be significant enough as to impact performance only under extreme cases.

Below are batch requests/sec measurements with no trace as well as unfiltered sql_batch_completed and sql_statement completed traces. The results of these tests show about a 8% decrease on throughput with the sql_batch_completed trace while the sql_statement_completed trace reduced throughput by a considerable 63%.

enter image description here

Given that it's quite easy to start an existing trace with a right-click in SSMS Object Explorer, my recommendation is to run traces that don't have targets only when you want to actively view live data, even though the impact will be insignificant unless high-frequency events are captured.

Below are the scripts I used for the test.

--script to generate sql_batch_completed and sql_statement completed events
SET NOCOUNT ON;
DECLARE @IterationCount int = 0;
WHILE @IterationCount < 1000
BEGIN
    SET @IterationCount += 1;
END;
GO 1000000

Script to run traces during test execution and measure throughput:

DECLARE @BeginCounterValue int;

--measure throughput with no trace
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE 
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'no trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE 
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';

--measure throughput with sql_batch_completed trace
CREATE EVENT SESSION sql_batch_completed ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1))
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=OFF);
ALTER EVENT SESSION sql_batch_completed ON SERVER STATE=START;
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE 
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'sql_batch_completed trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE 
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';

--measure throughput with sql_statement_completed trace
DROP EVENT SESSION sql_batch_completed ON SERVER; 
CREATE EVENT SESSION sql_statement_completed ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1))
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=OFF);
ALTER EVENT SESSION sql_statement_completed ON SERVER STATE=START;
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE 
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'sql_statement_completed trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE 
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
DROP EVENT SESSION sql_statement_completed ON SERVER;
GO

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.