K can ingest metadata and logs from SqlServer.
Metadata:
K Platform has native integration to extract metadata from SqlServr. For this, K requires access to READ the information_schema per database
Query logging:
SqlServer Extended events needs to be setup to capture query log data.
The following template can be used if Extended events is not currently configured
Note: Some customers may require some tuning depending on the volume of activity and the types of queries occurring in the SqlServer environment.
| Code Block |
|---|
--Query To Create Extended Events Session
CREATE EVENT SESSION [KADA] ON SERVER ADD EVENT sqlserver.sp_statement_completed (
ACTION(package0.collect_system_time, package0.event_sequence, sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash, sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.server_principal_sid, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.transaction_id, sqlserver.username) WHERE (
(
[statement] LIKE '%CREATE %'
OR [statement] LIKE '%DROP %'
OR [statement] LIKE '%MERGE %'
OR [statement] LIKE '%FROM %'
)
AND [sqlserver].[server_principal_name] <> N'USERS_TO_EXCLUDE'
AND [sqlserver].[is_system] = (0)
AND NOT [statement] LIKE 'Insert into % Values %'
AND [sqlserver].[Query_hash] <> (0)
)
), ADD EVENT sqlserver.sql_statement_completed (
SET collect_statement = (1) ACTION(package0.collect_system_time, package0.event_sequence, sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash, sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.transaction_id, sqlserver.username) WHERE (
(
[statement] LIKE '%CREATE %'
OR [statement] LIKE '%DROP %'
OR [statement] LIKE '%MERGE %'
OR [statement] LIKE '%FROM %'
)
AND [sqlserver].[server_principal_name] <> N'N'USERS_TO_EXCLUDE'
AND [sqlserver].[is_system] = (0)
AND NOT [statement] LIKE 'Insert into % Values %'
AND [sqlserver].[Query_hash] <> (0)
)
) ADD TARGET package0.event_file (SET filename = N'G:\extended events\Extendedevents.xel', max_file_size = (20), max_rollover_files = (100))
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = ON, STARTUP_STATE = ON)
GO
-- Check if the session is dropping events and see other data about the session
-- https://sqlperformance.com/2019/10/extended-events/understanding-event-loss-with-extended-events
SELECT
s.name,
s.total_regular_buffers,
s.regular_buffer_size,
s.total_large_buffers,
s.large_buffer_size,
s.dropped_event_count,
s.dropped_buffer_count,
s.largest_event_dropped_size
FROM sys.dm_xe_sessions AS s;
-- Also check log growth rate. Apply filters to remove noise.
-- some filters:
-- [sqlserver].[server_principal_name] = N'name of principal'
-- [sqlserver].[is_system] = (0)
-- [sqlserver].[client_app_name] = N'name of app'
|
K will require access to read and load the extended events log file generated