Quais são as principais causas de deadlocks e podem ser evitadas?

Question

Learning_DBAdmin

Asked: 2019-03-26 04:53:57 +0800 CST2019-03-26 04:53:57 +0800 CST 2019-03-26 04:53:57 +0800 CST

Sugestão sobre a criação de índice ausente

772

Eu tenho usado sp_blitzindex e é muito útil (Graças a Brent Ozar e equipe). Eu executei este procedimento no meu banco de dados e abaixo está a descoberta de uma tabela que se enquadra no grupo Indexaphobia:

abaixo está a definição da tabela subjacente:

CREATE TABLE [dbo].[table_name](
    [L] [int] IDENTITY(1,1) NOT NULL,
    [R] [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 
(
    [L] ASC,
    [R] ASC
)

Seguindo esses detalhes de índice ausentes, estou planejando criar um índice com a definição abaixo:

create nonclustered index table_name_incl(A,VAR32_02) include(L,R,E,T,D,VAR32_10,VAR32_18,VAR32_19,VAR32_20,VAR64_11,VAR64_02,VAR32_42,VAR32_39,VAR32_38,VAR32_35,VAR32_39,VAR32_24,VAR32_25,VAR32_27)

Cheguei às colunas acima com base no número de ocorrências dessas colunas em todas as 6 estatísticas de índice ausentes.

Como você pode ver na definição da tabela, este é um heap e não possui índice clusterizado nesta tabela.

Agradeço sua orientação ou qualquer ajuda sobre isso.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

3 respostas

Voted

Brent Ozar · Answer 1 · 2019-03-26T05:05:23+08:00

Brent Ozar

2019-03-26T05:05:23+08:002019-03-26T05:05:23+08:00

sp_BlitzIndex está apenas pegando as recomendações do SQL Server diretamente de sys.dm_db_missing_index_details , que tem muitas pegadinhas:

Eles recomendarão índices semelhantes aos índices que já existem
Eles têm uma preocupação mínima com a velocidade das operações de exclusão/atualização/inserção (DUI) e recomendarão tantos índices que seus DUIs ficarão mais lentos
Eles recomendarão índices muito grandes (muitos campos) que seu hardware pode não suportar
Os campos não estão necessariamente na ordem - a ordem da chave de recomendação de índice ausente é determinada pela ordem dos campos na tabela
Eles não recomendam índices clusterizados

Com tudo isso em mente, sp_BlitzIndex é uma ferramenta para ser usada com treinamento, como qualquer outra ferramenta. Você está no caminho certo pensando primeiro em um índice clusterizado - eu começaria por aí antes de adicionar índices não clusterizados.

5

Erik Darling · Answer 2 · 2019-04-04T06:21:56+08:00

De comentários

O índice clusterizado apresenta muitas restrições, como nulo, duplicatas, etc., enquanto o não clusterizado não representa nenhum risco para o fornecedor e eles concordam com isso facilmente.

Isso não é verdade, índices clusterizados não permitem duplicatas, NULLs ou mesmo NULLs duplicados, a menos que sejam declarados como UNIQUE.

Todas essas inserções serão bem-sucedidas:

DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t ( id INT NULL );

CREATE CLUSTERED INDEX c ON dbo.t ( id );


INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );

INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );

SELECT *
FROM   dbo.t AS t;

Essas inserções falharão após o primeiro valor exclusivo:

DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t ( id INT NULL );

CREATE UNIQUE CLUSTERED INDEX c ON dbo.t ( id );


INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );

INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );

SELECT *
FROM   dbo.t AS t;

Sua sugestão deu certo, criei um índice clusterizado na coluna A no ambiente de teste e até agora nenhuma reclamação de índice ausente, apenas para acrescentar que havia reclamação de índice ausente no teste também. Estaremos promovendo essa mudança para Produção logo após a confirmação com o fornecedor. Muito obrigado.

???

Alen · Answer 3 · 2019-04-04T10:37:37+08:00

Alen

2019-04-04T10:37:37+08:002019-04-04T10:37:37+08:00

Criei índices a partir de recomendações dos DMVs de índice ausentes no SQL 2005, 2012 e 2016 e tive resultados mistos. Às vezes, as mesmas recomendações continuavam aparecendo, mesmo que o índice exato tenha sido criado. Outras vezes, o índice que criei com base na recomendação nunca era usado e acabava apagando-o um ou dois meses depois.

Prefiro criar índices com base no criador de perfil ou sessão de eventos estendidos, bem como alguns outros scripts que escrevi para procurar o uso de outros índices na tabela, juntamente com bloqueios de linha e divisão de página.

0

Sugestão sobre a criação de índice ausente

De comentários

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?

Sugestão sobre a criação de índice ausente

3 respostas

De comentários

relate perguntas