笔记58-徐 调整数据库设计来优化语句性能

笔记58-徐  调整数据库设计来优化语句性能

 要慎重选择索引的第一个字段，最好选择一个重复记录最少的字段。这是因为-》索引上的统计信息只保存索引第一个字段/索引前导列的数据直方图，《SQL Server2012实施与管理实战》

（直方图是索引中键值的抽样图，帮助sqlserver选择哪个最合适的索引）
https://www.cnblogs.com/lyhabc/articles/3914213.html

select * from tb where cola=xx and colb=xx and colc=xx
create index ixa(cola,colc)
create index ixb(colb,colc)
数据直方图可以协助sqlserver判断到底是使用ixa还是使用ixb
假如cola字段的重复值很多，colb字段的重复值很少，那么sqlserver可以通过直方图选择使用索引ixb

唯一索引没有直方图，因为唯一索引没有重复值

--调整数据库设计来优化语句性能 P436  2013-2-9

--当一个语句出现性能问题，而且已经排除了系统资源瓶颈、阻塞与死锁
--物理I/O、编译与重编译、参数嗅探这些因素以后，那所能做的，要不
--就是调整数据库设计，提高语句性能，要不就是修改语句本身，以达到
--更高效率

--调整数据库设计基本是两个思路：调整索引，或者使用 Plan Guide
--这两个方法都不用修改语句本身


----------------------------调整索引---------------------------------
--索引设计的优劣很大程度上决定了数据库的性能，也决定了一套系统的最大能力。
--运气比较好的时候，一套数据库系统性能本来很差，可加一两个索引后就会有
--很大的改善。所以做语句调优，调整索引是必备的功课

--什么样的索引才是好的索引呢？这个问题已经被广泛地讨论。


--1、聚集索引
--聚集索引是一个表格里最宝贵的资源。一张表格只能有一个聚集索引，他决定
--了表格里所有记录排列的顺序。如果一张表经常用某种顺序检索数据，那按照
--这个顺序建立聚集索引无疑是最理想的

--为了提高可检索性，聚集索引最好要建立在重复值很少的字段上。但是并不是
--说，聚集索引的字段就不可以有重复值。选择什么样的字段，值重复不重复是
--一个标准，表格的常见检索方式也是一个重要的标准。例如一个Employee表格
--里，有姓名字段，也有工号字段。一般来讲，姓名可能偶尔会有重复，但是
--工号不会重复。如果我们对Employee表的检索，通常只用姓名，很少使用工号
--那就应该选姓名作为聚集索引。如果检索经常使用的是工号，不是姓名，就
--应该用工号作为聚集索引


--2、非聚集索引
--一个表格有多种检索方式。例如Employee表，不但会按照姓名、工号，还会按照
--年龄段、性别、职务等方面来检索。一个聚集索引是无法覆盖所有要求的，需要
--有非聚集索引来辅助。在一张表格上，可以有多个非聚集索引。

--建立非聚集索引的字段，不用一定要很少不重复。像性别、职务这样的字段，肯定
--会有很多重复记录，但是为了提高检索性能，也可以在上面建立非聚集索引。但是
--非聚集索引里面没有存储记录的所有信息。如果查询要使用的字段没有完全包含在
--非聚集索引里，那SQL就要做Bookmark lookup。这会有额外的开销


--3、Covering Index（复合索引/涵盖索引）
--一个索引可以建立在一组字段上。这样的好处有：
--（1）查询的检索条件，本身是一组字段的组合。直接在上面建索引，检索更快
--（2）如果查询用到的字段索引都能够覆盖，那就不用做书签查找，能节省时间

--但是设计Covering Index的时候需要注意的是：
--（1）要慎重选择索引的第一个字段，最好选择一个重复记录最少的字段。这是因为
--索引上的统计信息只保存第一个字段的数据直方图。如果选一个重复数据很多的字段
--这个索引的可选度就比较低了，会影响索引的价值


--（2）SQL会按照索引里字段的组合值，建立B-树。字段越多，排序的代价就越高，
--维护这个索引的代价也就越高。所以选取字段的时候要有合理的要求，不能随便
--什么字段都往上放。一般不要把长度很长的字段放在索引字段里

