澳门在线威尼斯官方 > 电脑数据库 > MSSQL施行安插录取,Server怎么样固定实践布署

原标题:MSSQL施行安插录取,Server怎么样固定实践布署

浏览次数:105 时间:2019-10-13

   SQL Server 其实从SQL Server 2005开始,也提供了类似ORACLE中固定执行计划的功能,只是好像很少人使用这个功能。当然在SQL Server中不叫"固定执行计划"这个概念,而是叫"执行计划指南"(Plan Guide 很多翻译是计划指南,个人觉得执行计划指南稍好一些)。当然两者虽然概念与命名不同,实质上它们所说的是相同的事情,当然商业包装是很常见的事情。个人还是觉得“固定执行计划”这个概念叫起来顺口,通俗易懂,执行计划指南(Plan Guide)叫起来老感觉非常拗口,不知所云(后面会在这两个概念切换,你知道我所说的是一件事情就好)。其实我以前也很少使用这些功能,直到最近在SQL Server 2014数据库中使用固定执行计划解决了几个SQL的性能问题,所以觉得还是有必要总结、归纳一下。

--简介

 

在Oracle的世界里有很多存储执行计划的手段如SQL_Profile, Stored_Outline, Plan_Baseline,在SQL Server里好像只有这样一个选择

为什么要固定执行计划?

--Ref

 

1.

为什么要使用固定执行计划(Plan Guid)呢? 个人简单的从下面几个方面介绍一下,如有不足,敬请指正。个人也是在探索当中。

  --Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

 

2.《SQL Server 2005 Practical Troubleshooting The Database Engine》Chapter 4

由于一些特殊原因(例如Parameter Sniffing、统计信息的变化或采样比例低造成的统计信息出现偏差、或其他像SQL Server 2014新的基数评估(Cardinality Estimator)特性引起优化器选择不合适的JOIN操作等等),导致某个SQL的执行计划出现很大偏差,当数据库优化器为SQL选择了一个糟糕的执行计划时,就可能出现严重性能问题,我就碰到过这样一个例子,在SQL Server 2014中,有一个SQL的执行频率较频繁,有时候优化器突然选择了一个较差的执行计划时,这时就会出现严重的性能问题。所以,这个时候,我们就必须使用Plan Guide固定这个执行计划,从而让优化器使用正确的执行计划,从而解决这样的性能问题。

   本页下方有一个使用例子是从这本书上摘录的

 

    1. Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.aspx)
  • 2.《SQL Server 2005 Practical Troubleshooting The Database Engine》Chapter 4   本页下方有一个使用例子是从这本书上摘录的

另外一方面,因为优化器生成执行计划本身是很复杂的过程,我们所能干涉的不多,最多使用HINT提示来改变执行计划。而且优化器基于一些算法和开销考虑,也有可能生成的执行计划不是最优执行计划,而Plan Guid是DBA管理数据库的一件利器,如果你发现了一个比当前更好的执行计划,也能使用执行计划指南固定这个SQL的执行计划。当然这种情况非常、非常少,至少我在生产环境使用得不多。

--自己创建执行计划

 

  • 建立存储过程
    威尼斯澳门在线 1威尼斯澳门在线 2代码 

  • sp_create_plan_guide

    sp_create_plan_guide 
    @name = N'Guide1',
    @stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
            Sales.Customer AS c,
            Sales.SalesTerritory AS t
            WHERE h.CustomerID = c.CustomerID 
                AND c.TerritoryID = t.TerritoryID
                AND CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'
    --其中@Country_region = N''US''查询较多,所以以下plan把us为参数的语句放到缓存Buffer中
    @hints = N'OPTION (PARAMETERIZATION FORCED)'
    --其中PARAMETERIZATION FORCED把带参数所有语句都放到缓存Buffer中 
    
  • sp_get_query_template:按照特定的语句生成template,之后生成执行计划

    --Obtain the paramaterized form of the query:
    DECLARE @stmt nvarchar(max);
    DECLARE @params nvarchar(max);
    EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
        FROM Production.ProductModel pm 
        INNER JOIN Production.ProductInventory pi 
            ON pm.ProductModelID = pi.ProductID 
        WHERE pi.ProductID = 101 
        GROUP BY pi.ProductID, pi.Quantity 
        HAVING SUM(pi.Quantity) > 50',
    @stmt OUTPUT, 
    @params OUTPUT;
    --Force parameterization of the query. (This step is only required
    --if the query is not already being parameterized.)
    
    EXEC sp_create_plan_guide N'TemplateGuide1', 
        @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';
    --Create a plan guide on the parameterized query
    
    EXEC sp_create_plan_guide N'GeneralizedGuide1', 
    @stmt, 
    N'SQL', 
    NULL, 
    @params, 
    N'OPTION(HASH JOIN)'; --强制进行Hash Join
    

