SQL Server 性能调优(io)

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
日志服务 SLS,月写入数据量 50GB 1个月
简介:

 

目录

诊断磁盘io问题

常见的磁盘问题

容量替代了性能

负载隔离配置有问题

分区对齐配置有问题

总结

 

关于io这一块,前面的东西如磁盘大小,磁盘带宽,随机读取写入,顺序读取写入,raid选择,DASSAN比较都不讲了。

直接就开始讲关于io问题的诊断和一般问题的处理。

io这一块东西比较特殊,是数据库的重点,因为数据库一般都是io密集型产品,为了让io速度更快,那么数据库会使用内存来代替磁盘。

io出现问题,可能会连带其他资源的问题。cpu,内存,网络。所以在分析问题的时候对数据库结构的了解是必不可少的。

 

诊断磁盘io问题

一般使用的工具就是perfmon特别是 physical disk\disk reads/sec  physical disk\ disk writes/sec

对于这2个性能指标有一个阀值的表格:

• Less than 10 ms = good performance

• Between 10 ms and 20 ms = slow performance

• Between 20 ms and 50 ms = poor performance

• Greater than 50 ms = significant performance problem.

 

还有一些就是 PAGEIOLATCH_*,ASYNC_IO_COMPLETION,IO_COMPLETION,WRITELOG这些等待事件。关于这些 wait event的查询可以看SQL Server 性能调优(方法论 

 

常见的磁盘问题

在此之前,我们先要排除集中状况就是,接下来讨论的和miss index,较差性能的写入,多余的io无关。单纯从io层面讨论io的配置问题。

io的配置问题最常见的有以下4中情况:

• 容量替代了性能

• 负载隔离配置有问题

• 分区对齐配置有问题

• SAN的带宽配置有问题(这个SAN 小弟不是很熟悉,那么就不讨论了,如果想了解情况可以查看原书:《Troubleshooting SQL Server: A Guide for the Accidental DBA)

 

容量替代了性能

一个800g的数据库可以存放在11t的磁盘中,也可以是通过raid拆分为多个磁盘,当然一个磁盘的性能明显比多个磁盘的要差。

负载隔离配置有问题

可能读到这里不太清楚负载隔离是什么,简单的说就是数据文件要放在哪里,日志文件要放在哪里,tempdb数据库要放在哪里。

负载隔离对sql server 存储计划来说是很重要的,最简单的,也是容被忽略的,为啥,因为不知道日志文件数据文件的区别,不知道生产库和tempdb之间的关系和区别。日志文件是顺序写入的,数据文件是随即读写的,如果你把2个放在同一个磁盘或者raid里面,要日志文件的顺序读写算啥了。都体现不出优点来了。

所以日志文件和数据文件要分开无疑。

分区对齐配置有问题

有一片相关的文章:《Disk Partition Alignment Best Practices for SQL Server》,很多人都不知道分区对齐是什么概念,但是又20%-30%的性能都浪费在上面。

关键的问题就是有63个保留扇区被用来MBR。当windows创建分区的时候就会保留31.5kb的空间供MBR使用。这样就照成了对齐,和raid的条带单元代销对齐了。raid条带的单元大小是从4kb512kb的。

多数磁盘一个扇区的大小是512B,但是 ssd4kb一个扇区,sql server 的推荐分配单元大小是64kb但是ntfs的大小则是4kb,根据推荐值64kb的大小就是128个扇区。但是保留扇区是63个,当发生io的时候,sql server 读取128个扇区,那么就会发生2io,从第一个磁盘中读取65个扇区,从第二个磁盘中读取63个扇区。为了阻止,windows 的保留空间变为了1mb,那么就能包容所有的raid单元条带的大小。但是windows2008以下的版本默认是对齐的。就需要命令行下调用diskpar或者diskpart来显示的对齐。

查看是否对齐的唯一方法就是wmic命令:

wmic partition get BlockSizeStartingOffset, Name, Index

如果用Startingofferset/raid条带大小,如果为整数那么就是对齐的否则就是对齐的。

分区保留扇区只能在磁盘的创建分区的时候设置。

create partition primary align=64

分区对齐后,可以给分区分配盘符并且在格式化的时候指定分配单元大小,这样就避免了没对齐带来的性能冲击。



总结

关于隔离的内容个人觉得讲得不是很深入,大家可以去看原书:《Disk Partition Alignment Best Practices for SQL Server》第二章的Choosing the Right RAID Level下的Workload considerations小节,详细描述了关于数据文件,日志文件,tempdb的特点,并建议使用哪种raid 比较合适。

这里的io问题主要是讲io的配置问题。io的配置如果撇开SAN不讲,还是挺简单的。

 


    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2012/03/02/2377372.html,如需转载请自行联系原作者





相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
169 2
|
1月前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
97 10
|
1月前
|
SQL 关系型数据库 MySQL
惊呆:where 1=1 可能严重影响性能,差了10多倍,快去排查你的 sql
老架构师尼恩在读者交流群中分享了关于MySQL中“where 1=1”条件的性能影响及其解决方案。该条件在动态SQL中常用,但可能在无真实条件时导致全表扫描,严重影响性能。尼恩建议通过其他条件或SQL子句命中索引,或使用MyBatis的`<where>`标签来避免性能问题。他还提供了详细的执行计划分析和优化建议,帮助大家在面试中展示深厚的技术功底,赢得面试官的青睐。更多内容可参考《尼恩Java面试宝典PDF》。
|
3月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
587 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
2月前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差?其实可以这样优化
遇到SQL 子查询性能很差?其实可以这样优化
110 2
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
64 1
|
2月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
2月前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
2月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化策略
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生不利影响