George.Palacios's questions

假设我正在运行日志备份，并且该日志备份需要 10 分钟才能完成。在这 10 分钟的窗口中，将运行更多事务。鉴于以下示例，日志备份实际包含哪些事务？

1. 事务 A 提交
2. 交易 B 打开
3. 日志备份开始
4. 交易 C 开启
5. 事务 B 提交
6. 日志备份完成
7. 事务 C 提交

如何确定触发器是否针对 INSERT、UPDATE 或 DELETE 语句？

我已签入sys.triggers，但看不到与触发器触发的操作相关的任何内容。

我已经阅读了许多关于 NOLOCK 或未提交的读取隔离级别如何根据所采用的锁/闩锁起作用的不同观点。

当使用带有 NOLOCK 的 SELECT 或在未提交的读取隔离级别中使用时，唯一的锁是从架构稳定性锁中取出的，还是在查询通过行工作时以滚动方式获得共享锁？（显然这些锁需要立即丢弃）

闩锁呢？当我假设不允许引用正在修改的内存对象时，如何处理内存中的页面？

我发现几个来源表明 ALTER TABLE ... DROP COLUMN 是仅元数据操作。

资源

怎么会这样？DROP COLUMN 期间的数据是否不需要从底层非聚集索引和聚集索引/堆中清除？

此外，为什么Microsoft Docs暗示它是完全记录的操作？

对表所做的修改会被记录并完全恢复。影响大型表中所有行的更改（例如删除列或在某些版本的 SQL Server 上添加具有默认值的 NOT NULL 列）可能需要很长时间才能完成并生成许多日志记录。像影响许多行的任何 INSERT、UPDATE 或 DELETE 语句一样小心运行这些 ALTER TABLE 语句。

作为第二个问题：如果数据没有从底层页面中删除，引擎如何跟踪删除的列？

我有一个运行 SQL Server 2012 的两节点集群。我想在同一个 SQL Server 实例中创建两个可用性组。

我应该为每个 AG 添加一个端点，还是应该允许两个 AG 使用相同的端点？

我已阅读此处的文档，但与多个端点有关的唯一部分与多个 SQL 实例有关

请注意，如果给定计算机上的多个服务器实例需要数据库镜像端点，请为每个端点指定不同的端口号。

所以我最近换了工作——我在我们的新 SQL Server 安装构建脚本中发现的一段代码如下。

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT AUTHORITY\SYSTEM' )
    BEGIN
        DROP LOGIN [NT AUTHORITY\SYSTEM];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\SQLWriter' )
    BEGIN
        DROP LOGIN [NT SERVICE\SQLWriter];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\Winmgmt' )
    BEGIN
        DROP LOGIN [NT SERVICE\Winmgmt];
    END
GO

默认情况下，这些帐户是在 SQL Server 安装过程中创建的。

是否建议删除上述登录名？这会导致任何副作用吗？这些登录名有什么用？

我阅读了我可以删除 NT SERVICE\SQLWriter 和 NT SERVICE\Winmgmt 登录吗？但这还不够具体——我可以看到他们需要什么，但其他的就很少了。他们需要系统管理员访问权限吗？等等

例如（取自配置 Windows 服务帐户和权限）：

Local System 是一个非常高特权的内置帐户。它在本地系统上拥有广泛的权限，并充当网络上的计算机。帐户的实际名称是NT AUTHORITY\SYSTEM。

我应该怎么读这个？应该保留这些“高”特权吗？

假设我们有一个生产系统，其中包含多个使用 TDE 和自生成证书加密的数据库。

我们经常需要获取此数据库并刷新我们的非生产系统。此过程涉及备份源数据库、将其恢复到生产前状态、混淆个人数据和许多其他项目。

这里的关键事实是预生产系统有一份来自生产的加密证书的副本

我正在寻找使这个过程更安全的方法——我对预生产环境中的证书不满意。还有另一种方法可以：