--4、Index with Included Columns（具有包含性列的索引）
--有些查询语句根据一个字段的条件，查询另一个字段的值。例如：返回
--所有职务是“Marketing Manager”的人生日

USE [AdventureWorks]
GO
DBCC freeproccache
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS PROFILE ON
GO
SELECT [BirthDate] FROM [HumanResources].[Employee]
WHERE [Title]='Marketing Manager'

--如果建立一个covering index在（Title+[BirthDate]）上，数据会按照（Title+[BirthDate]）
--的方式排序。其实不需要这种排序，我只需要按Title的排序。在[BirthDate]上
--的排序是多做的。在SQL2005以后，就可以建立一个有包含性列（Included Columns）的索引

--老张"数据库命名规则"
--索引(聚集) ic icAuthorFullName
--索引(非聚集) in inClientStateCity
--索引
--idx+字段标识

USE [AdventureWorks]
GO
--只能在非聚集索引上建立包含性列索引 因为包含性列索引不需要维护排序和维护这个索引
--不同于聚集索引，SQL要按照索引里字段的组合值，建立B-树。字段越多，排序的代价就越高，
--维护这个索引的代价也就越高
CREATE INDEX idx_Employee_Title_ConverIndex ON [HumanResources].[Employee]([Title]) INCLUDE([BirthDate])

-------------------------------------------------------------------------
USE [AdventureWorks]
GO
DBCC freeproccache
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS PROFILE ON
GO
SELECT [BirthDate] FROM [HumanResources].[Employee]
WHERE [Title]='Marketing Manager'

--根据执行计划使用了索引查找
--stmt text列
  |--Index Seek(OBJECT:([AdventureWorks].[HumanResources].[Employee].[idx_Employee_Title_ConverIndex]), SEEK:([AdventureWorks].[HumanResources].[Employee].[Title]=CONVERT_IMPLICIT(nvarchar(4000),[@0],0)) ORDERED FORWARD)
 
--Argument列
--OBJECT:([AdventureWorks].[HumanResources].[Employee].[idx_Employee_Title_ConverIndex]), SEEK:([AdventureWorks].[HumanResources].[Employee].[Title]=CONVERT_IMPLICIT(nvarchar(4000),[@0],0)) ORDERED FORWARD

--这样的索引，既可以节省书签查找的消耗，也不需要做不必要的排序。是covering index
--的一种替代方式


--5、Indexed View（索引视图）
--视图只是一个逻辑定义，他里面并没有存储任何数据。对他的查询会转向对他所
--基于的表格的查询。如果一个视图的定义比较复杂，那么对他的查询会比较耗时

--为了提高视图操作的性能，SQL允许在一些视图上建立索引。索引里面的字段值会
--被重新存放一份。如果索引能够覆盖查询要使用到的所有字段，那查询就不必去
--访问基础表格。这样会大大提高检索速度。所以这是一个提高和视图相关的语句
--性能的好办法

--但是，SQL对索引视图有限制。不是所有的视图都能建索引。视图要符合一定的
--先决条件


--6、Primary Key（主键）
--很多人会把Primary Key和聚集索引搞混起来，或者认为这是同一个东西。
--这个概念是非常错误的。主键是一个约束（constraint），他依附在一个
--索引上，这个索引可以是聚集索引，也可以是非聚集索引。所以在一个
--（或一组）字段上有主键，只能说明他上面有个索引，但不一定就是
--聚集索引。而一个聚集索引里，是可以有重复值的。只要他没有被同时
--设为主键

--强调这一点，是因为有些用户觉得自己的表格上设置了主键，就认为表格上
--有聚集索引，按照B-树的方式管理了。如果没有指定主键是个聚集索引，可能
--表格还是会以堆的方式管理，效率低下

--那面对一个性能不理想的语句，怎麽调整索引，才能提高性能呢？当确认
--TotalSubtreeCost这一列是准确的以后，应该找对cost贡献最多的子句。
--如果他用的是Table Scan，或者Index Scan，请比较他返回的行数和表格
--实际行数。如果返回行数远小于实际行数，那就说明SQL没有合适的索引
--供他做seek。这时候加索引就是一个比较好的选择


