Tuesday, March 1, 2022

SQL 2016 – It Just Runs Faster: XEvent Linq Reader

Moved from: bobsql.com

 

SQL Server 2016 improves the XEvent Linq reader scalability and performance.    The XEvent UI in SQL Server Management Studio uses the XEvent Linq reader to process the events for display.   Careful study of the XEvent Linq reader revealed opportunities for scalability and performance improvements.

 

Note:  The XEvent Linq reader is .NET based and can be used by custom applications.

 

Various changes were made to the XEvent Linq provider logic to reduce context switching, memory allocations and other aspects.

 

The following are testing results of the XEvent reader.

 

WITHOUT FIX

8000 Events/sec

WITH FIX

80,000 Events/sec

 

‘It Just Runs Faster’ – The SQL Server 2016 client component processes XEvent files 10x+ faster improving the responsiveness of the XEvent UI and reader capabilities.

 

Ports

SQL 2012 – https://support.microsoft.com/en-us/kb/3112710

SQL 2014 – https://support.microsoft.com/en-us/kb/3075950

 

DEMO – It Just Runs Faster: XEvent Linq Reader

Overview

This demonstration shows the updated performance of the XEvent Linq Reader.

Steps

  1. Open SQL Server Management Studio (SSMS) and execute the following script to create and start the XEvent session on the SQL 2012 or 2014 instance.
     
CREATE EVENT SESSION [RPC Completed] ON SERVER
ADD EVENT sqlserver.rpc_completed
ADD TARGET package0.event_file(SET filename=N’c:\temp\RPCCompleted’)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
go

 

ALTER EVENT SESSION [RPC Completed] ON SERVER STATE = START
go
 
  1. Execute the following RML, OStress utility command from a Windows Command prompt on the server to produce the events. 
    ostress -E -S.\sql2014 -Q”{call sp_reset_connection()}” -q -r10000 -n100
     
  2. Stop the event session using the following script in SSMS.  (~185MB XEL file should be created.) 
    ALTER EVENT SESSION [RPC Completed] ON SERVER STATE = STOP
     
  3. Open the XEvent file in SSMS on a SQL Server 2012 or 2014 client and note the load time
     
  4. Open the XEvent file in SSMS on a SQL Server 2016 client and note the load time

    Note:  For comparison, make sure the file is copied locally to the client machine and the client machines have the same hardware footprint.

    Hint:  Try different filters and column sorting options to experience the performance gains as well.

    Fact: SSMS uses the exposed Linq reader interface that can be consumed in any application.

Actual Scenarios

SQL Server 2016 has been vetted by a wide range of customers.   The positive impact of these changes has been realized by:
 

  • Security firm leveraging XEvent telemetry is able to process the data 10x faster 
  • Microsoft SQL Server Support saves an average of 18 minutes (per collection) on those issues requiring XEvent processing 

Sample Results  ( times faster)

Machine

32GB RAM, 4 Core Hyper-threaded enabled 2.8Ghz, SSD Storage

SQL Server

Out of the box, default installation

 

SQL Server 2014

9sec

SQL Server 2016

4sec
Posted at https://sl.advdat.com/3vz3ZKjhttps://sl.advdat.com/3vz3ZKj
Labels: ,