在调用Truncate table之后,我们需要更新表统计信息吗?还是它会自动更新?
问:在截断表后,我们需要调用"UPDATE STATISTICS"吗?
在调用Truncate table之后,我们需要更新表统计信息吗?还是它会自动更新?
问:在截断表后,我们需要调用"UPDATE STATISTICS"吗?
ALTER DATABASE AdventureWorks
SET AUTO_UPDATE_STATISTICS ON;
手动更新统计数据,请点击此处。
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
一种验证方法是使用 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')
由于你没有数据,因此在插入数据之前它是毫无意义的,然后你会寻求更新统计数据。
不要忘记你可以自动更新统计数据,也可以每日/每周运行和更新统计作业...
如果仍然存在重大问题,请截断表格,然后更新表格上的统计数据。