--例如下面这个查询
use [AdventureWorks]
GO
SET STATISTICS PROFILE ON
GO
SELECT *
FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO

--在ProductID上加一个索引是一个比较自然的想法
--但是不是所有的查询都是这麽简单的。面对一个复杂的语句，找到一个优化
--的索引组合对人脑来讲，真的不是一件很简单的事情。好在SQL提供了两种
--“自动”功能，给你建议，该怎么调整索引。

--1、Missing Index
--从SQL2005以后，在SQL对任何一句语句做编译的时候，都会去评估一下，这句话
--是不是缺少什么索引的支持，如果他认为是，他还会预估，如果有这麽一个索引
--他的性能能提高多少。对单条语句，这个信息可以通过SQL Trace里的
--Performance-Showplan XML Statistics Profiles这个事件得到

--在SQL Trace里Performance-Showplan XML Statistics Profiles这个事件的内容
--会以一个执行计划图显示，我们想要的信息看不到。你需要选择这个事件，
--点击右键，选择Extract Event Data，把数据输出到一个.SQLPlan结尾的文件里

--用记事本打开（默认使用SSMS以图形方式打开）。在XML格式的文本里，如果
--你能找到<MissingIndexes>这个Tag，就说明SQL对你现在的数据库设计有些意见了。
--例如我们刚才那个查询，他的意见是：

--也就是说，他希望你在dbo.SalesOrderDetail_test的ProductID上建立一个索引，
--而这个索引最好能够include[UnitPrice]这个字段。他认为，如果加了这个索引
--性能可以提高99.6099%！！


--这里的这个建议还是比较合理的。但是，DBA还是需要去确认一下建议。因为这个
--建议完全是根据这个语句本身给出的，没有考虑对其他语句的影响，也没有考虑
--维护索引的成本，所以是很片面的。其准确性，也要再确认一下

--SQL有一个动态管理视图sys.dm_db_missing_index_details，记录了当前数据库
--下所有的missing index的信息。他针对的是SQL从启动以来所有运行的语句，
--而不是针对某一个查询。DBA可以看看，哪些表格SQL是最有“意见”的
USE [Monitoring] --要查询索引缺失的数据库
GO
SELECT * FROM sys.[dm_db_missing_index_details]
SELECT * FROM sys.[dm_db_missing_index_groups]
SELECT * FROM sys.[dm_db_missing_index_group_stats]
SELECT * FROM sys.[dm_db_missing_index_columns](index_handle)

--msdn:http://msdn.microsoft.com/zh-cn/library/ms345434.aspx
--[dm_db_missing_index_details]各列含义（返回有关缺失索引的详细信息，不包括空间索引）:
--1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥
--2、database_id :标识带有缺失索引的表所驻留的数据库
--3、object_id :标识索引缺失的表
--4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来（简单来讲就是where 后面的筛选字段），谓词的形式如下：table.column =constant_value
--5、inequality_columns :构成不等谓词的列的逗号分隔列表，例如以下形式的谓词：table.column > constant_value “=”之外的任何比较运算符都表示不相等。
--6、included_columns:用于查询的涵盖列的逗号分隔列表（简单来讲就是 select 后面的字段）。
--7、statement:索引缺失的表的名称


--例如下面查询结果要创建下面的索引
--column_id               column_name             column_usage
--  5                      ProductID                equality
--  1                      SalesOrderID             include

CREATE INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test(ProductID) INCLUDE(SalesOrderID)
----------------------------------------------------------------------------
USE [AdventureWorks]
GO
SELECT [SalesOrderID] FROM [dbo].[SalesOrderDetail_test] WHERE [ProductID]=69685
-------------------------------------------------------------------------------------
USE [AdventureWorks]
GO
DROP INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON [SalesOrderDetail_test]

--注意事项:
--由 sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新，因而不是持久化的。
--缺失索引信息只保留到重新启动 SQL Server 前。如果数据库管理员要在服务器回收后保留缺失索引信息，
--则应定期制作缺失索引信息的备份副本