1. 永远不要在任何地方保存未加密的备份
2. 不允许将个人数据从生产系统传播到非生产系统？

我很难理解执行计划中行估计的来源。

粘贴计划链接

declare
@BatchKey INT = 1, @ParentBatchKey INT = 1,
@QuoteRef varchar(50) = 'Q00018249',
@MpanRef varchar(50) = '1425431100004'


SELECT DISTINCT
        ISNULL(c.ContractReference,-1) AS [ContractReference] ,
        ISNULL(d_cd.ContractDetailsKey,-1) AS [ContractDetailsKey] ,
        -1 AccountManagerKey,
        -1 SegmentationKey,
        ISNULL(d_tpi.TpiKey,-1) AS [TpiKey] ,
        ISNULL(d_cu.CustomerKey,-1) AS [CustomerKey] ,
        ISNULL(d_p.ProductKey,-1) AS [ProductKey] ,
        -1 as PayPointKey,
        -1 AS [GspBandingKey], --Not used in Junifer ESOB
        ISNULL(d_pps.[ProductPricingStructureKey],-1) AS [ProductPricingStructureKey],
        ISNULL(d_tou.TouBandingKey,-1) AS [PricingStructureBandingKey],
        -1 AS [VolumePointCategoryKey],
        ISNULL(d_ppc.PowerPeriodCategoryKey,-1) AS [PowerPeriodCategoryKey],
        ISNULL(d_pcat.[PriceComponentAggregationTypeKey],-1) AS [PriceComponentAggregationTypeKey],
        -1 AS [MarginRateBandingKey], --Not used in Junifer ESOB
        -1 AS [DuosUrcBandingKey], --Not used in Junifer ESOB
        -1 AS [ConsumptionToleranceKey],
        ISNULL(d_mp.MeterPointKey,-1) AS [MeterPointKey] ,
        ISNULL(d.DateKey,-1) AS [ForecastDateKey] ,
        -1 AS [ForecastEFADateKey], 
        ISNULL(d_cw.DateKey,-1) AS [ContractWonDateKey] ,
        ISNULL(f.SiteVolumeKwh,0) AS [SiteVolume] ,
        ISNULL(f.GspVolumeKwh,0) AS [GspVolume] ,
        ISNULL(f.NbpVolumeKwh,0) AS [NbpVolume],
        @BatchKey,
        @ParentBatchKey,
        CAST(f.ForecastKey as NVARCHAR(100)) AS [SourceId]
