从Sql Server 2008升级到Sql Server 2016之后，快速存储过程现在变得很慢

Question

我们有一个存储过程，它返回地理空间区域（“地理”）内的所有记录。它使用一个CTE（with），一些联合体，一些内部联接并将数据作为XML返回;没有什么争议或尖端的，但也不是微不足道的。

这个存储过程在SQL Server 2008上很好的服务了我们很多年，它在一个相对较慢的服务器上运行了1秒。我们刚刚在具有大量内存和超快速SDD的超高速服务器上迁移到SQL Server 2016。

整个数据库和相关的应用程序在这台新服务器上快速，我们对此非常满意。然而，这个存储过程运行16秒而不是1秒 - 与完全相同的参数和完全相同的数据集相对应。

我们更新了此数据库的索引和统计数据。我们还将数据库的兼容级别从100更改为130.

有趣的是，我重写了存储过程以使用临时表并'插入'而不是使用CTE。这将时间从16秒缩短到4秒。

执行计划没有提供任何明显的洞察可能出现的瓶颈。

我们对想法有点卡住。我们接下来应该做什么？提前致谢。

-

现在我花了更多的时间比我愿意承认这个问题。我将存储过程归结为以下查询以演示该问题。

drop table #T 

declare @viewport sys.geography=convert(sys.geography,0xE610000001041700000000CE08C22D7740C002370B7670F4624000CE08C22D7740C002378B5976F4624000CE08C22D7740C003370B3D7CF4624000CE08C22D7740C003378B2082F4624000CE08C22D7740C003370B0488F4624000CE08C22D7740C004378BE78DF4624000CE08C22D7740C004370BCB93F4624000CE08C22D7740C004378BAE99F4624000CE08C22D7740C005370B929FF4624000CE08C22D7740C005378B75A5F4624000CE08C22D7740C005370B59ABF462406F22B7698E7640C005370B59ABF462406F22B7698E7640C005378B75A5F462406F22B7698E7640C005370B929FF462406F22B7698E7640C004378BAE99F462406F22B7698E7640C004370BCB93F462406F22B7698E7640C004378BE78DF462406F22B7698E7640C003370B0488F462406F22B7698E7640C003378B2082F462406F22B7698E7640C003370B3D7CF462406F22B7698E7640C002378B5976F462406F22B7698E7640C002370B7670F4624000CE08C22D7740C002370B7670F4624001000000020000000001000000FFFFFFFF0000000003) 

declare @outputControlParameter nvarchar(max) = 'a value passed in through a parameter to the stored that controls the nature of data to return. This is not the solution you are looking for' 

create table #T 
    (value int) 

insert into #T 
select 136561 union 
select 16482 -- These values are sourced from parameters into the stored proc 

select 
     [GeoServices_Location].[GeographicServicesGatewayId], 
     [GeoServices_Location].[Coordinate].Lat, 
     [GeoServices_Location].[Coordinate].Long 

     from GeoServices_Location 

     inner join GeoServices_GeographicServicesGateway 
      on GeoServices_Location.GeographicServicesGatewayId = GeoServices_GeographicServicesGateway.GeographicServicesGatewayId 

     where 
     (
      (len(@outputControlParameter) > 0 and GeoServices_Location.GeographicServicesGatewayId in (select value from #T)) 
      or (len(@outputControlParameter) = 0 and GeoServices_Location.Coordinate.STIntersects(@viewport) = 1) 
     ) 
     and GeoServices_GeographicServicesGateway.PrimarilyFoundOnLayerId IN (3,8,9,5) 

GO 

随着存储过程归结为这一点，它运行在0秒SQL Server 2008上和5秒的SQL Server 2016年

http://www.filedropper.com/newserver-slowexecutionplan

http://www.filedropper.com/oldserver-fastexecutionplan

的Windows Server 2016地理空间相交呼叫以94％的时间花在那里，令人窒息。 Sql Server 2008与其他一些步骤一起花费时间，包括Hash Matching和Parallelism以及其他标准的东西。

请记住，这是相同的数据库。一个刚刚被复制到SQL Server 2016机器，并且其兼容性级别提高了。

为了解决这个问题，我实际上重写了存储过程，以便Sql Server 2016不会窒息。我已经跑了250毫秒。然而，这不应该发生在第一个地方，我担心有其他以前精细调整的查询或存储过程，现在不能有效地运行。

在此先感谢。

-

另外，我有一个建议，添加跟踪标志-T6534启动该服务的参数。它对查询时间没有影响。另外我也尝试添加选项（QUERYTRACEON 6534）到查询的结尾，但它再次没有区别。

Answer 1

1. 检查数据的增长/登录新服务器（DBS）与旧服务器（DBS）配置上的文件：数据库的查询上+ tempdb中
2. 检查运行日志的I/O缓冲器错误
3. 检查数据库的恢复模型 - 简单vs完整/批量
4. 这是一个一致的行为？在执行过程中可能正在运行一个进程？
5. 关于统计/索引 - 你确定它运行在正确的数据样本上吗？ （看计划）

还有很多事情可以检查/完成 - 但这个问题没有足够的信息。

Answer 2

根据您提供的查询计划，我发现空间索引没有用于较新的服务器版本。 使用空间索引提示，以确保查询优化器选择与空间索引的计划：

select 
    [GeoServices_Location].[GeographicServicesGatewayId], 
    [GeoServices_Location].[Coordinate].Lat, 
    [GeoServices_Location].[Coordinate].Long 
from GeoServices_Location with (index ([spatial_index_name]))... 

我看到有提示问题或查询谓词操作，所以我有一丝的建议实际上也无济于事这个案例。 但是，我发现谓词依赖于@outputControlParameter，所以重写查询以便将这两种情况分开可能有所帮助（请参阅下面的提议）。 另外，从您的查询计划中，我发现SQL 2008上的查询计划是并行的，而SQL 2016是串行的。使用选项（重新编译，querytraceon 8649）强制执行并行计划（如果新的超高速服务器拥有更多的核心，那么旧的服务器会有更多的核心）。

if (len(@outputControlParameter) > 0) 
    select 
    [GeoServices_Location].[GeographicServicesGatewayId], 
    [GeoServices_Location].[Coordinate].Lat, 
    [GeoServices_Location].[Coordinate].Long 

    from GeoServices_Location 

    inner join GeoServices_GeographicServicesGateway 
    on GeoServices_Location.GeographicServicesGatewayId = GeoServices_GeographicServicesGateway.GeographicServicesGatewayId 

    where 
    GeoServices_Location.GeographicServicesGatewayId in (select value from #T)) 
    and GeoServices_GeographicServicesGateway.PrimarilyFoundOnLayerId IN(3,8,9,5) 
    option (recompile, querytraceon 8649) 
else 
    select 
    [GeoServices_Location].[GeographicServicesGatewayId], 
    [GeoServices_Location].[Coordinate].Lat, 
    [GeoServices_Location].[Coordinate].Long 

    from GeoServices_Location with (index ([SPATIAL_GeoServices_Location])) 

    inner join GeoServices_GeographicServicesGateway 
    on GeoServices_Location.GeographicServicesGatewayId = GeoServices_GeographicServicesGateway.GeographicServicesGatewayId 

    where 
    GeoServices_Location.Coordinate.STIntersects(@viewport) = 1 
    and GeoServices_GeographicServicesGateway.PrimarilyFoundOnLayerId IN (3,8,9,5) 
    option (recompile, querytraceon 8649)