--msdn：http://msdn.microsoft.com/zh-cn/library/ms345364.aspx
--sys.[dm_db_missing_index_columns]([sql_handle])
--返回与缺少索引（不包括空间索引）的数据库表列有关的信息。sys.dm_db_missing_index_columns 是一个动态管理函数
--sys.dm_db_missing_index_columns(index_handle)
--index_handle：唯一地标识缺失索引的整数。




--msdn：http://msdn.microsoft.com/zh-cn/library/ms345407.aspx
--sys.[dm_db_missing_index_groups]
--返回有关特定缺失索引组中包含的缺失索引（不包括空间索引）的信息


--msdn：http://msdn.microsoft.com/zh-cn/library/ms345421.aspx
--sys.[dm_db_missing_index_group_stats]
--返回缺失索引组的摘要信息，不包括空间索引
--这个视图说白了就是预估有这麽一个索引，他的性能能提高多少

--avg_user_impact: 实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。
--MSDN给出的示例
--缺失索引组句柄为 2

--查询提供缺失索引的数据库、架构和表的名称。它还提供应该用于索引键的列的名称
USE [AdventureWorks]
GO
SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 2




USE [AdventureWorks] --要查询索引缺失的数据库
GO
SELECT * FROM sys.[dm_db_missing_index_details]
SELECT * FROM sys.[dm_db_missing_index_groups]
SELECT * FROM sys.[dm_db_missing_index_group_stats]
SELECT * FROM sys.[dm_db_missing_index_columns](1) --1 ：1是根据dm_db_missing_index_details查出来的






--2、Database Engine Tuning Advisor（DTA）数据库引擎优化顾问
--这也是一个非常好用的工具，强力向大家推荐。在SSMS里选择
--工具-》数据库引擎优化顾问，就可以开启这个工具


--虽然DTA接受一个SQL Trace文件作为工作语句的输入，但是这种方法不太有效
--比较好的方法，是先找出你想要优化的语句，把他们存成一个.sql文件，作为
--输入。还要选好默认的数据库（否则DTA找不到表格）和你想要优化的数据库

--请看新开的文章


--------------------------------------------------------------------------------------------
--SQLSERVER Plan Guide（计划指南）
--前面说参数嗅探的时候，曾经提到在SQL2005以后，用户可以给一些语句定义
--计划指南，让他们在编译的时候，自动使用你想要的执行计划。这是提高
--语句性能在SQL层面上可以做的另外一件事。
--但是，使用这个方法要满足几个前提：

--（1）问题语句固定，你已经知道了他们的格式，而且应用程序就一直使用这种格式
--在指定执行计划指南的时候，需要告诉SQL完整的语句。当SQL发现任何一个用户将要
--运行同样的一句话时，就会应用计划指南。如果问题语句是动态生成的，每一次都
--不一样的话，那计划指南可能就不那么有用了


--（2）语句的性能问题可以通过选用一个固定的执行计划得到解决
--也就是说，存在一个“万能”的执行计划，他对任何参数值、任何用户，其执行效率
--都是稳定可接受的，而且也不会随着数据量的变化，执行效率变得不可接受。如果
--你找不到这样一个执行计划，那就没有办法使用这个方法。


--（3）使用这种方法后，需要有人不断监视语句的性能
--如果数据量或数据分布发生很大的变化，可能先前好的执行计划现在反而会导致更大
--的问题。需要DBA能够知道计划指南设置的合理，在必要的时候做相应的调整

--创建计划指南的语句是：
--msdn：http://msdn.microsoft.com/zh-cn/library/ms179880.aspx
sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      {
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
                 | N'XML_showplan'
                 | NULL }

--定义看起来有点羞涩。简单来说，用户可以给一个batch里的一句话定义计划指南，
--也可以给一个存储过程里的一句话定义。这句话可以有参数，也可以没有参数。
--计划指南可以是一个Query Hint，也可以直接是一个执行计划


--下面举一些例子，来展示一下计划指南的使用方法
--1、对单个固定语句定义计划指南
--假设有这麽一张表格：
USE [AdventureWorks]
GO
CREATE TABLE table1(
NAME NVARCHAR(256),
id INT
)
GO

