分享优化SqlServer查询性能01.docx
- 文档编号:24280887
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:21
- 大小:2.09MB
分享优化SqlServer查询性能01.docx
《分享优化SqlServer查询性能01.docx》由会员分享,可在线阅读,更多相关《分享优化SqlServer查询性能01.docx(21页珍藏版)》请在冰豆网上搜索。
分享优化SqlServer查询性能01
优化SQLServer查询性能
刘豹
0参考书籍
《SQLServer2005技术内幕:
查询、调整和优化》
《SQLServer2005技术内幕:
存储引擎》
《SQLServer2005技术内幕:
T-SQL查询》
《Dissecting+SQL+Server+Execution+Plans》
1SQLServer性能诊断和优化全局简介&本次分享的假设场景
SQLServer分享资料\1.0SQLServer性能诊断和优化全局.mmap
2优化SQLServer查询性能
2.1TSQL语句的分析、优化、编译执行过程
也称为:
解析、代数化、执行
注:
SQL语句以批的方式提交。
T-SQL执行查询的步骤小结:
1.将查询转换成某种内部表示,通常称为语法树或序列树。
2.根据一定的等价变换规则把语法树转换成标准形式即内部的一种数据结构。
3.选择底层的操作算法,即逻辑操作转换为物理操作。
4.生成查询计划。
查询计划也称查询执行方案,是由一系列内部操作组成的。
这些内部操作按一定的次序构成查询的一个执行方案。
通常这样的执行方案有多个,需要计算每个执行方案的执行代价,从中选择代价最小的一个。
SQL2005形象化理解查询优化器所做的工作:
[10]SQLServer2005查询优化器所做工作.ppt
[12]T-SQL物理查询.docx
[12]T-SQL物理查询.docx
2.2TSQL语句的分析、优化、编译执行过程DEMO
DEMO:
批处理是语句分析的单元(用SQLProfiler确定这点)
SQLProfilerDEMO
2.3概念和命令
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
冷查询
热查询
数据缓存和计划缓存
命中率
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
清除数据缓存:
DBCCDROPCLEANBUFFERS
清除计划缓存:
DBCCFREEPROCCACHE
查看缓存:
select*fromsys.syscacheobjects
开关IO统计:
SETSTATISTICSIOON
开关时间统计:
SETSTATISTICSTIMEON
设置计划显示的模式
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
DEMO:
1冷、热查询
SETSTATISTICSIOON
SETSTATISTICSTIMEON
DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE
SELECT*FROMp_Project
WHEREProjGUID='C96FC8E9-A544-4708-81BA-003BCB938E0D'
SELECT*FROMvp_Project
WHEREProjGUID='C96FC8E9-A544-4708-81BA-003BCB938E0D
EXECdbo.usp_cb_RecalcCost@ProjectCode='sz01.hjxc'--nvarchar(100)
2Adhoc&Prepared&Proc查询
不同类型的查询,初始寿命不同
SELECT*FROMp_Project
WHEREProjGUID='C96FC8E9-A544-4708-81BA-003BCB938E0D'
select*fromsys.syscacheobjectsORDERBYcacheobjtype
--(@1varchar(8000))SELECT*FROM[p_Project]WHERE[ProjGUID]=@1
EXECdbo.usp_cb_RecalcCost@ProjectCode='sh01.ssty'--nvarchar(100)
select*fromsys.syscacheobjectsORDERBYcacheobjtype
3执行计划概要
[Script01_initPerformance].sql
SELECT*FROMsys.syscacheobjects;
SELECT*FROMsys.dm_exec_cached_plans;
SELECT*FROMsys.dm_exec_plan_attributes(
SELECT*FROMsys.dm_exec_sql_text(
DEMO:
SETSTATISTICSIOON
DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE
SELECTcustid,empid,shipperid,COUNT(*)ASnumorders
FROMdbo.Orders
WHEREorderdate>='20060201'
ANDorderdate<'20060301'
GROUPBYcustid,empid,shipperid
WITHCUBE;
GO
---------------------------------------------------------------------
---------------------------------------------------------------------
--SHOWPLAN_TEXT
SETSHOWPLAN_TEXTON;
GO
SELECTorderid,custid,empid,shipperid,orderdate,filler
FROMdbo.Orders
WHEREorderid=280885;
GO
SETSHOWPLAN_TEXTOFF;
GO
--SHOWPLAN_ALL
SETSHOWPLAN_ALLON;
GO
--Runabovequery
SETSHOWPLAN_ALLOFF;
GO
--STATISTICSPROFILE
SETSTATISTICSPROFILEON;
GO
--Runabovequery
SETSTATISTICSPROFILEOFF;
GO
3SQLServer和性能有关的核心数据结构
3.1数据库基本构造-区,页结构
数据页:
数据库的任何数据都是由页(8K)组成的。
PageSize:
8KB
¡PageHeader
§96bytesaboutpageinformation(到底有啥?
)
▪Pagenumber
▪Pagetype
▪Freespace
▪Ownerid
¡DataRows
Note:
Logfilesdonotcontainpages;theycontainaseriesoflogrecords.
区:
分配的时候都是按区分配,一个区等于8页。
区是管理空间的基本单位。
一个区是八个物理上连续的页(即64KB)。
这意味着SQLServer数据库中每MB有16个区。
为了使空间分配更有效,SQLServer不会将所有区分配给包含少量数据的表。
SQLServer有两种类型的区:
统一区:
由单个对象所有。
区中的所有8页只能由所属对象使用。
混合区:
最多可由八个对象共享。
区中八页的每页可由不同的对象所有。
表的存储结构:
表->分区(默认一个,除非是分区表)->数据页
DEMO:
[Script02_查询数据页].sql
可以继续研究:
定长列、边长列、NULL、LOB数据等各种数据类型的存储。
3.2堆结构
堆:
没有建聚集索引的表,叫堆。
特点:
数据无序存放,杂乱无章。
“见缝插针”
注意点:
1>系统维护到堆的IAM指针、firstPage指针
2>区一般是有序的,但不一定是物理上连续的
DEMO:
/*定义临时变量用于表示表的对象ID*/
declare@idint
/*定义临时变量用于表示表的名称*/
declare@tablenamechar(20)
/*定义临时变量用于表示表的hobt_id:
分区ID*/
declare@hobtidbigint
set@tablename='没有建立索引的表'
set@id=(selectobject_idfromPerformance.sys.objectswherename=@tablename)
set@hobtid=(selecthobt_idfromPerformance.sys.partitionswhereobject_id=@id)
select@hobtid
3.3聚集索引结构
聚集索引:
聚集索引是按B+树结构来组织的,它在叶级表中维护所有的数据。
表的完整数据行就是按照索引键列的顺序被存储在索引的叶级。
有一个双向链表来维护这种结构
强调:
聚集索引不是该数据的副本,而是数据本身。
注意:
根据索引碎片级别,数据页的物理顺序可能与链表维护的逻辑顺序不同
注意索引的生成:
由页级向上,只一个级别只有一页,成为Root根页。
同时,注意到每一个数据页,只能生成上一级别页的一项纪录。
注意有3个指针:
IAM指针:
用于无序索引扫描。
FirstPage指针:
用于叶级有序扫描、有序局部扫描。
Root指针:
用于索引查找
注意NULL:
一般来说B+树不能存储NULL值,但是微软的索引可以有NULL值。
3.4非聚集索引结构
非聚集索引:
非聚集索引的页级行仅包含索引键列和指向特定数据行的行定位符。
行定位符的内容取决于该表是一个堆还是一个聚集表。
区别:
堆上非聚集索引中的行定位符是一个8字节的物理指针,称RID。
聚集表中的是聚集键值,由目标行的索引键值和唯一标识符组成。
堆上的非聚集索引:
有聚集索引的表的非聚集索引:
3.4八种类型的页
4SQL执行计划
表/索引扫描、索引查找(RID/Lookup)
表扫描与聚集索引扫描:
当一个表没有用到索引的时候,就会是表扫描或聚集索引扫描。
这种扫描会连续地扫描表中的所有数据页,没有用到索引链,而是通过该表的IAM链来按照物理顺序读取数据表,由于聚集索引的叶即是数据页,因此和表扫描没有区别。
总结:
性能很低,需要优化。
非聚集索引覆盖扫描:
非聚集索引覆盖了该查询。
但是由于没有合适的过滤条件,因此只能索引扫描。
由于只需要访问索引就能查找全部数据,因此不用访问完整的数据行,查询的页数也就比较少。
总结:
这也是一个细微的计划,不依赖于选择性。
当一个查询没有过滤条件,但是读取的页数又很多的时候,使用此扫描,性能提升比较明显。
有序扫描:
带有ORDERBY的语句,当用到表扫描或非聚集覆盖扫描的时候,就会产生有序扫描。
有序扫描的性能取决于索引碎片的级别。
随着碎片的级别越高,有序扫描性能就越低。
总结:
如果你不需要排序就不要排序,如果必须排序,那么就要解决索引中的碎片问题。
索引查找+Lookups:
当索引没有覆盖查询(注意:
索引第一个键必须是过滤条件中的字段),如果查询的选择性很高,就会执行该计划。
先进行索引查找加有序局部扫描,然后开始执行Lookups,在堆中只需要读取一页,而在聚集表中读取页数等于索引级数。
总结:
依赖查询选择性,性能适中。
可以在建比较少的索引情况下提升性能。
看情况而定。
注意:
有序局部扫描隐藏在索引查找中,其实是存在的。
索引扫描+Lookups:
当索引的第一个键列不是筛选列的时候,且查询的选择性足够高的时候,就会产生这中执行计划。
总结:
依赖查询选择性,性能不高,如果出现这种执行计划,最好能够调整为索引查找+Lookups。
索引查找:
索引覆盖了该查询,并且筛选列为索引的第一个键列。
总结:
不依赖查询选择性,性能最高,但是由于覆盖了查询,因此索引建的比较大,不方便维护,并且当更新数据库的时候会影响性能。
如果要求优化read数,这种方法是上上之选。
聚集索引查找:
筛选条件就是聚集键。
总结:
细微计划,不依赖查询选择性,性能很高。
不过不可多得。
4.1表扫描/无序聚集索引扫描
SELECTorderid,custid,empid,shipperid,orderdate
FROMdbo.Orders;
4.2非聚集索引扫描/无序扫描
SELECTorderid
FROMdbo.Orders;
4.3有序聚集索引扫描
SELECTorderid,custid,empid,shipperid,orderdate
FROMdbo.Orders
ORDERBYorderdate;
4.4有序非聚集索引扫描
SELECTorderid,orderdate
FROMdbo.Orders
ORDERBYorderid;
4.5段
SELECTorderid,custid,empid,orderdate
FROMdbo.OrdersASO1
WHEREorderid=
(SELECTMAX(orderid)
FROMdbo.OrdersASO2
WHEREO2.orderdate=O1.orderdate);
4.6非聚集索引查找+有序局部扫描+Lookups
SELECTorderid,custid,empid,shipperid,orderdate
FROMdbo.Orders
WHEREorderidBETWEEN101AND120;
4.7无序非聚集索引扫描+Lookups
SELECTorderid,custid,empid,shipperid,orderdate
FROMdbo.Orders
WHEREcustid='C0000000001';
SELECTname
FROMsys.stats
WHEREobject_id=OBJECT_ID('dbo.Orders')
ANDauto_created=1;
--Stringcardinalities
SELECTorderid,custid,empid,shipperid,orderdate
FROMdbo.Orders
WHEREcustidLIKE'%9999';
4.8聚集索引扫查找+Lookups
SELECTorderid,custid,empid,shipperid,orderdate
FROMdbo.Orders
WHEREorderdate='20060212';
4.9覆盖非聚集索引查找+序部分扫描
SELECTshipperid,orderdate,custid
FROMdbo.Orders
WHEREshipperid='C'
ANDorderdate>='20060101'
ANDorderdate<'20070101';
GO
5优化流程
5.1一般步骤
●先看IO,锁定产生IO大的表(关注逻辑、物理读数)
●查看是否低级失误
DEMO:
[Script03_SARG].sql
●再看执行计划,锁定开销比比较高的部分
●分析开销比较高的操作产生的原因(即瓶颈所有)
●针对瓶颈尝试优化
●优化前后性能对比
5.2高难度优化
●评估SQL语句本身优化空间:
工具+估算
●分析统计信息中的worktable
●按需取数:
从算法角度分析是否从过大的数据集合中查找记录
●从业务角度评估是否有优化空间
●运行方式调整
●设计调整
5.2常用优化手段
●建立适当的索引
●重建索引与碎片整理
●改写SQL(分很多情况了)
●适当的冗余字段
●分解表结构,建立视图
●拆分功能视图
5.3DEMO
[13]SQL优化典型案例分析.doc
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 分享 优化 SqlServer 查询 性能 01