SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Learning_DBAdmin

Asked: 2020-11-03 23:42:11 +0800 CST2020-11-03 23:42:11 +0800 CST 2020-11-03 23:42:11 +0800 CST

Enfrentando um impasse após a introdução de um novo índice não clusterizado no heap

772

Estou enfrentando um problema estranho em que analisei um procedimento armazenado que é executado a cada 3 minutos e estava sobrecarregando a CPU. Descobri que existem várias instruções select que fazem parte deste procedimento e todas elas estavam fazendo uma varredura completa da tabela (lendo todas as páginas nas tabelas). Então, eu os testei no ambiente de teste e suportei isso com um índice não clusterizado. O mesmo foi confirmado com o fornecedor em questão e eles concordaram com a mudança. Eu estava implantando-os na Produção ontem e verifiquei as leituras lógicas dessas consultas e verifiquei as mesmas após o novo índice, validei que está tendo um impacto positivo e as leituras lógicas caíram em 1/10.

Imediatamente após a implantação desse índice, o procedimento que é executado a cada 3 minutos começou a falhar. Executado manualmente para verificar o problema e descobri que, está causando deadlock todas as vezes, exceto 1 ou 2 vezes em 1 hora. Eu estava completamente perdido sobre como um índice poderia causar um impasse? Idealmente, o índice deve resolver o impasse, no entanto, foi o oposto.

A tabela a que me refiro é um heap e não possui chave primária clusterizada, em vez disso, possui chave primária não clusterizada. Eu tenho a concordância de todas as partes para fazer a mudança de NC para cluster, no entanto, ele está vinculado a várias tabelas por meio do relacionamento PK-FK e requer tempo de inatividade e, portanto, está atualmente em espera.

Eu capturei o gráfico de deadlock e também tenho sp_blitzlock no lugar. Parece que o impasse está acontecendo entre a consulta do aplicativo e este procedimento, no entanto, não consigo entender como esse índice está causando isso e como quando eu reverti esse índice, ele está funcionando sem problemas e sem impasse.

O gráfico de impasse é o seguinte:

<deadlock>
  <victim-list>
    <victimProcess id="processef645b848" />
  </victim-list>
  <process-list>
    <process id="processef645b848" taskpriority="0" logused="0" waitresource="PAGE: 6:1:965 " waittime="3661" ownerId="303318514" transactionname="INSERT" lasttranstarted="2020-11-02T12:18:12.793" XDES="0x31fbb78e0" lockMode="S" schedulerid="1" kpid="8564" status="suspended" spid="1246" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-02T12:18:00.500" lastbatchcompleted="2020-11-02T12:18:00.500" lastattention="1900-01-01T00:00:00.500" clientapp="SQLAgent - TSQL JobStep (Job 0x417A2365E91D1647B2C225CA23D84860 : Step 1)" hostname="DB_Server" hostpid="3628" loginname="SQL_Agent_Login" isolationlevel="read committed (2)" xactid="303318514" currentdb="7" currentdbname="DB_Name_1" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtend="2888" sqlhandle="0x02000000c984e814ec51be0d03e3852ee0b755da518273d00000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="DB_Name_1.dbo.Procedure_Name" line="171" stmtstart="13412" stmtend="13482" sqlhandle="0x03000700151ddc58cd73c00013ac000001000000000000000000000000000000000000000000000000000000">
EXECUTE (@EXEC_IMMEDIATE_VAR)    </frame>
        <frame procname="adhoc" line="1" sqlhandle="0x01000700d070832b90a421810300000000000000000000000000000000000000000000000000000000000000">
EXEC Procedure_Name 'DB_User_Application'    </frame>
      </executionStack>
      <inputbuf>
EXEC Procedure_Name 'DB_User_Application'   </inputbuf>
    </process>
    <process id="processe1435468" taskpriority="0" logused="996" waitresource="PAGE: 6:1:88348 " waittime="3841" ownerId="303318578" transactionname="user_transaction" lasttranstarted="2020-11-02T12:18:13.007" XDES="0x255f023b0" lockMode="IX" schedulerid="1" kpid="4120" status="suspended" spid="1271" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-02T12:18:13.017" lastbatchcompleted="2020-11-02T12:18:13.007" lastattention="1900-01-01T00:00:00.007" clientapp="Vendor_Name" hostname="APP_Server_Name" hostpid="1296" loginname="DB_User_Application" isolationlevel="read committed (2)" xactid="303318578" currentdb="6" currentdbname="DB_Name_2" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="720" stmtend="1560" sqlhandle="0x020000001fdba70fc3e8a833920a732fe1c19b282e28ac1c0000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="adhoc" line="1" stmtend="1190" sqlhandle="0x02000000f228391fab59e520bc237b6a919f53ced0b1ac290000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