--有下面这个不带参数的语句（注意，他没有以回车符结束）
USE [AdventureWorks]
GO
INSERT [dbo].[table1]
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.id=b.[id]
WHERE b.[indid]=2


--默认，这个查询将会使用merge join的方法。如果用户想要规定，这个join
--必须使用nested loops，除了直接在查询里加query hint以外，使用计划指南
---也可以达到同样效果。创建计划指南的语句应该是：
USE [AdventureWorks]
GO
EXEC [sys].[sp_create_plan_guide]
@name=N'Guide2',
@stmt=N'INSERT [dbo].[table1]
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.id=b.[id]
WHERE b.[indid]=2',
@type=N'SQL',
@module_or_batch=N'INSERT [dbo].[table1]
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.id=b.[id]
WHERE b.[indid]=2',
@params=NULL,
@hints=N'option(loop join)'

--创建计划指南的时候，@stmt和@module_or_batch参数所带的字符串，必须
--和语句本身一模一样，不能少一个空格，也不能多一个回车。SQL在这里
--做的是精确匹配

--计划指南创建完毕后，再运行这句话
USE [AdventureWorks]
GO
SET STATISTICS XML ON
GO
EXEC [sys].[sp_executesql] N'INSERT [dbo].[table1]
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.id=b.[id]
WHERE b.[indid]=2'
GO


--在以XML格式输出的执行计划里,就能看到执行计划是根据计划指南生成的

<StmtSimple StatementText="INSERT [dbo].[table1]&#xD;&#xA;SELECT a.[name],a.[id] &#xD;&#xA;FROM [sys].[sysobjects] a&#xD;&#xA;INNER JOIN [sys].[sysindexes] b&#xD;&#xA;ON a.id=b.[id]&#xD;&#xA;WHERE b.[indid]=2" StatementId="1" StatementCompId="2" StatementType="INSERT" PlanGuideDB="AdventureWorks" PlanGuideName="Guide2"


--就在XML的开头
PlanGuideDB="AdventureWorks" PlanGuideName="Guide2"


--2、对批处理里一个带有参数的语句定义计划指南
--一般来讲,用户发过来的批处理会不止一句话，而且语句里也会有参数。这个怎麽写
--假设下面这个批处理
USE [AdventureWorks]
GO
DECLARE @id INT
SET @id=2
DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]= @id


--这里在@stmt参数里，还是代入语句本身。但是在@module_or_batch里，要代入
--整个批处理的每一句话。但是语句的参数，不用写在@params里

USE [AdventureWorks]
GO
EXEC [sys].[sp_create_plan_guide]
@name=N'Guide3',
@stmt=N'INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]= @id',
@type=N'SQL',
@module_or_batch=N'DECLARE @id INT
SET @id=2
DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]= @id',
@params=NULL,
@hints=N'option(loop join)'



--3、使用带参数的方式调用批处理语句，给其中一句指定计划指南
--用户也可以用sp_executesql的方法调用这句话

EXEC [sys].[sp_executesql] N'DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]= @id' ,N'@id int',@id=2

--而这个时候，计划指南要这样写
EXEC [sys].[sp_create_plan_guide]
@name=N'Guide5',
@stmt=N'INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]= @id',
@type=N'SQL',
@module_or_batch=N'DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]= @id',
@params=N'@id int',
@hints=N'option(loop join)'


--用批处理的时候，要把整个批处理都抄在[sp_create_plan_guide]后面，比较麻烦
--如果是存储过程，就好办多了


--4、对一个存储过程里的一条语句定义计划指南
--假设存储过程是这样的
USE [AdventureWorks]
GO
CREATE PROC Demo_Plan(@id INT)
AS
DECLARE @Table TABLE(NAME VARCHAR(256),id INT)
INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]=@id
GO


--那么计划指南就可以这样定义
USE [AdventureWorks]
GO
EXEC [sys].[sp_create_plan_guide]
@name=N'Guide6',
@stmt=N'INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.[id]=b.[id]
WHERE b.[indid]=@id',
@type=N'Object',
@module_or_batch=N'Demo_Plan',
@params=NULL,
@hints=N'option(loop join)'


