Atualização na exibição em tabelas particionadas atualizando todos os índices

[Observação: também respondido em answers.SQLPerformance.com .]

Na verdade, essas não são tabelas particionadas e, mesmo que fossem, a eliminação de partições não funcionaria realmente para atualizar índices, a menos que todos os índices também fossem alinhados por partição.

Como você está usando o Express Edition e não pode realmente usar o particionamento, tenho uma abordagem diferente para recomendar: UPDATEs dinâmicos que afetam apenas a(s) tabela(s) representada(s) em @StartDate / @EndDate. Você terá que preencher a lista de parâmetros duas vezes; uma vez com seus tipos de dados - isso deve ser fácil, pois presumo que eles já estejam declarados em algum lugar.

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

Como você pode abranger no máximo dois anos (veja os comentários abaixo), você pode simplificar um pouco alterando o CTE para:

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

Mas observe que isso ainda terminará com o mesmo número de consultas a serem executadas e sem corrigir algumas das outras coisas (como as cláusulas não sargáveis ​​em algumas das colunas), isso ainda produzirá exatamente o mesmo desempenho que meu original versão, você simplesmente não precisava digitar os anos reais. Tenha muito cuidado com essa "otimização" se for possível que StartDate e EndDate tenham mais de 365 dias de diferença.

Abaixo desta seção, ele atualiza, exatamente da mesma maneira, todas as tabelas na exibição. Não consigo ver por que isso ocorre, pois especifiquei o Table_Year(no qual a tabela está particionada) no texto da consulta. O SQL não deveria atualizar apenas a tabela necessária?

A exibição atende a todos os requisitos de particionamento para arquivos Table_Yeare calStartDate. A última coluna é modificada pela UPDATEinstrução para que o otimizador de consulta tenha que produzir um plano capaz de mover linhas entre as partições.

Na verdade, as linhas não poderiam se mover entre as partições neste caso porque há uma relação de 1:1 entre Table_Yearos valores anuais de calStartDate, mas as etapas envolvidas nesse raciocínio são muito opacas para o otimizador.

O novo valor para calStartDateé baseado em uma expressão complexa que faz referência a variáveis. O plano de consulta será armazenado em cache e poderá ser reutilizado quando as variáveis ​​tiverem valores diferentes, que é apenas outro fator que significa que o plano deve ser muito geral.

Todas essas considerações levam a um plano que não apresenta eliminação de partições estáticas. No entanto, apresenta eliminação de partição dinâmica :

No lado da leitura, a cadeia de operadores Filter imediatamente abaixo da Concatenation são todos filtros de inicialização . Eles avaliam seu predicado antes que a subárvore seja executada. Se o predicado for avaliado como falso, a subárvore sob o Filtro não será executada.

O efeito geral é que apenas as tabelas sob a exibição que podem conter linhas qualificadas (dependendo dos valores das variáveis ​​de tempo de execução) são acessadas. Observe que o plano de execução mostra apenas as linhas sendo lidas de uma das tabelas base e a propriedade Execuções reais para todas as outras tabelas base é zero; esses operadores não foram executados em tempo de execução.

No fragmento do plano abaixo, os filtros de inicialização garantem que apenas os operadores verdes sejam executados; os vermelhos nunca começam:

No lado da escrita, o filtro normal (não 'inicialização') logo à direita de cada operador Clustered Index Update garante que apenas as alterações da tabela atual sejam transmitidas. No plano de exemplo, apenas uma atualização de índice clusterizado (e seus operadores de manutenção de índice não clusterizados associados) recebe qualquer linha: