search

billgraziano / xelogstash

Send SQL Server Extended Events to Logstash, Elastic Search, or JSON
Other
26 stars 11 forks source link

Can I use XEvent Writer for SQL server on 'offline' logfiles? #94

Closed fribse closed 2 months ago

fribse commented 11 months ago

Hi Bill

I've been running the excellent software here, but it seems to impact the server too much, and I have to find another solution. We have previously used this: --Then run this command --Create logging event CREATE EVENT SESSION [AuditLog] ON SERVER

ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1) ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)), ADD EVENT sqlserver.sql_batch_completed( ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.username)) --Set location and names for where the files should be placed on local server. Should NOT be on the drive where the database-files is! ADD TARGET package0.event_file(SET filename= N'F:\AUDIT\alpha02013.xel' ,max_file_size=(1024),max_rollover_files=(10)) 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=ON) GO

--And finally run this command ALTER EVENT SESSION AuditLog ON SERVER STATE = START; GO

To create logfiles, which we then import and analyze from another server, a filestorage. This does not impact the server that much, and works ok, allthough the analysis part of it is TERRIBLE.

So I was thinking, can I somehow use the XEvent Writer to grab those .xel files, and send them to the logstash, running it from another server than the sql server itself? That way I could lower the impact on the sql server itself, and still get the nice graphs that I made in kibana?

billgraziano commented 11 months ago

A few thoughts...

  1. When you say "impact", does it impact the SQL Server or the CPU usage outside SQL Server?
  2. I would never run this software on a SQL Server box. It has to do a LOT of deserializing a weird XML format and reserializing into JSON. That really eats up the CPU.
  3. You are capturing every single SQL Server batch. That will likely cause performance issues. SQL Server has to serialize those to that weird XML format and write them to disk. Then it has to read them back when asked. I would suggest setting a filter to only capture SQL above a certain threshold of CPU or reads. There are samples in there somewhere.
  4. There isn't a good way to run this offline right now. Microsoft doesn't publish the binary format of the files and I gave up trying to reverse engineer it.
  5. Make sure the OS has at least 5% of free RAM available. I've seen issues querying XE sessions when it falls below 5%.