代码之家 › 专栏 › 技术社区 › noob.spt

截断表和更新统计信息

performance sql-server-2005 sql-server sql

noob.spt · 技术社区 · 16 年前

Q: 截断表后是否需要调用“UPDATESTATISTICS”?

3 回复 | 直到 16 年前

gbn 16 年前

最初的答案是“是”,因为它不是TRUNCATE的一部分。这取决于你如何阅读问题:-)

记住,当查询需要时(例如行数更改),统计信息会自动更新。从“ Index Statistics “以BOL表示

每当查询执行计划中使用的统计信息未通过当前统计信息的测试时,就会启动统计信息更新

STATS_DATE ...

SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id)
FROM
   sys.indexes 
WHERE
   object_id = OBJECT_ID('MyTruncatedTable')

编辑 :我想确认一下:-)

您将看到统计数据仅由SELECT语句更新,而不是由INSERT、DELETE或TRUNCATE更新

IF OBJECT_ID('dbo.foo') IS NOT NULL DROP TABLE dbo.foo
CREATE TABLE dbo.foo (
    bar int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
    thing int NOT NULL
)
CREATE INDEX IX_thing ON dbo.foo (thing)

INSERT dbo.foo (thing) SELECT c1.object_id FROM sys.columns c1, sys.columns c2
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterLoad
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

SELECT DISTINCT thing FROM dbo.foo ORDER BY thing DESC
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterFirstQuery
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

DELETE TOP (50000) dbo.foo
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterDelete
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

SELECT DISTINCT thing FROM dbo.foo ORDER BY thing DESC
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS After2ndQuery
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

TRUNCATE TABLE dbo.foo
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterTruncate
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

SELECT DISTINCT thing FROM dbo.foo ORDER BY thing DESC
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS After3rdQuery
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

Andomar 16 年前

还可以选择 automatically update statistics :

当自动更新统计信息时选项AUTO_UPDATE_STATISTICS已启用, 查询优化器确定何时统计数据可能已经过时然后在使用时更新它们通过查询。

这可能会在截断后重新计算。

ALTER DATABASE AdventureWorks
    SET AUTO_UPDATE_STATISTICS ON;

update statistics 手动:

UPDATE STATISTICS Sales.SalesOrderDetail

SELECT 
    object_name = Object_Name(ind.object_id),
    IndexName = ind.name,
    StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc

kevchadders 16 年前

正如你所拥有的 无数据 在插入数据之前,它将毫无意义,然后您将需要更新统计数据。