526互联

SQLServer性能优化之二

发布时间 2023-12-02 18:11:04作者: 济南小老虎

SQLServer性能优化之二

背景

优化了机器的硬件配置之后性能好了很多
但是偶尔还是会出现阻塞.
SQL总是奇奇怪怪的. 
其实第一天时就感觉可能是索引存在问题. 
但是dbcc 重建所有数据库的索引太慢了. 
所以作罢了, 从HDD传输到SSD后大部分功能已经可以用了
以为问题就此解决, 但是跟踪发现还是存在风险.
所以继续跟踪一下, 怀疑跟索引的碎片率太高有关系.

SQLServer索引碎片的判断方法

SQLServer 判断索引碎片的方法
# 查看索引大小, 以及碎片情况
SELECT OBJECT_NAME(sys.indexes.OBJECT_ID) AS tableName,
 sys.indexes.name,   
 page_count,
 (page_count*8.0/1024)AS 'IndexSizeMB',
 avg_page_space_used_in_percent,
 avg_fragmentation_in_percent,
 record_count,avg_record_size_in_bytes,
index_type_desc,
fragment_count 
from sys.dm_db_index_physical_stats(db_id('dbname'),object_id('tablename'), null,null,'sampled') 
 JOIN sys.indexes  ON   sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
 AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id order by IndexSizeMB desc 

# 查看索引碎片率高于90%的表和索引情况
 SELECT object_name(object_id) ,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,
  fragment_count,avg_fragment_size_in_pages,page_count,record_count,
  avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('Yourschema'), 
OBJECT_ID(''),NULL,NULL,'Sampled')  
WHERE avg_fragmentation_in_percent>90  order by avg_fragmentation_in_percent desc 

# 查看具体表的索引碎片情况
SELECT object_name(object_id) ,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,
  fragment_count,avg_fragment_size_in_pages,page_count,record_count,
  avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('Yourschema'), 
OBJECT_ID(''),NULL,NULL,'Sampled')  
WHERE object_name(object_id) = 'tmjsdata'

比较简单的重建索引的办法

Oracle重新获取统计信息
exec dbms_stats.gather_schema_stats(ownname =>'username',options => 'GATHER',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 4)

Oracle 还可以这样:
select 'alter index '||index_name|| ' rebuild;' from user_indexes

----------------------------------------------------------------

SQLSERVER重新新建所有表的索引.
EXEC SP_MSFOREACHTABLE 'dbcc DBreindex("?")'

SQLSERVER重新新建所有表的索引.
EXEC SP_UPDATESTATS;

对单表进行索引重建

 ALTER INDEX ALL  ON Yourschema.xxxx  REBUILD  
 WITH(FILLFACTOR=80, SORT_IN_TEMPDB=ON ,STATISTICS_NORECOMPUTE = ON )

 使用SQL实现对所有表的索引重建
 select 'ALTER INDEX ALL  ON Yourschema.' + name +  ' REBUILD' from sys.objects where schema_id = 5 AND type = 'U'

注意,需要先将自己的架构对应的id获取到.

关于性能的思考

前几天学习了 Oracle 高低水位线对性能的影响
今天在查看SQLServer的资料时感觉两者其实是相似的

大量的插入,删除表中的记录对数据库表性能会造成很大的影响
会对OLTP或者是OLAP产生很大的性能压力
并且会造成较多的磁盘随机读写的损耗, 导致性能下降.

但是Oracle的expdp/exp的备份 可以选择不导出统计信息,导入之后由系统任务自动进行统计信息的获取与更新

但是今天进行了SQLServer数据库的备份恢复验证, 发现SQLSERVER的备份恢复并不会导致数据库的索引碎片下降. 
所以这一块SQLServer 必须等在一定的停机窗口进行相关的处理.

关于重建索引的性能

在应用不停,并且产品有计划任务的情况, 对一个四百万记录的单表执行索引重建. 

表一共四个索引, 涉及五个列, 但是耗费了 六个小时都没有成功.
我终止了服务, 重启了数据库后执行全局的 索引重建
耗时 20分钟就处理完成 
重建了大约 1.3万个索引信息. 

所以重建索引时建议是在停机窗口, 避免有对表的增删改查时进行表索引的重建.
不然会导致非常严重的卡顿, 对业务对产品都非常不好. 

另外 重建索引之前数据文件100G, 能够所以到 90G左右, 重建了所有的索引, 数据库表可以收缩到50G左右
磁盘空间也得到了巨大的释放.

一个简单的总结

1. 重建索引的成本其实很高, 但并不是不可接受. 
2. 重建索引建议在停机维护的阶段进行, 并且要关闭对表的增删改长的大量处理, 便于提高效率.
3. 对插入删除大量的表建议使用分区表的模式进行, 并且设置好分区模式. 这样可以减少分区碎片对产品性能的性能. 
4. 感觉数据库出现阻塞时比较难以根除. 理论上应该采用逻辑删除而不是物理删除, 并且定期清理归档历史数据的方式为最优. 
5. Oracle可以通过备份恢复对数据库表的相对位置进行更改, 但是SQLSERVER的备份恢复模式没有此类的效果(但是速度快)
6. 重要的数据库必须有专业的数据库专家进行定期的指标收集与分析. 避免小问题累积成大问题导致宕机卡顿等.