FROM 
        [Electricity].[Forecast] f 
              INNER JOIN Electricity.ContractMeterPoint cmp ON cmp.MeterPointKey = f.MeterPointKey and cmp.ContractKey = f.ContractKey  
              INNER JOIN Electricity.Contract c on c.ContractKey = cmp.ContractKey 
        INNER JOIN Electricity.MeterPoint mp ON mp.MeterPointKey = cmp.MeterPointKey

        --INNER JOIN Electricity.ContractMeterPoint cmp ON cmp.MeterPointKey = mp.MeterPointKey and cmp.ContractKey = c.ContractKey 
        INNER JOIN Electricity.ProductBundle pb ON c.ProductBundleKey = pb.ProductBundleKey
        LEFT JOIN Electricity.Quote q ON c.QuoteKey = q.QuoteKey
        LEFT JOIN Gdf.Tender t ON q.TenderKey = t.TenderKey
        LEFT JOIN Gdf.Customer cu ON q.CustomerKey = cu.CustomerKey
        LEFT JOIN Electricity.ProductBundleAggregationType pbat ON pbat.ProductName = pb.BundleName
        LEFT JOIN Dimensional_DW.DimensionElectricity.Product d_p ON d_p.ProductDurableKey = pb.ProductBundleKey
        LEFT JOIN Dimensional_DW.Dimension.Tpi d_tpi ON d_tpi.TpiDurableKey = c.TpiKey
        LEFT JOIN Dimensional_DW.DimensionElectricity.ProductPricingStructure d_pps ON d_pps.ProductPricingStructureDurableKey = f.PriceStructureKey
        LEFT JOIN Dimensional_DW.DimensionElectricity.TouBanding d_tou ON d_tou.TouBandingDurableKey = f.PriceRateKey
        LEFT JOIN Dimensional_DW.DimensionElectricity.MeterPoint d_mp ON d_mp.MeterPointDurableKey = cmp.MeterPointKey
        LEFT JOIN Dimensional_DW.DimensionElectricity.PriceComponentAggregationType d_pcat ON d_pcat.[TnuosAggregationType] =pbat.[TNUoSAggType] AND d_pcat.[DuosAggregationType] =pbat.[DUoSFixedAvailAggType] AND d_pcat.[DuosUrcAggregationType] =pbat.[DUoSURCAggType] AND d_pcat.[BsuosAggregationType] =pbat.[BSUoSAggType] AND d_pcat.[ROAggregationType] =pbat.[ROAggType]
        LEFT JOIN Dimensional_DW.Dimension.Date AS d ON d.DateKey = CAST(CONVERT(NVARCHAR(8), f.DeliveryDate, 112) AS INT) 
        LEFT JOIN Dimensional_DW.Dimension.Date AS d_cw ON d_cw.DateKey = CAST(CONVERT(NVARCHAR(8), c.QuoteWonDate, 112) AS INT) 
        LEFT JOIN Dimensional_DW.DimensionElectricity.PowerPeriodCategory d_ppc ON d_ppc.HhPeriod = f.Period
        LEFT JOIN Dimensional_DW.Dimension.Customer d_cu ON d_cu.CustomerDurableKey = cu.CustomerKey
        LEFT JOIN Dimensional_DW.DimensionElectricity.ContractDetails d_cd ON d_cd.ContractDetailsDurableKey = c.ContractKey

WHERE  1=1
   and     c.ContractReference = @QuoteRef
   and c.QuoteWonDate IS NOT NULL 
   and c.QuoteKey <> -1
           --(SELECT distinct C.ContractKey FROM Electricity.Contract WHERE ContractReference = @QuoteRef and c.QuoteWonDate IS NOT NULL and c.QuoteKey <> -1)
                --(SELECT distinct C1.ContractKey FROM Electricity.Contract c1 WHERE c1.ContractReference = @QuoteRef and c1.QuoteWonDate IS NOT NULL and c1.QuoteKey <> -1)
        and mp.MpanID = @MpanRef
              --and c.ContractKey = 18235
              --and d.DateKey =  20180522
              order by [ForecastDateKey]

我的问题是关于标量运算符 nodeId 26：

我不确定如何生成 5 的行估计 - 这似乎随后将计划级联到大多数其他运营商 - 嵌套循环运营商估计执行计数进一步下降计划似乎都表明估计为 ~5，然后 ~ 35k实际。

为什么要向标量运算符提供约 14000 行的估计，然后估计输出为 5？这是问题还是转移注意力？这与它正在执行的转换有什么关系吗？我可以理解这会影响连接，但为什么会影响转换的输出？

在过去一天左右的时间里，我一直在为这个问题绞尽脑汁——我只是不明白为什么一个过程在一个环境中工作，但由于转换错误（相同的数据，相同的代码）而在另一个环境中失败。

工作服务器的（大幅缩减版本）计划在这里：https ://www.brentozar.com/pastetheplan/?id=B1jZWTfOf

（这似乎不起作用，所以我已将计划 XML 上传到此处的 pastebin：https ://pastebin.com/47Q6nniw ）

导致问题的相关列的一些背景：

propertyInst 表包含一个名为 ValueStr 的列，它是 nvarchar 数据类型。GeneralLedgerCode 表有一个数据类型为 int 的列 id。我们的开发人员正在尝试连接两个表。ValueStr 列包含文本数据、XML 数据、int 数据、十进制数据等。理所当然地，我们决定添加一个函数以确保只解析整数 (ISNUMERIC(ValueStr) = 1)。我们没有意识到的是，这也会尝试转换任何十进制值——这会导致自然生产中的转换错误：

