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
Greg Lapinski - Senior SQL Server Software Engineer
Bob Dorr - Principal SQL Server Software Engineer
DEMO - It Just Runs Faster: XEvent Linq Reader
Overview
This demonstration shows the updated performance of the XEvent Linq Reader.
Steps
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
Actual Scenarios
SQL Server 2016 has been vetted by a wide range of customers. The positive impact of these changes has been realized by:
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 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.