# 查看数据库统计信息自动更新设置
SELECT name, is_auto_update_stats_on, is_auto_create_stats_on
FROM sys.databases
WHERE name = ‘fgedudb’;
GO

# 结果
/*
name is_auto_update_stats_on is_auto_create_stats_on
——— ———————– ———————–
fgedudb 1 1
*/

# 启用自动更新统计信息
ALTER DATABASE fgedudb SET AUTO_UPDATE_STATISTICS ON;
GO

# 启用自动创建统计信息
ALTER DATABASE fgedudb SET AUTO_CREATE_STATISTICS ON;
GO

# 启用异步自动更新统计信息
ALTER DATABASE fgedudb SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

# 更新单个表的统计信息
UPDATE STATISTICS dbo.fgedu_sales;
GO

# 更新单个表的指定统计信息
UPDATE STATISTICS dbo.fgedu_sales IX_fgedu_sales_region;
GO

# 更新单个表的所有统计信息，指定采样率
UPDATE STATISTICS dbo.fgedu_sales WITH SAMPLE 50 PERCENT;
GO

# 更新单个表的所有统计信息，使用完全扫描
UPDATE STATISTICS dbo.fgedu_sales WITH FULLSCAN;
GO

# 更新数据库中所有表的统计信息
EXEC sp_updatestats;
GO

# 更新数据库中所有表的统计信息，使用完全扫描
EXEC sp_updatestats ‘RESAMPLE’;
GO

# 查看表的统计信息
DBCC SHOW_STATISTICS(‘dbo.fgedu_sales’, IX_fgedu_sales_region);
GO

# 结果
/*
Name Updated Rows Rows Sampled Steps Density Average Key Length String Index
—————– ——————- ——— ————– —– ————- —————— ————
IX_fgedu_sales_region 2023-12-01 10:00:00.000 1000000 100000 20 0.2500000 26 NO

All Density Average Length Columns
————- ————– ——-
0.2500000 26 region

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
———— ———— ——— ——————– ————–
East 249999.75 250000 0 1
North 249999.75 250000 0 1
South 249999.75 250000 0 1
West 0 250000 0 1
*/

# 查看数据库中所有统计信息的状态
SELECT
t.name AS table_name,
s.name AS stats_name,
STATS_DATE(t.object_id, s.stats_id) AS last_updated,
s.auto_created,
s.user_created,
s.no_recompute
FROM sys.tables t
JOIN sys.stats s ON t.object_id = s.object_id
WHERE t.name LIKE ‘fgedu%’
ORDER BY t.name, s.name;
GO

# 创建统计信息监控表
CREATE TABLE dbo.fgedu_stats_monitor (
monitor_id INT PRIMARY KEY IDENTITY,
table_name NVARCHAR(128) NOT NULL,
stats_name NVARCHAR(128) NOT NULL,
last_updated DATETIME NOT NULL,
update_time DATETIME NOT NULL DEFAULT GETDATE(),
rows INT NOT NULL,
rows_sampled INT NOT NULL,
steps INT NOT NULL,
density FLOAT NOT NULL
);
GO

# 创建统计信息监控存储过程
CREATE PROCEDURE dbo.usp_monitor_stats
AS
BEGIN
SET NOCOUNT ON;

DECLARE @table_name NVARCHAR(128);
DECLARE @stats_name NVARCHAR(128);
DECLARE @last_updated DATETIME;
DECLARE @rows INT;
DECLARE @rows_sampled INT;
DECLARE @steps INT;
DECLARE @density FLOAT;

DECLARE stats_cursor CURSOR FOR
SELECT
t.name AS table_name,
s.name AS stats_name,
STATS_DATE(t.object_id, s.stats_id) AS last_updated
FROM sys.tables t
JOIN sys.stats s ON t.object_id = s.object_id
WHERE t.name LIKE ‘fgedu%’;

OPEN stats_cursor;
FETCH NEXT FROM stats_cursor INTO @table_name, @stats_name, @last_updated;

WHILE @@FETCH_STATUS = 0
BEGIN
— 执行DBCC SHOW_STATISTICS获取详细信息
— 这里需要使用动态SQL来执行DBCC命令
DECLARE @sql NVARCHAR(MAX);
SET @sql = N’DBCC SHOW_STATISTICS(”’ + @table_name + ”’, ”’ + @stats_name + ”’) WITH STAT_HEADER’;

— 这里可以使用临时表来存储DBCC结果
— 为了简化，这里只存储基本信息
INSERT INTO dbo.fgedu_stats_monitor (
table_name, stats_name, last_updated, rows, rows_sampled, steps, density
) VALUES (
@table_name, @stats_name, @last_updated, 0, 0, 0, 0
);

FETCH NEXT FROM stats_cursor INTO @table_name, @stats_name, @last_updated;
END;

CLOSE stats_cursor;
DEALLOCATE stats_cursor;
END;
GO

# 执行统计信息监控
EXEC dbo.usp_monitor_stats;
GO

# 查看监控结果
SELECT * FROM dbo.fgedu_stats_monitor ORDER BY update_time DESC;
GO

# 创建测试表
CREATE TABLE dbo.fgedu_orders (
order_id INT PRIMARY KEY IDENTITY,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
status NVARCHAR(50) NOT NULL,
create_time DATETIME DEFAULT GETDATE()
);
GO

# 插入测试数据
INSERT INTO dbo.fgedu_orders (
customer_id, order_date, total_amount, status
) VALUES
(1, ‘2023-01-01’, 1000.00, ‘Completed’),
(2, ‘2023-01-02’, 2000.00, ‘Pending’),
(3, ‘2023-01-03’, 1500.00, ‘Completed’),
(4, ‘2023-01-04’, 3000.00, ‘Pending’),
(5, ‘2023-01-05’, 2500.00, ‘Completed’);
GO

