问题简介

       SQL Server使用表变量进行表关联操作时，当表变量的值较大，并且关联表的数据量非常庞大时，性能表现并不好，其根源在于生成的执行计划低效。

虽然SQL Server官网上说可以通过重新编译等查询提示来改善，但实际上并没有效果。

       但在SQL Server2019（安装SP1）后，提到了一个智能查询处理的优化，尝试进行相关测试验证，发现效果改善明显。

验证过程

     环境说明

       T_ORG_AUTOALLOCLOGDETAIL_L数据量55423811

       T_ORG_AUTOALLOCLOGDETAIL   数据量840300

测试SQL语句如下（以主键字段作为过滤条件）：

        declare @t as udt_inttable

        insert into @t select top 500000 fpkid from T_ORG_AUTOALLOCLOGDETAIL_L order by FPKID desc

        select * from T_ORG_AUTOALLOCLOGDETAIL_L u

        join T_ORG_AUTOALLOCLOGDETAIL v on u.FPKID=v.FID

        join @t t on t.FID=v.FID

测试过程一

SQL Server 2016执行计划  

SQL Server 2019执行计划

执行计划对比

l  SQL Server 2019下采用自适应联接替代了SQL Server2016下的嵌套循环

l  T_ORG_AUTOALLOCLOGDETAIL在SQL Server 2016下扫描的次数为5000000，而在SQL Server2019下只有1次

l  SQL Server 2016下SQL取数执行的时间为7351毫秒，而在SQL Server 2019下只需要522毫秒

小结

       可以看到IO的减少，让性能提升了14倍。

测试过程二

       上面测试的SQL语句是以主键进行关联，很多时候，实际上并非主键字段关联，所以以非主键进行测试验证，看实际情况如何？

进一步测试，改用其他字段进行比较

       测试语句，使用FDETAILTITLE字段选择50条数据作为过滤条件

declare @t as udt_varchartable

insert into @t

select top 50 fid from (select distinct  FDETAILTITLE as fid from T_ORG_AUTOALLOCLOGDETAIL_L) t order by FID desc

select * from T_ORG_AUTOALLOCLOGDETAIL_L u

join T_ORG_AUTOALLOCLOGDETAIL v on u.FPKID=v.FID

join @t t on t.FID=u.FDETAILTITLE

where u.FLOCALEID=2052

SQL Server 2016执行计划

SQL Server 2019执行计划

执行计划对比

       三个主要变化

l  表变量进行计算标量后，sql server 2016下由50行变成了1行，sql server 2019下，依然是50行。这里说明数据已经出现了差异

l  T_ORG_AUTOALLOCLOGDETAIL_L关联时，sql server 2019下通过筛选器，将37235行记录变为788条，而SQL Server 2016依然采用了37235条数据，这就导致嵌套循环的次数在SQL Server 2019下只有SQL Server 2016下2%的成本

l  SQL计划执行的时间，SQL Server 2019由于合理的执行计划，计划执行时间花费了7716毫秒，而SQL Server 2016下花费了188952毫秒

小结

l         可以看到IO的减少，让性能提升了25倍左右。

结论

       从上面比对的结果，可以看到SQL Server2019下，在默认开启deferred_compilation_tv（表变量延迟编译）下，执行计划变得更加高效，性能提升明显。由于产品中使用表变量的地方相对较多，如果发现基于表变量查询导致执行计划低效，可以选择SQL Server 2019。

       alter database scoped configuration set deferred_compilation_tv=on;  --开启，默认是开启

       alter database scoped configuration set deferred_compilation_tv=off;  --关闭

附

l  基于SQL Server2019特性说明参考：

https://docs.microsoft.com/zh-cn/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15

l         查询数据库的配置选项情况（sys. database_scoped_configurations），针对数据设置，备份后，保留设置值。每个选项说明参考：

       https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-2017

SQL Server 2016配置项

SQL Server 2019配置项