SQL Server - 使用聚集索引时如何存储数据页

Question

Duck_DBA

Asked: 2018-09-28 05:12:48 +0800 CST2018-09-28 05:12:48 +0800 CST 2018-09-28 05:12:48 +0800 CST

如何查找查询存储何时刷新过时的查询数据？扩展事件不起作用

772

我在网上广泛搜索，没有看到其他人发帖，所以我想我会的。我正在尝试审核查询存储何时进行清理并从查询存储中清除。

我发现有两个扩展的事件会话，我认为是事件，但即使查询存储清除了陈旧的查询数据，我也没有获取任何数据：

query_store_db_cleanup_started
query_store_db_cleanup_finished

想知道当查询存储清除其数据时，是否还有其他人遇到过同样的问题并且和我一样好奇？

以下是我用于设置当前审计的代码：

   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

编辑 - 10/8/18 - 我终于能够通过 EE 跟踪清理事件！下面是我的代码，因为我缺少“query_store_plan_removal”和“query_store_size_retention_cleanup_update”EE 事件：

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

1 个回答

Voted

Josh Darnell · Answer 1 · 2018-09-28T07:41:29+08:00

有几种方法可以确定清除何时发生。

扩展事件

我已经在我的一台服务器上确认，“陈旧数据清除”事件确实会在陈旧数据清除开始时触发。

我会确认您有合格的数据，因为如果没有要清除的数据，XE 似乎会退出。您可以使用这样的查询来检查它：

USE [Your_Database];
GO

DECLARE @threshold AS INT;
SELECT @threshold = dbqso.stale_query_threshold_days 
FROM sys.database_query_store_options dbqso

SELECT * 
FROM sys.query_store_runtime_stats_interval qsrsi
WHERE 
    qsrsi.start_time < DATEADD(dd, -@threshold, CAST(GETDATE() AS DATE))
ORDER BY qsrsi.start_time;

由于我的数据库的 interval_length_minutes 为 60，因此当我接近 XE 触发时间时，此查询最多返回 25 行（日历日中每小时一个被清除，加上前一个日历日的“最后一小时”）：

这是我的 XE 会话中的最后一次清理事件 ( 2018-10-02 20:04:24.161)：

  <event name="query_store_db_cleanup__started" package="qds" timestamp="2018-10-02T20:04:24.161Z">
    <data name="database_id">
      <type name="uint32" package="package0"></type>
      <value>6</value>
    </data>
    <action name="collect_system_time" package="package0">
      <type name="filetime" package="package0"></type>
      <value>2018-10-02T20:04:24.160Z</value>
    </action>
  </event>

然后我就等待下一个事件，它发生在大约 24 小时后（2018-10-03 20:04:59.441所以“晚了”35 秒）：

  <event name="query_store_db_cleanup__started" package="qds" timestamp="2018-10-03T20:04:59.441Z">
    <data name="database_id">
      <type name="uint32" package="package0"></type>
      <value>6</value>
    </data>
    <action name="collect_system_time" package="package0">
      <type name="filetime" package="package0"></type>
      <value>2018-10-03T20:04:59.442Z</value>
    </action>
  </event>

现在查询返回一个结果（只是被清除的日历日的最后一小时）：

所以看起来这个 XE 像宣传的那样工作，但是当没有要清除的数据时它不会触发。因此，我会仔细检查您的数据和清除阈值设置，以确保您确实拥有正在清理的数据！

下面列出了一些用于查找陈旧数据清除的其他选项。

事务日志备份大小

我注意到在凌晨 1:30 我的一个非产品机器上发生了清除，因为每小时的事务日志备份异常大。由于清除主要只是从启用了查询存储的用户数据库中的查询存储表中删除行，因此该操作会像普通查询一样被记录下来。

观察查询

因为我碰巧记录了的内容，dm_exec_query_stats所以我能够将事务日志记录的增加与几个这样开始的查询相关联：

DELETE sys.plan_persist_plan WHERE query_id = -- etc, you get it

因此，您可以使用任何可用的工具来监视以“DELETE sys.plan_persist*”开头的查询，以识别清除发生的时间。

控制时间

正如您提到并注意到的那样，似乎没有任何方法可以配置或控制何时发生陈旧数据清除。当然，这通常不是问题。但这对我来说确实很奇怪。值得一提的是，在我的机器上，它似乎每天在同一时间发生一次。所以时间一旦确定，就好像是确定性的了。

如何查找查询存储何时刷新过时的查询数据？扩展事件不起作用

扩展事件

事务日志备份大小

观察查询

控制时间

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

如何查找查询存储何时刷新过时的查询数据？扩展事件不起作用

1 个回答

扩展事件

事务日志备份大小

观察查询

控制时间

相关问题