主页 / dba / 问题 / 114679
Accepted
pshore73
Asked: 2015-09-11 04:44:18 +0800 CST

导出查询计划

  • 772

我知道导出单个查询计划非常容易。我的问题是，当我创建一个返回多个计划的查询时，例如查看服务器上最昂贵的查询，有没有办法批量导出这些计划以供以后分析，还是我需要一次导出一个？简单地保存查询结果将允许我维护 XML，但它仍然是一个一个的审查，而且我在以后能够查看它的过程中遗漏了一些东西。

我有 99% 的把握，我缺少一些简单的东西。你能帮我找到那个吗？

sql-server tuning

3 个回答

  1. 我知道导出单个查询计划非常容易。我的问题是，当我创建一个返回多个计划的查询时，例如查看服务器上最昂贵的查询，有没有办法批量导出这些计划以供以后分析，还是我需要一次导出一个？

    我建议您创建一个实用程序数据库，例如dba_repository （或您喜欢的任何名称）并将信息与查询计划 XML 一起存储在该实用程序数据库中。随着时间的推移，它将作为仓库进行性能调整和基准测试。

    创建物理表并在其中存储相关信息。通过这种方式，您可以备份数据库以及您收集的所有信息。

    恕我直言，如果您继续将计划 XML 导出到文件夹中，它会变得很难看。

    基本上，您需要从服务器实例中探索计划缓存，这将允许您调整工作负载并为您提供以下信息：

    • 缺少索引的计划
    • 带有警告的计划
    • 具有隐式转换的计划
    • 带有索引扫描的计划
    • 带有查找的计划
    • 查找索引使用情况
    • 参数化计划
    • 并行计划的成本
    • 每个运营商详细的并行计划成本
    • 平行计划，其中平均。工人时间 > 平均 经过时间
    • 平行计划，其中平均。工人时间 > 平均 每个操作员的经过时间详细信息

    Pedro Azevedo Lopes撰写的博客上有大量信息和脚本-探索计划缓存 -第 1部分和第 2 部分。

    此外，Jonathan Kehayias 写了很多关于探索计划缓存的文章。

    温馨提示：不要进行下意识的性能调整，例如不要盲目地在找到缺失的索引时创建它们，等等。而是通过为服务器实例设置基线，然后从那里获取它来遵循更方法论的方法。

    • 4
  2. Best Answer

    你的问题听起来像是我一直在思考的问题。所以 Powershell 来救援。您可以针对要检查的痛点修改查询。唯一实际保存的是执行计划的 xml。每个计划都保存到其自己的文件中，该文件由 DB 和编号命名，位于 serverInstance 目录下的日期目录下。希望这对你有用！

    编辑：我稍微修改了脚本以添加一些参数和一些描述符。这个版本应该对用户更友好一点。当您使用 Get-Help 时，顶部的注释块将提供一些帮助上下文。我是 PoSh 脚本的新手，所以如果有些东西看起来很新，请善待。

        <#  
        .SYNOPSIS
           TopTen.ps1 - returns the top ten most expensive execution plans based on the criteria you provide 
        .DESCRIPTION
           Execution plans are saved to the location of your choosing as .sqlplan files
        .PARAMETER sqlServer
           SQL Server and Instance you want to query
        .PARAMETER filePath
            Defaulted to "C:\ExpensiveQueries\"; set to the location you want your files saved
        .PARAMETER painPoint
            Defaulted to "TotalCPU"; set to the pain point you want to float to the top

            Options are:
           --TotalReads
           --TotalCPU
           --TotalDuration
           --execution_count 
        .EXAMPLE
           c:\Scripts\TopTen.ps1 -sqlServer "Server\Instance"

           Queries against Server\Instance, saving files in the default location, looking specifically for High CPU plans
        .EXAMPLE
           c:\Scripts\TopTen.ps1 -sqlServer "Server\Instance" -filePath "D:\ServerAnalysis\ExpensivePlans" -painPoint "TotalDuration"

           Queries against Server\Instance, saving files in D:\ServerAnalysis\ExpensivePlans, looking specifically for long running plans
        .NOTES
           Script provided as is with no guarantees.  Use at your own risk and modify/share as desired
        #>
    param
    (
        [Parameter(Mandatory=$true, 
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true, 
                    ValueFromRemainingArguments=$false, 
                    Position=1,
                    ParameterSetName='Parameter Set 1')]
        [string]$sqlServer,


        [Parameter(Mandatory=$false, 
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true, 
                    ValueFromRemainingArguments=$false, 
                    Position=2,
                    ParameterSetName='Parameter Set 1')]
        [string]$filePath="C:\ExpensiveQueries\",

        [Parameter(Mandatory=$false, 
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true, 
                    ValueFromRemainingArguments=$false, 
                    Position=3,
                    ParameterSetName='Parameter Set 1')]
        [string]$painPoint="TotalCPU"
    )

    #=============================#

    $sqlcmd="SELECT TOP 10
           DB_NAME(st.dbid) as DBName,
           total_worker_time / execution_count AS AvgCPU,
           total_worker_time AS TotalCPU,
           total_elapsed_time / execution_count AS AvgDuration,
           total_elapsed_time AS TotalDuration,
           total_logical_reads / execution_count AS AvgReads,
           total_logical_reads AS TotalReads,
           --qs.plan_handle
           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_query_plan(qs.plan_handle) AS qp
    WHERE query_plan IS NOT NULL
    ORDER BY --Choose the one you want to use:
          $painPoint
           --TotalReads
           --TotalCPU
           --TotalDuration
           --execution_count 
           DESC
    OPTION (RECOMPILE);"

    $iAmHere=Get-Location
    $filePath=($filePath+"\").Replace("\\","\")

    $sqlResult=Invoke-SqlCmd -MaxCharLength 999999 -Query $sqlcmd -ServerInstance $sqlServer

    If($?)
    {
        $timestamp = Get-Date -Format s | foreach {$_ -replace ":", ""} | foreach {$_ -replace "-",""}

        $filePath+=($sqlServer.replace("\","_"))+"\"+$timestamp

        If (!(Test-Path $filePath))
        {
            New-Item -Path $filePath -ItemType "Directory"
        }

        $queryNum=0
        foreach ($d in $sqlResult)
        {
            $queryNum+=1
            $fileName=$filepath+"\"+$d.DBName+"_"+$queryNum+".sqlplan"

            $fileValue=$sqlResult.query_plan

            New-Item -Path $fileName -ItemType file -Value $d.query_plan
        }

        Set-Location $iAmHere
    }
    Else
    {    
        Throw $error[0].Exception
    }
    • 3

  3. 既然您已经有一个查询可以在服务器上为您提供最昂贵的查询，为什么不让它在您获得结果时保存每一行的计划呢？不可能，你说？当然是 :-) 只需创建一个 SQLCLR 标量函数，如下所示，并将其添加到您的查询中：

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = false, IsPrecise = true)]
public static SqlString SaveXmlToFile([SqlFacet(MaxSize = 4000)] SqlString FilePath,
    SqlXml XmlData)
{
    try
    {
        File.WriteAllText(FilePath.Value, XmlData.Value, Encoding.Unicode);
    }
    catch (Exception __Exception)
    {
        return __Exception.Message;
    }

    return String.Empty;
}

    由于我使用的是选项，因此无需对NULL输入参数进行任何检查：.IsNull()RETURNS NULL ON NULL INPUT

    CREATE FUNCTION [dbo].[SaveXmlToFile](@FilePath NVARCHAR(4000), @XmlData XML)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER,
     RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SomeAssemblyName].[FileUtils].[SaveXmlToFile];

    然后你可以像这样使用：

    SELECT *, Test.dbo.SaveXmlToFile(N'C:\TEMP\XmlQueryPlans\'
        + CONVERT(NVARCHAR(50), qstat.query_plan_hash, 2)
        + N'_'
        + CONVERT(NVARCHAR(10), qstat.statement_start_offset)
        + N'-'
        + CASE qstat.statement_end_offset
            WHEN -1 THEN N'InfinityAndBeyond'
            ELSE CONVERT(NVARCHAR(10), qstat.statement_end_offset)
        END
        + N'.sqlplan', qplan.query_plan)