--在上面这些例子里，用的都是Query Hint，并没有直接指定执行计划。
--能不能直接指定SQL在跑这句话的时候，就固定地使用某个执行计划呢？
--这个也是支持的。

--5、通过[sp_create_plan_guide]指定某个执行计划
--[sp_create_plan_guide]的@hints参数，在SQL2008里可以直接带入
--一个XML格式的执行计划（在SQL2005的时候，要使用N'OPTION(use plan...)'的方法）
--下面是一个例子

--首先我们创建一个存储过程。里面insert语句的join，默认会使用merge join的方式
USE [AdventureWorks]
GO
CREATE PROC TestPlan
AS
DECLARE @id INT
SET @id=2
DECLARE @Table TABLE(NAME VARCHAR(256),id INT)
INSERT INTO @Table
SELECT a.[name],a.[id]
FROM [sys].[sysobjects] a
INNER JOIN [sys].[sysindexes] b
ON a.id=b.[id]
WHERE b.[indid]=@id


--然后我们搞一个使用nested loops的执行计划，把他作为这个存储过程里
--insert语句的计划指南

USE [AdventureWorks]
GO
DBCC freeproccache
--清空所有缓存的执行计划
GO
DECLARE @id INT
SET @id = 2
DECLARE @Table TABLE
    (
      NAME NVARCHAR(256) ,
      id INT
    )
INSERT  INTO @Table
        SELECT  a.[name] ,
                a.[id]
        FROM    [sys].[sysobjects] a
                INNER JOIN [sys].[sysindexes] b ON a.[id] = b.[id]
        WHERE   b.[indid] = @id
OPTION  ( LOOP JOIN )
--使用query hint运行语句，让SQL生成一个nested loops的执行计划
GO

--DECLARE @xml_showplan NVARCHAR(MAX)
--SET @xml_showplan=(SELECT [query_plan] FROM
--sys.[dm_exec_query_stats] AS qs
--CROSS APPLY sys.[dm_exec_sql_text](qs.[sql_handle]) AS st
--CROSS APPLY sys.[dm_exec_text_query_plan](qs.[plan_handle],DEFAULT,DEFAULT) AS qp
--WHERE st.[text] LIKE N'declare @id int%' AND SUBSTRING(st.[text],(qs.[statement_start_offset]/2)+1,
--((case statement_end_offset when -1 then detalength(st.text) else qs.statement_start_offset/2)+1)
--like 'INSERT into @table%'
----从缓存里把这个执行计划取出来
--);


USE [AdventureWorks]
GO
EXEC [sys].[sp_create_plan_guide]
@name=N'Guide7',
@stmt=N'insert into @table
select a.name,a.id from sysobjects a
inner join sysindexes b
on a.id=b.id
where b.indid=@id',
@type=N'Object',
@module_or_batch=N'TestPlan',
@params=null,
@hints=@xml_showplan
--使用执行计划的内容直接定义计划指南
GO

--以后再运行存储过程，就能够使用到nested loops的执行计划了。在SQL2008的SQL Trace里，
--新加入了两个事件：Performance-Plan Guide Successful和Performance-Plan Guide Unsuccessful
--可以用来跟踪语句的执行有没有正确地使用计划指南

--如果想要关闭，或者删除某个计划指南，可以调用[sp_control_plan_guide]系统存储过程
--例如：
USE [AdventureWorks]
GO
EXEC [sys].[sp_control_plan_guide] N'drop',N'Guide5'
GO
--删除guide3这个计划指南
USE [AdventureWorks]
GO
EXEC [sys].[sp_control_plan_guide] N'disable all'
GO
---------------------------------------------------
USE [AdventureWorks]
GO
EXEC [sys].[sp_control_plan_guide] N'enable all'
GO
--关闭当前数据库里的所有计划指南


--计划指南这个功能，在急着调整执行计划，数据库和应用设计又不能修改的时候，能
--起到应急的作用，还是值得DBA掌握的。但是和使用query hint的局限性一样，计划指南
--更多用作短期的解决方案，长期来讲可能会有风险。数据变化后，计划指南可能就不再
--准确