提高SQL Server性能

如何提高SQL Server的性能

提供SQL Server性能总的来说有两种方式：

1、扩容，提高服务器性能，显著提高CPU、内存，解决磁盘I/O瓶颈。

2、优化应用程序

引起数据性能问题主要原因：

1.    不了解系统性能和可扩展行

2.    一次检索太多数据

3.    错误的使用数据库事务

4.    错误的使用数据库索引

5.    混淆OLTP、OLAP和报表工作负载

6.    相对低效的模式Schema

7.    使用了相对低效的磁盘

模式

什么是高效的模式？

1.    模式设计不能太随意

2.    分离OLAP和OLTP工作负载

3.    设计时首先遵循范式，后续再基于性能进行反范式设计。

4.    定义号所有的主键和外键关系

5.    选择合适的数据类型（小于8000的字符类型，建议使用varchar而非text。对于使用Unicode的场景，建议使用nchar和nvarchar;sql_variant存储类型灵活，但代价是影响性能，因为涉及类型转换）

6.    对于反范式设计，请使用索引视图。

7.     对表进行水平拆分和垂直拆分

查询语句

1.    书写高效的查询语句

2.    了解你所书写的查询语句的性能和可扩展性特征。

3.    查询语句首先要正确，比如：检查join语句中的on后面的关联字段，特别注意确保不会出现笛卡尔积的情况。

4.    避免使用*，查询语句只返回必须的行和列

5.    避免使用类似Note Like的运算符

6.    where语句中，尽量避免使用显式或者隐式的函数，因为优化器不会针对函数中的列使用索引，这种场景下，列被当成了表达式而非列，所以列就不会在执行计划优化中使用。

比如,对于日期列，尽量将其转换为文字表达式，下面这种情况下是全表扫描

SELECTOrderID FROM NorthWind.dbo.Orders WHERE DATEADD(day, 15,

OrderDate)= '07/23/1996'

而下面这种场景就会使用索引

SELECTOrderID FROM NorthWind.dbo.Orders WHERE OrderDate = DATEADD(day, -15,'07/23/1996')

7.    另外要特别小心因类型不一致所导致的隐式的类型转换也会引起表扫描和索引扫描，

比如，数据库定义nvarchar，而变量定义varchar，数据库定义nchar,而变量定义char,这都会引起隐式类型转换和扫描

索引使用统计信息中dm_db_index_usage_stats,有针对scan和seek统计信息，scan是扫描，seek索引查询

使用锁和隔离等级提示来减少锁影响

SQL中有三种类型的锁：共享、更新、排他。锁对数据库性能影响巨大，SQL事务中你可以指定四种ANSI级别的隔离等级：

Readuncommitted：可导致脏读

Readcommitted： 可导致不可重复（默认级别）

Repeatableread：可导致幻读

Serializable

8.    使用select语句时，建议使用with nolock,with readcommitted避免产生不必要的读锁。

9.    处理死锁一个惯用做法是针对事务中涉及的查询语句使用updlock

10. 对于批量插入的场景，建议对表使用tablock

使用存储过程或者参数化查询

对于在实际开发中就是该选择过程过程或参数化查询，还是在应用程序中个使用嵌入SQL，考虑以下场景：

1.    逻辑分离

2.    优化与部署

3.    网络带宽

4.    简化的批量操作

5.    提高数据安全性和完整性

6.    防止SQL注入

减少游标的使用

避免触发器上进行耗时长的操作，很可能会引起锁操作

正确的使用临时表和表变量

如果程序频繁的创建临时表，建议考虑使用表变量或者持久表

减少查询提示和索引提示

表提示（MERGE, HASH, LOOP, and FORCE ORDER ）这些会直接导致优化器选择使用一种连接算法,索引提示是告诉优化器使用某个索引的表提示。通常情况下，查询优化器会使用执行效率最高的执行计划。强制指定索引或者连接算法不推荐。

