最喜欢的性能调优技巧
喜爱的性能调优技巧
稍微离题一点,但如果您对这些问题有控制权...
高级别和高影响。
- 对于高IO环境,请确保您的磁盘是RAID 10或RAID 0+1,或者是RAID 1和RAID 0的某种嵌套实现。
- 不要使用小于1500K的驱动器。
- 确保您的磁盘仅用于您的数据库。即没有日志记录,没有操作系统。
- 关闭自动增长或类似的功能。让数据库使用预期的所有存储空间,而不仅仅是当前使用的空间。
- 根据查询类型设计模式和索引。
- 如果是日志类型的表(仅插入),并且必须在数据库中,请勿对其进行索引。
- 如果您要进行大量的报告(具有许多连接的复杂选择),那么您应该考虑使用带有星型或雪花模式的数据仓库。
- 不要害怕为了性能而复制数据!
喜爱的性能调优技巧
在进行查询之前,要对最佳运行路径有一个相当清晰的了解。
始终检查查询计划。
打开STATS,以便可以检查IO和CPU的性能。专注于降低这些数字,而不是仅仅关注查询时间(因为查询时间可能受其他活动、缓存等影响)。
查找大量行进入运算符,但出来的行数很少。通常,索引可以通过限制输入行数来提高性能(从而节省磁盘读取)。
首先关注成本最高的子树。更改该子树通常可以改变整个查询计划。
常见的问题包括:
- 如果有很多连接,有时Sql Server会选择展开连接,然后应用WHERE子句。您通常可以通过将WHERE条件移到JOIN子句中或带有内联条件的派生表中来解决此问题。视图可能会导致相同的问题。
- 子优化连接(LOOP vs HASH vs MERGE)。我的经验法则是,当顶部行与底部行相比非常少时使用LOOP连接,当集合大致相等且有序时使用MERGE连接,并对其他情况使用HASH连接。添加连接提示将使您能够测试自己的理论。
- 参数嗅探。如果一开始使用的存储过程的值不现实(比如用于测试的值),那么缓存的查询计划可能对生产值不优化。再次运行WITH RECOMPILE应该可以验证这一点。对于一些存储过程,特别是处理可变大小范围的存储过程(比如在今天和昨天之间的所有日期 - 这将涉及INDEX SEEK - 或者在去年和今年之间的所有日期 - 这将更适合INDEX SCAN),您可能需要每次都运行WITH RECOMPILE。
- 错误的缩进... 好吧,Sql Server对此没有问题 - 但我确实发现在修复格式之前很难理解查询。
包括错误缩进是个不错的点子。格式很重要! 🙂
喜欢性能调优技巧
当我被别人询问关于优化方面的问题时,我总是给出下面这个便利的列表。
我们主要使用Sybase,但是大部分建议都适用于其他数据库。例如,SQL Server自带了一系列的性能监控/调优工具,但如果你没有类似的工具(甚至如果有),我建议考虑以下几点...
99%的问题都是由于在连接中使用了太多的表。解决方法是先进行一半的连接(使用其中的一些表),并将结果缓存到临时表中。然后再使用这个临时表进行剩余的查询连接。
查询优化清单
- 对基础表运行UPDATE STATISTICS
- 许多系统会定期按周运行此任务
- 从基础表中删除记录(可能存档已删除的记录)
- 考虑每天或每周自动执行此操作
- 重建索引
- 重建表(导出/导入数据)
- 备份/恢复数据库(激进的方法,但可能修复损坏)
- 构建新的、更合适的索引
- 运行DBCC来检查数据库中是否存在可能的损坏
- 锁/死锁
- 确保没有其他进程在数据库中运行
- 特别是DBCC
- 是否使用了行级锁或页级锁?
- 在开始查询之前锁定表
- 检查所有进程是否以相同的顺序访问表
- 索引是否被适当地使用?
- 只有在两个表达式完全相同的数据类型时,连接才会使用索引
- 只有在查询中匹配索引的第一个字段时,索引才会被使用
- 是否在适当的地方使用了聚集索引?
- 范围数据
- WHERE field between value1 and value2
- 小连接是好连接
- 默认情况下,优化器一次只考虑4个表
- 这意味着在连接超过4个表的情况下,它有很大可能选择一个非最优的查询计划
- 分解连接
- 是否可以将连接分解?
- 将外键预先选择到临时表中
- 只连接一半并将结果放入临时表中
- 是否使用了正确类型的临时表?
- 对于大量数据(成千上万行),#temp
表的性能可能要好于使用变量。
- 维护汇总表
- 使用触发器在基础表上构建
- 每天/每小时/等等构建
- 临时构建
- 增量构建或拆除/重建
- 使用SET SHOWPLAN ON查看查询计划
- 使用SET STATS IO ON查看实际发生的情况
- 使用pragma强制使用索引:(index: myindex)
- 使用SET FORCEPLAN ON强制表的顺序
- 参数嗅探:
- 将存储过程分成两个
- 从proc1调用proc2
- 允许优化器在proc1更改了索引的情况下选择proc2中的索引
- 是否可以改进硬件?
- 您运行的时间是什么时候?是否有更安静的时间段?
- 是否正在运行复制服务器(或其他无停机的进程)?是否可以暂停它?例如每小时运行一次?
至于你提到的哪一部分?
这是一些很酷的东西,但我希望你能提供一些对某些观点的参考。例如:我从来没有听说过优化器一次只考虑4个表的说法。我不明白这怎么可能是正确的。你能提供一些关于这个问题的参考吗?我很想知道你是从哪里得到这个观点的。