MSSQL-統計資訊

 在 SQL Server 2012 中，您可以選擇關閉特定表的自動統計信息更新。自動統計信息更新是幫助 SQL Server 優化器選擇最佳查詢計劃的一種機制。然而，有時候出於性能考慮或是在某些特定的負載下，您可能會選擇手動管理統計信息。

要禁止特定表的自動統計信息更新，您可以使用 ALTER TABLE 語句結合 NORECOMPUTE 選項。但請注意，SQL Server 2012 並不直接支持對單個表禁用自動統計信息的語法。相反，您需要對表中的每一個統計信息使用 UPDATE STATISTICS 語句配合 NORECOMPUTE 選項。

以下是一個示例步驟，展示如何為特定表的統計信息禁用自動更新：

SELECT name, object_id FROM sys.stats WHERE object_id = OBJECT_ID('您的表名');

 

禁用自動統計資訊： 如果您希望整個數據庫中的所有表都不生成自動統計資訊，可以將自動統計資訊選項設置為OFF。這將禁用自動統計資訊的生成，進而阻止所有表的統計資訊更新。您可以使用以下命令：

ALTER DATABASE  [您的表名]  SET AUTO_CREATE_STATISTICS OFF;

 假設您有一個表名為 YourTableName，並且該表上有一個名為 Stat1 的統計資訊，您可以使用以下命令刪除它：

DROP STATISTICS YourTableName.Stat1;

禁用自動更新：對於查詢結果中的每一個統計信息，使用以下語句禁用自動更新。

UPDATE STATISTICS [您的表名] [統計信息名稱] WITH NORECOMPUTE;

 

 

請將 [您的表名] 和 [統計信息名稱] 替換為您實際的表名和統計信息名稱。

請注意，這樣做之後，相關表的統計信息將不會自動更新，這可能會影響查詢性能，因為統計信息可能會過時。您需要定期手動更新統計信息，以保證查詢性能。可以使用與上述相同的 UPDATE STATISTICS 語句手動更新統計信息，只是不帶 NORECOMPUTE 選項。

要檢查 SQL Server 中的統計信息是否設置為自動更新，您可以查詢 sys.stats 目錄視圖。sys.stats 视图包含有关数据库中所有统计信息对象的信息。每个统计信息对象都有一个与之关联的 is_auto_created 和 is_auto_updated 属性，指示该统计信息是否是自动创建和自动更新的。

以下是一个 SQL 查询示例，它展示了如何检查特定表的统计信息是否设置为自动更新：

SELECT

    name AS StatisticsName,

    auto_created,

    user_created,

    no_recompute,

    is_incremental

FROM sys.stats

WHERE object_id = OBJECT_ID('您的表名');

在这个查询中：

• name 是统计信息的名称。
• auto_created 指示统计信息是否是由 SQL Server 自动创建的。
• user_created 指示统计信息是否是由用户手动创建的。
• no_recompute 指示是否禁用了统计信息的自动更新。如果 no_recompute 为 1，则表示不自动更新统计信息；如果为 0，则表示自动更新。
• is_incremental 指示统计信息是否为增量更新的。

请将 '您的表名' 替换为您想要检查的实际表名。通过查看 no_recompute 列的值，您可以判断统计信息是否设置为自动更新。如果您发现 no_recompute 的值为 1，这意味着该统计信息的自动更新已经被禁用。如果值为 0，则表示启用了自动更新。 

 

 

在 SQL Server 中，"无效的统计信息"通常指的是那些由于数据变更而可能不再反映数据实际分布的统计信息。尽管 SQL Server 没有直接的方式来查询“无效”的统计信息（因为 SQL Server 内部会自动决定何时更新统计信息），我们可以间接地查找可能需要更新的统计信息。这可以通过比较统计信息的最后更新时间与表中数据修改的情况来实现。

以下是一种方法，用于查找可能已经过时（即可能“无效”）的统计信息：

1. 查找统计信息的最后更新时间：可以通过查询 sys.stats 和 sys.dm_db_stats_properties 系统视图来找到统计信息的最后更新时间。

2. 比较数据修改次数：sys.dm_db_stats_properties 视图还提供了自统计信息上次更新以来对应对象所发生的数据修改次数。如果这个数字相对较大，那么统计信息可能已经过时。

以下是一个 SQL 查询示例，它可以帮助您找到特定表上可能需要更新的统计信息：

SELECT

    s.name AS StatisticsName,

    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,

    sp.rows AS RowsTotal,

    sp.rows_sampled AS RowsSampled,

    sp.modification_counter AS ModificationsSinceLastUpdate

FROM sys.stats AS s

CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp

WHERE s.object_id = OBJECT_ID('您的表名');

 

• StatisticsName 是统计信息的名称。
• LastUpdated 是统计信息最后更新的时间。
• RowsTotal 是统计信息基于的行总数。
• RowsSampled 是用于计算统计信息的样本行数。
• ModificationsSinceLastUpdate 是自上次更新统计信息以来对应表的数据修改次数。

请将 '您的表名' 替换为您实际的表名。

通过检查 ModificationsSinceLastUpdate 列的值，您可以识别出自上次更新以来发生了大量修改的统计信息。如果这个数字相对于表总行数很大，那么可能表明统计信息已经过时，需要更新。

 

更新一个表的所有统计信息

如果您想要更新一个表的所有统计信息，可以使用以下语句：

UPDATE STATISTICS 表名;

 将 表名 替换为您想要更新统计信息的实际表名。

更新表的特定统计信息

如果您只想更新表中某个特定的统计信息，可以指定统计信息的名称：

UPDATE STATISTICS 表名 统计信息名;

 

 

在这里，将 表名 替换为您的表名，将 统计信息名 替换为您想要更新的具体统计信息的名称。

使用 FULLSCAN 选项

为了获取最准确的统计信息，您可以在更新统计信息时使用 FULLSCAN 选项，这会使 SQL Server 对整个表进行扫描来收集统计信息：

UPDATE STATISTICS 表名 WITH FULLSCAN;

FULLSCAN 选项会导致 SQL Server 读取表中的每一行来生成统计信息，这可以提供最精确的数据分布信息，但可能会消耗更多的时间和资源。

注意事项

• 性能影响：手动更新统计信息，特别是使用 FULLSCAN 选项时，可能会对数据库性能产生短暂的影响。建议在低负载时段进行此操作。
• 自动更新统计信息：在大多数情况下，允许 SQL Server 自动更新统计信息是推荐的做法，因为它能根据数据变化自动调整。手动更新统计信息通常用于调试或优化特定查询性能的特殊情况。