最喜欢的性能调整技巧

当您有一个需要性能调优的查询或存储过程时,您尝试的第一件事是什么？

1> AJ...：

---

这是一个方便的花花公子列表,我总是给一些问我优化的人.
我们主要使用Sybase,但大多数建议都将全面适用.

例如,SQL Server附带了许多性能监视/调整位,但是如果你没有这样的东西(甚至可能没有),那么我会考虑以下内容......

我看到的99%的问题是由于在连接中放入太多表而造成的.对此的修复是执行连接的一半(使用某些表)并将结果缓存到临时表中.然后在该临时表上继续查询的其余部分.

查询优化清单

在基础表上运行UPDATE STATISTICS

许多系统将此作为计划的每周工作运行

从基础表中删除记录(可能存档已删除的记录)

考虑每天一次或每周一次自动执行此操作.

重建索引

重建表(bcp数据输出/输入)

转储/重新加载数据库(极端,但可能会修复损坏)

建立新的,更合适的索引

运行DBCC以查看数据库中是否存在可能的损坏

锁定/死锁

确保数据库中没有运行其他进程

特别是DBCC

您使用行级或页级锁定吗？

在开始查询之前独占锁定表

检查所有进程是否以相同的顺序访问表

指数是否正确使用？

如果两个表达式完全相同,则联接将仅使用索引

仅当索引中的第一个字段在查询中匹配时才使用索引

是否在适当的地方使用聚集索引？

范围数据

value1和value2之间的WHERE字段

小联盟是很好的联盟

默认情况下,优化器一次只会考虑表4.

这意味着在具有4个以上表的联接中,很有可能选择非最佳查询计划

分手加入

你可以分手吗？

将外键预先选择到临时表中

做一半的连接并将结果放入临时表中

你使用的是正确的临时桌吗？

#temp表可能比@table具有大量(数千行)的变量执行得更好.

维护汇总表

在底层表上使用触发器构建

每日/每小时/等等

临时建立

逐步构建或拆除/重建

使用SET SHOWPLAN ON查看查询计划

查看SET STATS IO ON实际发生的情况

使用pragma强制索引:(index:myindex)

使用SET FORCEPLAN ON强制执行表顺序

参数嗅探:

将存储过程分为2

从proc1调用proc2

如果proc1更改了@parameter,则允许优化器在proc2中选择索引

你能改进你的硬件吗？

你几点跑？有更安静的时间吗？

Replication Server(或其他不间断进程)是否正在运行？你可以暂停吗？运行它,例如.每小时？

---

你指的是哪一点？

---

这是一些很酷的东西,但我希望你能提供一些索赔.例如:我从未听说过优化在连接中一次只考虑4个表.我不明白这是怎么回事.你能特别提供一些参考资料吗？我很想知道你在哪里得到这个.

2> Mark Bracket..：

---

非常了解运行查询的最佳路径.

检查查询计划 - 始终.

打开STATS,以便检查IO和CPU性能.专注于驱动这些数字,不一定是查询时间(因为它可能受到其他活动,缓存等的影响).

寻找进入运营商的大量行,但是数量很少.通常,索引可以通过限制进入的行数来帮助(这节省了磁盘读取).

首先关注最大的成本子树.更改该子树通常可以更改整个查询计划.

我见过的常见问题是:

如果有很多连接,有时Sql Server会选择扩展连接,然后应用WHERE子句.您通常可以通过将WHERE条件移动到JOIN子句或具有内联条件的派生表来解决此问题.视图可能会导致相同的问题.

次优连接(LOOP vs HASH vs MERGE).我的经验法则是当顶行与底部相比只有很少的行时使用LOOP连接,当集合大致相等和有序时,使用MERGE,对其他所有内容使用HASH.添加连接提示可以让您测试理论.