update Deadlock_Table set Error_Code = '000',TIMEOUT_NETWORK_ID=NULL , var32_32='XXXX', var32_15='000', var32_22='1', var32_04='IB', var32_05='XXX', var256_01='Processed OK', var32_09='XXX', var32_14='048', var64_01='XXXXX', var32_06='02112020121802', var32_03='XXX', var32_10='XXXX', var32_07='XXXX', var32_02='XXX', var32_01='XXX', Node_Id='APP_Server_Name', Message_Id='XXXX', End_Point_Id='XXXXX' where Log_Id=XXXXX and Receive_Time='XXXXX'   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="965" dbid="6" subresource="FULL" objectname="DB_Name_2.dbo.Deadlock_Table" id="lock4d151f900" mode="IX" associatedObjectId="72057594079739904">
      <owner-list>
        <owner id="processe1435468" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="processef645b848" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="88348" dbid="6" subresource="FULL" objectname="DB_Name_2.dbo.Deadlock_Table" id="lock4e1da7d00" mode="S" associatedObjectId="72057594079739904">
      <owner-list>
        <owner id="processef645b848" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="processe1435468" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

Abaixo está o DDL da tabela:

CREATE TABLE [dbo].[Deadlock_Table](
    [Log_id] [int] IDENTITY(1,1) NOT NULL,
    [Receive_time] [varchar](15) NOT NULL,
    [A] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    [VAR32_03] [varchar](32) NULL,
    [VAR32_04] [varchar](32) NULL,
    [VAR32_05] [varchar](32) NULL,
    [VAR32_06] [varchar](32) NULL,
    [VAR32_07] [varchar](32) NULL,
    [VAR32_08] [varchar](32) NULL,
    [VAR32_09] [varchar](32) NULL,
    [VAR32_10] [varchar](32) NULL,
    [VAR32_11] [varchar](32) NULL,
    [VAR32_12] [varchar](32) NULL,
    [VAR32_13] [varchar](32) NULL,
    [VAR32_14] [varchar](32) NULL,
    [VAR32_15] [varchar](32) NULL,
    [VAR32_16] [varchar](32) NULL,
    [VAR32_17] [varchar](32) NULL,
    [VAR32_18] [varchar](32) NULL,
    [VAR32_19] [varchar](32) NULL,
    [VAR32_20] [varchar](32) NULL,
    [VAR32_21] [varchar](32) NULL,
    [VAR32_22] [varchar](32) NULL,
    [VAR32_23] [varchar](32) NULL,
    [VAR32_24] [varchar](32) NULL,
    [VAR32_25] [varchar](32) NULL,
    [VAR32_26] [varchar](32) NULL,
    [VAR32_27] [varchar](32) NULL,
    [VAR32_28] [varchar](32) NULL,
    [VAR32_29] [varchar](32) NULL,
    [VAR32_30] [varchar](32) NULL,
    [VAR32_31] [varchar](32) NULL,
    [VAR32_32] [varchar](32) NULL,
    [VAR32_33] [varchar](32) NULL,
    [VAR32_34] [varchar](32) NULL,
    [VAR32_35] [varchar](32) NULL,
    [VAR32_36] [varchar](32) NULL,
    [VAR32_37] [varchar](32) NULL,
    [VAR32_38] [varchar](32) NULL,
    [VAR32_39] [varchar](32) NULL,
    [VAR32_40] [varchar](32) NULL,
    [VAR32_41] [varchar](32) NULL,
    [VAR32_42] [varchar](32) NULL,
    [VAR32_43] [varchar](32) NULL,
    [VAR32_44] [varchar](32) NULL,
    [VAR32_45] [varchar](32) NULL,
    [VAR32_46] [varchar](32) NULL,
    [VAR32_47] [varchar](32) NULL,
    [VAR32_48] [varchar](32) NULL,
    [VAR32_49] [varchar](32) NULL,
    [VAR32_50] [varchar](32) NULL,
    [VAR32_51] [varchar](32) NULL,
    [VAR32_52] [varchar](32) NULL,
    [VAR32_53] [varchar](32) NULL,
    [VAR32_54] [varchar](32) NULL,
    [VAR32_55] [varchar](32) NULL,
    [VAR32_56] [varchar](32) NULL,
    [VAR32_57] [varchar](32) NULL,
    [VAR32_58] [varchar](32) NULL,
    [VAR32_59] [varchar](32) NULL,
    [VAR32_60] [varchar](32) NULL,
    [VAR32_61] [varchar](32) NULL,
    [VAR32_62] [varchar](32) NULL,
    [VAR32_63] [varchar](32) NULL,
    [VAR32_64] [varchar](32) NULL,
    [VAR64_01] [varchar](64) NULL,
    [VAR64_02] [varchar](64) NULL,
    [VAR64_03] [varchar](64) NULL,
    [VAR64_04] [varchar](64) NULL,
    [VAR64_05] [varchar](64) NULL,
    [VAR64_06] [varchar](64) NULL,
    [VAR64_07] [varchar](64) NULL,
    [VAR64_08] [varchar](64) NULL,
    [VAR64_09] [varchar](64) NULL,
    [VAR64_10] [varchar](64) NULL,
    [VAR64_11] [varchar](64) NULL,
    [VAR64_12] [varchar](64) NULL,
    [VAR64_13] [varchar](64) NULL,
    [VAR64_14] [varchar](64) NULL,
    [VAR64_15] [varchar](64) NULL,
    [VAR64_16] [varchar](64) NULL,
    [VAR64_17] [varchar](64) NULL,
    [VAR64_18] [varchar](64) NULL,
    [VAR64_19] [varchar](64) NULL,
    [VAR64_20] [varchar](64) NULL,
    [VAR64_21] [varchar](64) NULL,
    [VAR64_22] [varchar](64) NULL,
    [VAR64_23] [varchar](64) NULL,
    [VAR64_24] [varchar](64) NULL,
    [VAR64_25] [varchar](64) NULL,
    [VAR64_26] [varchar](64) NULL,
    [VAR64_27] [varchar](64) NULL,
    [VAR64_28] [varchar](64) NULL,
    [VAR64_29] [varchar](64) NULL,
    [VAR64_30] [varchar](64) NULL,
    [VAR64_31] [varchar](64) NULL,
    [VAR64_32] [varchar](64) NULL,
    [VAR128_01] [varchar](128) NULL,
    [VAR128_02] [varchar](128) NULL,
    [VAR128_03] [varchar](128) NULL,
    [VAR128_04] [varchar](128) NULL,
    [VAR128_05] [varchar](128) NULL,
    [VAR128_06] [varchar](128) NULL,
    [VAR128_07] [varchar](128) NULL,
    [VAR128_08] [varchar](128) NULL,
    [VAR128_09] [varchar](128) NULL,
    [VAR128_10] [varchar](128) NULL,
    [VAR128_11] [varchar](128) NULL,
    [VAR128_12] [varchar](128) NULL,
    [VAR128_13] [varchar](128) NULL,
    [VAR128_14] [varchar](128) NULL,
    [VAR128_15] [varchar](128) NULL,
    [VAR128_16] [varchar](128) NULL,
    [VAR256_01] [varchar](256) NULL,
    [VAR256_02] [varchar](256) NULL,
    [VAR256_03] [varchar](256) NULL,
    [VAR256_04] [varchar](256) NULL,
    [VAR256_05] [varchar](256) NULL,
    [VAR256_06] [varchar](256) NULL,
    [VAR256_07] [varchar](256) NULL,
    [VAR256_08] [varchar](256) NULL,
    [VAR512_01] [varchar](512) NULL,
    [VAR512_02] [varchar](512) NULL,
    [VAR512_03] [varchar](512) NULL,
    [VAR512_04] [varchar](512) NULL,
    [VAR1024_01] [varchar](1024) NULL,
    [VAR1024_02] [varchar](1024) NULL,
    [E] [varchar](20) NULL,
    [M] [varchar](40) NULL,
    [E] [varchar](50) NULL,
    [N] [varchar](40) NULL,
    [TN] [int] NULL,
    [T] [numeric](1, 0) NULL,
    [D] [numeric](1, 0) NULL,
 CONSTRAINT [XPKtable_name] PRIMARY KEY NONCLUSTERED 
(
    [Log_id] ASC,
    [Receive_time] ASC
)

Atualmente, ele tem apenas um índice diferente da chave primária na 3ª coluna (digamos A).

O índice que eu havia proposto estava abaixo:

CREATE NONCLUSTERED INDEX [IX_Deadlock_Table] ON [dbo].[Deadlock_Table]
(
 var32_02 ,
 D,
 T
 ) 
GO

Obrigado por ler uma pergunta tão longa e realmente aprecio sua contribuição para resolver esse impasse. Caso eu tenha perdido alguns detalhes vitais, por favor, coloque-os na seção de comentários e eu os adicionaria.

Plano de consulta do aplicativo para declaração de atualização --> https://www.brentozar.com/pastetheplan/?id=r14x3TCOD

A consulta que é executada dentro do cursor são várias instruções de inserção que são basicamente instruções de seleção envolvendo esta deadlock_table unida a outra tabela e são as seguintes:

INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,null,null,null,var32_07,null,var32_02,var32_01,var32_20,Coalesce(null,var32_10,var32_11,var32_21),var32_21,null,null,Coalesce(var32_31,var32_12,var32_13),null,null,null,null,null,convert(varchar(2),N),null,1,1,null,null,null,null,null,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,var32_17,var32_19,var32_18,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =4 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var32_02 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 4) AND var32_02 NOT IN ('0044')
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,var64_12,var64_02,var32_42,var32_20,var32_45,null,null,null,null,convert(varchar(2),N),var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,NULL,NULL,NULL,NULL,var64_22,var32_30,var64_23,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =10 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 10)
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,var64_12,var64_02,var32_42,var32_20,var32_45,null,null,null,null,var32_62,var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,var1024_02,NULL,NULL,NULL,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =11 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 11)
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,      case when var64_19 = '64' then var64_10 else var64_12 end as TO_ACCOUNT_NUM, var64_02,var32_42,var32_20,var32_45,null,null,null,null,var32_62,var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,var1024_02,var64_17,var64_21,var64_20,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =12 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1))       AND (var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 12)      or  var64_19 in (SELECT B.CODE_NAME FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 12))
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,null,null,null,var32_44,null,var32_02,var32_46,null,null,null,null,null,null,null,null,null,null,null,convert(varchar(2),N),null,1,1,null,null,null,null,null,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =13 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var32_02 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 13)