有时候,某个系统是购买供应商的,你发现数据库里面有大量几乎相同的SQL解析,然后缓存了,其实你发现这些SQL完全可以只解析一次,完全可以参数化,没有必要大量解析。但是现在供应商没有提供技术支持了,不可能去优化代码里面的SQL语句,那么你也可以使用执行计划指南来帮你解决这个问题。

--从缓存(sys.dm_exec_query_stats)中的查询计划内创建执行计划

 

  • sp_create_plan_guide_from_handle

    USE AdventureWorks;
    GO
    SELECT WorkOrderID, p.Name, OrderQty, DueDate
    FROM Production.WorkOrder AS w 
    JOIN Production.Product AS p ON w.ProductID = p.ProductID
    WHERE p.ProductSubcategoryID > 4
    ORDER BY p.Name, DueDate;
    GO
    -- Inspect the query plan by using dynamic management views.
    
    SELECT * FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
    WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
    GO
    -- Create a plan guide for the query by specifying the query plan in the plan cache.
    
    DECLARE @plan_handle varbinary(64);
    DECLARE @offset int;
    SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
    WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
    
    EXECUTE sp_create_plan_guide_from_handle 
        @name = N'Guide1',
        @plan_handle = @plan_handle,
        @statement_start_offset = @offset;
    GO
    -- Verify that the plan guide is created.
    SELECT * FROM sys.plan_guides
    WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
    GO 
    
  • 为多语句批处理创建多个计划指南

    USE AdventureWorks;
    GO
    SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
    SELECT * FROM Person.Address;
    SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
    GO
    
    -- Examine the query plans for this batch
    
    SELECT * FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
    WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
    GO
    
    -- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
    
    BEGIN TRANSACTION
    
    DECLARE @plan_handle varbinary(64);
    DECLARE @offset int;
    
    SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
    WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
    AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                            ((CASE statement_end_offset 
                                  WHEN -1 THEN DATALENGTH(st.text)
                                  ELSE qs.statement_end_offset END 
                                  - qs.statement_start_offset)/2) + 1) like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
    
    EXECUTE sp_create_plan_guide_from_handle 
        @name = N'Guide_Statement1_only',
        @plan_handle = @plan_handle,
        @statement_start_offset = @offset;
    
    SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
    WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
    AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                            ((CASE statement_end_offset 
                                  WHEN -1 THEN DATALENGTH(st.text)
                                  ELSE qs.statement_end_offset END 
                                  - qs.statement_start_offset)/2) + 1) like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'
    
    EXECUTE sp_create_plan_guide_from_handle 
        @name = N'Guide_Statement3_only',
        @plan_handle = @plan_handle,
        @statement_start_offset = @offset;
    
    COMMIT TRANSACTION
    GO
    
    -- Verify the plan guides are created.
    
    SELECT * FROM sys.plan_guides;
    GO
    

还有就是使用Plan Guide来调优,对比不同的执行计划的优劣。当然应该还有一些其它应用场景,只是我没有碰到过而已。

--删除,启用或禁用执行计划

 

  • 删除、启用或禁用计划指南 -- sp_control_plan_guide

    --Create a procedure on which to define the plan guide.
    IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
        DROP PROCEDURE Sales.GetSalesOrderByCountry;
    GO
    CREATE PROCEDURE Sales.GetSalesOrderByCountry 
        (@Country nvarchar(60))
    AS
    BEGIN
        SELECT *
        FROM Sales.SalesOrderHeader AS h 
        INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
        INNER JOIN Sales.SalesTerritory AS t ON c.TerritoryID = t.TerritoryID
        WHERE t.CountryRegionCode = @Country;
    END
    GO
    
    --Create the plan guide.
    EXEC sp_create_plan_guide N'Guide3',
        N'SELECT *
        FROM Sales.SalesOrderHeader AS h 
        INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
        INNER JOIN Sales.SalesTerritory AS t ON c.TerritoryID = t.TerritoryID
        WHERE t.CountryRegionCode = @Country',
        N'OBJECT',
        N'Sales.GetSalesOrderByCountry',
        NULL,
        N'OPTION (OPTIMIZE FOR (@Country = N''US''))';
    GO
    --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 AdventureWorks;
    GO
    EXEC sp_control_plan_guide N'DISABLE ALL';
    

