enable audit logs databases query supper slow(sys.fn_get_audit_file)

Question

hi we have sql server 2016 sp3 ,and enabled audits

1. SERVER AUDIT [Audit_]

 
CREATE SERVER AUDIT [Audit_01]
TO FILE 
(	FILEPATH = N'C:\Audit'
	,MAXSIZE = 10 GB
	,MAX_FILES = 100
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)

1. enabled audit
2. CREATE SERVER AUDIT SPECIFICATION
3. USE USER_DATA

go

CREATE DATABASE AUDIT SPECIFICATION

We have done server level and DB level auditing

Plan is to load the data into custom table where can extract data quicky when we need data.

there are multiple files created in the folder xxx.sqlaudit .

when run a query to extract last 2 hours data, it will take over 2 hours to run the SQL. its really time consuming

Question: any any one knows how to speed up and at least get the data. this is look like not practical audit solution from Microsoft ? any one have same issue?

SELECT 
 CONVERT(datetime, 
        SWITCHOFFSET(CONVERT(datetimeoffset, event_time), 
           DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS
      event_time
	  	   	   ,server_instance_name,action_id,class_type,sequence_number,succeeded,permission_bitmask,is_column_permission,session_id,server_principal_sid,server_principal_name,database_principal_id ,session_server_principal_name,*  FROM sys.fn_get_audit_file ('C:\Audit\Audit_*.sqlaudit',default,default)  
where CONVERT(datetime, 
        SWITCHOFFSET(CONVERT(datetimeoffset, event_time), 
           DATENAME(TzOffset, SYSDATETIMEOFFSET()))) >= DATEADD(Hour, -2, GETDATE())
  

Answer 1

when run a query to extract last 2 hours data, it will take over 2 hours to run the SQL. its really time consuming

The audit log is stored on disk as a kinf of text file and if it's large, query on a text file can take some time.