Skip to main content
Database Administrators

Return to Question

Found root cause of why events werent firing off
Source Link
Duck_DBA
Duck_DBA
  • 43
  • 4
   CREATE EVENT SESSION [QueryStore_Cleanup_Audit]
ON SERVER
    ADD EVENT qds.query_store_db_cleanup__finished
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    ),
    ADD EVENT qds.query_store_db_cleanup__started
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    )
WITH
(
    MAX_MEMORY = 40096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO




query_store_db_cleanup_started

   CREATE EVENT SESSION [QueryStore_Cleanup_Audit]
ON SERVER
    ADD EVENT qds.query_store_db_cleanup__finished
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    ),
    ADD EVENT qds.query_store_db_cleanup__started
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    )
WITH
(
    MAX_MEMORY = 40096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO




query_store_db_cleanup_started

Edit - 10/8/18 - I was able to finally track cleanup events via the EE! Below is my code as i was missing the "query_store_plan_removal" and "query_store_size_retention_cleanup_update" EE events:

CREATE EVENT SESSION [QueryStore_Cleanup_Audit] ON SERVER
ADD EVENT qds.query_store_db_cleanup__finished
(ACTION
 (
     package0.last_error,
     package0.process_id,
     sqlos.cpu_id,
     sqlos.system_thread_id,
     sqlos.task_time,
     sqlserver.client_app_name,
     sqlserver.client_connection_id,
     sqlserver.client_hostname,
     sqlserver.client_pid,
     sqlserver.context_info,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.server_instance_name,
     sqlserver.session_id,
     sqlserver.session_nt_username,
     sqlserver.sql_text,
     sqlserver.transaction_id,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_db_cleanup__started
(ACTION
 (
     package0.last_error,
     package0.process_id,
     sqlos.cpu_id,
     sqlos.system_thread_id,
     sqlos.task_time,
     sqlserver.client_app_name,
     sqlserver.client_connection_id,
     sqlserver.client_hostname,
     sqlserver.client_pid,
     sqlserver.context_info,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.server_instance_name,
     sqlserver.session_id,
     sqlserver.session_nt_username,
     sqlserver.sql_text,
     sqlserver.transaction_id,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_execution_runtime_info_discarded
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.session_id,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_flush_failed
(ACTION
 (
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.query_plan_hash_signed,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_plan_removal
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_plan_hash_signed,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_finished
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.query_plan_hash_signed,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_skipped
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.query_plan_hash_signed,
     sqlserver.session_id,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_started
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.query_plan_hash_signed,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_update
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_plan_hash_signed,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_query_deleted
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.sql_text,
     sqlserver.username
 )
) WITH (
MAX_MEMORY = 40096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON ); GO

   CREATE EVENT SESSION [QueryStore_Cleanup_Audit]
ON SERVER
    ADD EVENT qds.query_store_db_cleanup__finished
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    ),
    ADD EVENT qds.query_store_db_cleanup__started
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    )
WITH
(
    MAX_MEMORY = 40096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO




query_store_db_cleanup_started

   CREATE EVENT SESSION [QueryStore_Cleanup_Audit]
ON SERVER
    ADD EVENT qds.query_store_db_cleanup__finished
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    ),
    ADD EVENT qds.query_store_db_cleanup__started
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    )
WITH
(
    MAX_MEMORY = 40096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO




query_store_db_cleanup_started

Edit - 10/8/18 - I was able to finally track cleanup events via the EE! Below is my code as i was missing the "query_store_plan_removal" and "query_store_size_retention_cleanup_update" EE events:

CREATE EVENT SESSION [QueryStore_Cleanup_Audit] ON SERVER
ADD EVENT qds.query_store_db_cleanup__finished
(ACTION
 (
     package0.last_error,
     package0.process_id,
     sqlos.cpu_id,
     sqlos.system_thread_id,
     sqlos.task_time,
     sqlserver.client_app_name,
     sqlserver.client_connection_id,
     sqlserver.client_hostname,
     sqlserver.client_pid,
     sqlserver.context_info,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.server_instance_name,
     sqlserver.session_id,
     sqlserver.session_nt_username,
     sqlserver.sql_text,
     sqlserver.transaction_id,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_db_cleanup__started
(ACTION
 (
     package0.last_error,
     package0.process_id,
     sqlos.cpu_id,
     sqlos.system_thread_id,
     sqlos.task_time,
     sqlserver.client_app_name,
     sqlserver.client_connection_id,
     sqlserver.client_hostname,
     sqlserver.client_pid,
     sqlserver.context_info,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.server_instance_name,
     sqlserver.session_id,
     sqlserver.session_nt_username,
     sqlserver.sql_text,
     sqlserver.transaction_id,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_execution_runtime_info_discarded
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.session_id,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_flush_failed
(ACTION
 (
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.query_plan_hash_signed,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_plan_removal
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_plan_hash_signed,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_finished
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.query_plan_hash_signed,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_skipped
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.query_plan_hash_signed,
     sqlserver.session_id,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_started
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.query_plan_hash_signed,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_cleanup_update
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_plan_hash_signed,
     sqlserver.sql_text,
     sqlserver.username
 )
),
ADD EVENT qds.query_store_size_retention_query_deleted
(ACTION
 (
     sqlos.task_time,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.nt_username,
     sqlserver.plan_handle,
     sqlserver.query_hash,
     sqlserver.sql_text,
     sqlserver.username
 )
) WITH (
MAX_MEMORY = 40096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON ); GO
Tweeted twitter.com/StackDBAs/status/1045916363439648768
capitalization
Source Link
edit approved Sep 27, 2018 at 14:28
Glorfindel
edit approved Sep 27, 2018 at 14:28
Glorfindel
  • 2.2k
  • 5
  • 18
  • 26

I searched online far and wide and didntdidn't see anyone else posting about this so iI thought iI would. I am trying to audit when the query store does its cleanup and is purged from the query store.

There are two extended event sessions iI found that iI thought were the events but iI have no data being picked up even after the query store has purged stale query data:

Wondering if anyone else has had the same issue and is as curious as iI when the query store purged its data?

Below is the code iI have for setting up my current audit:

I searched online far and wide and didnt see anyone else posting about this so i thought i would. I am trying to audit when the query store does its cleanup and is purged from the query store.

There are two extended event sessions i found that i thought were the events but i have no data being picked up even after the query store has purged stale query data:

Wondering if anyone else has had the same issue and is as curious as i when the query store purged its data?

Below is the code i have for setting up my current audit:

I searched online far and wide and didn't see anyone else posting about this so I thought I would. I am trying to audit when the query store does its cleanup and is purged from the query store.

There are two extended event sessions I found that I thought were the events but I have no data being picked up even after the query store has purged stale query data:

Wondering if anyone else has had the same issue and is as curious as I when the query store purged its data?

Below is the code I have for setting up my current audit:

Source Link
Duck_DBA
Duck_DBA
  • 43
  • 4

How to find when Query Store flushes stale query data? Extended Events not working

I searched online far and wide and didnt see anyone else posting about this so i thought i would. I am trying to audit when the query store does its cleanup and is purged from the query store.

There are two extended event sessions i found that i thought were the events but i have no data being picked up even after the query store has purged stale query data:

  1. query_store_db_cleanup_started
  2. query_store_db_cleanup_finished

Wondering if anyone else has had the same issue and is as curious as i when the query store purged its data?

Below is the code i have for setting up my current audit:

   CREATE EVENT SESSION [QueryStore_Cleanup_Audit]
ON SERVER
    ADD EVENT qds.query_store_db_cleanup__finished
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    ),
    ADD EVENT qds.query_store_db_cleanup__started
    (ACTION
     (
         package0.last_error,
         package0.process_id,
         sqlos.cpu_id,
         sqlos.system_thread_id,
         sqlos.task_time,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.client_pid,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.username
     )
     WHERE ([database_id] = (5))
    )
WITH
(
    MAX_MEMORY = 40096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO




query_store_db_cleanup_started