SQL SERVER-高级性能
--高级性能
--虽然可以对数据库进行调整和优化，但是对数据库性能影响最大的却时数据库的设计和开发，而且这种影响从项目的第
--天就开始了。通常，所谓的性能优化实际上就是重新开发数据库系统中设计的很糟的那一部分
--优化准则
--对于作为数据库应用系统的所有者和用户的企业或者组织来说，应当明白良好的数据库设计和精心编写的代码的价值和重要性
--花费尽可能多的努力来设计数据库模式，因为所有的优化都要基于数据库模式
--集中精力优化运行最频繁的代码，而不是那些运行最慢的代码
--在升级硬件之前进行优化。即使在速度快的服务器上，坏代码仍旧时坏代码
--列出所有可能的优化思路，即使你没有时间在现在去实施它们
--新特性会与优化竞争资源；因此，最好的办法是开发一个维持现有功能，但提高了性能的版本
--优化是一个研究与探索的过程，你很难对它做出预测。所以，最好不要对优化的效果或交付日期做出承诺
--以索引的形式列出系统中那些易于修改和优化的部分
--在兼容性测试时有可能需要使用一台与用于生产的服务器相同的服务器，但在开发和测试时可以使用型号较老且速度较慢的
--服务器，这并不会掩盖性能问题
--集中精力修改应用程序中性能最坏的部分
--花费一些时间作为用户使用应用程序
--总是保留一些开发时间(如：总开发时间的25%)以便进行案例测试
--度量准确性
--测试准确性的过程包含了一系列预测与解释。实际上，使用测试案例是证明应用程序准确性的唯一有效方法。
--案例测试时通过将查询的实际结果与预计的结果相比较来度量数据库的正确性。
--对于所有数据库来说，案例测试都是最为重要的测试。如果数据库没有经过仔细的案例测试，就不要交付它，或者说将它
--作为生产数据库来运行
--度量响应时间
--脚本测试
--综合性的响应时间测试是通过在数据库上运行脚本程序来完成的。在脚本程序中要执行一系列的查询或者存储过程调用
--以便对整个数据库进行检查。其中的select和update语句的比例以及受到影响的表的分布情况应当能够代表前端应用
--程序和批处理过程的实际情况
--负载测试
--负载测试用于评价数据库的可伸缩性，其方法是分别度量系统负载达到预计负载的一半以及满负载的时候系统的响应时间
--数据负载测试
--用户负载测试
--监控SQL Server
--性能监视器：“控制面板”->“管理工具”->“性能”->“+”添加
--性能监视器主要用于确定硬件是否能够支持当前的负载。也可以使用它来查找特定的SQL Server问题
--SQL事件探查器：“开始”->“程序”->“Microsoft SQL Server”->“事件探查器”
--开发性能良好的数据库
--决定数据库是否具有良好的性能的因素：
--数据库驱动的、规范化的数据库物理模式设计
--完善的和平衡的索引策略
--使用基于集合的查询方式编码，并避免以过程化(基于行的方式)来操作数据
--出色地使用数据库约束和触发器来实施业务规则
--精心地设计表、索引和代码以避免锁争用
--在升级硬件之前进行数据库优化是一种好的开发方法和习惯。通过优化设计和代码来提高性能的方法要远远胜过通过购
--买新硬件来提高性能的方法
--数据库设计与性能
--高性能的数据库模式需要满足以下设计规范：
--将数据库规范化到第三范式，随后精心地实施使用高性能的、单列主键的物理设计
--不要过度地规范化数据库，或者说使数据库过度复杂化，而应当坚持不懈地努力直至找到简单而优雅的数据库设计为止
--避免要以事务的方式在表和表之间来回倒数据的数据库设计
--使用数据驱动的数据库设计风格，不要使用静态编码的值来进行设计
--如果要使用代码来创建多个临时表或者额外的工作表，那就说明数据库的设计是不充分的
--在设计数据库模式的时候，必须考虑那些基于它的查询
--必要的时候，要将数据从OLTP表复制到非规范化的、只读的表中去，以便加快数据库的读速度
--约束和触发器
--实施规则和触发器时要遵循的要点：
--要在数据库级实施规则，以便能够快速地执行这些规则，并保证在任何情况下都无法避开这些规则的检查
--用数据库约束来实施数据库规则和业务规则，对于那些无法用数据库约束来实施的规则，再使用触发器来实施
--必须使用触发器处理多行的操作，在完成这项工作时，这些触发器必须使用基于集合的DML语句，而不要使用游标
--查询设计和性能
--一个人可以对数据库做的最坏的事情就是在不是绝对必要的情况下使用了游标。为了达到避免使用游标或者说循环的目标，
--并使用基于集合的查询来完成数据库的工作，应当遵循以下规则：
--总是指定表的所有者，以便缓存查询执行计划
--只能用视图支持复杂的用户查询，除此之外从不在代码中使用视图
--使用子查询将庞大而复杂的查询分解为多个更小的逻辑单元
--查询优化
--查询是一种描述性的语言，这意味着在SQL查询中只需要描述问题，而SQL Server将会确定如何来执行这个查询
--SQL server的查询优化器将会考察解决查询各个部分(如where条件、连接和函数)的多种可能的方法。通过估算每个操作
--的每项逻辑开销，并考虑可用的索引、硬件的限制和数据的统计信息，查询优化器将会计算出最快的、可行的查询执
--行计划
--这意味着多数的优化工作是由SQL Server而不是查询本身来完成的
--优化查询的工作在很大的程度是通过提供合适的索引，以便查询优化器可以使用快速的索引查找来代替速度慢的表扫描来
--实现的
--查询执行计划
--查询分析器显示查询执行计划：“查询”->“显示预计的执行计划”->可以查看预计的查询计划；“查询”->“显示查
--询执行计划”->运行查询之后，将可以查看实际的执行计划
--索引查找与嵌套循环
--当可以使用索引的时候，SQL Server获取一行时可以使用的最快方法是进行索引查找，即从索引根节点开始快速地
--遍历B树索引，通过中间节点到达叶子节点，并最终到达要获取的行
--索引扫描
--在可以使用索引，但要查找的数据列的顺序与索引中的索引列的顺序不一致的情况下，SQL Server由可能会选择进行
--索引扫描。虽然索引扫描要优于表扫描，但与索引查找相比，它却慢了很多。索引扫描说明索引中的索引列的顺序
--并不是最佳的。
--表扫描与散列
--表扫描操作的成本取决于表的大小，它可能会非常的高。可以导致表扫描的情况：
--如果没有索引可以使用，查询优化器就别无选择，只能扫描表中的每一行
--如果SQL Server根据数据的统计信息，或者因为表很小而确定索引是没有用的，查询优化器就会使用表扫描
--筛选与排序
--在一些情况下，SQL Server会从表中取出所有的数据，然后通过筛选操作来选取正确的行，并利用排序操作完成
--排序。筛选和排序都时较慢的操作。如果SQL Server使用了它们，就说明缺乏有用的索引
--可优化的SARG
--查询优化可用性的一个重要概念是与where条件的使用相关的。如果SQL Server可以使用索引优化where条件，该
--条件就会被称为搜索参数(SARG)。但并不是每一个条件都是可以作为SARG搜索参数的。例如：
--使用AND连接到一起的多个条件是SARG，但使用OR连接在一起的多个条件就不是SARG
--否定的搜索条件(<>、!>、<!、Not Exists、Not In、Not Like)是不可优化的。因为证明一行是存在的很容易
--可是如果要证明一行是不存在的，就需要检查每一行
--由通配符开始的条件是不能使用索引的
--使用了表达式的条件也不能使用索引，SQL Server需要将表达式按照代数运算进行分解，以便获取到有效的输入数据
--如果where子句包含了函数(如字符串函数)，就需要使用表扫描，以便可以使用函数来测试每个行中的数据
--度量查询性能
--除了查询分析器图形化的查询执行计划以外，SQL Server还提供其他几个查询性能指示器
--statistics io：报告对于查询中包含的每个表所进行的I/O活动信息，包含扫描的行数和读取的次数
set statistics io on
go
use obxkites
select lastname + ' ' + firstname as customer,product.productname,product.code
from dbo.contact join dbo.[order] on contact.contactid = [order].contactid
join dbo.orderdetail on [order].orderid = orderdetail.orderid
join dbo.product on orderdetail.productid = product.productid
where product.code = '1002'
order by lastname,firstname
go
set statistics io off
--statics time：报告关于CPU和查询的全部执行时间以及作为查询的结果而运行的其他系统过程的信息
set statistics time on
go
use obxkites
select lastname + ' ' + firstname as customer
from dbo.contact order by lastname,firstname
go
set statistics time off
--showplan_all：可以以文本的形式报告查询执行计划，它可以提供很多的细节信息
set showplan_all on
go
use obxkites
select lastname from dbo.contact
go
set showplan_all off
--均衡的索引策略
--索引基础
--SQL Server使用聚集和非聚集索引，它们都可以用来创建独立索引或者主键索引
--聚集索引将数据页和索引的叶节点合并起来，这样，数据就会按照与索引同样的顺序进行存储。对于表来说，只能采用
--一种物理的排序顺序来进行存储。所以一个表只能有一个聚集索引
--非聚集索引是B树索引，它从根节点开始，通过中间节点到达叶节点，然后指向数据行。一个表最多可以有249个非聚集索引
--如果存在有聚集索引，非聚集索引的叶节点就会指向聚集索引，此时，聚集索引的索引列就好像是被添加到了非聚集索引
--中去了一样。例如：如果一个表在col1上具有聚集索引，那么col3和col4上的非聚集索引，实际上是一个建立在col3、
--col3和col1上的索引
--主键在声明时可以定义为聚集或者非聚集的索引
--索引可以提高读性能，但会降低写性能
--基础索引
--甚至在调优之前，就可以方便地确定几个索引的位置。这些基础索引时创建稳固的索引集合的第一步。创建这些基础索引
--时的注意事项：
--1、为每一个主键创建非聚集索引。这是因为主键通常用于单行检索；当把具有一对多联系的两个表进行连接时，通常也
--是把作为一的一方的表的主键与作为多的一方的表的外部键组成的连接条件进行连接，所以使用非聚集索引是最佳选择
--2、为每个表创建一个聚集索引。对于主表，应当在那些最常用来排序的列上建立聚集索引。但应当注意不要在主键上
--建立聚集索引。对于从表，应当为最重要的外部键创建聚集索引
--3、除了在第二步中已经为其创建了索引的外部键以外，对于每个外部键中的那些列创建非聚集索引
--4、对于where子句或者order by子句中的所引用的每个列创建单列索引
--索引调优
--索引调优一半是科学，一半是艺术。在调优索引的时候，有可能需要考虑几种策略。每种策略的目标都是一样——减少每个
--查询所需要的物理数据页读取次数
--1、使用聚集索引
--聚集索引是与数据页合并在一起的，所以它可以通过跳过索引的叶节点来节省一次逻辑读操作；虽然如此，聚集索引的
--真正用途却在于加快从磁盘物理读取数据的速度。可以说，聚集索引不仅是最重要的索引，也是需要确定的最为复杂
--的索引
--每个表都是按照一定的物理顺序存放在磁盘上的。如果没有索引索引，表中的数据就会按照其输入的顺序来存储
--使用聚集索引来选取特定范围内的行要比使用非聚集索引快
--聚集索引按照索引的顺序来存储数据，在检索几行具有类似的聚集列值的记录时所需要的数据页读操作，要比使用非聚
--集索引完成同样的查询所需要的少得多
--因为聚集索引的主要优点是它可以将类似的数据集中地存放在一起，并且可以加快检索特定范围内数据的查询速度，所以
--在一对多联系中多的一方的外部键(例如orderdetail表中的orderid列)上建立聚集索引就非常合适了。这样，当选定
--了一个订单的时候，所有与之相关的订单细节行都已经集中存储在单个数据页上了
--主表可以根据使用频率最高的范围查询(例如帐号或者地区)来创建聚集索引，使数据分组集中存放。依赖于前端应用程序
--编写的方式，为那些最常被数据视图网格用来排序的列创建聚集索引，将有可能提高前端应用程序的性能
--2、复合索引
--在对个列上创建的索引称为复合索引。创建有用的复合索引的关键在于按照最合适的顺序来排列索引列
--如果索引包含了三个列，那么当以col1、col1和col2、或者col1、col2和col3为条件进行查询时，可以较好的利用索引
--但是，当单独以col3为条件进行查询时，就不能很好地利用索引了
--在规划复合索引的时候，应当注意表上是否建有聚集索引，如果是这样，所有的非聚集索引都会是包含了聚集索引列的复
--合索引
--3、覆盖索引
--使用复合索引的一种好方法是：通过在复合索引中包含所有需要的列来消除数据页读操作。采用这种方式，复合索引会覆
--盖整个查询，这样就可以直接从索引中获取数据，而不必去读取任何的数据页。这项技术的速度非常快；它能够通过设
--计少数几个覆盖索引来提高多个查询的性能
--如果创建了一个同时包含了多对多连接表两个外部键的覆盖索引，那么SQL Server在通过连接表将一个主表与另一个主表
--进行连接时候就不必读取连接表的数据页了
--4、索引的选择性
--索引的另一个方面时索引的选择性。具有很强选择性的索引具有较多的索引值，而且每个索引值对应的数据行较少。显然，
--主键或者唯一索引具有最高的选择性。
--如果索引只包含少数几个索引值，而且这些值是分别布在一个大表之中的，这样的索引就缺乏选择性。缺乏选择性的索引
--甚至都无法起到索引的作用。如果一个列只包含了三个分散在整个表中的值，那它就不能作为一个好的索引列。bit列
--的选择性就很低，因而用不着对其进行索引。
--SQL Server使用内部的索引统计信息来跟踪索引的选择性。DBCC Show_Statistics命令可以报告上一次更新索引统计信
--息的日期，以及索引统计信息中的一些基本内容，包括索引的有用性。如果索引的列密度较低，说明索引的选择性很高
--。反之，如果索引的列密度较高，则说明一个给定的索引节点会指向表中的多个行，因而索引就不是很有用。
use cha2
DBCC show_statistics (customer,ixcustomername)
--5、索引的填充因子和填充方法
--在将数据写入每个数据页和索引页的时候，必须将其插入到已有的页中，除非是将数据添加到索引的尾部。用于插入操作
--的自由空间的数量是由索引的填充因子来设置的。如果将它设置为1或者100，将会保留两行的空间；而其他的设置值
--则指定了可以用数据填充空间占整个页面空间的百分比
--填充因子会对性能产生影响。如果填充因子太小，就需要使用更多的页来保存数据。而更多页就意味着每个查询要进行的
--物理数据页读操作的数量也变得更多了；此时，读和写操作的性能都会降低。
--如果页填满了，又需要插入一行数据，SQL Server就会将该页分割为两个页，每个页上都只使用了50%的空间。页面分割
--的代价是很高的，所以应当尽量避免页面分割。将填充因子设得过高，就会导致页面分割，并降低写操作的性能
--最好的填充因子是基于预期的在两次索引重建之间会发生的数据修改的百分比，计算时应当对每个表分别加以估算。如果
--在一个具有100 000行表中发现增加了4 000个新行，并对索引的列进行了6 000次更新，就说明表上的写操作所占的比
--例大约为10%。此时，就可以基于这个写操作的比例，再加上一些余量(5%？)来计算填充因子了。按照这种方法，在上
--面的情况下，就应当将填充因子设置为85%
--聚集索引的填充因子将会影响到数据页。对于非聚集索引，填充因子会影响到索引的叶节点，但不会影响到任何中间节点
--。如果要将填充因子应用到索引的中间节点，就需要将padindex选项设置为true
--使用索引优化向导
--“向导”->“管理”->“索引优化向导”->
--选择服务器和数据库、是否保留现有索引、可以选择添加索引视图->优化模式
--快速：对索引进行快速检查。对于聚集索引、索引视图不推荐使用这个选项；在需要删除已有索引的时候，不能使用它
--适中：默认值；对于聚集索引和索引视图建议使用这个选项，在需要删除已有索引的时候，可以使用它
--彻底：对工作负荷进行最为详细地分析。如果要为包含许多查询的巨大工作负荷生成一组有效的索引，这个选项就非常有用了
--“下一步”：选择一组用于分析的查询
--“下一步”：选择要使用该向导调整的表
--“下一步”：分析，给出的建议是一个索引的清单
--“下一步”：完成
--加锁和性能
--当数据库中发生锁争用的时候，就会出现严重的问题。依赖于等待释放锁的时间、锁超时设置以及应用程序处理超时的方式
--，事务有可能会以用户无法觉察的方式等待加锁。要识别锁定问题，可以跟踪lock waits per sencond性能监视器计数
--器，或者在SQL事件查看器中跟踪lock:cancel或者lock:timeout事件
--由于聚集索引会将相似的行集中存放在同一个页上，因而也会对加锁产生影响。如果事务要将行锁升级为表锁，并且持有锁
--而不释放，就会产生热点
--要减轻锁定问题的严重程度，可以做以下工作：
--检查事务的隔离级别，并确保它没有高于所需要的级别
--确保快速地启动和提交事务。重新设计所有的包含游标的事务
--如果两个程序有可能会死锁，应当确保它们按照同样的顺序对资源进行锁定
--确保客户应用程序在获取数据之后立即释放所有的锁
--检查聚集索引，并修复所有的热点问题
--确保所有可以使用(nolock)提示的select语句都使用了该提示
--考虑使用(rowlock)选项强制使用行级锁来锁定页，从而防止锁升级
