死锁的主要原因是什么，可以预防吗？

Question

George.Palacios

Asked: 2014-08-05 05:05:42 +0800 CST2014-08-05 05:05:42 +0800 CST 2014-08-05 05:05:42 +0800 CST

更新分区表视图更新所有索引

772

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

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

架构如下：

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 不应该只更新必要的表吗？

2 个回答

Voted

Aaron Bertrand · Answer 1 · 2014-08-05T06:10:00+08:00

[注意：也在answers.SQLPerformance.com上回答。]

这些实际上不是分区表，即使它们是分区消除也不会真正用于更新索引，除非所有索引也都是分区对齐的。

由于您使用的是 Express Edition 并且实际上无法使用分区，因此我推荐了一种不同的方法：仅影响 @StartDate / @EndDate 中表示的表的动态更新。您必须两次填充参数列表；一旦有了它们的数据类型——这应该很容易，因为我认为这些已经在某处声明了。

DECLARE 
  @StartDate DATE = '20140605',
  @EndDate   DATE = '20150201';


DECLARE 
  @SQL NVARCHAR(MAX) = N'',
  @baseSQL NVARCHAR(MAX) = N'UPDATE dbo.[Call_$y$] 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));',
  @params NVARCHAR(MAX) = N'@MonthlyDay INT,...,@StartDate DATETIME,@EndDate DATETIME';
  -- you'll need to update the params list 
  -- to reflect all of the params coming in

;WITH y AS 
(
  SELECT y FROM 
  (
    -- all valid years to choose from:
    VALUES(2014),(2015),(2016)
  ) AS y(y)
)
SELECT @SQL += REPLACE(@baseSQL, N'$y$', CONVERT(CHAR(4),y))
  FROM y 
  WHERE y >= YEAR(@StartDate) 
    AND y <= YEAR(@EndDate);

EXEC sp_executesql @SQL,
  @params,
  @MonthlyDay,...,@StartDate,@EndDate;
-- you'll also need to update the list of params here

由于您最多只能跨越两年（请参阅下面的评论），因此您可以通过将 CTE 更改为：

;WITH y AS 
(
  SELECT y = YEAR(@StartDate)
  UNION SELECT YEAR(@EndDate)
)
SELECT @SQL += REPLACE(@baseSQL, N'$y$', CONVERT(CHAR(4),y))
  FROM y;

但是请注意，这仍然会以相同数量的查询结束，并且没有修复其他一些东西（比如针对某些列的非 sargable 子句），这仍然会产生与我原来的完全相同的性能版本，您不必输入实际年份。如果 StartDate 和 EndDate 可能相隔超过 365 天，则对这种“优化”要非常小心。

Paul White · Answer 2 · 2014-08-06T00:03:32+08:00

在本节下方，它继续以完全相同的方式更新视图中的每个表。我不明白这是为什么，因为我Table_Year在查询文本中指定了（表在其上分区）。SQL 不应该只更新必要的表吗？

该视图满足和的所有分区Table_Year要求calStartDate。后一列由UPDATE语句修改，因此查询优化器必须生成一个能够在分区之间移动行的计划。

事实上，在这种情况下，行不能在分区之间移动，因为Table_Year和的年度值之间存在 1:1 的关系calStartDate，但是该推理中涉及的步骤对于优化器来说太不透明了。

的新值calStartDate基于引用变量的复杂表达式。查询计划将被缓存并在变量具有不同值时可以重用，这只是意味着计划必须非常通用的另一个因素。

所有这些考虑导致了一个不具有静态分区消除功能的计划。但是，它确实具有动态分区消除功能：

在阅读方面，Concatenation 正下方的 Filter 运算符字符串都是启动过滤器。他们在执行子树之前评估他们的谓词。如果谓词的计算结果为假，则不执行 Filter 下的子树。

总体效果是，仅访问视图下可以包含符合条件的行（取决于运行时变量值）的表。请注意，执行计划仅显示从其中一个基表读取的行，而所有其他基表的实际执行属性为零；这些运算符在运行时根本没有执行。

在下面的计划片段中，启动过滤器确保只有绿色操作符执行；红色的根本就没有开始：

读端计划片段

在写作方面，每个聚集索引更新运算符右侧的正常（不是“启动”）过滤器确保仅传递当前表的更改。在示例计划中，只有一个聚集索引更新（及其关联的非聚集索引维护运算符）接收任何行：

写端计划片段

更新分区表视图更新所有索引

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

更新分区表视图更新所有索引

2 个回答

相关问题