Cfrgtkky

I have been using sp_blitzindex and it is very very helpful(Thanks to Brent Ozar and Team). I executed this procedure against my database and below is the finding for one table falling in Indexaphobia group:

below is definition of underlying table:

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

)

Following these missing index details, I am planning to create index with below definition:

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)

I have arrived at above columns based on the number of occurrences of these columns in all 6 missing index stats.

As you can see from table definition, this is a heap and doesn't have clustered index on this table.

Appreciate your guidance or any assistance on this.

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)

From Comments

Clustered index poses many constraints like null, duplicates etc
whereas non-clustered doesn't pose any risk for vendor and they agree
on it easily.

This isn't true, clustered indexes don't disallow duplicates, NULLs, or even duplicate NULLs unless they're declared as UNIQUE.

These inserts will all succeed:

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;

These inserts will fail after the first unique value:

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;

Your suggestion did the trick, I created clustered index on column A
in test environment and so far no complaint of missing index at all,
just to add that there was missing index complaint in Test also. We
will be promoting this change to Production very soon after confirming
with vendor. Thanks a lot.

👍👍👍

sp_BlitzIndex is just taking SQL Server's recommendations straight from sys.dm_db_missing_index_details, which have a lot of gotchas:

• They'll recommend indexes similar to indexes that already exist


• They have minimal concern about the speed of delete/update/insert (DUI) operations, and will recommend so many indexes that your DUIs will slow down


• They'll recommend indexes that are way too large (too many fields) that your hardware may not be able to support


• The fields aren't necessarily in the order - the missing index recommendation key order is driven by field order in the table
  


• They won't recommend clustered indexes

With all that in mind, sp_BlitzIndex is a tool to be used with training, just like any other tool. You're on the right track by thinking about a clustered index first - I'd start there before adding nonclustered indexes.

I've created indexes from recommendations of the missing index DMV's on SQL 2005, 2012 and 2016 and have had mixed results. Sometimes the same recommendations kept on popping even though the exact index had been created. Other times the index that I had created based on the recommendation was never used and I'd end up deleting it a month or two later.

I prefer to create indexes based on profiler or extended events session as well as some other scripts i've written to look for usage of other indexes on the table along with row locks and page splitting.