死锁的主要原因是什么，可以预防吗？

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

关于创建缺失索引的建议

772

我一直在使用 sp_blitzindex，它非常有用（感谢 Brent Ozar 和团队）。我对我的数据库执行了这个过程，下面是对一个表的索引恐惧症组的发现：

下面是基础表的定义：

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
)

根据这些缺失的索引详细信息，我计划创建具有以下定义的索引：

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)

我根据所有 6 个缺失索引统计信息中这些列的出现次数得出了上述列。

正如您从表定义中看到的那样，这是一个堆并且在该表上没有聚簇索引。

感谢您的指导或任何帮助。

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 个回答

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 只是直接从sys.dm_db_missing_index_details中获取 SQL Server 的建议，其中有很多问题：

他们会推荐类似于已经存在的索引的索引
他们很少关心删除/更新/插入 (DUI) 操作的速度，并且会推荐如此多的索引，以至于您的 DUI 会减慢速度
他们会推荐您的硬件可能无法支持的太大（太多字段）的索引
字段不一定按顺序 - 缺少的索引推荐键顺序由表中的字段顺序驱动
他们不会推荐聚簇索引

考虑到所有这些，sp_BlitzIndex 是一种用于训练的工具，就像任何其他工具一样。首先考虑聚簇索引是正确的——我会在添加非聚簇索引之前从这里开始。

5

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

来自评论

聚簇索引带来许多约束，如 null、重复等，而非聚簇索引不会给供应商带来任何风险，而且他们很容易就此达成一致。

这不是真的，聚簇索引不允许重复、NULL，甚至不允许重复的 NULL，除非它们被声明为 UNIQUE。

这些插入都会成功：

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;

这些插入将在第一个唯一值之后失败：

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;

您的建议奏效了，我在测试环境中的 A 列上创建了聚簇索引，到目前为止根本没有丢失索引的投诉，只是补充说在测试中也有丢失索引的投诉。在与供应商确认后，我们将很快将此更改推广到生产环境。非常感谢。

???

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

Alen

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

我根据 SQL 2005、2012 和 2016 上缺失索引 DMV 的建议创建了索引，结果喜忧参半。有时，即使创建了确切的索引，相同的建议也会不断出现。其他时候，我根据建议创建的索引从未使用过，一两个月后我最终将其删除。

我更喜欢基于探查器或扩展事件会话以及我编写的一些其他脚本来创建索引，这些脚本用于查找表上其他索引的使用情况以及行锁和页面拆分。

0

关于创建缺失索引的建议

来自评论

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

关于创建缺失索引的建议

3 个回答

来自评论

相关问题