欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

在SQL server的性能优化过程中的常见技巧

程序员文章站 2022-06-08 08:37:04
...

在SQL server 的性能优化过程中,TSQL的语句优化是很重要的一环。当您使用各种手段找出系统最需要优化的语句后,应该如何对该语句进行优化呢?下面列出一些TSQL 语句优化的常见技巧。 1. 语句的执行计划分析 首先要对该语句的执行计划(execution plan)进

在SQL server 的性能优化过程中,TSQL的语句优化是很重要的一环。当您使用各种手段找出系统最需要优化的语句后,应该如何对该语句进行优化呢?下面列出一些TSQL 语句优化的常见技巧。

1. 语句的执行计划分析

首先要对该语句的执行计划(execution plan)进行分析,找出语句运行慢的原因。比如说,

在检查执行计划是否包含table scan /index scan等昂贵的操作?

对table, worktable是否进行了大量的逻辑读?

是否使用了不合适的join类型?

并发(串行)执行计划是否不合适 等等

举一个的例子,

Table 'myTable'. Scan count 1, logical reads 15877, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 174 ms.

Rows Executes StmtText

------ --------- ----------------------------------------------------------------------------------------------

10 1 select EmpNo, Code, MAX(DueDate) from myTable where EmpNo = '21250' group by EmpNo,Code

10 1 |--Stream Aggregate(GROUP BY:([myTable].[Code]) DEFINE:([Expr1002]=MAX([CERTIFICAT

10 1 |--Sort(ORDER BY:([myTable].[Code] ASC))

10 1 |--Table Scan(OBJECT:([SSS].[dbo].[myTable]), WHERE:([myTable]

大家看上图,logical reads15877,很大的一个值。 执行计划里面有table Scan,那么明显就是一个缺少index导致表被全扫描的例子。加一个索引就会好了。

再看另外一个例子:

表 'myTableStatus'。扫描计数 0,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次

表 'myTable'。扫描计数 8,逻辑读取 1408666 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次

表 'myTableType'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'Transactions'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

Rows Executes StmtText

-------------------- -------------------- -----------------------------------------------------------------------------------

2 1 SELECT * FROM vwmyTableItems WHERE 1=1 AND "myTableTypeId" = 1 AND "BranchId" = 1

2 1 |--Sort(DISTINCT ORDER BY:([j].[myTableID] DESC, [j].[UUID] ASC, [j].[ParentID] A

2 1 |--Compute Scalar(DEFINE:([Expr1009]=[Log_DB].[dbo].[FormatDate]([Log_DB].[d

2 1 |--Nested Loops(Inner Join)

1 1 |--Clustered Index Seek(OBJECT:([Log_DB].[dbo].[Transactions].[PK_

2 1 |--Nested Loops(Inner Join)

1 1 |--Clustered Index Seek(OBJECT:([Log_DB].[dbo].[myTableType].

2 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([j].[myTableSta

0 0 |--Compute Scalar(DEFINE:([Expr1011]=(((substring(replic

2 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnI

468971 1 | |--Index Seek(OBJECT:([Log_DB].[dbo].[myTable]

2 468971 | |--Clustered Index Seek(OBJECT:([Log_DB].[dbo]

2 2 |--Clustered Index Seek(OBJECT:([Log_DB].[dbo].[myTableS

从上面计划看,问题是表myTable逻辑读取 1408666 次,非常巨大。另外Nested Loop的cluster index seek 执行了468971 次. 这样的执行计划导致CPU 很高。 如何减少逻辑读和减少Nested Loop里面的执行次数是关键。 对这类执行计划,可以考虑改写语句,或者尝试不同的join type。比如,使用option(hash join) 来改变join类型,看看性能是否改善。

2. 语句的常见优化手段

分析完毕执行计划,你知道了语句为什么慢。接下来语句的优化常见方法是如下。

表/索引 的统计信息是否最新?运行update statistics with FULLSCAN更新统计信息再看看。

对有table scan或者index scan的地方,仔细检查是否缺少索引?运行Database tuning wizard对该语句分析下,或者手工加上索引看看。也可以查询sys.dm_db_missing_index_details来看看系统是否大量缺少index。

join的类型是否合适,使用join hint试试试用不同的join类型。

使用index hint 试下不同的index

index是否合适,索引字段的顺序是否最佳?

WHERE 语句的写法是否不够有效率?比如说,它是否包含了OR, ,等符号?

语句里面是否使用了自定义函数UDF?UDF常导致table scan。

语句是否导致频繁recompile? 看看是不是temp table导致的。

语句是否返回了大量的结果集合? 返回几万十几万笔资料是有些多哦。可以使用TOP N限制结果集。

是否使用了低效率的游标?尽量使用fast_forward readonly 类型的游标比较好。

如果语句开销很大,那么该语句是否有必要?能否减少它的执行次数?

3.简化和重写语句

在系统的整体性能优化里面, TSQL优化优先级并不是最高的。 下面按照对系统性能影响的重要程度依次列出优化的几个层面:

Application

Database Design

Microsoft SQL Server

Operating System

Hardware

也就是说,程序的优化效果最明显,接下来是的设计优化,再接下来才是TSQL的优化。硬件的优化是最后考虑比较好。一味增加内存和CPU未必能够解决性能问题。

在程序的优化里面,如果能够改写数据库访问逻辑,改写TSQL语句, 或者简化TSQL语句,有时候你能够获得惊人的性能回报。