# 查看统计信息
SELECT
t.name AS table_name,
s.name AS stats_name,
STATS_DATE(t.object_id, s.stats_id) AS last_updated
FROM sys.tables t
JOIN sys.stats s ON t.object_id = s.object_id
WHERE t.name = ‘fgedu_orders’;
GO

# 结果
/*
table_name stats_name last_updated
———– ——————– ———————–
fgedu_orders PK__fgedu_or__46596229A1234567 2023-12-01 10:00:00.000
*/

# 插入大量数据
DECLARE @i INT = 1;
WHILE @i <= 10000 BEGIN INSERT INTO dbo.fgedu_orders ( customer_id, order_date, total_amount, status ) VALUES (@i, DATEADD(day, @i % 365, '2023-01-01'), 1000.00 + @i, CASE WHEN @i % 2 = 0 THEN 'Completed' ELSE 'Pending' END); SET @i = @i + 1; END; GO # 查看统计信息更新情况 SELECT t.name AS table_name, s.name AS stats_name, STATS_DATE(t.object_id, s.stats_id) AS last_updated FROM sys.tables t JOIN sys.stats s ON t.object_id = s.object_id WHERE t.name = 'fgedu_orders'; GO # 手动更新统计信息 UPDATE STATISTICS dbo.fgedu_orders WITH FULLSCAN; GO # 再次查看统计信息更新情况 SELECT t.name AS table_name, s.name AS stats_name, STATS_DATE(t.object_id, s.stats_id) AS last_updated FROM sys.tables t JOIN sys.stats s ON t.object_id = s.object_id WHERE t.name = 'fgedu_orders'; GO

# 创建测试表
CREATE TABLE dbo.fgedu_products (
product_id INT PRIMARY KEY IDENTITY,
product_name NVARCHAR(100) NOT NULL,
category NVARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL,
create_time DATETIME DEFAULT GETDATE()
);
GO

# 插入测试数据
INSERT INTO dbo.fgedu_products (
product_name, category, price, stock
) VALUES
(‘Product 1’, ‘Electronics’, 1000.00, 100),
(‘Product 2’, ‘Electronics’, 2000.00, 50),
(‘Product 3’, ‘Clothing’, 500.00, 200),
(‘Product 4’, ‘Clothing’, 800.00, 150),
(‘Product 5’, ‘Home’, 1200.00, 80);
GO

# 创建索引
CREATE INDEX IX_fgedu_products_category ON dbo.fgedu_products(category);
GO

# 查看统计信息
DBCC SHOW_STATISTICS(‘dbo.fgedu_products’, IX_fgedu_products_category);
GO

# 执行查询并查看执行计划
SELECT * FROM dbo.fgedu_products WHERE category = ‘Electronics’;
GO

# 插入大量数据，使数据分布发生变化
DECLARE @i INT = 1;
WHILE @i <= 10000 BEGIN INSERT INTO dbo.fgedu_products ( product_name, category, price, stock ) VALUES ('Product ' + CAST(@i + 5 AS NVARCHAR(10)), CASE WHEN @i % 10 = 0 THEN 'Electronics' ELSE 'Clothing' END, 1000.00 + @i, 100 + @i); SET @i = @i + 1; END; GO # 再次执行查询并查看执行计划 SELECT * FROM dbo.fgedu_products WHERE category = 'Electronics'; GO # 更新统计信息 UPDATE STATISTICS dbo.fgedu_products IX_fgedu_products_category WITH FULLSCAN; GO # 再次执行查询并查看执行计划 SELECT * FROM dbo.fgedu_products WHERE category = 'Electronics'; GO

# 查看统计信息更新历史
SELECT
t.name AS table_name,
s.name AS stats_name,
STATS_DATE(t.object_id, s.stats_id) AS last_updated,
s.auto_created,
s.user_created
FROM sys.tables t
JOIN sys.stats s ON t.object_id = s.object_id
WHERE t.name LIKE ‘fgedu%’
ORDER BY last_updated ASC;
GO

# 检查统计信息是否过期
SELECT
t.name AS table_name,
s.name AS stats_name,
STATS_DATE(t.object_id, s.stats_id) AS last_updated,
DATEDIFF(day, STATS_DATE(t.object_id, s.stats_id), GETDATE()) AS days_since_update
FROM sys.tables t
JOIN sys.stats s ON t.object_id = s.object_id
WHERE t.name LIKE ‘fgedu%’
AND STATS_DATE(t.object_id, s.stats_id) < DATEADD(day, -7, GETDATE()) ORDER BY days_since_update DESC; GO # 检查表的修改统计信息 SELECT t.name AS table_name, ps.row_count, ps.modification_counter FROM sys.tables t JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id WHERE t.name LIKE 'fgedu%' ORDER BY ps.modification_counter DESC; GO # 强制更新过期的统计信息 DECLARE @table_name NVARCHAR(128); DECLARE @sql NVARCHAR(MAX); DECLARE table_cursor CURSOR FOR SELECT t.name AS table_name FROM sys.tables t JOIN sys.stats s ON t.object_id = s.object_id WHERE t.name LIKE 'fgedu%' AND STATS_DATE(t.object_id, s.stats_id) < DATEADD(day, -7, GETDATE()) GROUP BY t.name; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'UPDATE STATISTICS dbo.' + @table_name + ' WITH FULLSCAN'; EXEC sp_executesql @sql; PRINT 'Updated statistics for table: ' + @table_name; FETCH NEXT FROM table_cursor INTO @table_name; END; CLOSE table_cursor; DEALLOCATE table_cursor; GO