参数嗅探.如果您首先使用不切实际的值运行存储过程(例如,用于测试),则缓存的查询计划可能不是您的生产值的最佳值.再次使用RECOMPILE运行应验证这一点.对于一些存储的特效,特别是那些与应对不同大小的范围(比如,昨天和今天之间的所有日期 - 这将需要一个索引查找 - 或者说,去年和今年之间的所有日期 - 这将是索引扫描过得更好)您可能每次都必须使用RECOMPILE运行它.

坏缩进...好吧,所以Sql Server没有这个问题 - 但我确实发现在我修复格式化之前无法理解查询.

3> jason saldo..：

---

稍微偏离主题,但如果您可以控制这些问题......
高水平和高影响.

对于高IO环境,请确保您的磁盘适用于RAID 10或RAID 0 + 1或某些嵌套的raid 1和raid 0实现.

不要使用低于1500K的驱动器.

确保您的磁盘仅用于您的数据库.IE没有记录没有操作系统.

关闭自动增长或类似功能.让数据库使用预期的所有存储.不一定是目前使用的.

设计类型查询的模式和索引.

如果它是一个日志类型表(仅插入)并且必须在DB中,则不要将其编入索引.

如果你做了大量的报告(复杂选择有很多连接),那么你应该考虑创建一个星型或雪花模式的数据仓库.

不要害怕复制数据以换取性能!

4> 小智..：

---

CREATE INDEX

确保您WHERE和JOIN条款可以使用索引.这将大大加快数据访问速度.

如果您的环境是数据集市或仓库,那么几乎任何可以想象的查询都应该有很多索引.

在事务环境中,索引的数量应该更低,并且它们的定义更具战略性,以便索引维护不会拖累资源.(索引维护是指必须更改索引的叶子以反映基础表的更改,如INSERT, UPDATE,和DELETE操作一样.)

另外,要注意索引中字段的顺序 - 字段越具有选择性(更高基数),它应该出现在索引中越早.例如,假设您要查询二手车:

SELECT   i.make, i.model, i.price
FROM     dbo.inventory i
WHERE    i.color = 'red'
  AND    i.price BETWEEN 15000 AND 18000

价格通常具有较高的基数.可能只有几十种颜色可供使用,但很可能有数千种不同的要价.

在这些索引选择中,idx01提供了更快的路径来满足查询:

CREATE INDEX idx01 ON dbo.inventory (price, color)
CREATE INDEX idx02 ON dbo.inventory (color, price)

这是因为与颜色选择相比,更少的汽车将满足价格点,从而使查询引擎能够分析更少的数据.

我已经知道有两个非常相似的索引,仅在字段顺序上有所不同,以加快一个中的查询(名字,姓氏)和另一个中的(姓氏,名字).

5> jandersson..：

---

我最近学到的一个技巧是SQL Server可以在更新语句中更新局部变量和字段.

UPDATE table
SET @variable = column = @variable + otherColumn

或者更易阅读的版本:

UPDATE table
SET
    @variable = @variable + otherColumn,
    column = @variable

我在实现递归计算时使用它来替换复杂的游标/连接,并且还获得了很多性能.

以下是详细信息和示例代码,这些代码在性能方面取得了很大的进步:http: //geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal. ASPX

6> DavidM..：

---

假设MySQL在这里,使用EXPLAIN来查找查询的内容,确保尽可能高效地使用索引并尝试消除文件排序.高性能MySQL:优化,备份,复制等是与MySQL性能博客一样的关于此主题的好书.

---

这对MySQL很有用,但问题是标记为"sqlserver".尽管如此,这样做还是件好事.在SSMS中要做的类似事情是使用"显示估计执行计划"和"包括实际执行计划".如果您可以消除巨大的表扫描并使用聚簇索引搜索,那么您就可以获得最佳性能.

7> AlexCuse..：

---

@Terrapin isnull和coalesce之间还有一些值得一提的差异(除了ANSI合规性,这对我来说很重要).

Coalesce vs. IsNull