将 nvarchar 值“0.1”转换为数据类型 int 时转换失败。

我的问题是，鉴于 propertyInst 表的表扫描将获取包括小数在内的所有数值，为什么标量运算符中的隐式转换不会因相同的转换错误而失败？ISNUMERIC 查询的输出返回其中一些小数。我根本看不出附加的计划是如何运作的。

隐式转换运算符是否永远不会完全失败，转换错误是否来自散列匹配运算符中的探测残差？再一次，当隐式转换失败时，这个值怎么可能传给运算符呢？

作为一个小小的帮助，这里提供了计划图像，其中圈出了我要询问的计算标量。

此外，我可以看到没有针对标量运算符记录的实际行——这是否意味着由于转换错误，引擎决定在运行时不使用该特定运算符？

任何帮助，将不胜感激。

所以我有一个我正在尝试优化的执行计划，并且有一段我不太理解的运算符。

计划的一部分使用了计算标量，然后是使用哈希匹配的聚合。

我有一个问题：

我如何使用哈希匹配聚合器的属性来告诉我它正在输出哪些列？我无法判断正在对哪些列执行聚合。

这是计划的一部分：

这是运算符属性：

为元素规划 XML：

<RelOp NodeId="489" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="25432.8" EstimateIO="0" EstimateCPU="621.202" AvgRowSize="1038" EstimatedTotalSubtreeCost="12113" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList>
                    <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
                    <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="description" />
                    <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="unitPrice" />
                    <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="units" />
                    <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="billCurrencyAmount" />
                    <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
                    <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
                    <ColumnReference Column="Expr1066" />
                    <ColumnReference Column="Expr1067" />
                    <ColumnReference Column="Expr1068" />
                    <ColumnReference Column="Expr1069" />
                    <ColumnReference Column="Expr1070" />
                    <ColumnReference Column="Expr1071" />
                    <ColumnReference Column="Expr1072" />
                    <ColumnReference Column="Expr1073" />
                    <ColumnReference Column="Expr1074" />
                    <ColumnReference Column="Expr1075" />
                    <ColumnReference Column="Expr1076" />
                    <ColumnReference Column="Expr1077" />
                    <ColumnReference Column="Expr1078" />
                    <ColumnReference Column="Expr1079" />
                  </OutputList>
                  <MemoryFractions Input="0.00395919" Output="0.379612" />
                  <Hash>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="description" />
                        <ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[description] as [bbl].[description])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="description" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="unitPrice" />
                        <ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[unitPrice] as [bbl].[unitPrice])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="unitPrice" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="units" />
                        <ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[units] as [bbl].[units])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="units" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="billCurrencyAmount" />
                        <ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[billCurrencyAmount] as [bbl].[billCurrencyAmount])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="billCurrencyAmount" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Expr1077" />
                        <ScalarOperator ScalarString="ANY([Expr1077])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1077" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Expr1078" />
                        <ScalarOperator ScalarString="ANY([Expr1078])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1078" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Expr1079" />
                        <ScalarOperator ScalarString="ANY([Expr1079])">
                          <Aggregate Distinct="0" AggType="ANY">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1079" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <HashKeysBuild>
                      <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
                      <ColumnReference Column="Expr1066" />
                      <ColumnReference Column="Expr1067" />
                      <ColumnReference Column="Expr1068" />
                      <ColumnReference Column="Expr1069" />
                      <ColumnReference Column="Expr1070" />
                      <ColumnReference Column="Expr1071" />
                      <ColumnReference Column="Expr1072" />
                      <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
                      <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
                      <ColumnReference Column="Expr1073" />
                      <ColumnReference Column="Expr1074" />
                      <ColumnReference Column="Expr1075" />
                      <ColumnReference Column="Expr1076" />
                    </HashKeysBuild>
                    <BuildResidual>
                      <ScalarOperator ScalarString="[Staging].[Junifer].[BillBreakdownLine].[id] as [bbl].[id] = [Staging].[Junifer].[BillBreakdownLine].[id] as [bbl].[id] AND [Expr1066] = [Expr1066] AND [Expr1067] = [Expr1067] AND [Expr1068] = [Expr1068] AND [Expr1069] = [Expr1069] AND [Expr1070] = [Expr1070] AND [Expr1071] = [Expr1071] AND [Expr1072] = [Expr1072] AND #BillLineCategorisation.[Item] as [blc].[Item] = #BillLineCategorisation.[Item] as [blc].[Item] AND #BillLineCategorisation.[InvoiceLineCategory] as [blc].[InvoiceLineCategory] = #BillLineCategorisation.[InvoiceLineCategory] as [blc].[InvoiceLineCategory] AND [Expr1073] = [Expr1073] AND [Expr1074] = [Expr1074] AND [Expr1075] = [Expr1075] AND [Expr1076] = [Expr1076]">
                        <Logical Operation="AND">

                                  ...                                                                             <Logical Operation="AND">
                                                                          <ScalarOperator>
                                                                            <Compare CompareOp="IS">
                                                                              <ScalarOperator>
                                                                                <Identifier>
                                                                                  <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
                                                                                </Identifier>
                                                                              </ScalarOperator>
                                                                              <ScalarOperator>
                                                                                <Identifier>
                                                                                  <ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
                                                                                </Identifier>
                                                                              </ScalarOperator>
                                                                            </Compare>
                                                                          </ScalarOperator>
                                                                          <ScalarOperator>
                                                                            <Compare CompareOp="IS">
                                                                              <ScalarOperator>
                                                                                <Identifier>
                                                                                  <ColumnReference Column="Expr1066" />
                                                                                </Identifier>
                                                                              </ScalarOperator>
                                                                              <ScalarOperator>
                                                                                <Identifier>
                                                                                  <ColumnReference Column="Expr1066" />
                                                                                </Identifier>
                                                                              </ScalarOperator>
                                                                            </Compare>
                                                                          </ScalarOperator>
                                                                        </Logical>
                                                                      </ScalarOperator>
                                                                      <ScalarOperator>
                                                                        <Compare CompareOp="IS">
                                                                          <ScalarOperator>
                                                                            <Identifier>
                                                                              <ColumnReference Column="Expr1067" />
                                                                            </Identifier>
                                                                          </ScalarOperator>
                                                                          <ScalarOperator>
                                                                            <Identifier>
                                                                              <ColumnReference Column="Expr1067" />
                                                                            </Identifier>
                                                                          </ScalarOperator>
                                                                        </Compare>
                                                                      </ScalarOperator>
                                                                    </Logical>
                                                                  </ScalarOperator>
                                                                  <ScalarOperator>
                                                                    <Compare CompareOp="IS">
                                                                      <ScalarOperator>
                                                                        <Identifier>
                                                                          <ColumnReference Column="Expr1068" />
                                                                        </Identifier>
                                                                      </ScalarOperator>
                                                                      <ScalarOperator>
                                                                        <Identifier>
                                                                          <ColumnReference Column="Expr1068" />
                                                                        </Identifier>
                                                                      </ScalarOperator>
                                                                    </Compare>
                                                                  </ScalarOperator>
                                                                </Logical>
                                                              </ScalarOperator>
                                                              <ScalarOperator>
                                                                <Compare CompareOp="IS">
                                                                  <ScalarOperator>
                                                                    <Identifier>
                                                                      <ColumnReference Column="Expr1069" />
                                                                    </Identifier>
                                                                  </ScalarOperator>
                                                                  <ScalarOperator>
                                                                    <Identifier>
                                                                      <ColumnReference Column="Expr1069" />
                                                                    </Identifier>
                                                                  </ScalarOperator>
                                                                </Compare>
                                                              </ScalarOperator>
                                                            </Logical>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Compare CompareOp="IS">
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Column="Expr1070" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Column="Expr1070" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                            </Compare>
                                                          </ScalarOperator>
                                                        </Logical>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Compare CompareOp="IS">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Column="Expr1071" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Column="Expr1071" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                        </Compare>
                                                      </ScalarOperator>
                                                    </Logical>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Compare CompareOp="IS">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1072" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1072" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Logical>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Compare CompareOp="IS">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Compare>
                                              </ScalarOperator>
                                            </Logical>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Compare CompareOp="IS">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="IS">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="Expr1073" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="Expr1073" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="IS">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="Expr1074" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="Expr1074" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Compare CompareOp="IS">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1075" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1075" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Logical>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="Expr1076" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="Expr1076" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Logical>
                      </ScalarOperator>
                    </BuildResidual>

