Archive events on SQL Server

[marcosqlbi]

There should be a procedure to archive .xel files in a SQL Server table. Using columnstore index, it could be possible to consider a DirectQuery approach, possibly removing the issue of the in-memory dictionary for the query text. The archive should remove/move the .xel files and move the content to SQL Server. Archiving is a challenge: the current T-SQL script reads from *.xel files open, but we should not archive the same rows twice. At the same time, a daily archive could have an high latency for certain reports. This area has to be investigated.

[byobi]

this is a tough one...several options come to mind...

• the max file size property of the xevent trace can be set to a low value (e.g. 1MB) so that it rolls more frequently throughout the day. latency will vary from system to system depending on amount of activity.
• stopping/starting the xevent trace on a controlled interval... yes, possible to lose a few events between the stop/start, but much better control over the latency.
• similar to the previous solution but using overlapping traces... i.e. start new trace before stopping the current trace. This allows better control over the latency and eliminates the risk of missing events...but adds quite a bit of complexity to the procedure that extracts the relevant data from the trace files.

[marcosqlbi]

I would say that 90% of the times, stopping/starting the events should work well (you have nightly maintenance task, and you may stop/start events just before nightly process, or just after it)

I had problems changing the size property of xevent trace to less than 1GB...

[byobi]

think i got the plumbing in for this one via agent jobs, stored procedures, and powershell...

First off, the following 4 jobs were created...

• 1 job to create the xEventProcessTrace on the ssas instance (xEventProcessTrace-Start)
• 1 job to delete the xEventProcessTrace on the ssas instance (xEventProcessTrace-Stop)
• 1 job to create the xEventQueryTrace on the ssas instance (xEventQueryTrace-Start)
• 1 job to delete the xEventQueryTrace on the ssas instance (xEventQueryTrace-Stop)

...which allow for the query and/or process traces to be manually "rolled" (via a stored procedure stg.xEventTrace-Roll that simply stops the existing trace and starts a new one).

The last job (Process-xEventFiles) handles the processing and archiving of query/process trace files. This job consists of the following 4 steps...

1. move xel files from the log directory to a processing directory (via powershell)
2. shred the process trace files (via sproc)
3. shred the query trace files (via sproc)
4. move the xel files from the processing directory to the archive directory (via powershell)

All of this is contained in the SsasEventsAnalyzerDB project (which now contains a lot of the configuration variables (which I still need to document))...

[marcosqlbi]

I would like to complete the documentation for installing and running the jobs you created. Can you write me a few notes about the process to deploy and configure? I am not used to the database projects and I might not be aware of something obvious here - thanks!