如何固定执行计划?

--一个例子(忘了是从哪里看到的,没修改就放到这里了)

 

  • 1 制造分散数据 威尼斯澳门在线 3威尼斯澳门在线 4代码

  • 2 开始测试

    DBCC FREEPROCCACHE
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
    
    EXEC sp 2
    /*SQL Server 分析和编译时间:CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
      SQL Server 分析和编译时间:CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。
    表 't'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
    SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。
    */
    EXEC sp 2
    /*SQL Server 分析和编译时间:CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
      SQL Server 分析和编译时间:CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。
    表 't'。扫描计数 1,逻辑读取 786890 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
      SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
      SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。
    */
    
    Alter proc sp @i int as select c2,c3 from t where c2 = @i Option (Optimize for (@i = 1));
    
    EXEC sp_create_plan_guide
        @Name=N'Guide1',
        @stmt = N'SELECT c2,c3 FROM t WHERE c2=@i',
        @type = N'OBJECT',
        @module_or_batch = N'dbo.sp',
        @params=NULL,
        @hints=N'OPTION (OPTIMIZE FOR (@i = 1))'
    GO
    
    SET SHOWPLAN_XML ON;
    GO
    select c2,c3 from t where c2 = 1
    go
    SET SHOWPLAN_XML OFF;
    
    --在查询配置中,直接使用该查询计划,如: 
    EXEC sp_create_plan_guide @Name=N'Guide1',
        @stmt = N'SELECT c2,c3 FROM t WHERE c2=@i',
        @type = N'OBJECT',
        @module_or_batch = N'dbo.sp',
        @params=NULL,
        @hints=N'OPTION (USE PLAN
    N'上一个脚本的XML结果')'
    

    采用上述的作法直接影响SQL SERVER编译执行计划的方式,但如同各种数据表提示(hint),非得不得已不要轻易使用;使用上述查询计划,适用1,但用户又偏好2,则上述配置则反而有害了;

Plan Guide主要用到下面几个存储,关于这些系统存储过程的使用方法、功能介绍,官方文档有详细的介绍。在此就不画蛇添足了。

sys.sp_create_plan_guide,

sys.sp_create_plan_guide_from_handle,

sys.sp_control_plan_guide

下面我们还是看看一些应用场景案例吧!构造一个合适、贴切的例子实在是太花精力和时间,生产环境案例又不能搬出来,我们先来看看官方文档提供的例子吧,如下SQL所示,在测试数据库AdventureWorks2014,该SQL使用Nested Loop关联两个表

SELECT COUNT(*) AS c

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

  ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';

威尼斯澳门在线 5

 

假如(注意这里是假设)发现如果这个SQL中,两个表使用MERGE JOIN的方式,效率更高,那么我们可以使用sp_create_plan_guide来创建执行计划指南(固定执行计划),如下所示

EXEC sp_create_plan_guide 

    @name = N'my_table_jon_guid',

    @stmt = N'SELECT COUNT(*) AS c

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

  ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate >= ''20000101'' AND h.OrderDate <=''20050101'';',

    @type = N'SQL',

    @module_or_batch = NULL,

    @params = NULL,

    @hints = N'OPTION (MERGE JOIN)';

 

那么此时再执行这个SQL时,你就会发现执行计划就会变成Merge Join方式了。 这样好过在SQL Server中使用HINT,为什么呢? 有可能这个SQL是写死在应用程序里面,如果以后这个执行计划变成了一个糟糕的执行计划,维护的成本非常高(一方面如果没有记录,需要耗费精力去定位、查找这段SQL,另外一方面,DBA是没有权限接触这些应用程序代码的,可能需要你沟通、协调开发人员、运维人员。耗费无数的时间、精力.....,还有可能其他接手维护的人不了解情况等等),而使用执行计划指南,那么你查找、禁用、删除这个执行计划指南即可。非常方便、高效,也许你一分钟就能搞定,如果是Hint,说不定处理完,需要几天,想必这样的耗费精力沟通、协调的事情很多人都遇到过。