Plano de consulta para a 1ª instrução select sem meu índice (em sua forma atual) - https://www.brentozar.com/pastetheplan/?id=BkDnpbktw

Plano de consulta para a instrução select após a criação do índice - https://www.brentozar.com/pastetheplan/?id=r1SCeGktD

Abaixo estão as leituras lógicas antes e depois da criação do índice:

Table 'deadlock_table'. Scan count 5, logical reads 326203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'deadlock_table'. Scan count 58, logical reads 12055, physical reads 0, read-ahead reads 23, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Versão SQL

Versão: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 7 de setembro de 2018 01:37:51 Enterprise Edition: Licenciamento baseado em núcleo (64 bits) no Windows NT 6.3 (Build 9600: ) (Hypervisor )

1 respostas

Voted

Josh Darnell · Answer 1 · 2020-11-04T07:49:41+08:00

É difícil responder sua pergunta em detalhes com o plano anônimo e o impasse, e não uma tonelada de informações sobre o que está no trabalho do agente envolvido no impasse. Mas, em termos gerais, a resposta a essa pergunta pode ser útil para ajudá-lo a rastrear o problema:

Eu estava completamente perdido sobre como um índice poderia causar um impasse?

A adição de índices pode fazer com que as consultas acessem dados em uma ordem diferente daquela sem esses índices.

