Mark Sinkinson's questions

Estamos vendo muitos desses deadlocks de threads paralelos intra-consulta em nosso ambiente de produção (SQL Server 2012 SP2 - sim... eu sei...), no entanto, ao analisar o XML de deadlock que foi capturado por meio de eventos estendidos, a lista de vítimas está vazia.

<victim-list />

O deadlock parece estar entre 4 threads, dois com o WaitType="e_waitPipeNewRow"e dois com o WaitType="e_waitPipeGetRow".

 <resource-list>
  <exchangeEvent id="Pipe13904cb620" WaitType="e_waitPipeNewRow" nodeId="19">
   <owner-list>
    <owner id="process4649868" />
   </owner-list>
   <waiter-list>
    <waiter id="process40eb498" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe30670d480" WaitType="e_waitPipeNewRow" nodeId="21">
   <owner-list>
    <owner id="process368ecf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process46a0cf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe13904cb4e0" WaitType="e_waitPipeGetRow" nodeId="19">
   <owner-list>
    <owner id="process40eb498" />
   </owner-list>
   <waiter-list>
    <waiter id="process368ecf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe4a106e060" WaitType="e_waitPipeGetRow" nodeId="21">
   <owner-list>
    <owner id="process46a0cf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process4649868" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>

Então:

1. A Lista de Vítimas está vazia
2. O aplicativo que executa a consulta não apresenta erro e conclui a consulta
3. Até onde podemos ver, não há nenhum problema óbvio, exceto que o gráfico é capturado

Portanto, isso é algo para se preocupar além do ruído?

Editar: Graças à resposta de Paul, posso ver onde o problema provavelmente ocorre e parece se resolver com o vazamento de tempdb.

Estou tentando entender como a amostragem de estatísticas funciona e se o comportamento abaixo é esperado ou não nas atualizações de estatísticas de amostra.

Temos uma grande tabela particionada por data com alguns bilhões de linhas. A data de partição é a data comercial anterior e, portanto, é uma chave crescente. Carregamos apenas dados nesta tabela para o dia anterior.

O carregamento de dados é executado durante a noite, portanto, na sexta-feira, 8 de abril, carregamos dados para o dia 7.

Após cada execução, atualizamos as estatísticas, embora façamos uma amostra, em vez de um arquivo FULLSCAN.

Talvez eu esteja sendo ingênuo, mas esperava que o SQL Server identificasse a chave mais alta e a chave mais baixa no intervalo para garantir que obtivesse uma amostra de intervalo precisa. De acordo com este artigo :

Para o primeiro balde, o limite inferior é o menor valor da coluna na qual o histograma é gerado.

No entanto, ele não menciona o último intervalo/maior valor.

Com a atualização das Estatísticas amostradas na manhã do dia 8, a amostra perdeu o valor mais alto da tabela (dia 7).

Como fazemos muitas consultas nos dados do dia anterior, isso resultou em uma estimativa de cardinalidade imprecisa e em várias consultas com tempo limite esgotado.

O SQL Server não deve identificar o valor mais alto para essa chave e usá-lo como o máximo RANGE_HI_KEY? Ou este é apenas um dos limites de atualização sem uso FULLSCAN?

Versão SQL Server 2012 SP2-CU7. No momento, não podemos atualizar devido a uma mudança de OPENQUERYcomportamento no SP3 que estava arredondando para baixo os números em uma consulta de servidor vinculada entre o SQL Server e o Oracle.

Deparamos com o seguinte erro quando uma consulta é executada em uma tabela particionada com grupos de arquivos somente leitura:

O índice "ix_my_index" da tabela "dbo.my_table" (RowsetId 72057600379912192) reside em um grupo de arquivos somente leitura ("MyFileGroup"), que não pode ser modificado

Como eu identificaria qual linha a consulta está tentando modificar no RowsetID?

Eu sei sysrowsetsque existia, mas não parece no SQL Server 2012.

Estou ajustando uma consulta no SQL Server 2012 que está vazando para tempdb devido a uma estimativa de cardinalidade incorreta ao usar a DATENAME()função em uma coluna para verificar se os dados são de fim de semana ou dia da semana. Devido ao uso da função, a consulta não pode ser sargável e estima incorretamente o número de linhas (est 1700, real 38000).

A cláusula where era simplesmente:

WHERE DATENAME(WEEKDAY, vqc.DateRecorded) NOT IN ('Saturday', 'Sunday')

Eu esperava usar uma coluna computada persistente e, em seguida, indexá-la:

ALTER TABLE <table_name> ADD IsWeekend AS DATENAME(WeekDay, DateRecorded) PERSISTED;

No entanto, obtenha o erro:

