SQLServer性能调优之2T-SQL与性能调优

2.1健壮

需要对T-SQL的语法与深入的了解。

例：Select Count(1) From Patient Where Name Like '王%' 

Select Count(1) From Patient Where Left(Name,1) = '王' 

上面的SQL比下面的效率更高

2.2 Select

2.2.1Select

1.只Select需要的字段，避免*

2.2.2 From

1.关联表时使用Join，不建议使用Where条件里的=。例：

Select ... From A Inner Join B On A.BID = B.BID

Select ... From A,BWhere A.BID= B.BID

2.小表操作优先，小表驱动大表。多表联查时，小表在前

3.多表联查时，尽量不超过5个表

2.2.3Where

1.Where语句中的字段能否使用索引，是否有必要加上索引

2.不要对索引字段做任何计算，包括函数。会使索引失效。如果索引是计算型索引，也可以命中。但用处较少，计算公式需要固定，局限性大。一般不提这一点。

2.2.4 Ordery

1.尽量使用有索引的字段做排序条件

2.查询的结果集量大时，不建议使用排序功能

SQL对每个查询都指定了内存大小，超出时，查询出来的数据先存到系统库TempDB中，然后再进行排序。这里又要读写硬盘，更影响了速度。

2.2.9 其它

1.SQL越简单越好，复杂的SQL考虑拆分

2.3Update

Select使用的是共享锁，Update使用的是更严格的更新锁和排他锁。对SQL的性能要求更高，更容易发生阻塞。执行时先用更新锁锁表找数据，找到数据后再变成排他锁。所以Select章节的注意点同样适用于Update章节。

更新的数据多时，分批更新。

更新的结果不确定时，先计算出来再更新。尽量不要使用SQL自身的计算能力。

2.4Delete

Delete也用到更新锁和排他锁，还要更新索引字段，对SQL的性能要求更高。

Delete成功后不能回滚，建议先把Delete的数据备份起来，方便及时恢复。

Truncate Table 快速，日志少，如果是自增主键，重新开始计数

 

2.5子查询

1。子查询尽量写在Where中

2。子查询数量不超过3个

3。避免子查询中对大数据汇总和排序

4。子查询返回的结果集尽量小，尽量精确

5。For XML，拼接信息数量量越大越高效。

2.6批量更新数据

2.6.1 BCP/Bulk Insert

用BCP把数据导出来，用Bulk Insert以文件方式将数据导入库中

2.6.2Insert....Select

导入已在存在的表中

2.6.3Select....Insert

导入到不存在的表中（新建表）

2.6.4OutPut

输出Insert Update Delete语句中被修改数据的旧版本及当前版本，例：

Update Patient

Set Address = '北京市1'

Output Inserted.Address,Deleted.Address

Where ID='2'

Insert Patient (Name)

Output Inserted.ID

values ('Test204')

2.6.5 Merge

原理:

有两个表名：source 表和 target 表，并且要根据 source 表中匹配的值更新 target 表。

有三种情况：

• source 表有一些 target 表不存在的行。在这种情况下，需要将 source 表中的行插入到 target 中。
• target 表有一些 source表不存在的行。这种情况下，需要从 target 表中删除行。
• source 表的某些行具有与 target 表中的行相同的键。但是，这些行在非键列中具有不同的值。这种情况下，需要使用来自 source 表中的值更新 target 表中的行。

如果单独使用 INSERT, UPDATE和DELETE语句，则需要三个单独的语句，来使 source 表中的匹配行的数据更新到 target表。

但是，使用 merge 可以同时执行三个操作。下面是 merge语句的语法

MERGE target_table USING source_table

ON merge_condition

WHEN MATCHED

THEN update_statement

WHEN NOT MATCHED

THEN insert_statement

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

 

2.7运行时动态管理视图

Sys.dm_exec_requests

当前正在执行的任务

Sys.dm_exec_sql_text

不单独使用

Sys.dm_exec_sessions

存在的会话

Sys.dm_exec_connections

存在的会话

Sys.dm_exec_query_stats

对编译次数、占用CPU资源总量、执行次数等都进行了详细的记录。优化SQL利器

Sys.dm_exec_query_resource_semaphores

 

例1：查当前正在执行的SQL内容

Select * From Sys.dm_exec_requests der

cross apply Sys.dm_exec_sql_text(der.sql_handle) As desc

Where der.session_id > 50

sessionid在50以内的进程为系统预留进程

例2：查正在建立的会话

Select * From Sys.dm_exec_sessions Where session_id > 50

例3：优化SQL。执行过的每个SQl的耗时等信息

SELECT s2.dbid,

    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

      ( (CASE WHEN statement_end_offset = -1

         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,

    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,

    max_worker_time,    total_physical_reads,     last_physical_reads,

    min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 

WHERE s2.objectid is null

ORDER BY s1.total_worker_time desc