No seu caso, a consulta do aplicativo tem este padrão:

Atualize exatamente 1 linha no heap dbo.deadlock_table, obtendo um bloqueio IX na página onde esta linha está localizada (e, claro, um bloqueio X na linha real)
Um gatilho é acionado, o que atualiza potencialmente várias linhas no mesmo heap, obtendo um bloqueio IX nas páginas em que essas linhas estão localizadas

Depois de adicionar seu novo índice NC, parece que a consulta de trabalho do agente está solicitando bloqueios S nessas páginas de heap na ordem oposta.

O problema é que o heap não tem uma ordem definida. Supondo que você tenha pesquisas RID na consulta de trabalho do agente (ou seja, o novo índice NC não é um índice de cobertura), então ele está essencialmente emitindo bloqueios S "aleatoriamente" em todo o heap. As linhas que estão sendo atualizadas pela consulta do aplicativo, enquanto estão próximas umas das outras no NC PK, também podem ser distribuídas essencialmente aleatoriamente em todo o heap.

Sem o índice NC, o acesso ao heap pela consulta de trabalho do agente provavelmente era mais previsível (uma varredura ordenada de alocação) ou alinhado com o PK (varredura de pesquisas NC PK + RID), então você conseguiu evitar esses deadlocks.

A meu ver, com base nas informações disponíveis, algumas opções viáveis são:

remover o índice NC (voltar ao modo como as coisas eram), ou
faça o índice NC "cobrindo" (adicionando colunas às inclusões ou alterando a consulta para selecionar menos colunas) para que ele não precise adquirir bloqueios no heap

Enfrentando um impasse após a introdução de um novo índice não clusterizado no heap

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Enfrentando um impasse após a introdução de um novo índice não clusterizado no heap

1 respostas

relate perguntas