我们有一个表，它通过日期字段划分为不同的年份。

可以查看所有这些表（调用）

架构如下：

CREATE TABLE [dbo].[Call_2015](
    [calID] [uniqueidentifier] NOT NULL,
    [calPackageID] [int] NULL,
    [calClientID] [int] NULL,
    [calStartDate] [datetime] NOT NULL,
    [calEndDate] [datetime] NOT NULL,
    [calTimeIn] [char](5) NULL,
    [calTimeOut] [char](5) NULL,
    [calMinutes] [smallint] NULL,
    [calPreferredTimeIn] [char](5) NULL,
    [calPreferredTimeOut] [char](5) NULL,
    [calActualTimeIn] [char](5) NULL,
    [calActualTimeOut] [char](5) NULL,
    [calActualMinutes] [smallint] NULL,
    [calConfirmed] [smallint] NULL,
    [calCarerID] [int] NULL,
    [calRepCarerID] [int] NULL,
    [calOriginalCarerID] [int] NULL,
    [calContractID] [int] NULL,
    [calNeedID] [int] NULL,
    [calMedicationID] [int] NULL,
    [calFrequency] [smallint] NULL,
    [calFromDate] [datetime] NULL,
    [calWeekNo] [smallint] NULL,
    [calAlert] [smallint] NULL,
    [calNoLeave] [smallint] NULL,
    [calTimeCritical] [smallint] NULL,
    [calStatus] [smallint] NULL,
    [calClientAwayReasonID] [int] NULL,
    [calCarerAwayReasonID] [int] NULL,
    [calOutsideShift] [smallint] NULL,
    [calHistoryID] [int] NULL,
    [calInvoiceID] [int] NULL,
    [calWagesheetID] [int] NULL,
    [calReasonID] [int] NULL,
    [calCallConfirmID] [varchar](50) NULL,
    [calCreated] [datetime] NULL,
    [calUpdated] [datetime] NULL,
    [calVariation] [int] NULL,
    [calVariationUserID] [int] NULL,
    [calException] [smallint] NULL,
    [calRetained] [smallint] NULL,
    [calDoubleUpID] [uniqueidentifier] NULL,
    [calDoubleUpOrder] [smallint] NULL,
    [calNeedCount] [smallint] NULL,
    [calNoStay] [smallint] NULL,
    [calCoverCarerID] [int] NULL,
    [calPayAdjustment] [real] NULL,
    [calChargeAdjustment] [real] NULL,
    [calTeamID] [int] NULL,
    [calExpenses] [money] NULL,
    [calMileage] [real] NULL,
    [calOverrideStatus] [smallint] NULL,
    [calLocked] [smallint] NULL,
    [calDriver] [smallint] NULL,
    [calPostcode] [char](10) NULL,
    [calDayCentreID] [int] NULL,
    [calMustHaveCarer] [smallint] NULL,
    [calRoleID] [int] NULL,
    [calUnavailableCarerID] [int] NULL,
    [calClientInformed] [smallint] NULL,
    [calFamilyInformed] [smallint] NULL,
    [calMonthlyDay] [smallint] NULL,
    [calOriginalTimeIn] [char](5) NULL,
    [calLeadCarer] [smallint] NULL,
    [calCallTypeID] [int] NULL,
    [calActualStartDate] [datetime] NULL,
    [calActualEndDate] [datetime] NULL,
    [Table_Year] [int] NOT NULL,
 CONSTRAINT [PK_Call_2015] PRIMARY KEY CLUSTERED 
(
    [Table_Year] ASC,
    [calID] ASC,
    [calStartDate] ASC,
    [calEndDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Call_2015]  WITH CHECK ADD  CONSTRAINT [CK_Call_Year_2015] CHECK  (([Table_Year]=(2015)))
GO

ALTER TABLE [dbo].[Call_2015] CHECK CONSTRAINT [CK_Call_Year_2015]
GO

ALTER TABLE [dbo].[Call_2015]  WITH CHECK ADD  CONSTRAINT [CK_calStartDate_2015] CHECK  (([calStartDate]>=CONVERT([datetime],'01 Jan 2015 00:00:00',(0)) AND [calStartDate]<=CONVERT([datetime],'31 DEC 2015 23:59:59',(0))))
GO

ALTER TABLE [dbo].[Call_2015] CHECK CONSTRAINT [CK_calStartDate_2015]
GO

ALTER TABLE [dbo].[Call_2015] ADD  CONSTRAINT [DF_Call_2015_Table_Year]  DEFAULT ((2015)) FOR [Table_Year]
GO

表的更新如下：

UPDATE Call SET
        calStartDate = CASE 
            WHEN calFrequency = 14 THEN dbo.funDate(@MonthlyDay, MONTH(calStartDate), YEAR(calStartDate))
            WHEN calFrequency IN (15,16) THEN dbo.funMonthlyCallDate(calFrequency, @MonthlyDay, calStartDate)
            ELSE DateAdd(d, @StartDay-1, (calStartDate - datepart(dw,calStartDate)+1)) 
        END,
        calEndDate = CASE 
            WHEN calFrequency = 14 THEN dbo.funDate(@MonthlyDay + @EndDay - @StartDay, MONTH(calStartDate), YEAR(calStartDate))
            WHEN calFrequency IN (15,16) THEN DATEADD(D, @EndDay - @StartDay, dbo.funMonthlyCallDate(calFrequency, @MonthlyDay, calStartDate))
            ELSE DateAdd(d, @StartDay-1+@DayCount, (calStartDate - datepart(dw,calStartDate)+1)) 
        END,
        calTimeIn = @TimeIn,
        calTimeOut = @TimeOut,
        calMinutes = @Minutes,
        calMonthlyDay = @MonthlyDay,
        calClientInformed = Null, 
        calFamilyInformed = Null
    WHERE calPackageID = @PackageID
    AND calClientID = @ClientID
    AND calWeekNo = @WeekNo
    AND (DatePart(dw, calStartDate) = @OriginalDay OR calFrequency IN (14,15,16))
    AND calStartDate BETWEEN @StartDate AND @EndDate
    AND (calInvoiceID = 0 OR calInvoiceID Is Null OR @InvoicesFinalised = 1)
    AND (calWagesheetID = 0 OR calWagesheetID Is Null OR @WagesFinalised = 1)
    AND (calLocked = 0 OR calLocked Is Null)
    AND (Table_Year = YEAR(@StartDate) 
            OR Table_Year =YEAR(@EndDate))

SP 更新一批依赖于输入到@StartDate 和@EndDate 的行（用两者之间的 calStartDate 更新所有行）

然后问题就出现在执行计划上。该操作有巨大的 IO 成本，我已经将其确定为 SQL 如何处理更新。

目前我们有 20 个这样的表；每年分区。每次更新都会导致对每个表的索引进行更新，无论表是否实际被更新操作触及。

执行计划

在本节下方，它继续以完全相同的方式更新视图中的每个表。

我不明白这是为什么，因为我在查询文本中指定了 Table_Year（表在其上分区）。SQL 不应该只更新必要的表吗？