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

Question

DForck42

Asked: 2018-03-08 07:36:51 +0800 CST2018-03-08 07:36:51 +0800 CST 2018-03-08 07:36:51 +0800 CST

Um índice não clusterizado na chave primária aceleraria as exclusões e evitaria deadlocks?

772

Eu tenho um banco de dados de tráfego muito alto. No aplicativo, eles emitirão exclusões e, frequentemente, essas exclusões travarão com outras exclusões na mesma tabela. Estou pesquisando maneiras de remediar isso, e uma resposta que vi foi garantir que as exclusões tenham o caminho mais rápido para o registro.

Atualmente, todas as exclusões seguem este formulário:

(@0 int) delete [dbo].[table] where (table_id = @0)

Cada uma dessas tabelas tem uma chave primária e um índice clusterizado em table_id.

Minha pergunta é: adicionar um índice não clusterizado table_idpode ajudar a acelerar essas exclusões e impedir a ocorrência de deadlocks?

Gráfico de impasse:

<deadlock victim="process2b53a408c8"><process-list><process id="process2b53a408c8" taskpriority="0" logused="284" waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" waittime="2034" ownerId="14899585071" transactionname="user_transaction" lasttranstarted="2018-03-07T09:47:18.833" XDES="0x50746b1c0" lockMode="S" schedulerid="7" kpid="967168" status="suspended" spid="185" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-07T09:47:18.850" lastbatchcompleted="2018-03-07T09:47:18.850" lastattention="1900-01-01T00:00:00.850" clientapp="redacted_service" hostname="redacted_host" hostpid="164656" loginname="redacted_domain\_SQL_redacted_database_P" isolationlevel="read committed (2)" xactid="14899585071" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="redacted_database"><executionStack><frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x020000001335f1027371c186c8d7405191cdef00ddd0ebe70000000000000000000000000000000000000000">
unknown     </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame></executionStack><inputbuf>
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0)    </inputbuf></process><process id="process218b0a5468" taskpriority="0" logused="284" waitresource="KEY: 19:72057597368270848 (c94c2713ec0f)" waittime="2036" ownerId="14899585063" transactionname="user_transaction" lasttranstarted="2018-03-07T09:47:18.833" XDES="0x1fcd0c4d10" lockMode="S" schedulerid="4" kpid="962372" status="suspended" spid="384" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-07T09:47:18.850" lastbatchcompleted="2018-03-07T09:47:18.847" lastattention="1900-01-01T00:00:00.847" clientapp="redacted_service" hostname="redacted_host" hostpid="164656" loginname="redacted_domain\_SQL_redacted_database_P" isolationlevel="read committed (2)" xactid="14899585063" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="redacted_database"><executionStack><frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x020000001335f1027371c186c8d7405191cdef00ddd0ebe70000000000000000000000000000000000000000">
unknown     </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame></executionStack><inputbuf>
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0)    </inputbuf></process></process-list><resource-list><keylock hobtid="72057597368270848" dbid="19" objectname="redacted_database.dbo.redacted_table_2" indexname="PK_redacted_table_2" id="lock9d21dab80" mode="X" associatedObjectId="72057597368270848"><owner-list><owner id="process218b0a5468" mode="X" /></owner-list><waiter-list><waiter id="process2b53a408c8" mode="S" requestType="wait" /></waiter-list></keylock><keylock hobtid="72057597368270848" dbid="19" objectname="redacted_database.dbo.redacted_table_2" indexname="PK_redacted_table_2" id="lock4de4b1800" mode="X" associatedObjectId="72057597368270848"><owner-list><owner id="process2b53a408c8" mode="X" /></owner-list><waiter-list><waiter id="process218b0a5468" mode="S" requestType="wait" /></waiter-list></keylock></resource-list></deadlock>

definição da tabela:

create table [dbo].[redacted_table](
    [id] [int] identity(1,1) not for replication not null,
    [loan_id] [int] not null,
    [user_role_id] [int] not null,
    [assigned_by_user_id] [int] not null,
    [out_for_assignment] [bit] not null,
    [assignment_date] [datetime] not null,
    [recognize_date] [datetime] not null,
    [routing_source] [varchar](50) null,
    [request_guid] [uniqueidentifier] null,
 constraint [PK_redacted_table] primary key clustered 
(
    [id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY]
) on [PRIMARY]

go

alter table [dbo].[redacted_table] add  constraint [DF_redacted_table_assignment_date]  default (getdate()) for [assignment_date]
go

alter table [dbo].[redacted_table] add  constraint [DF_redacted_table_recognize_date]  default (getdate()) for [recognize_date]
go

alter table [dbo].[redacted_table]  with check add  constraint [FK_redacted_table_redacted_table3] foreign key([loan_id])
references [dbo].[redacted_table3] ([id])
go

alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table3]
go

alter table [dbo].[redacted_table]  with check add  constraint [FK_redacted_table_user_redacted_table4] foreign key([user_role_id])
references [dbo].[user_redacted_table4] ([id])
go

alter table [dbo].[redacted_table] check constraint [FK_redacted_table_user_redacted_table4]
go

alter table [dbo].[redacted_table]  with check add  constraint [FK_redacted_table_redacted_table5] foreign key([assigned_by_user_id])
references [dbo].[redacted_table5] ([id])
go

alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table5]
go

2 respostas

Voted

Hannah Vernon · Answer 1 · 2018-03-08T07:54:28+08:00

Como sua instrução está excluindo linhas por meio de uma WHEREcláusula na chave primária, table_id, é improvável que a adição de um índice não clusterizado table_idajude e pode aumentar o número de deadlocks ocorrendo.

DELETE FROM ...exclui a linha da tabela (ou índice clusterizado), bem como todos os índices não clusterizados definidos na tabela em que a linha está presente. A adição de um índice extra requer uma operação de exclusão extra.

Veja este exemplo muito simples:

IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

O plano de execução real para o acima DELETE:

Você provavelmente deve adicionar as CREATE TABLEdeclarações à sua pergunta, juntamente com detalhes sobre o impasse por meio de um gráfico de impasse.

Agora, se adicionarmos um índice não clusterizado adicional e executarmos outra exclusão:

CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

Vemos o seguinte plano:

Como você pode ver nas duas imagens, o segundo delete custa quase o dobro; 0,0132841 para a primeira exclusão e 0,0232851 para a segunda exclusão.

Se você usar o SentryOne Plan Explorer (gratuito!) para examinar os planos de execução, poderá ver o número de operações adicionais de exclusão de índice não clusterizado que estão ocorrendo:

Observando seu gráfico de deadlock, em combinação com a lista de índices não clusterizados em sua pergunta, parece que o deadlock provavelmente é causado por exclusões simultâneas de linhas contidas na mesma página. Adicionar uma ROWLOCKdica à sua exclusão pode evitar esse impasse específico. Você também pode testar usando SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;antes da operação de exclusão. Esteja ciente de que altera o nível de isolamento para todas as instruções que ocorrem posteriormente no mesmo lote. Certifique-se de redefinir o nível de isolamento da transação se executar outras instruções após a operação de exclusão.

O deadlock mostra que o recurso wait é o mesmo para ambas as instruções, exceto o número da linha: waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" O número da linha é o número entre colchetes. @Kin mostra uma ótima maneira de ver os detalhes waitresourceem sua resposta aqui

ROWLOCK
Especifica que os bloqueios de linha são feitos quando os bloqueios de página ou tabela são normalmente feitos. Quando especificado em transações operando no nível de isolamento SNAPSHOT, os bloqueios de linha não são obtidos, a menos que ROWLOCK seja combinado com outras dicas de tabela que requerem bloqueios, como UPDLOCK e HOLDLOCK.

paparazzo · Answer 2 · 2018-03-08T08:37:38+08:00

paparazzo

2018-03-08T08:37:38+08:002018-03-08T08:37:38+08:00

Eu não acho. Eu testei em uma tabela simples e o plano de consulta não agrupado mudou para algo que parecia pior.

Um alcance e dicas muitas vezes não são o caminho a percorrer, mas tente

delete [Table_1] with (rowlock) where pk = @pk;

As chaves estrangeiras para a tabela precisam ser verificadas.

0

Um índice não clusterizado na chave primária aceleraria as exclusões e evitaria deadlocks?

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?

Um índice não clusterizado na chave primária aceleraria as exclusões e evitaria deadlocks?

2 respostas

relate perguntas