使用完整匹配的数据库对象

比如：

推荐使用

SELECT* FROM dbo.Authors statement or the EXEC dbo.CustOrdersHist

而非

SELECT* FROM Authors or the EXEC CustOrderHist

明确指定数据库对象的所有者，这样可尽量减少名字解析的成本。

索引

索引对于高效的数据访问至关重要。

1.    要基于使用场景创建数据索引。

如果表很少查询，不建议建立索引。不要在bit、text、ntext或者image类型上建索引。不要创建宽索引或者索引无法选择

2.    聚集索引应尽量小

3.    范围数据考虑建立聚集索引，比如：查询语句中的between，或者> <

4.    在所有的外键上建立索引。

5.    在区分度高（数据密度低）列上创建索引。

6.    为经常使用、影响大的查询创建覆盖索引。

7.    多个窄索引要好于宽索引。

8.    组合索引只有第一列存储统计信息，所以创建组合索引要将最受限制的列放在第一个。

9.    WHERE, ORDER BY, GROUP BY 以及DISTINCT语句中的列宜建立索引。

10. 删除未使用的索引

11. 使用索引优化向导

事务

1.    避免执行时间长的事务。

2.    避免使用需要用户输入提交的事务。

3.    避免在事务结束时访问大量数据

4.    尽量在事务中以相同的顺序访问数据库资源。

5.    使用隔离等级提示以减少数据库锁。

6.    明确使用事务提交或回滚

存储过程

1.    存储过程中使用set nocount on,这样SQL Server服务器就不会在存储过程中每执行一句就发送一次DONE_IN_PROC消息了。

2.    对于自定义存储过程，不要使用sp_prefix

执行计划

1.    评估查询执行计划

2.    避免表扫描和索引扫描

3.    评估哈希连接

4.    评估bookmarks

5.    评估排序和过滤器

6.    比较实际和预估行数与执行次数。

预编译执行计划

1.    使用存储过程或参数化查询

2.    对于动态sql使用sp_executesql

3.    避免在存储过程中交叉使用DDL和DML，包括在tempdb使用DDL

4.    避免在临时表中使用游标

SQLXML

1.    避免在大的XML文档中使用OPENXML

2.    避免在XML文档中使用大并发的OPENXML

优化

1.    通过SQL Profiler确定运行时间长的查询

2.    留意使用率高的小查询

3.    用sp_lock sp_who2 来查看死锁和阻塞

4.    查看master数据库sysprocesses中的waittype和waittime

5.    使用dbcc opentran来查看执行时间长的事务。

测试

当试图以大量测试数据来填充表时，注意以下原则：

1.    确保事务日志不会填满

2.    为数据增长做好预算

3.    通过工具来填充数据

4.    使用现有的生产数据

5.    通过正常的用户场景以确保读写操作平衡

6.    通过测试工具来进行压力和负载测试

监控

1.    保持统计信息最新，这对查询优化器选择最佳执行计划至关重要

2.    用SQL Profiler来优化运行时间长的查询

3.    用SQL Profiler来监控表扫描和索引扫描

4.    使用性能监视器来监控高资源使用率

5.    建立操作与开发反馈回路

部署

1.    如无特殊要求，使用数据库默认配置

2.    保持数据和日志与临时表在不同的磁盘

3.    对于频繁访问的表和索引提供单独磁盘

4.    使用合适的RAID配置

5.    使用多个磁盘控制器

6.    预扩充数据和日志库，避免自动增长和碎片性能影响

7.    最大化可用内存

8.    管理索引碎片（删除重建索引、使用DBCC DBREINDEX命令、使用DBCC INDEXDEFRAG命令，注意使用前两种命令会引起数据库锁，而DBCC INDEXDEFRAG 不会锁数据库资源）

9.    时刻谨记数据库管理任务

原文： https://msdn.microsoft.com/en-us/library/ff647793.aspx