A coluna calculada 'IsWeekend' na tabela não pode ser mantida porque a coluna não é determinística.

De acordo com BOL , um grande número de funções Date não pode ser persistido porque elas não são determinísticas.

Isso ocorre porque os resultados dependem das configurações LANGUAGE e DATEFORMAT da sessão do servidor. Por exemplo, os resultados da expressão CONVERT (datetime, '30 listopad 1996', 113) dependem da configuração LANGUAGE porque a string '30 listopad 1996' significa meses diferentes em idiomas diferentes. Da mesma forma, na expressão DATEADD(mm,3,'2000-12-01'), o Mecanismo de Banco de Dados interpreta a string '2000-12-01' com base na configuração DATEFORMAT.

Havia uma maneira interessante de contornar esse problema para a MONTH()função usando uma CASEinstrução que foi postada no Stack Overflow , mas isso não funciona DATENAMEdevido a SET DATEFIRSTe à LANGUAGEconfiguração.

Além de ingressar em uma tabela de calendário em minha consulta e, em seguida, filtrar os fins de semana a partir daí, existe uma maneira de determinar se uma data é o fim de semana para que possa ser mantida e indexada?

Ou estou tentando complicar demais a situação?

Tenho notado o que considero problemas incomuns com nosso cache de plano de consulta, em que os planos no cache nunca tiveram mais de um dia.

Através da execução da consulta a seguir (cortesia de Kimberly Tripp ), ele mostrou que a maioria dos planos (4,5 Gb de planos em cache de 6 Gb ou 44813 de ~ 50000) eram consultas Ad-hoc que tinham uma contagem de uso de 1.

SELECT objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
    , avg(usecounts) AS [Avg Use Count]
    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

Eu determinei a consulta do problema (dinâmica, com um EXEC, é claro...), que é bastante hedionda, mas também complicada de apenas 'consertar', então eu estava procurando melhorias imediatas que poderiam ser feitas.

A instância já está configurada para usar o Optimize for Ad-hoc Workloads, no entanto, todos os CacheObjTypede sys.dm_exec_cached_planssão Plano Compilado em vez de Stub de Plano Compilado .

Ao usar o modo Ad-hoc Workload, os planos não devem ser Compiled Plan Stub até usecountsque sejam maiores que 1? Ou não é assim que funciona?

Há também um refcountscampo ao qual ninguém parece se referir ao falar sobre consultas Adhoc. O refcounts é sempre 1 quando o tipo é Compiled Plan Stub e 2 quando o tipo é Compiled Plan . Mesmo lendo BOL , não tenho certeza do que esse campo significa. Alguém pode esclarecer?

Tenho mantido um registro de consultas caras em execução, juntamente com seus planos de consulta, em uma tabela para nos permitir monitorar tendências de desempenho e identificar áreas que precisam ser otimizadas.

No entanto, chegou ao ponto em que os planos de consulta estão ocupando muito espaço (já que estamos armazenando o plano inteiro em cada consulta).

Estou, portanto, tentando normalizar os dados existentes extraindo o QueryPlanHash e o QueryPlan para outra tabela.

CREATE TABLE QueryPlans
(
    QueryPlanHash VARBINARY(25),
    QueryPlan XML,
    CONSTRAINT PK_QueryPlans PRIMARY KEY
    (
      QueryPlanHash
    )
);

Como a definição de query_plan_hashin sys.dm_exec_query_statsé um campo binário (e estarei inserindo novos dados regularmente), eu estava usando VARBINARYpara o tipo de dados na minha nova tabela.

No entanto, a inserção abaixo falha ...

INSERT INTO QueryPlans
    ( QueryPlanHash, QueryPlan )
SELECT queryplanhash, queryplan
FROM
(
    SELECT 
      p.value('(./@QueryPlanHash)[1]', 'varchar(20)') queryplanhash,
      QueryPlan,
      ROW_NUMBER() OVER (PARTITION BY p.value('(./@QueryPlanHash)[1]', 'varchar(20)') ORDER BY DateRecorded) rownum
    FROM table
    CROSS APPLY QueryPlan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryPlanHash]') t(p)
) data
WHERE rownum = 1

....com o erro

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

O problema é que os hashes do plano de consulta já estão em formato binário, porém armazenados como VARCHAR no Plano de Consulta XML, por exemplo

0x9473FBCCBC01AFE

e CONVERT to BINARY dá um valor completamente diferente

0x3078393437334642434342433031414645

Tentei alterar a definição de valor na seleção de XQuery para binário, mas não retornou nenhum valor.

Como eu extrairia o valor de 0x9473FBCCBC01AFEum plano de consulta XML como um VARBINARY, em vez de um VARCHAR?