FROM    sys.dm_exec_query_stats qstat
OUTER APPLY sys.dm_exec_query_plan(qstat.plan_handle) qplan;

    由于这是一个可以在任何查询中工作的功能，因此它可以自动化，这与 SSMS 插件不同。

    它比将查询嵌入到将保存计划的程序中更灵活（尽管我会说史蒂夫回答中的 PowerShell 建议是下一个最佳选择）。

    而且，它并不特定于保存查询计划。它可以保存任何 XML 字段或变量，因此也可以在其他几个地方使用 :-)。

    使上述 SQLCLR 函数正常工作的几个简单步骤（以及您创建的几乎任何需要EXTERNAL_ACCESS或的程序集UNSAFE）：

    1. 程序集需要签名。在 Visual Studio 中，转到项目属性 -> SQLCLR 选项卡 -> 签名...按钮。

    2. 需要启用“CLR 集成”：

      EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

    3. 从DLL创建一个非对称密钥：[master]

      USE [master];
CREATE ASYMMETRIC KEY [KeyName]
FROM EXECUTABLE FILE = 'Path\to\SomeAssemblyName.dll';

    4. [master]从 DLL创建登录：

      CREATE LOGIN [SomeLoginName]
FROM ASYMMETRIC KEY [KeyName];

    5. 授予基于密钥的登录适当的权限：

      GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLoginName];

    请注意，这些步骤都不是将数据库属性TRUSTWORTHY转换为ON!!!

    现在，如果您有兴趣分析特定查询或一组查询（相互比较），请查看我对 StackOverflow 上相关问题的回答：

    以编程方式读取 SQL Server 的查询计划建议索引以执行特定的 SQL？

    更新：

    @Kin 的回答提醒我，除了计划本身之外，还有其他信息可能对与计划一起保存非常有用。至少有所有的附加字段sys.dm_exec_query_stats，而且还有一个sys.dm_exec_plan_attributes DMV 非常有趣。

    如果您想按照 Kin 的建议将所有这些数据存储到实用程序数据库中，那么关于如何将每个查询统计计划的一行和每个计划属性计划的多行放入一个或多个表中应该是相当简单的. 但是，如果您仍想导出计划，是否也可以捕获此信息并将其与查询计划一起保存，这样您就不必查看多个文件或其他什么？答案：是的。sys.dm_exec_query_stats实际上，我们可以将与当前计划在同一行的来自 的字段以及来自当前计划的所有行移植sys.dm_exec_plan_attributes到查询计划 XML 中，这样它：

    • 仍然是一个文件，并且
    • 在 SSMS 和 SQL Sentry Plan Explorer 中打开方式相同
    SELECT *, Test.dbo.SaveXmlToFile(N'C:\TEMP\XmlQueryPlans\'
        + CONVERT(NVARCHAR(50), qstat.query_plan_hash, 2)
        + N'_'
        + CONVERT(NVARCHAR(10), qstat.statement_start_offset)
        + N'-'
        + CASE qstat.statement_end_offset
            WHEN -1 THEN N'InfinityAndBeyond'
            ELSE CONVERT(NVARCHAR(10), qstat.statement_end_offset)
        END
        + N'.sqlplan', STUFF(
            tplan.query_plan,
            CHARINDEX(N'<BatchSequence>', tplan.query_plan),
            0,
            (
                SELECT stat.data + attrs.data
                FROM (
                        SELECT qstat.*
                        FOR XML RAW('QueryStats'), BINARY BASE64
                    ) stat(data)
                CROSS JOIN (
                        SELECT * FROM sys.dm_exec_plan_attributes(qstat.plan_handle)
                        FOR XML RAW('Attr'), ROOT('PlanAttributes'), BINARY BASE64
                    ) attrs(data)
                )
            )
         ) AS [SavingXmlToFile]
FROM    sys.dm_exec_query_stats qstat
OUTER APPLY sys.dm_exec_text_query_plan(qstat.plan_handle, 0, -1) tplan;

    生成的 XML 如下所示：

    <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" ...>
  <QueryStats 
        sql_handle="AgAAABFDQzHOhP02ljxkCQcP9kCIEz5aAAAAAAAAAAAAAAAAAAAAAAAAAAA=" 
        statement_start_offset="0" statement_end_offset="-1" plan_generation_num="1" 
        plan_handle="BgABABFDQzGQSbH4AQAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ... />
  <PlanAttributes>
    <Attr attribute="set_options" value="251" is_cache_key="1" />
    <Attr attribute="objectid" value="826491665" is_cache_key="1" />
    <Attr attribute="dbid" value="1" is_cache_key="1" />
    <Attr attribute="dbid_execute" value="0" is_cache_key="1" />
    ...
  </PlanAttributes>
  <BatchSequence>
    <Batch>
    ...

    使用 XML DML 和.modify()函数将新元素移植到现有结构中更容易，但这只适用于UPDATE语句，因此在这种情况下它不是一个选项。

    • 1

相关问题