sqlserver 重建索引和update 统计数据的区别

发布网友发布时间：2022-04-10 08:56

我来回答

共2个回答

懂视网时间：2022-04-10 13:18

在创建索引的时候，SQLServer就会在索引列上创建统计信息。简单来说，统计信息就是索引或者列上能够描述数据分布的数据。

查询选择性：

公式：列上不重复数据的总数/列上的数据总数

选择性越高，索引性能越好，当上述公式的值为1时，可以用于做为主键或者唯一键。

创建和更新统计信息：

统计信息有助于SQLServer优化引擎选择合适的索引及相关操作用于执行SELECT语句。有两个方式创建和更新统计信息：

1、手动创建和更新统计信息

2、自动创建和更新统计信息

准备工作：

在开始之前，先来看看如何查找数据库的当前统计信息设置：

[sql] view plain copy print?

SELECT CASE WHEN DATABASEPROPERTYEX(‘master‘, ‘IsAutoCreateStatistics‘) = 1
THEN ‘Yes‘
ELSE ‘No‘
END AS ‘IsAutoCreateStatistics?‘ ,
CASE WHEN DATABASEPROPERTYEX(‘Master‘, ‘IsAutoUpdateStatistics‘) = 1
THEN ‘Yes‘
ELSE ‘No‘
END AS ‘IsAutoUpdateStatistics?‘ ,
CASE WHEN DATABASEPROPERTYEX(‘Master‘, ‘Is_Auto_Update_stats_async_on‘) = 1
THEN ‘Yes‘
ELSE ‘No‘
END AS ‘IsAutoUpdateStatsaAyncOn?‘
GO

SELECT CASE WHEN DATABASEPROPERTYEX(‘master‘, ‘IsAutoCreateStatistics‘) = 1
  THEN ‘Yes‘
  ELSE ‘No‘
 END AS ‘IsAutoCreateStatistics?‘ ,
 CASE WHEN DATABASEPROPERTYEX(‘Master‘, ‘IsAutoUpdateStatistics‘) = 1
  THEN ‘Yes‘
  ELSE ‘No‘
 END AS ‘IsAutoUpdateStatistics?‘ ,
 CASE WHEN DATABASEPROPERTYEX(‘Master‘, ‘Is_Auto_Update_stats_async_on‘) = 1
  THEN ‘Yes‘
  ELSE ‘No‘
 END AS ‘IsAutoUpdateStatsaAyncOn?‘
GO

下面的语句用于显示where子句中的数据库或者表的统计信息情况：

[sql] view plain copy print?

SELECT object_id ,
OBJECT_NAME(object_id) AS TableName ,
name AS StatisticsName ,
auto_created
FROM sys.stats
--where object_id=OBJECT_ID(‘Sales.SalesOrderHeader‘)
ORDER BY object_id DESC
GO

SELECT object_id ,

 OBJECT_NAME(object_id) AS TableName ,

 name AS StatisticsName ,

 auto_created

FROM sys.stats

--where object_id=OBJECT_ID(‘Sales.SalesOrderHeader‘)

ORDER BY object_id DESC 

GO

还可以使用以下方式查看：

[sql] view plain copy print?

sp_helpstats ‘Sales.SalesOrderHeader‘

sp_helpstats ‘Sales.SalesOrderHeader‘

步骤：

1、现在开始来看看创建和更新统计信息的不同方式，在数据库级别，有一个选项，默认为ON，这个选项是：Auto_Create_Statistics：

[sql] view plain copy print?

ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON

 ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON

2、启用同步创建列上统计信息的选项，Auto_Create_Statistics，当执行一个查询一个精确数据量的数据时，优化引擎会在这个列上创建一个柱状图表。由SQLServer创建的统计信息以_WA开头，可以看看这些列表：

[sql] view plain copy print?

SELECT st.name AS StatName ,
COL_NAME(stc.object_id, stc.column_id) AS ColumnName ,
OBJECT_NAME(st.object_id) AS TableName
FROM sys.stats AS st
INNER JOIN sys.stats_columns AS stc ON st.object_id = stc.object_id
AND st.stats_id = stc.stats_id
WHERE st.name LIKE ‘_WA%‘

SELECT st.name AS StatName ,

 COL_NAME(stc.object_id, stc.column_id) AS ColumnName ,

 OBJECT_NAME(st.object_id) AS TableName

FROM sys.stats AS st

 INNER JOIN sys.stats_columns AS stc ON st.object_id = stc.object_id

      AND st.stats_id = stc.stats_id