SELECT COUNT(*) AS c

FROM Sales.SalesOrderHeader AS h

INNER MERGE JOIN Sales.SalesOrderDetail AS d

  ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';

威尼斯澳门在线 6

 

另外,我们再来构造一个例子,模拟系统里面出现大量解析的SQL语句的案例,如下所示

USE AdventureWorks2014;

GO

SET NOCOUNT ON;

GO

DROP TABLE TEST

GO

CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(8));

GO

CREATE INDEX PK_TEST ON TEST(OBJECT_ID);

GO

 

DECLARE @Index INT =1;

 

WHILE @Index <= 10000

BEGIN

    INSERT INTO TEST

    SELECT @Index, 'kerry';

   

    SET @Index = @Index +1;

END

GO

UPDATE STATISTICS  TEST WITH FULLSCAN;

GO

 

构造了上面案例后,我们清空该数据库所有缓存的执行计划(仅仅是为了干净的测试环境,避免以前缓存的执行计划影响实验结果),生产环境你不能使用DBCC FREEPROCCACHE清空所有缓存的执行计划,但是可以用DBCC FREEPROCCACHE删除特定的执行计划。

DBCC FREEPROCCACHE;

GO

然后我们开始测试我们的例子,假设系统里面有大量类似的SQL语句,数量惊人(我们仅仅测试四个)。如果这个系统是从供应商那里购买的,现在又没有技术支持和Support的人(或者及时有人Support,但是不严重影响使用的情况,人家不想花费精力去优化),没有人协助你优化这些SQL,你又不能将数据库参数“参数化”从简单设置为强制(因为影响太大,而且没有测试,不确定是否带来潜在的性能问题).....

SELECT * FROM TEST WHERE OBJECT_ID=1;

GO

SELECT * FROM TEST WHERE OBJECT_ID=2;

GO

SELECT * FROM TEST WHERE OBJECT_ID=3;

GO

SELECT * FROM TEST WHERE OBJECT_ID=4;

GO

....................................................................

 

此时查看执行计划,发现缓存了4个执行计划

SELECT qs.sql_handle,

       qs.statement_start_offset,

       qs.statement_end_offset,

       qs.plan_handle,

       qs.creation_time,

       qs.execution_count,

       qs.query_hash,

       qs.query_plan_hash,

       st.text,

       qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp

WHERE text LIKE N'%SELECT * FROM TEST WHERE OBJECT_ID%' AND text NOT LIKE 'SELECT qs.sql_handle%';

威尼斯澳门在线 7

 

那么此时,执行计划指南就能发挥其作用了,使用sp_create_plan_guide创建执行计划指南,强制SELECT * FROM TEST WHERE OBJECT_ID=xxx这样的SQL参数化

DECLARE @stmt nvarchar(max);

DECLARE @params nvarchar(max);

EXEC sp_get_query_template N'SELECT * FROM TEST WHERE OBJECT_ID=1',

@stmt OUTPUT, 

@params OUTPUT;

 

EXEC sp_create_plan_guide N'my_sql_parameter_test', 

    @stmt, 

N'TEMPLATE', 

NULL, 

@params, 

N'OPTION(PARAMETERIZATION FORCED)';

 

威尼斯澳门在线 , 

然后我们执行下面命令,清空该数据库所有缓存的执行计划,然后执行上面四个SQL语句

DBCC FREEPROCCACHE;

 

GO

 

SELECT * FROM TEST WHERE OBJECT_ID=1;

 

SELECT * FROM TEST WHERE OBJECT_ID=2;

 

SELECT * FROM TEST WHERE OBJECT_ID=3;

 

SELECT * FROM TEST WHERE OBJECT_ID=4;

 

 

你会发现他们全部使用执行计划指南里面的执行计划了。不用多次解析了。

威尼斯澳门在线 8

 

还是使用上面的例子,我们来解决一个Parameter Sniffing(参数嗅探)的问题,在实验前,我们先删除前面创建的Plan Guide,以免这个影响测试结果,

EXEC sp_control_plan_guide @operation=N'DROP', @name=N'my_sql_parameter_test';

 

我们构造一个数据倾斜的案例,这样方便我们演示

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:MSSQL施行安插录取,Server怎么样固定实践布署

关键词:

上一篇:威尼斯澳门在线一次非法关机导致mysql数据表损坏

下一篇:没有了