SQL SERVER监控和优化思路Word格式.docx
- 文档编号:16211802
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:17
- 大小:738.34KB
SQL SERVER监控和优化思路Word格式.docx
《SQL SERVER监控和优化思路Word格式.docx》由会员分享,可在线阅读,更多相关《SQL SERVER监控和优化思路Word格式.docx(17页珍藏版)》请在冰豆网上搜索。
,10),
('
李四'
20),
王五'
赵红'
20);
CREATETABLEDEPARTMENT
DEPTNOINTEGER,--部门编号
DEPTNAMEVARCHAR(10)--部门名
);
INSERTINTODEPARTMENT(DEPTNO,DEPTNAME)VALUES
(10,'
市场部'
),
(20,'
技术部'
SELECT*FROMEMPLOYELEFTJOINDEPARTMENTD
ONE.DEPTNO=D.DEPTNOANDD.DEPTNO=40--结果集为EMPLOYE表中数据
SELECT*FROMEMPLOYELEFTJOINDEPARTMENTD
ONE.DEPTNO=D.DEPTNOwhereD.DEPTNO=40--结果集为空
SQLSERVER查询响应慢常见原因:
1、没有索引或者没有用到索引
2、查询出的数据量过大
3、锁或者死锁
4、查询语句写的不好,没有优化
5、硬件原因(内存太小,磁盘读写速度慢,CPU,网络瓶颈等)
原因1-4可以通过调优的方式提高数据库的性能,原因5需要升级硬件配置
在性能测试过程中,针对SQLSERVER查询响应慢问题,一般会采取以下步骤进行分析:
1、操作系统系统资源,监控SQLSERVER性能指标,初步断定是否数据库瓶颈导致响应慢;
2、使用SQLServerProfiler,定位出哪个语句响应比较慢或产生锁;
3、测试前,必须保证统计信息是最新的。
查看语句的执行计划并进行分析,通过执行计划可以看出SQL在数据库中执行的路径,辨别出SQL语句的优劣,只有自己能分辨优劣才能写出高效的语句。
分析出SQL响应慢的主要原因;
4、优化SQL,通过执行计划和响应时间对比原SQL和优化后的SQL的性能;
下面将逐一描述以上步骤:
1)监控性能资源
当发现SQLSERVER比较慢时,操作系统在数据库的底层,所以在操作系统层面已经有所体现,我将瓶颈分成下面4种类型:
1、磁盘IO
2、CPU
3、内存
4、lazy系统,监控没有明显的CPU,磁盘,内存,网络等方面的瓶颈,即系统资源没有消耗。
这种情况最常见的原因是数据库锁
使用perfmon监控windows的性能指标:
使用perfmon监控SQLServer的性能指标:
性能对象
说明
SQLServer:
Databases
提供有关SQLServer数据库的信息,如可用的日志空间量或数据库中的活动事务数。
这个对象可有多个实例。
Locks
提供有关SQLServer执行的单个锁请求的信息,如锁超时和死锁。
SQLErrors
提供有关SQLServer错误的信息。
SQLStatistics
提供有关Transact-SQL查询各个方面的信息,如SQLServer收到的Transact-SQL语句的批数。
2)使用SQLServerProfiler,获取有问题的SQL或存储过程
Step1:
StartProfiler
2.createanewtracedefinition
Filenewtrace
3.selectingtheeventstotrace
4.Runningthetrace
从以上图可以获取到SQL,并对SQL进行进一步的分析。
3)查看语句的执行计划
辨别SQL语句的优劣非常重要,只有自己能分辨优劣才能写出高效的语句,在优化SQL前,先检查一下表中的统计信息是否是最新的,如果不是,先更新表的统计信息,如下图所示:
【SQL执行计划图】
选中setstatisticstoon
执行语句时,会显示逻辑读,物理读等信息
表'
T_UserInfo'
。
扫描计数1,逻辑读取2次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。
做对比测试时,可以通过3个值“逻辑读取2次,物理读取0次,预读0次”来判断一个SQL是否进行了优化。
一般来说,一个SQL语句进行优化后,逻辑读的次数会明显减少。
一般都是CLUSTEREDINDEXSEEK、INDEXSEEK、CLUSTEREDINDEXSCAN、INDEXSCAN、TABLESCAN等,其中出现SCAN说明语句有优化的余地
SQL优化一般使用以下手段:
1)从业务方面入手,根据业务知识,看是否存在多余的步骤,比如:
select后面查询出10列,实际上只使用到2列;
是否可以再增加过滤条件减少数据到应用端,比如:
查询出1个月的数据给应用端,业务上实际只需要1周的数据。
2)增加索引,并不是索引越多越好,它会增加insert,update,delete的维护成本。
可参考金总写的《编写高性能SQL的注意事项》
3)SQL语句的改写,同一个结果集会有很多种SQL的编写方式。
经过优化器后,所执行的路径也是不一样的。
当你发现一条SQL经过建立索引后,发现性能依然低下,当穷途末路时,可以通过本文SQL改写思路,也许能柳暗花明又一村。
4)修改逻辑设计,从表结构入手,通过表设计冗余字段的方式减少表连接数量。
数据量较大的表是否可以通过表分区方式较少表数据的访问等等手段。
这种方式缺点是代码改动量较大,所以在数据库逻辑设计时,必须考虑完善,避免后期对表结构的改动。
Where上谓词使用不上索引的情况:
1)col<
>
value
2)col<
expr
3)colisnotnull
4)colnotbetweenvalues1andvalues2
5)colnotbetweenexpr1andexpr2
6)colnotin(list)
7)collike‘%char’
8)collike‘_char’
9)t1.col1=t1.col2
10)co1<
(subg)
11)expr(co1)
12)查询出的数据占用总数据量的20%以上
SQL语句改写:
根据经验,很多语句的书写方式是可以用其他方式代替,通过尝试修改语句的写法,往往取得不错的效果。
3.1
OR<
->
IN
3.2
IN<
EXISTS
3.3
JOIN
3.4
INTERSECT<
3.5
DISTINCT<
3.6
EXISTS<
3.7
NOTIN<
NOTEXISTS
3.8
NOTIN/NOEXISTS<
OUTERJOIN+ISNULL
3.9条件中的子查询<
谓词中的子查询
3.10GROUPBY
GROUPBY在不同情况下可以用不同方式转换:
3.10.1
DECODE
假如B的取值范围为1,2,3
3.10.2
MINUS
3.10.3
OLAP函数
3.10.4
HAVING->
WHERE
3.11MINUS->
NOTIN
3.12OR->
UNION
3.13UNION->
UNIONALL+DISTINCT
案例1:
(使用3.12)
在客户现场维护人员反映,有条SQL响应速度很慢,通过监控发现有这条语句的io次数很大,达到了150万次IO,而两个表的数据也就不到20万,为何有如此多的IO次数,下面是执行语句:
select
ws.nodeid,wi.laststepid,wi.curstepid
from
Workflowinfo
wi,
Workflowstep
ws
where
ws.workflowid='
402881db1b441e6f011c0cff320e4766'
and
(wi.laststepid
=
ws.id
or
(wi.curstepid
isreceived=1
issubmited
=1))
查看执行计划发现:
主要是嵌套循环算法占的开销最大。
Laststepid,curstepid都没有有使用上索引,有可能是“Or”引起的性能问题,后来根据业务逻辑改写。
如下:
语句修改如下:
wswhere
ws.id)
union
all
=1)
查询性能约提高2倍。
案例2:
(使用3.2)
*
workflowbase
id
not
in
a.workflowid
a
)
发现Workflowinfo
表数据量大约30W,数据量较大,使用in语句效率较低,使用exists改写
b
exists(select
'
X'
a.workflowid=b.id
改写后,查询性能提升2.8倍。
案例3:
(使用3.3)
selectserv_id,serv_seq_id,agreement_id,cust_id,service_type,product_id
fromLS7_SID2.SERV_T
where(serv_id,serv_seq_id)in(selectserv_id,max(serv_seq_id)fromLS7_SID2.SERV_Tawhere1=1andacc_nbr='
6613211'
andstatein('
F0A'
'
F0D'
F0X'
)andpartition_id_region=1040andacct_id=200002540010groupbyserv_id)orderbystate
fromLS7_SID2.SERV_Ta,
(selectserv_idserv_1,max(serv_seq_id)max_serv_1fromLS7_SID2.SERV_T
where1=1andacc_nbr='
6905457'
)and
partition_id_region=1040andacct_id=200000796830groupbyserv_id)b
wherea.serv_id=b.serv_1anda.serv_seq_id=b.max_serv_1
调优前45.23s
调优后10.7s
案例4:
SELECTA.BELONG_OBJECT_IDSERV_ID,
A.PRICING_PLAN_ID
FROMCUST_PRICE_PLAN_TA
WHEREA.PARTITION_ID_REGION=1001
ANDA.STATEIN('
00A'
'
00X'
ANDA.BELONG_OBJECT_TYPEIN('
80A'
80C'
ANDA.EFF_DATE<
A.EXP_DATE
TO_DATE('
20090401000000'
YYYYMMDDHH24MISS'
ANDA.EXP_DATE>
=TO_DATE('
ANDEXISTS(SELECT1
FROMPRICING_PLAN_TD
WHERED.DAY_RENT_FLAG='
T'
ANDD.RENT_SPEC_MASK>
1
ANDD.RENT_SPEC_MASK<
8
ANDD.PRICING_PLAN_ID=A.PRICING_PLAN_ID
AND(TO_CHAR(D.REGION_ID)LIKE'
1001%%'
OR
'
1001'
LIKETO_CHAR(D.REGION_ID)||'
%%'
))
ANDA.BELONG_OBJECT_TYPE='
UNIONALL
SELECTC.SERV_ID,
FROMCUST_PRICE_PLAN_TA,OFFER_DETAIL_INSTANCE_TC
ANDA.BELONG_OBJECT_TYPE='
ANDC.PARTITION_ID_REGION=1001
ANDC.PRODUCT_OFFER_INSTANCE_ID=A.BELONG_OBJECT_ID
ORDERBYSERV_ID,EFF_DATE,EXP_DATE,PRICING_PLAN_ID
这条SQL很长,但我们细看下去发现黑色粗体部分重复执行了2次
A.PARTITION_ID_REGION=1001
使用With语句提取出相同的部分,避免重复访问2次。
语句改写为:
Withtest_tempas
(selectA.BELONG_OBJECT_IDSERV_ID,
A.PRICING_PLAN_ID,
A.BELONG_OBJECT_TYPE
fromCUST_PRICE_PLAN_TA
)))
selecttest_temp.serv_idasserv_id,test_temp.eff_dateaseff_date,test_temp.exp_dateasexp_date,test_temp.pricing_plan_idaspricing_plan_id
fromtest_temp
wheretest_temp.BELONG_OBJECT_TYPE='
unionall
selectc.serv_idasserv_id,a.eff_dateaseff_date,a.exp_dateasexp_date,a.pricing_plan_idaspricing_plan_id
fromtest_tempa,OFFER_DETAIL_INSTANCE_TC
wherea.BELONG_OBJECT_TYPE='
ANDC.PRODUCT_OFFER_INSTANCE_ID=a.SERV_ID
生产环境
优化前的837.841秒
优化后的459.328秒
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL SERVER监控和优化思路 SERVER 监控 优化 思路