Microsoft SQL Server 2005技术内幕 查询、调整和优化笔记
Microsoft SQL Server 2005技术内幕 查询、调整和优化笔记
hash union，hash aggression，hash join
聚合 aggression：标量聚合（流聚合实现），流聚合，哈希聚合
联合 union union all：哈希联合，merge join
sqlserver会把哈希聚合和流聚合混合在一起使用
哈希聚合（哈希匹配是不需要先排序的，除非强制排序 强制加order by 无论哈希聚合还是哈希join还是哈希联合都非常快！）
map-reduce
高继伟的map-reduce
http://www.cnblogs.com/shanksgao/p/4097145.html
在并行执行计划中是多个线程(CPU核)协同工作,这里面的Sort面对大量数据结果集时即便多核同时进行,在复杂的预算面前也是有些力不从心.在分布式的思想中,讲究分而治之,我们只要将大的结果集化为多个小的部分并多核同时进行排序,这样就达到了分而治之的效果.也就是标题说的”MapReduce”
sort拖死性能，改为nested loop
Declare @p1 int, @p2 nvarchar(56), @p3 smallint, @p4 int, @p5 bigint, @p6 bigint, @i int select @i=3001; with p as ( select productid, ProductNumber=convert(nvarchar(56),ProductNumber), reorderpoint from bigproduct as bp ) select @p1=p.productid, @p2=p.productnumber, @p3=p.reorderpoint, @p4=th.transactionid, @p5=rank()over (partition by p.productid order by th.actualcost desc), @p6=rank()over (partition by p.productid order by th.quantity desc) from bigproduct as p join bigtransactionhistory as th on th.productid=p.productid where p.productid between 1001 and @i option(OPTIMIZE FOR (@i=5001))
先哈希匹配，后order by （sort）
我们可以看到通过将外表数据放入临时表中,使得内存消耗进一步降低,而数据较为平均的分布到多个threads中,你可能看到其中不少threads是没有数据的,其实有时需要我们根据查询管控并行度的.而在执行时间上有可能得到进一步的改善!
目录
f
f
f
f
f
f
f
f
第一章性能故障检修方法
sqlserver每个连接大约需要50KB的存储空间
F
san存储
存储区域网络 ，逻辑单元luns
F
F
F
使用Windows纤程（轻型池） CLR不被支持，因为linkedserver和CLR都需要线程模式
F
每个工作进程消耗512KB内存，x64消耗2MB，ia64消耗4MB,在sqlserver启动时，只打开少量的工作进程
F
内存
F
建立性能基线 baseline
F
F
F
TEMPDB的剩余空间以KB计算
F
数据库引擎优化顾问是sqlserver2000索引优化向导ITW的一个更强大的替代品
F
SELECT * FROM SYS.[dm_tran_version_store] SELECT * FROM SYS.[dm_os_buffer_descriptors]
上面两个视图一般不会 用来监控性能
dm_os_buffer_descriptors缓存描述
F
逻辑线程worker （1:1）内部映射到Windows线程，如果Windows轻型池开启，那么还可以映射到纤程
F
在sql2005，编译是语句级的，只有受到影响的语句才会被重编译
sql2000 不同，在后者中整个批处理都会被重编译
F
F
查询优化器占用的时间
SELECT * FROM SYS.[dm_exec_query_optimizer_info] WHERE [counter]='OPTIMIZATIONS' OR [counter]='ELAPSED TIME'
F
F
如果与4G的内存和8G的交换文件，pagefile.sys，服务器上的所有活动进程可以提交的VAS虚拟地址空间总数就有8GB，但引用内存中只有4GB是确实在RAM中的，剩下的其他内存都被调出了，当在内存中找不到页面，需要到虚拟内存找页面的时候就会产生缺页中断，page fault
F
F
查看内存
DBCC memorystatus
F
F
F
F
MARS使用tempdb
F
磁盘每次读/写平均用时 指标
小于10ms 非常好
10ms~20ms还过得去
20~50ms 需要多注意
大于50ms 验证I/O瓶颈
F
SELECT DB_NAME([database_id]) AS dbname, [file_id], [io_stall_read_ms], [io_stall_write_ms] FROM SYS.[dm_io_virtual_file_stats](DB_ID(),NULL)
I/O瓶颈 SYS.[dm_io_virtual_file_stats]
F
tempdb瓶颈
从sql2005开始，redo信息不再记录到tempdb中
F
表变量的作用域为批处理或请求中，不能为表变量建立索引和统计信息，同用户表一样，元数据信息为临时表和表变量所创建
F
F
MARS
F
分配瓶颈，GAM,SGAM ,PFS
F
DDL瓶颈
SELECT [session_id] , [wait_duration_ms] , [resource_description] FROM SYS.[dm_os_waiting_tasks] WHERE [wait_type] LIKE 'PAGE%LATCH_%' AND [resource_description] LIKE '2:%'
F
用跟踪标志1118来移除SGAM页的分配，当激活该跟踪标志，sqlserver就会停止混合区分配，而只使用统一区分配，这可以减少SGAM竞争，但是对GAM和PFS竞争没有作用 ，使用跟踪标志1118会影响实例下的所有库，包括系统库和用户库
F
缓存临时对象
F
向磁盘中写零，速度慢，文件快速初始化
F
SELECT * FROM sys.[dm_os_wait_stats] ORDER BY [wait_time_ms] DESC
F
F
SELECT * FROM sys.[dm_os_wait_stats] WHERE [wait_type] LIKE '%se%' ORDER BY [wait_time_ms] DESC
lazywriter_sleep:延迟写入器等待是很正常的
RESOURCE_SEMAPHORE :信号量等待是一个工作进程被授权访问资源和他被列入CPU scheduler之间的时间，一个长时间的信号量等待可能意味着出现了严重的CPU竞争
信号量
RESOURCE_SEMAPHORE
SOSHOST_SEMAPHORE
CLR_SEMAPHORE
RESOURCE_SEMAPHORE_MUTEX
RESOURCE_SEMAPHORE_QUERY_COMPILE
F
使用快照隔离级别需要修改应用程序，而读提交快照隔离级别
SELECT * FROM sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) WHERE object_id =
sys.[dm_db_index_operational_stats]视图提供了访问索引遇到的阻塞的综合索引统计数据
forwarded_fetch_count列 估计应该是读取前转行次数
F
F
F
死锁跟踪标志1222，1204
超过5000个锁就会锁升级
锁内存超过可用内存的40%也会锁升级
F
1250个新锁
F
跟踪标志1211禁用锁升级
F
跟踪标志1224禁用锁升级
F
第二章 跟踪和性能分析
F
sql跟踪体系和术语
一个跟踪可以规定只有超过50毫秒的事件才应该被返回
F
内部跟踪组件的原理
整个数据库引擎的各个组件都是事件发送器，查询处理器，锁管理器，高速缓存管理器
跟踪控制器一个全局位图被更新
全局事件接收器
F
跟踪I/O提供者
为了避免线程等待跟踪缓冲区，就必须保证有足够快的磁盘系统来完成跟踪，要想监视这些等待，就要了解SQLTRACE_LOCK和IO_COMPLETION
IO_COMPLETION 估计等待I/O完成
SELECT * FROM sys.[dm_os_wait_stats] WHERE [wait_type] LIKE '%IO_COMPLETION%' AND [wait_type] LIKE '%TRACE%'
后台跟踪管理线程
1、文件跟踪提供者（将跟踪数据保持到.trc文件中）在设计上能保证不遗漏任何事件数据
2、表提供者（将跟踪数据保持到sqlserver表中）在设计上不能保证不丢失任何数据，如果数据没有被及时取走并且内部缓存区已经填满，他就会先等待20秒，然后再抛弃事件以清空缓冲区使事件继续进行，用户可以通过监视写等待类型来看自己是否出现这种情况
F
F
之前版本sqlserver只允许系统管理员sa来访问跟踪，后来又放宽了。。
F
当遇到 with password选项，加密的存储过程，加密用户定义函数，加密的视图，跟踪就会忽略
F
F
F
F
跟踪事件
F
F
F
EVENTSEQUENCE
每个跟踪上的EVENTSEQUENCE都是唯一的，这样避免了使用starttime和endtime来排序，EVENTSEQUENCE是一个64位整数，每次sql重启时，这个数都会重置，跟踪运行时不大可能会超出范围
F
F
服务器端跟踪和收集
在sqlserver里使用跟踪存储过程
F
F
f
f
sp_trace_create解释
F
F
实际启动跟踪是调用EXEC sys.[sp_trace_setstatus] 设置为1来启动的
EXEC sys.[sp_trace_setstatus] @tranceid, 1
F
SELECT * FROM sys.[traces]
查看服务器上的跟踪
--处理I/O所使用的缓冲区大小 SELECT [buffer_count] FROM sys.[traces]
F
F
跟踪所使用的过滤器
SELECT * FROM SYS.[trace_events] SELECT * FROM SYS.[trace_columns]
SELECT * FROM FN_TRACE_GETFILTERINFO(2)
F
查询trc文件
SELECT * FROM FN_TRACE_GETTABLE('C:\INSIDE_SQL.TRC',1)
F
F
sqlserver服务重启之后，跟踪定义就会自动删除，所以要把跟踪定义保存为一个模板或者脚本
行集提供者的接口没有公开，那么性能分析器怎麽利用接口和行集提供者交互
使用C#的Microsoft.Sqlsever.Management.Trace命名空间提供了一系列的管理类
F
F
F
F
sp_trace_getdata
F
Microsoft.Sqlsever.Management.Trace命名空间提供了一系列的管理类
F
性能分析器可以输出清晰的死锁图
F
F
要检测被激发的事件是否基于一个汇集连接，可以查看eventsubclass列的是是否为2
F
F
F
F
http://msdn.microsoft.com/zh-cn/library/ms175126.aspx
NTILE (Transact-SQL) SQL Server 2014 其他版本 1（共 1）对本文的评价是有帮助 - 评价此主题 将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，NTILE 将返回此行所属的组的编号。 适用范围：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。 Transact-SQL 语法约定 语法 NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > ) 参数 integer_expression 一个正整数常量表达式，用于指定每个分区必须被划分成的组数。 integer_expression 的类型可以是 int 或 bigint。 <partition_by_clause> 将 FROM 子句生成的结果集划分成此函数适用的分区。 若要了解 PARTITION BY 语法，请参阅 OVER 子句 (Transact-SQL)。 <order_by_clause> 确定 NTILE 值分配到分区中各行的顺序。 当在排名函数中使用 <order_by_clause> 时，不能用整数表示列。 返回类型 bigint 注释 如果分区的行数不能被 integer_expression 整除，则将导致一个成员有两种大小不同的组。 按照 OVER 子句指定的顺序，较大的组排在较小的组前面。 例如，如果总行数是 53，组数是 5，则前三个组每组包含 11 行，其余两个组每组包含 10 行。 另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。 例如，如果总行数为 50，有五个组，则每组将包含 10 行。 NTILE 具有不确定性。 有关详细信息，请参阅确定性函数和不确定性函数。 示例 A.将行分为组 下面的示例根据员工的年初至今销售额将行分到四个员工组中。 由于总行数不能被组数整除，因此前两个组将包含四行，而其余各组包含三行。 USE AdventureWorks2012; GO SELECT p.FirstName, p.LastName ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile ,CONVERT(nvarchar(20),s.SalesYTD,1) AS SalesYTD , a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; GO 下面是结果集： FirstName LastName Quartile SalesYTD PostalCode ------------- --------------------- --------- -------------- ---------- Linda Mitchell 1 4,251,368.55 98027 Jae Pak 1 4,116,871.23 98055 Michael Blythe 1 3,763,178.18 98027 Jillian Carson 1 3,189,418.37 98027 Ranjit Varkey Chudukatil 2 3,121,616.32 98055 José Saraiva 2 2,604,540.72 98055 Shu Ito 2 2,458,535.62 98055 Tsvi Reiter 2 2,315,185.61 98027 Rachel Valdez 3 1,827,066.71 98055 Tete Mensa-Annan 3 1,576,562.20 98055 David Campbell 3 1,573,012.94 98055 Garrett Vargas 4 1,453,719.47 98027 Lynn Tsoflias 4 1,421,810.92 98055 Pamela Ansman-Wolfe 4 1,352,577.13 98027 (14 row(s) affected) B.使用 PARTITION BY 划分结果集 以下示例将 PARTITION BY 参数添加到示例 A 中的代码。 首先按 PostalCode 将行分区，然后在每个 PostalCode 内将行分成四个组。 该示例还声明一个变量 @NTILE_Var 并使用该变量指定 integer_expression 参数的值。 USE AdventureWorks2012; GO DECLARE @NTILE_Var int = 4; SELECT p.FirstName, p.LastName ,NTILE(@NTILE_Var) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS Quartile ,CONVERT(nvarchar(20),s.SalesYTD,1) AS SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; GO 下面是结果集： FirstName LastName Quartile SalesYTD PostalCode ------------ -------------------- -------- ------------ ---------- Linda Mitchell 1 4,251,368.55 98027 Michael Blythe 1 3,763,178.18 98027 Jillian Carson 2 3,189,418.37 98027 Tsvi Reiter 2 2,315,185.61 98027 Garrett Vargas 3 1,453,719.47 98027 Pamela Ansman-Wolfe 4 1,352,577.13 98027 Jae Pak 1 4,116,871.23 98055 Ranjit Varkey Chudukatil 1 3,121,616.32 98055 José Saraiva 2 2,604,540.72 98055 Shu Ito 2 2,458,535.62 98055 Rachel Valdez 3 1,827,066.71 98055 Tete Mensa-Annan 3 1,576,562.20 98055 David Campbell 4 1,573,012.94 98055 Lynn Tsoflias 4 1,421,810.92 98055 (14 row(s) affected)
通常从设置为100毫秒的过滤器开始，然后从开始进行工作，方法是增加每次迭代上的信噪比，淘汰较小的查询，只保留有较高潜力进行性能调优的查询，根据应用程序和服务器负载不同，通常对每个迭代跟踪运行10~15分钟，然后查看结果并适度地增加这个数值直到在跟踪期间只得到几百个事件为止。这个10~15分钟的数字对于有些特别忙的应用程序来说太长了
另一个选项是只运行初始跟踪，然后开始过滤结果，简单的方法是使用sql2005的开窗函数ntile函数（跟PARTITION BY 差不多功能）
SQL2005的ntile开窗函数，他将输入行分为数目相等的“桶”，如果只查看一个基于生存期的跟踪表里前10%的查询，可以使用如下查询 SELECT * FROM (SELECT *, NTILE(10) OVER(ORDER BY duration) bucket FROM tracetable)x WHERE [bucket]=10
F
F
数据库引擎优化顾问DTA也是需要跟踪才能给出sqlserver优化建议
F
F
在跟踪表上的eventsequence列创建聚集索引来提高查询的性能
F
调试死锁
死锁的消息号为1205，严重度为13
F
F
F
存储过程调试
EXEC [sys].[sp_generateevent]
F
跟踪的性能测试
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
F
F
审核sqlserver的内置默认跟踪
SELECT * FROM sys.[traces] WHERE id=1 SELECT * FROM [sys].[fn_trace_gettable]() --如果不用默认跟踪，使用下面sql语句,无须重启sql EXEC [sys].[sp_configure] @configname = 'default trace enabled', -- varchar(35) @configvalue = 0 -- int RECONFIGURE WITH override
F
黑盒跟踪
F
F
设置黑盒跟踪开机自动启动
EXEC [sys].[sp_procoption] @ProcName = N'StartBlackBoxTrace', -- nvarchar(776) @OptionName = 'STARTUP', -- varchar(35) @OptionValue = 'ON' -- varchar(12)
sql2000增加了一个C2审核跟踪
C2审核跟踪利用SQL跟踪捕捉一系列数据，包括登录，登出，表访问以及许多其他数据点，使用sp_trace_create中的值为4的@option参数，每当写跟踪文件发生任何错误时，该参数就会自动关闭sqlserver服务。这可以保证即使出现磁盘错误，也不可能丢失任何审核数据
EXEC [sys].[sp_configure] @configname = 'show advanced', -- varchar(35) @configvalue = 1 -- int RECONFIGURE WITH override EXEC [sys].[sp_configure] @configname = 'c2 audit mode', -- varchar(35) @configvalue = 1 -- int RECONFIGURE WITH override
F
第三章 查询执行
F
调用open（）方法
调用getrow（）方法
调用close（）方法
查询树
F
F
F
如果多个运算符进行内存竞争，那么当前查询需要等到获取必须的内存授权后才能进行，延迟执行，将直接影响性能
内存溢出会对查询以及系统性能产生不良影响。更有甚者，当运算符产生过多数据时，他将耗尽tempdb上的磁盘空间并以失败告终
最主要的内存消耗运算符是分类运算符、哈希运算符、哈希聚合运算符
阻断运算符和非阻断运算符
F
Fshow plan 计划展示选项
图形执行计划，文本执行计划，xml执行计划
在sqlserver2005才支持xml执行计划，之前只能使用图形执行计划和文本执行计划
F
F
F
F
保存.sqlplan执行计划文件
xml计划可以保存到xml列中，使用sql2005的xquery功能来进行查询
xml计划包含了sqlserver2000中的所有可用信息
F
新增的USEPLAN查询提示，使用xml计划
F
预估执行计划和实际执行计划
F
f
变量和参数是两个不同的元素（element）
F
F
XML执行计划解释
MemoryGrant的单位是KB
F
fraction
一般来讲，在运算符执行到输入阶段时，一定要和其子运算符共享内存；在运算符执行到输出阶段时，一定要和其父运算符共享内存
F
F
F
自动参数化
F
F
F
SQL2000不会显示聚集索引书签查找和索引书签查找
F
使用包含性列include使优化器的编译成本低一些，不用比较每一个非聚集索引，当然也可以另建一个非聚集索引
是查询走新建的非聚集索引，但是查询优化器要多比较一个非聚集索引
F
sqlserver对嵌套循环提高性能的两个重要技术
1、关联参数
2、在连接内部中基于关联参数的索引查询
3、连接内部使用存储池
F
一个存储池能够从连接内部缓存和重新访问结果，通过使用存储池，sqlserver可以避免对相同关联参数的连接内部重新计算多次
F
不能使用嵌套循环的逻辑运算符
右外连接和右半连接和左反半连接
F
F
F
合并连接 merge join
F
F
F
MANY TO MANY
F
HASH JOIN 哈希连接
哈希连接是第二个物理连接操作符
嵌套循环连接对于小数据集很有用，合并连接对于中型数据集很有用，哈希连接对于大型数据集很有用
构建哈希表，对于两表join，对第一个表构建哈希表，对第二个表构建哈希表，然后利用连接键，在两个哈希表进行key比较
F
F
F
F
F
F
对于聚合，sqlserver支持两种物理操作符：标量聚合（使用流聚合），流聚合，哈希聚合
F
F
scalar distnct 对标量聚合添加distnct关键字进行查询
sort运算符：sort运算符是sqlserver用来消除重复行的常见方法之一
如果存在唯一索引，sqlserver可用跳过sort步骤
F
F
没有连接谓词，他是交叉连接
交叉连接就是吧结果的两行“粘贴”为单一的行
f
流聚合
F
流聚合之前都需要排序 sort的原因是把属于同一组的不管重复还是不重复挑选出来
可以认为标量组合是一个包含所有行的大组，因此，对于一个标量聚合，没有必要将行排序为不同的组
F
加上order by依然只有一个sort运算符
如果有一个合适的索引，执行计划根本不需要一个sort运算符
F
如果没有一个索引提供排序，优化器会使用没有经过排序的流聚合，扫描之后马上进入流聚合，而不需要经过sort运算符，
在所有已选择的没有聚合函数的列上，select dstinct本质上和group by是一样的
USE [AdventureWorks] SELECT DISTINCT([CustomerID]) FROM [Sales].[SalesOrderDetail] --上面的 DISTINCT语句和下面GROUP BY语句等价 USE [AdventureWorks] SELECT [CustomerID] FROM [Sales].[SalesOrderDetail] GROUP BY [CustomerID]
F
F
residual：剩余的
F
哈希聚合
F
F
流聚合在同一时刻只能对一个组进行计算，而哈希表可以同时对所有的组进行计算
如同哈希join，一个哈希聚合也可以使用一个哈希表存储这些组
内存和溢出
在group by行上如果有较少的唯一值和较少的组，则哈希聚合只需要较少的内存，如果在group by行上有较多的唯一值和较多的组，则哈希聚合需要较多的内存
在执行哈希聚合之前，sqlserver使用基数（cardinal）统计信息对需要多少内存来执行查询进行估计
F
哈希聚合
哈希join
一定要分清楚
要看hash match后面是什么，是aggregate还是inner join ，hash:
hash match(aggregate,hash:)
hash match(inner join ,hash:)
哈希聚合在组的数目减少时需要的内存大小也减少，但是排序需要的内存大小和输入行的数量是成比例的
F
如果有order by语句，那么需要排序，sqlserver因此选择一个流聚合执行计划而不选择哈希聚合
hash match(aggregate,hash:)
F
如果表很大，同时组很小，那么优化器会使用哈希聚合
sort运算符是最后的运算符，因为哈希聚合不需要数据在较早时被排序
F
sqlserver会吧哈希聚合和流聚合混合在一起使用
F
F
UNION 和UNION ALL 联合
F
F
F
当存在很多重复数据的时候，优化器更倾向于选择哈希聚合
F
F
哈希联合
F
哈希联合对于大表消除许多重复行是很好的选择
hash match(union)
F
高级索引运算
动态索引查找
F
F
F
索引合并
F
用union来改写or
F
F
索引交叉
F
F
F
标量子查询
断言操作
F
断言运算符（assert）检测其他条件：例如 约束条件，检查，参照完整性，普通表表达式的最大递归层次警告，重复键插入，ignore_dup_key选项的索引建立等
F
因为有唯一索引，那么不需要流汇总和断言操作
相关标量子查询
F
index spool
F
F
重绑和重绕
F
段运算符：段操作符将customerid列有相同值的行分成zu（或段）。因为这些行都是被排序的，具有相同customerid值的行的集合将是连续的。接下来，表spool-》一个段spool-》读并保存其中一组拥有相同customerid值的行
F
F
F
top运算符
top运算符返回每一组运费最大的行或几行
F
消除关联
F
F
使用哈希连接，这个连接本身是一个右外连接，外连接保证这种计划返回所有的订单，即使那些含有null值的列customerid，内连接丢掉这些行，因为null从来没有连接
SQLSERVER中大多使用半连接来计算exists中的子查询因为半连接几乎不检查其中连接输入中行的匹配情况，但是一般的半连接（或反半连接）只返回那些匹配的行（或不匹配的）
F
F
标量子查询被加到一个不唯一的列，他会返回不止一行，这样可能导致错误，本身标量就是返回一行的
passthru的特殊谓词，如果passthru谓词为真，就直接返回其外部行而不用执行其内部输入
如果passthru谓词为假，会继续执行下去并会尝试连接内部行和外部行
isfalseornull（）函数
passthru谓词是唯一一个嵌套循环连接中外表的行数不能准确匹配内表行数的案例
在sql2000上运行该例子，依然得到一个passthru谓词的计划，但他在计划中会以一般的where子句谓词形式出现
sql2000中没有一个简便的计划能区分where子句谓词和passthru谓词
F
F
划分为奇数和偶数分配到两个cpu上
F
如果没有足够的可用线程，在极端情况下，sqlserver会将并行计划变为串行计划
F
并行交换运算符出来线程间的数据划分和线程间的移动，他是独一无二的
F
F
并行死锁
使用[sys].[dm_os_waiting_tasks]来检测并行死锁
SELECT * FROM [sys].[dm_os_waiting_tasks]
如果所有和某个会话相关联的线程都以CXPACKET等待类型被阻塞，那么这时并出现死锁了，
一般来说，并行死锁唯一的解决方法就是减少并行度，使用串行计划或变更查询计划，可减少其中的合并交换
F
并行扫描
并行扫描算法特点
3、
能够适应倾斜和负载不均衡，如果一个线程运行得比其他线程慢，这个线程就会请求较少的页面而让其他快的线程分担额外的任务。总的执行时间就会平稳地减少，对比sqlserver静态地将页面分派给线程的案例就会发现，这种方案中，执行缓慢的线程将成为主宰总的执行时间
F
F
负载均衡
局部聚合：sqlserver很许多地方使用局部聚合，像流聚合和哈希聚合，大多数内置用户自定义聚合函数、标量聚合函数、分组聚合。
分组聚合伴随着分组数量减少，局部聚合的好处就更明显，分组数目越小，局部聚合实际能执行的任务越多
F
F
并行嵌套循环连接
F
F
并行扫描比循环赛交换的优势是可以在各个线程间负载均衡
F
广播交换
F
并行 merge join
F
并行hash join
F
F
位图过滤运算符
F
F
F
第四章 关于查询性能的疑难解决
F
编译 优化
查询被解析器处理，然后被一个叫做代数化的组件处理，解析器负责解析SQL语句，并将其他转换成编译就绪的数据结构
如果是DML语句，那么会生成语法解析树，如果是DDL语句，那么会生成序列树
序列树源于sql6.5，当时用的是序列化，序列树和解析树之间不同之处在于序列树是二叉树，而解析树的每个节点可以有多个子节点
代数化负责检查有效的表名和列名
F
F
F
F
LIKE是不是sargable取决于使用的通配符类型和位置，例如'jon%'是sargable 但 '%jon'不是，因为出现在起始的通配符阻止了索引的使用
为什麽 '%jon'不是sargable ，想以下mysql的查找
不是sargable的表达式不能限制查询，因此，对于nonsargable表达式，一个索引不是没用的
NOT,NOT IN,NOT LIKE,否操作符，通配符开始的字符串都是nonsargable
F
索引选择
索引统计值
索引选择是查询优化的第二阶段，查询优化器确定一个sargable子句中是否存在索引，通过确定子句的选择性来评估索引的有效性
F
sqlserver统计信息包含一个柱状图，该柱状图由基于当前数据索引码制的样本或组合索引码的第一列构成，
为了充分估计索引的有用性，优化器还要知道表中或索引中页面的数量，通过兼容性视图sysindexes和sysallocation _units来获取
估计行数都要获取，评估搜索数据占用表的总数据量的百分比
统计信息中的密度
F
柱状图仅记录索引第一列的样本，而密度信息用来存储一个组合索引的所有列的多个组合，例如组合索引（lastname，firstname）上有一个索引，那么，统计值包括两个密度值其一是lastname的密度值，另一个是关于lastname和firstname组合的密度值
sqlserver定义密度的公式
密度=1/索引码的基数 应该翻译为 密度=1/索引列的基数
例如：组合索引 （lastname ，firstname）
那么，密度=1/（lastname ，firstname）的基数
索引码=key column
唯一索引的基数是最低的！那么算出来的密度也最低！唯一索引的选择性是最高的！
密度越低，选择性越高
例如，假设一个索引由3个列构成，第一列上的密度可能为0.5，这不太好，但是
当你看到索引中更多索引列的时候，被指定行的数量要少于第一列，因此，密度值下降了，
如果把第一列和第二列都考虑进来，密度值可能为0.25，稍微好些，如果检查三个列，那么密度值可能为0.03
这就是高选择性
基数意味着存在于数据中唯一值的数量
基数意味着存在于数据中唯一值的数量。单个属性列索引的统计值由一个柱状图和一个密度值组成
统计信息包含柱状图，最后一次统计收集的时间，用于产生柱状图和密度信息行的数量，索引列的平均长度，其他索引列的组合密度
F
F
F
统计信息能存储多达200个样本值
defeat：击败
F
F
缓存空启动（started out empty）：缓存里没有缓存数据？
F
F
使用多索引
查询优化器可以决定使用两个或更多的非聚集索引来满足单个查询
如果优化器决定使用所有索引，那么sqlserver将会有两个工作区，每个索引寻找的结果
F
通过仔细研究，如果单表有多个由OR连接起来的SARGS，那么也能使用多重索引，这种情况下，sqlserver找到由每个索引返回的行的合并
F
不能使用统计信息的情况
USE [AdventureWorks] DECLARE @NAME VARCHAR(30) SET @NAME='ZELDA' SELECT LASTNAME,FIRSTNAME,[EmployeeID] FROM [HumanResources].[Employee] WHERE LASTNAME=@NAME
查询优化器必须猜测这个值会是什么
不要混淆了变量和参数，尽管他们的语法几乎是相同的。直到语句被真正执行时，才能知道一个变量的值，在编译期间是不可能知道的，当存储过程被编译时，就知道存储过程的参数了，因为直到存储过程被真正调用时，也就是为参数赋予具体的值，编译和优化才会发生
因为任何一个原因而使得查询优化器不能使用统计信息，那么sqlserver会使用固定的百分比来估计有多少行满足一个给定的SARG
F
set statistics profile on 可以容易看出基数估计错误，也可以在实际的执行计划图形界面里看
简单比较一下估计的和实际的行数，在任何真实环境下总会有一些错误，错误的级差通常比错误的绝对数值更为重要。例如，估计值和实际值分别为1和1000的错误要比估计值和实际值分别为100000和200000的错误更令人担扰
基数错误趋向于向上波及。例如，在扫描或查找时产生的错误可能会影响所有要扫描或查找结果的运算符的基数估计，包括筛选、连接、聚集等
F
这种错误将导致糟糕的选择遍布整个执行计划
谓词越简单估计值越准确，明确的谓词还增大了优化器使用索引查找的机会
如果基数错误原发于一个非叶运算符，就不太可能轻松改正
如果基数错误致使计划不合理，可能需要使用“提示”
使用recompile查询提示重编译
F
哈希连接，哈希聚合，排序
使用事件探查器检查排序警告和哈希警告事件类
有排序运算符时，sort warning
哈希运算符时，hash warning
递归层数过大，会转到救助算法
并行计划 里合并交换要占用更多的服务器资源，而且容易受倾斜和伸缩性问题的困扰
f
f
同一页面可以被读取多次（例如当一个查询使用了索引），所以对某一个表逻辑读的次数可以比该表的页数大
缓存命中率=（逻辑读-物理读）/逻辑读
IO作用于每个表和每个查询，可能需要审查某些列，用
SELECT * FROM sys.[dm_exec_query_stats]
来跟踪物理读次数，
查看得到的信息包括最小物理读次数，最大物理读次数，物理读总次数
[query_hash],[query_plan_hash] 查看查询的hash值和执行计划的hash值
预读，逻辑读，物理读
这些预读不一定被查询用到，如果用到了，只增加逻辑读次数而不增加物理读次数
预读由执行查询的线程执行异步请求，正因为是异步的，所以扫描不会因预读而被阻塞，只有要扫描的页恰巧是预读进去缓存的页时才会被阻塞
f
因为可能使用了worktable，所以在嵌套循环连接的内表扫描计数有可能是1
SELECT [memory_usage]*8 AS '单位KB 公式 [memory_usage]*8KB 因为这个字段是页面数' FROM sys.[dm_exec_sessions]
f
堆表中
要解决这个问题要定期检查转向指针的个数forwarded record，特别是表中有可变长度而且频繁更新的字段时，当发现有大量的转向指针时，可以重构该表创建聚集索引
f
CPU时间不包括等待资源的时间，比如锁，读，cpu时间分为两部分：解析和编译查询的时间和执行查询的时间
查询优化
重写查询：避免过于复杂的谓词，过于复杂的谓词可能会导致基数（cardinality）错误和坏的计划
f
避免并行计划中的动态索引查找，优化器倾向于带有OR谓词或IN列表的被参数化的查询选择动态索引查找（dynamic index seeks）
使用union 来重写OR 查询或IN查询
静态索引查找
与SQL注入相比（injection attacks），参数化参数和存储过程更安全一些
避免使用表值函数代替多语句表值函数，避免使用cross apply
f
坚持使用集合查询，而不使用游标查询，避免使用动态游标dynamic cursors而使用静态static游标或keyset游标
数据库引擎顾问DTA，以前是索引调整向导ITW，可以帮助识别缺失索引
f
避免在带有不匹配数据类型的列上join表
尽管sqlserver能进行隐式数据类型转换，在某些情况下会阻止sqlserver使用索引查找
F
统计信息名字总是以_WA_Sys这8个字符串开始，紧接着这个8个字符串是一个8字节的十六进制数值，该数值表明了表中列的序数位置，
也代表了表的object_id，手动创建的统计信息名字可以是任意的，只要是合法的表示符就可以。sql2005提供了一个目录视图sys.stats
[_WA_Sys_00000001_7E6CC920]
查看某个库的统计信息
USE [sss] --要先use一下数据库 SELECT OBJECT_NAME([stat].[object_id]) AS '表名', [stat].[name] '统计信息名', [stat].[object_id] , [col].[name] AS '列名' FROM sys.stats AS stat JOIN sys.[stats_columns] AS statcol ON [statcol].[object_id] = [stat].[object_id] AND [statcol].[stats_id] = [stat].[stats_id] JOIN sys.[columns] AS col ON [col].[column_id] = [statcol].[column_id] AND [col].[object_id] = [statcol].[object_id] ORDER BY [stat].[object_id]
_WA_Sys_00000001_7E6CC920
17E6CC920
转为十进制：6416025888
object_id：2121058592
USE [sss] SELECT * FROM [testrow] EXEC [sys].[sp_helpstats] @objname = N'[testrow]'
不会在非索引列上建立统计信息
F
使用下面语句建立多列统计值，覆盖索引默认会创建多列统计值
CREATE STATISTICS LocationStatistics ON Locations(city,state)
F
手动更新统计信息
UPDATE STATISTICS [testrow] WITH FULLSCAN
创建有用索引
F
F
F
F
在哪一列建立非聚集索引，那么原来可以使用update-in-place的更新只能使用delete/insert
SELECT CHECKSUM('232dfgdgdgdfghjhjhj566')
---1957216408
CHECKSUM函数生成4字节的整数
F
F
search argument：SARG
连接密度（join density）
F
F
DTA数据库引擎优化顾问根据workload file上的建议，workload file是捕获跟踪事件的文件，捕获跟踪事件至少包含RPC,SQL批处理开始或完成的事件，
也包括RPC和批处理文本，他也可能是SQL语句的文件。如果使用SQL配置文件profile来创建workload文件，就能获取一段时间内被所有用户提交的全部SQL语句
DTA也会建议使用索引视图和分区表
HINT在某些情况下会导致优化器改变他探查的计划集。例如，一般来说，优化器不会考虑浓密连接树（参考第三章 左深度，右深度，浓密连接树的区别的解释）然而，某些hint（force order和use plan）可能导致优化器把这些计划列为可选的
F
研究任何基数的评估可能存在的严重错误
分为三类计划hint：表hint，查询hint，连接hint
计划选择hint也可以被分成两组，1目标导向hint 2 物理运算符hint
F
强制一个特定计划使用目标导向hint可能会很困难，甚至是不可能的
查询hint在option中指定 option（）
F
fast n hint
USE [sss] SELECT * FROM [testrow] OPTION (FAST 1)
F
F
optimize for hint，optimize for hint是另一种目标导向hint，是sqlserver2005的新特性
F
DECLARE @SHIPCODE NVARCHAR(20) SET @SHIPCODE=N'05022' SELECT * FROM orders WHERE shippotalcode=@SHIPCODE OPTION(OPTIMIZE FOR(@SHIPCODE=N'05022'))
F
SELECT *
FROM orders
OPTION(LOOP JOIN,MERGE JOIN)
排除掉hash join 只用loop join 和merge join
F
F
连接hint不能为每个连接定制hint，必须要所有的连接，如果有一个很多连接的查询，并且你希望或需要独立控制每个连接的连接类型，请考虑使用ANSI-style连接hint
group hint强制聚合类型
order group
hash group
order group 选择流聚合
hash group 选择哈希聚合
一个组hint也影响到所有查询的执行计划，可以使用use plan hint来强制使用一个流聚合和哈希聚合的混合
SELECT customerid,MAX(orderdate) FROM orders GROUP BY customerid OPTION(HASH GROUP)
F
优化器总是为标量聚合使用流聚合运算符，因此没有任何办法强制优化器为一个标量聚合使用哈希聚合，如果强制使用哈希聚合会报错
优化器选择了一个带有排序和流聚合的计划
F
union hint
concat union
merge union
hash union
USE [AdventureWorks2008] SELECT [CustomerID] FROM [Sales].[SalesOrderHeaderSalesReason] UNION SELECT [CustomerID] FROM [Sales].[Customer] WHERE [City]='london' OPTION(MERGE UNION)
F
强制顺序
强制连接顺序
force order hint告诉优化器把表按照他们在查询的FROM子句中出现的顺序进行连接
如果FROM子句只是由逗号分隔的基本列表组成，force order hint 的结果会导致一个左深度树，表按照他们出现在FROM子句中的顺序连接
USE [AdventureWorks] SELECT * FROM [Sales].[Customer] c JOIN orders o WHERE c.city='xx' OPTION(FORCE ORDER,HASH JOIN)
左深度树
如果from子句包含了cte，视图或内联tvf，只要force order还生效，这些组件的行为就如同子查询一样，同样需要注意的是，
如果有一个带外连接的查询，除非改变这个查询的语义，否则有可能不能强制全部连接顺序
F
加where，加on防止cross join 交叉连接
一个查询中，对于给定的表数目，可能的连接顺序的数量
bushy tree：浓密树
F
像olap，你有一个星模型，在连接一个大事实表前，将维度表和最具选择性的过滤器连接
F
F
F
F
F
USE [AdventureWorks] SELECT * FROM [HumanResources].[Employee] E JOIN ( [Sales].[Customer] c JOIN orders o ON [c].[CustomerID] = O.[CustomerID] ) ON E.[EmployeeID] = O.[EmployeeID] WHERE c.city = 'xx' OPTION ( FORCE ORDER )
用括号括起来来强制这个查询计划
F
F
F
F
我们非常小心地通过把customers表列在前面来强制优化器把customers表连接在orders表
前面。这个连接顺序导致了一个高效的嵌套循环连接。如果把表按照相反的顺序列举从而强制
相反的连接顺序，优化器就不会使用嵌套循环连接而是被迫使用一个效率差得多的多对多合并连接merge join
option(force order)
F
F
F
maxdop n hint并不保证真实的并行度就是N，也不会保证优化期间使用的并行度会和执行期间使用的并行度相匹配
F
expand views hint 仅仅在sqlserver企业版有用：不使用基表，指导sqlserver使用索引视图来执行查询
表hint：index，noexpand，fastfirstrow，不像查询hint，表hint应用于一个单一的表，并且可以在子查询，视图和内联tvf中使用
F
index hint
sql2005不能强制优化器使用索引查找和索引扫描，sql2008可以使用force scan，sql2008r2可以使用force scan和force seek
书签查找由很多随机I/O组成
F
F
F
F
F
--使用index hint强制索引交集，避免书签查找 SELECT orderid , customerid FROM orders WITH ( INDEX ( orderdate, shippostalcode ) ) WHERE orderdate = '2013'
F
索引联合 index union
F
F
fastfirstrow hint
ansi-style 连接hint
F
F
USE [AdventureWorks] SELECT * FROM [HumanResources].[Employee] E INNER LOOP JOIN ( [Sales].[Customer] c INNER HASH JOIN orders o ON [c].[CustomerID] = O.[CustomerID] ) ON E.[EmployeeID] = O.[EmployeeID] WHERE c.city = 'xx' OPTION ( FORCE ORDER ) --注意这个连接要使用inner关键字，即使他对于ansi连接是缺省的
F
F
F
USE PLAN HINT
use plan hint是sql2005新加入的
use plan hint和计划向导不一样 plan guide！！
F
模式改变：当删除表的一个索引或约束，那么你提供的use plan hint就会失效，在任何数据库架构改变后核实所有的强制计划依然有效并且所有的use plan hint依旧按照预期的情况工作会是一个很好的锻炼
F
use plan hint不能使用在insert，update，delete的查询计划
use plan hint不能与其他的hint一起使用，例如表hint，index hint，但是可以跟ansi语法连接hint结合使用
F
有几个运算符是不能被use plan hint强制的，包括：assert断言，bitmap位图过滤，compute scalar计算标量
F
F
USE [AdventureWorks] SELECT * FROM [HumanResources].[Employee] E JOIN ( [Sales].[Customer] c JOIN orders o ON [c].[CustomerID] = O.[CustomerID] ) ON E.[EmployeeID] = O.[EmployeeID] WHERE c.city = 'xx' OPTION ( USE PLAN N'<showplanxml...>...</showplanxml>' )
F
查询优化最佳实践
使用面向集合的编程
F
为优化器提供约束和统计信息
F
避免不必要的复杂性
F
谨慎使用动态sql
F
改变连接设置，如ansi_null或arith_abort算术终止会影响查询的语义,同样会影响为这些查询产生的计划,
改变set选项后再执行相同的查询会影响性能,因为需要重编译,并且减少了可重用的执行计划.最佳做法是对所有应用都保持默认的sqlserver连接设置
F
第五章 计划缓存和重编译
F
plan cache
sqlserver7.0之前的版本中，通过两个有效配置值来控制plan cache的大小，他们被称为过程内存（procedure cache），一个用来指定sqlserver的总可用内存的固定大小；
另一个用来指定被存储过程计划独占使用的内存（在确定需要被满足后）的百分比。而且，在sqlserver7.0之前的版本中，
adhoc sql语句的查询计划从不会被保存在缓存中，只有存储过程的计划。这就是之前版本中被称为进程缓存的原因。
F
能查看执行计划缓存对应哪个数据库
SELECT DB_NAME([txt].[dbid]) AS 'dbname', p.[usecounts] , p.[cacheobjtype] , p.[objtype] , txt.[text] FROM SYS.[dm_exec_cached_plans] P CROSS APPLY SYS.[dm_exec_sql_text]([plan_handle]) txt WHERE [cacheobjtype] = 'COMPILED PLAN' AND TEXT NOT LIKE '%dm_exec_cached_plans%'
F
F
SYS.[dm_exec_cached_plans]
[cacheobjtype]有5个不同的值
compiled plan
parse tree
extended proc
clr compiled func
crl compiled proc
[objtype]有11个不同的值
proc
prepared
adhoc
replproc
trigger
view
default
usrtab
systab
check
rule
adhoc查询缓存
F
注意：select语句都在自己的批处理中，由GO分隔开，如果没有GO，就只有一个批处理，而且每个批处理都有自己的计划，
包括批处理中每个独立查询的执行计划，为了重用adhoc查询计划，整个批处理必须是等同的！！
F
观察usecounts输出
USE [AdventureWorks2008] GO DBCC FREEPROCCACHE GO SELECT [FirstName] , [LastName] , [Title] FROM [HumanResources].[Employee] WHERE [EmployeeID] = 6; GO SELECT [FirstName] , [LastName] , [Title] FROM [HumanResources].[Employee] WHERE [EmployeeID] = 2; GO SELECT p.[usecounts] , p.[cacheobjtype] , p.[objtype] , txt.[text] FROM sys.[dm_exec_cached_plans] p CROSS APPLY sys.[dm_exec_sql_text]([plan_handle]) AS txt WHERE [cacheobjtype] = 'compiled plan' AND [text] NOT LIKE '%dm_exec_cache_plans%' GO
F
查询会有指向预定义计划的指针，sys.[dm_exec_cached_plans]的objtype里会看到prepared
查询模版
F
有很多通常不允许自动参数化的查询结构
强制参数化
F
不能使用强制参数化的语句
包含2097个以上字符的语句不被强制参数化！！
自动参数化的缺陷
使用自动参数化，sqlserver选择假定一个tinyint类型的参数，如果参数超出了tinyint范围0~255，sqlserver就不能使用同样的自动参数化查询！！
F
开启强制参数化
ALTER DATABASE [sss] SET PARAMETERIZATION FORCED ALTER DATABASE [sss] SET PARAMETERIZATION SIMPLE
F
SQLSERVER：SQL Statistics 计数器观察每秒钟的参数化情况，包括：自动参数化和强制参数化
Auto-Param Attempts/sec
Forced Parameterizations/sec
Safe Auto-Params/sec
Unsafe Auto-Params/sec
预定义查询的两个方法
1、sp_executesql
2、使用C# prepare/execute方法
F
F
没有方法可以查出预定义计划是通过sp_executesql还是使用C# prepare/execute方法
F
存储过程重编译
EXEC usp_Customers 'SAVEA' WITH RECOMPILE
F
函数被缓存的计划的objtype为proc，usecount值为2
在表达式内部的标量函数是无法重编译的
--在表达式内部的标量函数是无法重编译的 select dbo.fnmaskssn(au_id),au_lname from authors;
F
内联函数实际上被视为一个视图
基于正确性的重编译：添加索引，删除索引，添加列，添加触发器，删除触发器
基于最佳性的重编译
F
sp_recompile的奥秘就是修改sysobjects表的modify_date列
这样使表发生了模式改变，那么跟表有关的都会重编译
查看各个session的set选项值是否已经缓存在plan cache
--查看各个session的set选项值是否已经缓存在plan cache SELECT p.[usecounts] , p.[cacheobjtype] , p.[objtype] , [attr].[is_cache_key], [attr].[attribute], [attr].[value] FROM sys.[dm_exec_cached_plans] p CROSS APPLY sys.[dm_exec_plan_attributes]([plan_handle]) AS attr WHERE [cacheobjtype] = 'compiled plan' GO
查看每个执行计划的里set选项，根据位来判断改变了哪个选项值，因为执行计划缓存同时会缓存set选项
SELECT [plan_handle] , pvt.set_option , pvt.object_id , pvt.sql_handle FROM ( SELECT [plan_handle] , epa.attribute , epa.value FROM sys.[dm_exec_cached_plans] AS p OUTER APPLY sys.[dm_exec_plan_attributes]([plan_handle]) AS epa WHERE [cacheobjtype] = 'compiled plan' ) AS ecpa PIVOT ( MAX([ecpa].value) FOR ecpa.attribute IN ( "set_option", "object_id", "sql_handle" ) ) AS pvt
F
F
下面的set选项不会导致执行计划重编译
USE [sss] SELECT OBJECTPROPERTY(OBJECT_ID('[testrow]'),'ExecIsQuotedIdentOn')
如果返回为null表明输入错误值或没有合适的权限
关于最佳性的重编译
F
统计信息失效
每张表都有一个重编译阀值，或者RT值，他决定了表上统计信息失效之前可以有多少个改变，批处理中涉及到的表RT值和查询计划存储在一起
RT值依赖于表类型（持久的，还是临时的），编译时表中的行数
RT值的算法涉及到每个service pack的不同，对于sqlserver2005的rtm版本
各个service pack使用的公式类似与下面，但不能肯定他们是完全相同的，N表明表间关系
意味着很小的表，至少要有500次改变才能触发重编译，对于更大的表，改变的次数至少要500，再加上20%的行数
企业管理实践 统计信息更新算法
--（1）如果统计信息是定义在普通表格上，那么当发生下面变化之一后，统计信息就
--被认为是过时的了。下次使用到时，会自动触发一个更新动作
--分离数据库的时候，也可以手动选择是否更新统计信息
--1、表格从没有数据变成有大于等于1条数据
--2、对于数据量小于500行的表格，当统计信息的第一个字段数据累计变化量大于500以后
--3、对于数据量大于500行的表格，当统计信息的第一个字段数据累计变化量大于
--500+(20%*表格数据总量)以后。所以对于比较大的表，只有1/5以上的数据发生变化后
--SQL才会去重算统计信息
--（2）临时表（temp table）上可以有统计信息。其维护策略基本和普通表一致。
--但是表变量（table variable）上不能建立统计信息
表变量 没有RT值（重编译阀值），因此表变量上行数的变化不会引起重编译
F
SELECT * FROM [testrow] OPTION (KEEP PLAN)
SELECT * FROM [testrow] OPTION (KEEPFIXED PLAN)
F
SQL2005版本，在sysindexes系统表的rowmodctr列上跟踪实际发生在表上改变的数目
rowmodctr：row modify counter 会保存在sysindexes表
colmodctr：column modify counter 不会保存在sysindexes表，sysindexes表没有colmodctr字段
colmodctr只对查询优化器可见《深入解析sql2008》
SELECT [name] , [rowcnt] , [rowmodctr] FROM sys.[sysindexes]
KEEPFIXED PLAN：sqlserver不会因为任何与最佳性相关的原因重编译计划
SELECT * FROM [testrow] OPTION (KEEPFIXED PLAN)
KEEPFIXED PLAN与KEEP PLAN的区别
KEEP PLAN：正确性
KEEPFIXED PLAN：最佳性
f
若查询中数据库是只读的，那么sqlserver不会重编译执行计划
在sqlserver开始执行任何单个查询前，他会检测此计划的正确性和最佳性，若其中某个检查失败，相关语句被再次编译，可能产生一个不同的查询计划
批处理包含大于8KB的文本时，他就不会被缓存
sql2000批处理级的重编译
sql2005语句级的重编译
f
清空某个特定数据库的执行计划
在sql2008里可以删除某个特定的执行计划缓存，但在sql2005里无法做到
输入plan handle清除某个执行计划 ，准确来说是已编译计划，编译和执行是两个阶段
如果是SQL server 2008 R2就很好办，直接使用DBCC FREEPROCCACHE然后传入plan handle 即可，如：
DBCC FREEPROCCACHE(0x060001002903DC0B4001B887000000000000000000000000)
但是SQL server 2005的FREEPROCCACHE并没有这个用法。
SQL 2005里面如果运行DBCC FREEPROCCACHE那么所有的缓存都会被清空了。
这对性能的影响比较大，因为SQL server 要对所有的语句重新编译然后重新生成缓存。
SQL server 2005里面有没有其他方法只清除特定的语句的缓存呢？
有的，答案就使用使用plan guide
F
type列显示了执行计划的存储类型
SELECT * FROM sys.[dm_os_memory_cache_counters]
SELECT *
FROM sys.[dm_os_memory_cache_counters]
type列显示了执行计划的存储类型
cachestore_objcp:对象计划 存储过程，函数，触发器
cachestore_sqlcp：adhoc缓存计划，自动参数化计划，预定义计划
cachestore_phdr：界树（Bound Trees）叫绑定树 代数化组件生成的结构 ，绑定阶段（sql2005 技术内幕 tsql 查询
绑定：确定SQL语句所引用对象的特征检查请求语义是否有意义，例如检查From A join B的查询时，如果A是一个表）
cachestore_xproc：扩展存储过程
《sql2005技术内幕 存储引擎》
通用缓存框架，除数据高速缓存之外的所有高速缓存都用到了他
该框架包含了一套存储仓库 cachestore和资源监视器
一共有三种类型的cachestore
高速缓冲cachestore（plan cache）
用户cachestore（实际上与用户并没有任何关系） （元数据）
对象cachestore对象cachestore 仅仅是内存块组成的内存池，并不需要LRU或成本计算
对象cachestore（例如 SNI 网络接口 作为网络缓冲池 结果集？）
cachestore_xproc：扩展存储过程，只缓存扩展存储过程名称和dll名称
bound a.被束缚的，一定的 n. 界限
band n.条，(布)带；乐队；波段 ; v.缚，绑扎
记忆技巧
其实这2个单词是同源的
sqlserver使用哈希表来存储这些执行计划
哈希key的计算公式：（object_id*database_id）mod(hash table size)
SELECT [type] AS 'plan cache store' , [buckets_count],* FROM sys.[dm_os_memory_cache_hash_tables] WHERE [type] IN ( 'cachestore_objcp', 'cachestore_sqlcp', 'cachestore_phdr', 'cachestore_xproc' )
界树的存储大小大概是对象计划和sql计划的10%
扩展存储过程大小总被设置为127
F
已编译的计划为整个批处理而生成，而不只是为某个语句
可执行计划被认为依赖于已编译计划，可执行计划是运行时对象，在已编译计划执行时创建
F
已编译计划中每个独立的语句有自己的可执行计划，可将其作为一个运行时查询计划看待
100个用户同步执行相同的批处理，使用相同的已编译计划，但会生成100个可执行计划，可执行计划从已编译计划重新生成，而且生成成本非常低
sys.[dm_exec_cached_plans]视图对每个已编译计划都有一个plan handle值。plan handle是一个哈希值，sqlserver从整个批处理的已编译计划中获得，他保证对每个当前存在的已编译计划来说都是唯一的
就算重编译，plan_handle也会保持不变
plan_handle包含整个批处理文本的哈希，每个批处理都是唯一的，plan_handle作为SQLMGR缓存中的批处理文本的标识符
SQL管理缓存SQLMGR，sql文本会放进去
sql_handle和plan_handle的关系是1:N
F
sql_handle和plan_handle的关系是1:N （1个sql语句 对应多个执行计划）
plan_handle是一个很模糊的值，很难确定每个sql_handle关联哪个查询，因而可以使用另一个函数来得到此信息
SELECT * FROM sys.[dm_exec_sql_text]()函数可以使用sql_handle和plan_handle作为参数，他会返回关联于此句柄的sql文本，任何包含在sql文本中的敏感信息，例如密码会被阻塞
从SELECT * FROM sys.[dm_exec_sql_text]()函数查看sql文本，对快速识别相同批处理有用，
这些批处理由于一些因素（例如set选项不同）可能有不同的已编译计划
F
plan_handle值不同的位代表不同的选项
F
SELECT * FROM sys.[dm_exec_cached_plans]
对每个被缓存的计划都有一行
[size_in_bytes]：被缓存对象消耗的字节数
[cacheobjtype]：缓存对象类型，就是说，他是已编译计划，或是分析树，或是扩展存储过程
[memory_object_address]：缓存对象内存地址，可用于得到缓存对象的内存故障
SELECT st.[text] , cp.[plan_handle] , [cp].[usecounts] , cp.[size_in_bytes] , cp.[cacheobjtype] , cp.[objtype] FROM sys.[dm_exec_cached_plans] cp CROSS APPLY sys.[dm_exec_sql_text]([cp].[plan_handle]) st ORDER BY cp.[usecounts] DESC
sys.[dm_exec_cached_plan_dependent_objects]
sys.[dm_exec_cached_plan_dependent_objects]返回依赖对象，包括之前讨论的可执行计划，还有已编译计划使用的游标的计划
SELECT [text] , [plan_handle] , d.[usecounts] , d.[cacheobjtype] FROM sys.[dm_exec_cached_plans] cp CROSS APPLY sys.[dm_exec_sql_text](cp.[plan_handle]) st CROSS APPLY sys.[dm_exec_cached_plan_dependent_objects](cp.[plan_handle]) d
SELECT [statement_start_offset],[statement_end_offset] FROM sys.[dm_exec_requests]
[statement_start_offset]和[statement_end_offset]偏移
表明在整个批处理内可以找到当前运行的语句位置，偏移从0开始，-1结束
F
下面查询返回10个当前执行运行时间最长的查询
SELECT TOP 10 SUBSTRING(st.[text], ( [execre].[statement_start_offset] / 2 ) + 1, DATALENGTH([st].[text])) , ( ( CASE [execre].[statement_end_offset] WHEN -1 THEN DATALENGTH([st].[text]) ELSE [execre].[statement_end_offset] END - [execre].[statement_start_offset] / 2 ) + 1 ) AS query_text , * FROM sys.[dm_exec_requests] execre CROSS APPLY sys.[dm_exec_sql_text]([execre].[sql_handle]) st ORDER BY [execre].[total_elapsed_time]
从sql_handle返回的文本是整个批处理的文本一样，返回的已编译计划是整个批处理的
sys.[dm_exec_query_stats]返回批处理内独立查询的性能信息，视图返回查询的性能统计信息，
集合所有查询的执行返回sql_handle和plan_handle，还有sql语句偏移
下面语句查询总CPU时间返回前10个查询，协助识别sqlserver上运行成本最高的查询
SELECT TOP 10 SUBSTRING(st.[text], ( [qs].[statement_start_offset] / 2 ) + 1, DATALENGTH([st].[text])) , ( ( CASE [qs].[statement_end_offset] WHEN -1 THEN DATALENGTH([st].[text]) ELSE [qs].[statement_end_offset] END - [qs].[statement_start_offset] / 2 ) + 1 ) AS query_text , * FROM sys.[dm_exec_query_stats] qs CROSS APPLY sys.[dm_exec_sql_text](( [plan_handle] )) st CROSS APPLY sys.[dm_exec_query_plan]([plan_handle]) qp ORDER BY [total_elapsed_time] / [execution_count] DESC
单独一句sql就是0~-1，否则会指定偏移位置
F
缓存大小管理
F
执行计划清除策略，对象计划存储清除策略，sql计划存储清除策略
F
在以后的service pack补丁包中，缓存压力的公式可能会改变
F
缓存存储：cachestore
sqlserver2000只有4GB 的plan cache上限
F
若添加特定plan 到plan cache，引起cachestore超过此限制，其他计划的删除就会发生在与添加新计划相同的线程中，这会引起新查询的反应时间增加
除了内存压力不只发生在内存总数达到特定限制时，sqlserver还表明内存压力发生在某cachestore中计划的数量超过此存储的哈希表大小的4倍时，而不管这些计划的实际大小是多少
下面的查询用于确定哈希表中对象存储和sql存储的存储单元数目，以及每个存储的条目数目
SELECT [type] AS 'plan cache store' , [buckets_count] FROM sys.[dm_os_memory_cache_hash_tables] WHERE [type] IN ( 'cachestore_objcp', 'cachestore_sqlcp' ) SELECT [type] , COUNT(*) AS total_entries FROM sys.[dm_os_memory_cache_entries] WHERE [type] IN ( 'cachestore_objcp', 'cachestore_sqlcp' ) GROUP BY [type]
F
全局内存压力适用于被所有缓存存储cachestore一起使用的内存，可以是外部的，也可以是内部的
sqlserver检测到内存压力时，索引零开销计划会从缓存中删除
f
MEMORY最多64个页面
没有内存压力时，开销不会减少，直到所有缓存计划的总大小达到缓冲池大小的50%
F
列出任何缓存条目的当前和原始开销，联合[memory_object_address]列，在sys.[dm_os_memory_cache_entries]中找到指定条目，此条目对应[sys].[dm_exec_cached_plans]中的特定计划
SELECT text , [objtype] , [refcounts] , [usecounts] , [size_in_bytes] , [disk_ios_count] , [context_switches_count] , [original_cost] , [current_cost] FROM sys.[dm_exec_cached_plans] p CROSS APPLY [sys].[dm_exec_sql_text]([plan_handle]) st JOIN sys.[dm_os_memory_cache_entries] e ON p.[memory_object_address] = e.[memory_object_address] WHERE [cacheobjtype] = 'compiled plan' AND [type] IN ( 'cachestore_sqlcp', 'cachestore_objcp' ) ORDER BY [objtype] DESC , [usecounts] DESC
F
元数据对象syscacheobjects实际上是一个伪表
在sql2000中，syscacheobjects假表还包括可执行计划的条目，也就是cacheobjtype列的值可能为Executable Plan，sql2005中，因为可执行计划作为独立对象，和已编译计划完全隔离存储，所以需要从
sys.[dm_exec_cached_plan_dependent_objects]()传入plan_handle作为参数
F
master数据库中创建的任何以sp_开头的对象可以从任何其他数据库中访问，不用完全限制对象名称
setopts：影响已编译计划的set选项设置，此列值的变化说明用户已经修改了set选项
sql：模块定义，或提交的批处理的前3900个字符
查询sys.[syscacheobjects]视图就知道，实际上sqlserver把resourcedb里的对象都缓存到内存，然后那些目录视图和兼容视图的架构都是从
resourcedb来的
--resourcedb的obejectname解释不了 SELECT CASE [dbid] WHEN 32767 THEN 'resourcedb' ELSE DB_NAME([dbid]) END AS dbname , OBJECT_NAME([objid]) AS objectname , * FROM sys.[syscacheobjects] ORDER BY [dbid]
CREATE VIEW sys.columns AS SELECT id AS object_id, name, colid AS column_id, xtype AS system_type_id, utype AS user_type_id, length AS max_length, prec AS precision, scale, convert(sysname, CollationPropertyFromId(collationid, 'name')) AS collation_name, sysconv(bit, 1 - (status & 1)) AS is_nullable, -- CPM_NOTNULL sysconv(bit, status & 2) AS is_ansi_padded, -- CPM_NOTRIM sysconv(bit, status & 8) AS is_rowguidcol, -- CPM_ROWGUIDCOL sysconv(bit, status & 4) AS is_identity, -- CPM_IDENTCOL sysconv(bit, status & 16) AS is_computed, -- CPM_COMPUTED sysconv(bit, status & 32) AS is_filestream, -- CPM_FILESTREAM sysconv(bit, status & 0x020000) AS is_replicated, -- CPM_REPLICAT sysconv(bit, status & 0x040000) AS is_non_sql_subscribed, -- CPM_NONSQSSUB sysconv(bit, status & 0x080000) AS is_merge_published, -- CPM_MERGEREPL sysconv(bit, status & 0x100000) AS is_dts_replicated, -- CPM_REPLDTS sysconv(bit, status & 2048) AS is_xml_document, -- CPM_XML_DOC xmlns AS xml_collection_id, dflt AS default_object_id, chk AS rule_object_id, sysconv(bit, status & 0x1000000) AS is_sparse, -- CPM_SPARSE sysconv(bit, status & 0x2000000) AS is_column_set -- CPM_SPARSECOLUMNSET FROM sys.syscolpars WHERE number = 0 AND has_access('CO', id) = 1
CREATE VIEW sys.system_objects$ AS SELECT name00f008 collate database_default AS name, id AS object_id, convert(int, null) AS principal_id, schid AS schema_id, sysconv(int, 0) AS parent_object_id, type, crdate AS create_date, crdate AS modify_date, sysconv(bit, 1) AS is_ms_shipped, sysconv(bit, 0) AS is_published, sysconv(bit, 0) AS is_schema_published FROM sys.sysobjrdb
F
-- Create a view to show most of the same information as SQL Server 2000's syscacheobjects -- This script has been updated for SQL Server 2012 to remove plans dealing with filetable and fulltext activities, -- and to also not return queries from system databases. -- by Kalen Delaney, 2012 -- Feel free to remove those filters from the WHERE clause at the bottom USE master GO IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'sp_cacheobjects') DROP VIEW sp_cacheobjects; GO CREATE VIEW sp_cacheobjects(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, refcounts, usecounts, pagesused, setopts, langid, date_first, dateformat, status, lasttime, maxexectime, avgexectime, lastreads, lastwrites, sqlbytes, sql) AS SELECT pvt.bucketid, CONVERT(nvarchar(19), pvt.cacheobjtype) as cacheobjtype, pvt.objtype, CONVERT(int, pvt.objectid)as object_id, CONVERT(smallint, pvt.dbid) as dbid, CONVERT(smallint, pvt.dbid_execute) as execute_dbid, CONVERT(smallint, pvt.user_id) as user_id, pvt.refcounts, pvt.usecounts, pvt.size_in_bytes / 8192 as size_in_bytes, CONVERT(int, pvt.set_options) as setopts, CONVERT(smallint, pvt.language_id) as langid, CONVERT(smallint, pvt.date_format) as date_format, CONVERT(int, pvt.status) as status, CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2), CONVERT(nvarchar(3900), fgs.text) FROM (SELECT ecp.*, epa.attribute, epa.value FROM sys.dm_exec_cached_plans ecp OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) as ecpa PIVOT (MAX(ecpa.value) for ecpa.attribute IN ([set_options],[objectid],[dbid], [dbid_execute],[user_id],[language_id],[date_format],[status])) as pvt OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs WHERE cacheobjtype like 'Compiled%' AND text NOT LIKE '%filetable%' AND text NOT LIKE '%fulltext%' AND pvt.dbid between 5 and 32766;
这个视图在sqlserver2012里有所改变，所以需要改一下
kalen_delaney的博客
http://sqlblog.com/blogs/kalen_delaney/archive/2013/07/31/sp-cacheobjects-for-sql-server-2012.aspx
F
缓存中的多计划
select * from orders
会有二义性
select * from dbo.orders --没有二义性
任何执行执行此查询的用户会一直引用相同的对象
sys.[syscacheobjects]的uid列说明生成此计划的那个连接用户ID，对于adhoc查询，只有另一个有相同用户ID值的连接可以使用相同的计划，如果uid=-2，这说明提交的查询不依赖隐式名称方案，并可在不同用户间共享，这是首选方法
注意：强烈推荐对象一直使用他们包含的模式名称限制dbo，这样就不会依赖于隐式名称方案，plan cache的重用会更有效
一般我们数据库都只会用一个用户来连接
SELECT CASE [dbid] WHEN 32767 THEN 'resourcedb' ELSE DB_NAME([dbid]) END AS dbname , OBJECT_NAME([objid]) AS objectname , USER_NAME(uid) AS 'user', * FROM sys.[syscacheobjects] ORDER BY [dbid]
开发一般都不会加dbo
F
查看等待的统计信息来检查plan cache的状态
SELECT * FROM SYS.[dm_os_wait_stats] ORDER BY [waiting_tasks_count] DESC
SYS.[dm_os_wait_stats]这个视图显示的值是累积的，因此若要查看特定时期内被等待的资源，必须在此时期的开始和结束时轮训此视图
三种等待类型查看plan cache问题
CMEMTHREAD
SOS_RESERVEDMEMBLOCKLIST
RESOURCE_SEMAPHORE_QUERY_COMPILE
F
CMEMTHREAD等待类型说明缓存描述符分配的内存对象上有冲突，plan cache中很可能插入记录，这会引起冲突问题，sql2005 sp2，缓存描述符分配的内存对象已经被所有cpu中划分
SOS_RESERVEDMEMBLOCKLIST等待类型说明某些查询的缓存计划的出现，这些查询有大量的参数，例如in从句中有太多参数，这要求sqlserver使用大的单元来分配，称为多页分配
可以查看 sys.[dm_os_memory_cache_counters]视图来看多页单元分配的内存数量
SELECT [name] , [type] , [single_pages_kb] , [multi_pages_kb] , [single_pages_in_use_kb] , [multi_pages_in_use_kb] FROM sys.[dm_os_memory_cache_counters] WHERE [type] = 'cachestore_sqlcp' OR [type] = 'cachestore_objcp'
sql2005的sp2降低了SOS_RESERVEDMEMBLOCKLIST上的等待，创建一张in列表值的表来代替in参数会有改善
RESOURCE_SEMAPHORE_QUERY_COMPILE等待说明当前编译的数目很大，为了防止查询内存的低效，sqlserver2005限制了需要额外内存的并发编译操作的数目
若看到RESOURCE_SEMAPHORE_QUERY_COMPILE等待值很高，可以使用sys.[dm_exec_cached_plans]视图检查plan cache中的条目，
没有objtype值为prepared的结果，说明sqlserver没有自动参数化查询，这种情况可以使用ALTER DATABASE [sss] SET PARAMETERIZATION FORCED或者plan guide
SELECT [usecounts] , [cacheobjtype] , [objtype] , [bucketid] , [text] FROM sys.[dm_exec_cached_plans] CROSS APPLY sys.[dm_exec_sql_text]([plan_handle]) WHERE [cacheobjtype] = 'compiled plan' ORDER BY [objtype]
缓存在每个批处理级别完成
f
预定义查询实际上指定了参数的数据类型，因此很容易保证一直使用相同的类型
C#代码
http://book.51cto.com/art/201008/219123.htm
PreparedStatement所包含的方法与Statement一样，只不过增加了动态参数的概念，如下： String employeeInsertSql = "insert into employee (name,age) values(?,?)"; 其中？表示的是需要PreparedStatement向SQL语句注入的参数，按照？出现的顺序（左-右），分别编号为1，2….. PreparedStatment ps = connection.prepareStatement (employeeInsertSql); for(int i=1;i<10;i++){ ps.setString(1,"emp_"+i);//1 表示的是第一个问 号的位置,setString表示赋予字符串 //类型的数据 ps.setInt(2,i);//2表示的是第二个问号的位 置，setInt表示赋予整数类型的数据 ps.execute(); //执行SQL过程 }
值8.4的数据类型是为精度（2，1），值8.44的数据类型为精度（3，2），对于varchar数据类型，服务器端参数化不依赖实际值的长度，总是varchar（8000）
F
监视plan cache和data cache
性能计数器
SQLSERVER:Plan Cache\Cache Pages(_Total)
SQLSERVER:BufferManager\Database Pages
系统监视器perfmon和sql trance监视编译和重编译
SQL Recompilations/Sec
Batch Requests/Sec
trace
SP:Recompile（存储过程分类）/SQL:StmtRecompile（TSQL分类）
F
如之前所讨论，临时表的重编译阀值比普通表的低
F
防止重编译的方法
keepfixed plan
表加上对象名称，例如dbo
检查存储过程创建时是否使用了WITH RECOMPILE
第一种会使整个存储过程的所有语句重编译
CREATE PROCEDURE usp_abc WITH RECOMPILE AS SELECT 1
第二种只会在指定重编译的语句上重编译
CREATE PROCEDURE usp_abc AS SELECT 1 SELECT 1 OPTION(RECOMPILE)
F
如果批处理包含变量声明和赋值，赋值实际上不会在编译阶段发生，下面批处理被优化时，sqlserver没有指定变量的值！！
F
sql2005支持3种plan guide：sql，对象，模版
plan guide的信息会保存在元数据里面那么将数据库分离出来附加到另一个sqlserver实例上，plan guide信息不会丢失
F
对象计划向导
SQL计划向导
对于CLR对象或扩展的存储过程，或用exec（sql_string）结构调用的动态SQL部分，要使用SQL计划向导
F
DBCC FREEPROCCACHE为什么叫PROCCACHE而不叫SQLCACHE，因为在sqlserver2000之前，
DBCC FREEPROCCACHE 只能情况存储过程的执行计划，而不能清空独立sql adhoc的执行计划！！
对象和SQL计划向导的缺点：如果整个批处理是计划向导里的SQL语句，计划向导可以使用，否则计划向导里的SQL语句只是批处理的一部分，计划向导不会使用
模版计划向导
F
模版计划向导：
1、先调用sp_get_query_template存储过程
2、调用sp_create_plan_guide存储过程
F
管理计划向导 plan guide
sp_control_plan_guide '<control_option>'[,'<plan_guide_name>']
control_option有6个值：disable，disable all，enable，enable all，drop，drop all
drop all，enable all，disable all应用于当前数据库中的所有计划向导
sp_control_plan_guide (Transact-SQL)
--http://msdn.microsoft.com/zh-cn/library/ms188733.aspx
删除、启用或禁用计划指南。
--Disable the plan guide. EXEC sp_control_plan_guide N'DISABLE', N'Guide3'; GO --Enable the plan guide. EXEC sp_control_plan_guide N'ENABLE', N'Guide3'; GO --Drop the plan guide. EXEC sp_control_plan_guide N'DROP', N'Guide3'; USE AdventureWorks2012; GO EXEC sp_control_plan_guide N'DISABLE ALL';
SELECT * FROM sys.[plan_guides]
F
xml执行计划显示中
在XML计划中
PlanGuideDB
PlanGuideName
如果计划向导是临时的
TemplatePlanguideDB
TemplatePlanGuideName
若在数据库中有任何的计划向导，sqlserver会首先检查语句是否匹配SQL计划向导和对象计划向导，sql会被切碎
F
若存在这种计划向导，并匹配成功，语句文本被修改为包含计划向导中指定的查询提示，然后语句被使用指定提示编译
F
创建临时计划向导
F
在XML计划中 <ParameterList> <ColumnReference Column="@0" ParameterCompileValue="4" ><ParameterRuntimeValue="5"/> </ParameterList>
F
第六章 并发问题
F
解决并发问题的新DMV
SELECT * FROM sys.[dm_os_waiting_tasks]
SELECT * FROM sys.[dm_os_wait_stats]
加强的SQLDiag.exe
新的1222跟踪标志
F
F
F
当session中的锁超时时，sqlserver只是退出当前程序语句，并没有终止事务，因此需要在tsql代码上使用try/catch模块来捕获1222错误，若发生超时，可能需要回滚事务
F
sql trace
lock：escalation事件类观察锁升级
给定的时间点探测表锁
SELECT [request_session_id] , [resource_type] , DB_NAME([resource_database_id]) AS databasename , OBJECT_NAME([resource_associated_entity_id]) AS tablename , [request_mode] , [request_type] , [request_status] FROM sys.[dm_tran_locks] AS tl JOIN sys.[all_objects] AS ao ON [tl].[resource_associated_entity_id] = ao.[object_id] WHERE [resource_type] = 'lock' AND [request_status] = 'grant' AND [request_mode] IN ( 'x', 's' ) AND [ao].[type] = 'u' AND [resource_type] = 'object' AND [tl].[resource_database_id] = DB_ID()
F
锁数量到达5000升级表锁
跟踪标志1211和1224 禁用锁升级
F
allow_row_locks 和allow_page_locks默认为ON
阻塞是一种等待类型，还有其他等待类型，例如等待CPU,I/O,内存
F
F
sqlserver：general statistics提供了阻塞进程数目
sqlserver：wait statistics 锁等待
F
F
SELECT * FROM sys.[dm_os_waiting_tasks]
session_id为负数时，有3个可能的代码
-2 被阻塞资源属于孤立分布式事务
-3 被阻塞资源属于延迟恢复事务
-4 对于锁存器等待，内锁存器状态转换阻止了session id的识别
SELECT * FROM sys.[dm_os_waiting_tasks] WHERE [wait_duration_ms] > 5000
下面查询显示5秒以上的等待
F
sys.[dm_os_waiting_tasks]显示的阻塞不一定与锁有关，可能和I/O或内存有关，要看锁阻塞，可以使用
sys.[dm_tran_locks]
SELECT * FROM sys.[dm_tran_locks] AS tl WHERE [tl].[request_status]='wait' ORDER BY [resource_database_id],[request_session_id]
F
F
SELECT [tl].[resource_type] , DB_NAME([tl].[resource_database_id]) AS dbname , CASE [tl].[resource_type] WHEN 'object' THEN OBJECT_NAME([tl].[resource_associated_entity_id], [tl].[resource_database_id]) WHEN 'database' THEN 'database' ELSE CASE WHEN [tl].[resource_database_id] = DB_ID() THEN ( SELECT OBJECT_NAME([object_id], [tl].[resource_database_id]) FROM sys.[partitions] WHERE [hobt_id] = [tl].[resource_associated_entity_id] ) ELSE NULL END END AS objectname , [tl].[resource_description] , [tl].[request_session_id] , [tl].[request_mode] , [tl].[request_status] FROM sys.[dm_tran_locks] AS tl JOIN sys.[dm_tran_locks] AS t2 ON [tl].[resource_associated_entity_id] = [t2].[resource_associated_entity_id] WHERE [tl].[request_status] <> [t2].[request_status] AND ( [tl].[resource_description] = [t2].[resource_description] ) OR ( [tl].[resource_description] IS NULL ) AND [t2].[resource_description] IS NULL ORDER BY [tl].[resource_database_id] , [tl].[resource_associated_entity_id] , [tl].[request_status]
通过sys.partitions目录视图来查找，之前的版本对resource_associated_entity_id进行解码
通过sp_configure设置阻塞超时
EXEC [sys].[sp_configure] @configname = 'show advanced ', -- varchar(35) @configvalue = 1 -- int RECONFIGURE WITH OVERRIDE EXEC [sys].[sp_configure] @configname = 'blocked process threshold (s)', -- varchar(35) @configvalue = 60 -- int RECONFIGURE WITH OVERRIDE
F
F
SQLDiag功能
SQLNexus工具
Microsoft PSS PerfStats脚本收集阻塞信息
F
F
查看blocking_session_id是否为非空非负数，有3个负值
杀死session会导致回滚事务，直到回滚完成锁不会被释放，杀死上下文中的spid，直到回滚完成阻塞不会解除
F
F
在read uncommited的隔离级别下使用 nolock提示会遇到601错误，could not continue scan with nolock due to data movement
F
读写分离
F
sqlserver的死锁由两个互相阻塞的线程组成，他们互相等待对方完成，这些相互的阻塞通常被称作环
F
产生死锁的资源类型包括
锁：数据库对象锁，包括数据行，页，表，索引键
worker：包括调度程序和CLR同步对象
内存：两个线程都需要更多的内存，但获得授权前一个必须等待另一个
同一个查询内的并行线程
多结果集资源：MARS线程内部的冲突
目前最常见的死锁是基于锁的，其他类型的死锁都非常特殊，并不常见
与并行相关的死锁在sqlserver内部解决，查询就会完成
F
F
F
级联死锁：在sql2000中，有时可以在1204跟踪输出看到死锁涉及到两个以上的进程。在这种情况下，sqlserver会选择一个牺牲者，去掉一个死锁，但剩下的进程可能还是死锁，最后sqlserver会一个一个选择牺牲者，直到去掉死锁，sqlserver2005增加了1223跟踪标志显示级联死锁
DBCC TRACEON(1223,-1)
F
检测死锁
性能监视器
sql trace 死锁事件类
DBCC TRACEON(3605,1222,-1)
3605 把死锁信息写在sql errorlog里
F
F
F
跟踪标志1222的输出
F
解决死锁
F
F
使用try catch来捕获死锁错误1205，并重试
DECLARE @Tries TINYINT DECLARE @error INT SET @Tries = 1 WHILE @Tries <= 3 BEGIN BEGIN TRAN BEGIN TRY --<code goes here> IF XACT_STATE() = 1 COMMIT BREAK END TRY BEGIN CATCH SET @error = ERROR_NUMBER() IF @error = 1205 BEGIN IF XACT_STATE() = -1 ROLLBACK END SET @Tries = @Tries + 1 CONTINUE END CATCH END
一个任务回滚时，他不能被选为死锁牺牲者
F
F
尽量不要提高事务隔离级别，最好使用锁提示updlock，holdlock
F
F
F
F
F
不会被快照隔离级别删除的其他种类的共享锁包括：由索引视图引用多于一张表而造成的共享锁
F
F
F
快照隔离级别下的触发器需要注意的地方
带有output子句的触发器
DELETE TOP ( 5000 ) FROM [aa] OUTPUT [Deleted].[NAME] INTO [#aa](name) WHERE id > 9
如果和触发器一起使用output自己，触发器中的select语句就会恢复到基于锁的readcommited
没有output子句，触发器里的select语句就会读取行版本
F
F
添加readcommitedlock提示重新定义触发器的select语句 USE [sss] SELECT * FROM [testrow] WITH (READCOMMITTEDLOCK)
潜在snapshot隔离级别冲突
DDL冲突
更新冲突
F
F
F
更新丢失的解决办法
不要提高事务隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
使用updlock锁提示
F
F
F
实际上，READ COMMITTED，READ UNCOMMITTED，READ_COMMITTED_SNAPSHOT都允许丢失更新，这也包括已提交读快照（RCSI）
如果把隔离级别改为REPEATABLE READ或SERIALIZABLE，就会发生冲突死锁，可以使用UPDLOCK锁提示来解决这个死锁问题
解决更新丢失的办法：
1、在默认隔离级别下（READ COMMITTED），使用with（UPDLOCK）
2、使用快照隔离级别，注意是快照隔离级别，不是已提交读快照隔离级别
使用重试事务的方法会温柔一些
F
F
一旦不再需要旧存储单元的行版本时，后台线程会删除这些存储单元，这些单元不是统一大小的，而且根据活动级别，一个新单元大概被每分钟创建一次
F
更新冲突计数器：查看潜在的更新丢失
SELECT * FROM sys.[dm_tran_top_version_generators]
SELECT * FROM [sys].[dm_tran_version_store] SELECT * FROM sys.[dm_tran_active_snapshot_database_transactions] SELECT * FROM sys.[dm_tran_current_transaction] SELECT * FROM sys.[dm_tran_transactions_snapshot]
F
监视tempdb
检查tempdb空闲空间的SQL语句
USE tempdb; GO SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage;
f
版本清理速度跟得上版本创建速度
查看当前的活动事务导致版本存储不能清理的原因
SELECT * FROM sys.[dm_tran_active_snapshot_database_transactions]
写入tempdb时使用异步I/O
f
孤儿行：在主表里没有该行，但是在子表里有该行，子表里的行就称为孤儿行，因为他没有父亲，即主表没有那行数据
f
snapshot比已提交读snapshot具有更强的一致性
f
快照隔离级别不能删除所有共享锁，共享锁依然被使用在外键关联的表和涉及多个表时某些索引视图的操作
f
快照隔离级别也会发生死锁
f