WHERE st.name LIKE ‘_WA%‘

3、上面的统计信息不会因为Auto_Create_Statistics选项设为ON而结束。这些是强制你的统计信息更新以保证性能优秀。这个只是定义你的统计信息是否同步更新。默认情况下这个选项是为ON的。但是有时候不一定符合你的要求，此时可以使用手动更新计划：

[sql] view plain copy print?

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON

4、 Auto_Update_Statistics选项会在创建索引时、通过Auto_Create_Statistics或者用户使用CREATE STATISTICS命令手动创建统计信息时自动更新统计信息，下面命令使用异步方式更新统计信息：

[sql] view plain copy print?

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON

5、此时来看看执行上面语句后的数据库统计信息配置情况：

[sql] view plain copy print?

SELECT is_auto_update_stats_async_on ,
is_auto_create_stats_on ,
is_auto_update_stats_on
FROM sys.databases
WHERE name = ‘AdventureWorks‘

 SELECT is_auto_update_stats_async_on ,

 is_auto_create_stats_on ,

 is_auto_update_stats_on

FROM sys.databases

WHERE name = ‘AdventureWorks‘

6、上面的方式均为自动创建和更新统计信息，现在来看看如何手动实现：

[sql] view plain copy print?

--创建统计信息在Sales.SalesOrderHeader表的DueDate列上
CREATE STATISTICS st_DueDate_SalesOrderHeader ON Sales.SalesOrderHeader(DueDate)
GO
--更新Sales.SalesOrderHeader表的全部统计信息
UPDATE STATISTICS Sales.SalesOrderHeader
GO
--更新Sales.SalesOrderHeader表的st_DueDate_SalesOrderHeader统计信息
UPDATE STATISTICS Sales.SalesOrderHeader st_DueDate_SalesOrderHeader
GO
--更新数据库中所有可用的统计信息
EXEC sys.sp_updatestats
GO
--手动删除统计信息
DROP STATISTICS Sales.SalesOrderHeader.st_DueDate_SalesOrderHeader
GO

--创建统计信息在Sales.SalesOrderHeader表的DueDate列上

CREATE STATISTICS st_DueDate_SalesOrderHeader ON Sales.SalesOrderHeader(DueDate)

GO

--更新Sales.SalesOrderHeader表的全部统计信息

UPDATE STATISTICS Sales.SalesOrderHeader

GO

 

--更新Sales.SalesOrderHeader表的st_DueDate_SalesOrderHeader统计信息

UPDATE STATISTICS Sales.SalesOrderHeader st_DueDate_SalesOrderHeader

GO

 

--更新数据库中所有可用的统计信息

EXEC sys.sp_updatestats

GO

--手动删除统计信息

DROP STATISTICS Sales.SalesOrderHeader.st_DueDate_SalesOrderHeader

GO

分析：

当索引创建时，优化器会创建统计信息到索引列所在的表或者视图上，除此之外，如果对Auto_Create_Statistics选项设置了ON，优化器会创建一个单列统计信息，及时它没有出现在查询的所需列上。如果你觉得一些查询性能有问题，检查所有谓词，如果这些列缺失了统计信息，你可以手动增加，有时候，DTA（数据库优化顾问）也会建议你创建统计信息。

一般情况下，在查询编译之前，如果开启了同步更新统计信息，SQLServer如果发现统计信息过时，会引发更新统计信息的操作，然后你的查询就会使用上实时的统计信息。而这个操作会阻塞查询，知道更新结束，但是不会保留这些查询，它会更新统计信息以便下次运行查询的时候可以使用上较新的统计信息。

扩充知识：

默认情况下，只有sysadmin/db_owner/对象的创建者这三种角色的成员才有权限创建和更新统计信息。

柱状图：

柱状图是一类由SQLServer为了统计信息而生成的表。可以认为是一个显示对应列上统计信息最大和最小值范围的报表。

第十二章——SQLServer统计信息（1）——创建和更新统计信息

标签：启用 order by 权限 column http 命令 title like 列表

热心网友时间：2022-04-10 10:26

这个东西没有必须，谁也无法规定你一定要怎样，搞清楚做这件事是为了什么，你才知道有没有必要做。重建索引的目的是为了让索引更高效的工作，如果一个索引长时间的没有整理，那么整个索引上的数据就会杂乱无章的排列，无法起到提高效率的作用，...