Suggestion on Missing Index Creation












1















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:



sp_blitzindex output



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)





sql-server index sp-blitzindex







share|improve this question


















  • 1





    Silly question: why not create a clustered index? it looks like if you created one on A, it would solve a few different problems? Is the identity column a nonclustered primary key?

    – Erik Darling
    yesterday











  • @ErikDarling Trying to learn from many experts in the forum. Apologies for silly question.I wish, I could have been in the position to create clustered index. Since, its a vendor supported code, don't have much room for change. Yes, primary key is created as non-clustered column.

    – Learning_DBAdmin
    yesterday











  • I didn't mean your question was silly, I meant mine was. I understand the pain of supporting a vendor database. But to get things right, you can create nonclustered indexes, but not clustered indexes?

    – Erik Darling
    yesterday











  • @ErikDarling 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. I have tried many times for clustered index creation however have faced so much resistance from them.I will try once again with them to create clustered index on A column, I would also see if we can have clustered index on L & A; this would be safer for them(I guess).

    – Learning_DBAdmin
    yesterday






  • 1





    No, clustered indexes don't do that. See my demo here.

    – Erik Darling
    yesterday
















1















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:



sp_blitzindex output



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)





sql-server index sp-blitzindex







share|improve this question


















  • 1





    Silly question: why not create a clustered index? it looks like if you created one on A, it would solve a few different problems? Is the identity column a nonclustered primary key?

    – Erik Darling
    yesterday











  • @ErikDarling Trying to learn from many experts in the forum. Apologies for silly question.I wish, I could have been in the position to create clustered index. Since, its a vendor supported code, don't have much room for change. Yes, primary key is created as non-clustered column.

    – Learning_DBAdmin
    yesterday











  • I didn't mean your question was silly, I meant mine was. I understand the pain of supporting a vendor database. But to get things right, you can create nonclustered indexes, but not clustered indexes?

    – Erik Darling
    yesterday











  • @ErikDarling 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. I have tried many times for clustered index creation however have faced so much resistance from them.I will try once again with them to create clustered index on A column, I would also see if we can have clustered index on L & A; this would be safer for them(I guess).

    – Learning_DBAdmin
    yesterday






  • 1





    No, clustered indexes don't do that. See my demo here.

    – Erik Darling
    yesterday














1












1








1








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:



sp_blitzindex output



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)





sql-server index sp-blitzindex







share|improve this question














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:



sp_blitzindex output



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)





sql-server index sp-blitzindex




sql-server index sp-blitzindex






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked yesterday









Learning_DBAdminLearning_DBAdmin

380114




380114








  • 1





    Silly question: why not create a clustered index? it looks like if you created one on A, it would solve a few different problems? Is the identity column a nonclustered primary key?

    – Erik Darling
    yesterday











  • @ErikDarling Trying to learn from many experts in the forum. Apologies for silly question.I wish, I could have been in the position to create clustered index. Since, its a vendor supported code, don't have much room for change. Yes, primary key is created as non-clustered column.

    – Learning_DBAdmin
    yesterday











  • I didn't mean your question was silly, I meant mine was. I understand the pain of supporting a vendor database. But to get things right, you can create nonclustered indexes, but not clustered indexes?

    – Erik Darling
    yesterday











  • @ErikDarling 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. I have tried many times for clustered index creation however have faced so much resistance from them.I will try once again with them to create clustered index on A column, I would also see if we can have clustered index on L & A; this would be safer for them(I guess).

    – Learning_DBAdmin
    yesterday






  • 1





    No, clustered indexes don't do that. See my demo here.

    – Erik Darling
    yesterday














  • 1





    Silly question: why not create a clustered index? it looks like if you created one on A, it would solve a few different problems? Is the identity column a nonclustered primary key?

    – Erik Darling
    yesterday











  • @ErikDarling Trying to learn from many experts in the forum. Apologies for silly question.I wish, I could have been in the position to create clustered index. Since, its a vendor supported code, don't have much room for change. Yes, primary key is created as non-clustered column.

    – Learning_DBAdmin
    yesterday











  • I didn't mean your question was silly, I meant mine was. I understand the pain of supporting a vendor database. But to get things right, you can create nonclustered indexes, but not clustered indexes?

    – Erik Darling
    yesterday











  • @ErikDarling 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. I have tried many times for clustered index creation however have faced so much resistance from them.I will try once again with them to create clustered index on A column, I would also see if we can have clustered index on L & A; this would be safer for them(I guess).

    – Learning_DBAdmin
    yesterday






  • 1





    No, clustered indexes don't do that. See my demo here.

    – Erik Darling
    yesterday








1




1





Silly question: why not create a clustered index? it looks like if you created one on A, it would solve a few different problems? Is the identity column a nonclustered primary key?

– Erik Darling
yesterday





Silly question: why not create a clustered index? it looks like if you created one on A, it would solve a few different problems? Is the identity column a nonclustered primary key?

– Erik Darling
yesterday













@ErikDarling Trying to learn from many experts in the forum. Apologies for silly question.I wish, I could have been in the position to create clustered index. Since, its a vendor supported code, don't have much room for change. Yes, primary key is created as non-clustered column.

– Learning_DBAdmin
yesterday





@ErikDarling Trying to learn from many experts in the forum. Apologies for silly question.I wish, I could have been in the position to create clustered index. Since, its a vendor supported code, don't have much room for change. Yes, primary key is created as non-clustered column.

– Learning_DBAdmin
yesterday













I didn't mean your question was silly, I meant mine was. I understand the pain of supporting a vendor database. But to get things right, you can create nonclustered indexes, but not clustered indexes?

– Erik Darling
yesterday





I didn't mean your question was silly, I meant mine was. I understand the pain of supporting a vendor database. But to get things right, you can create nonclustered indexes, but not clustered indexes?

– Erik Darling
yesterday













@ErikDarling 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. I have tried many times for clustered index creation however have faced so much resistance from them.I will try once again with them to create clustered index on A column, I would also see if we can have clustered index on L & A; this would be safer for them(I guess).

– Learning_DBAdmin
yesterday





@ErikDarling 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. I have tried many times for clustered index creation however have faced so much resistance from them.I will try once again with them to create clustered index on A column, I would also see if we can have clustered index on L & A; this would be safer for them(I guess).

– Learning_DBAdmin
yesterday




1




1





No, clustered indexes don't do that. See my demo here.

– Erik Darling
yesterday





No, clustered indexes don't do that. See my demo here.

– Erik Darling
yesterday










1 Answer
1






active

oldest

votes


















5














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.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233020%2fsuggestion-on-missing-index-creation%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    5














    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.






    share|improve this answer




























      5














      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.






      share|improve this answer


























        5












        5








        5







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered yesterday









        Brent OzarBrent Ozar

        35.5k19108241




        35.5k19108241






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233020%2fsuggestion-on-missing-index-creation%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            He _____ here since 1970 . Answer needed [closed]What does “since he was so high” mean?Meaning of “catch birds for”?How do I ensure “since” takes the meaning I want?“Who cares here” meaningWhat does “right round toward” mean?the time tense (had now been detected)What does the phrase “ring around the roses” mean here?Correct usage of “visited upon”Meaning of “foiled rail sabotage bid”It was the third time I had gone to Rome or It is the third time I had been to Rome

            Image
            Engineer refusing to file/disclose patents Extending the spectral theorem for bounded self adjoint operators to bounded normal operators Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries? Why is it that I can sometimes guess the next note? What linear sensor for a keyboard? Why don’t women say שלא עשני גויה and שפחה, according to Ashkenazim? Did arcade monitors have same pixel aspect ratio as TV sets? anything or something to eat Query about absorption line spectra Can I use my Chinese passport to enter China after I acquired another citizenship? Fuse symbol on toroidal transformer Create all possible words using a set or letters Does the Mind Blank spell prevent the target from being frightened? Can I sign legal documents with a smiley face? When quoting, must I also copy hyphens used to divide words that continue on the next line? Hot bath for aluminium engine block and heads Journal losing indexing services Is it possib
            Read more

            Bunad

            Image
            Tradisjonelle folkedrakter frå Aust-Telemark, truleg frå 1880-åra. Drakter som dette er grunnlaget for moderne bunadar. Foto: Axel Lindahl Bunad frå Eggedal i Buskerud fotografert i 1906. Bunad (frå kledebunad , der norrønt «búnaðr» har same rot som (føre)bu) er ei samnemning brukt om klesdrakter i tradisjonell nordisk stil — ofte utvikla frå folkedrakter, men likevel i ein eigen tradisjon. Ein bunad er typisk ei forseggjort og kostbar drakt, med mykje broderi, og med handsmidde smykke i gull eller sølv, særleg dei særeigne søljene, som obligatorisk prynad i tillegg. Bunad og bunadbruk er eit debattemne med mange ulike oppfatningar, til dømes om kva som er ein bunad, og om ein kan endra på utforminga av draktene eller ikkje. Den norske bunadrørsla går tilbake til folkedansrørsla og føregangsfolk som Hulda Garborg og Klara Semb. I dag blir bunad brukt som nasjonaldrakt. Festbunad er likestilt med galla-antrekk, og ofte nytta ved særskilde høve, som konfirmasjonar eller d
            Read more

            Færeyskur hestur Heimild | Tengill | Tilvísanir | LeiðsagnarvalRossið - síða um færeyska hrossið á færeyskuGott ár hjá færeyska hestinum

            Image
            FæreyjarHestar færeyskahjaltlandshestinumíslenski hesturinnBretlandskolanámumenskuWikipediafæreyskuWikipediaRossið - síða um færeyska hrossið á færeysku Færeyskur hestur Úr Wikipediu, frjálsa alfræðiritinu Jump to navigation Jump to search Rani og Grani. Grani í vetrarfeldi. Færeyski hesturinn (færeyska: Føroyska rossið ) er hestur sem hefur lifað í Færeyjum í hundruði ára. Hann er smágerður, 120-132 cm á hæð, og helst skyldur hjaltlandshestinum. Hlutverk hans var að draga vagna og plóg og bera klyfjar. Hann hefur fjórar gangtegundir eins og íslenski hesturinn (þar með talið tölt) og er með fjölda litaafbrigði. Nú til dags er hann notaður til frístunda. Í lok 19. aldar voru til um 800 hross. Mörg þeirra voru seld til Bretlands til að vinna í kolanámum og um 1960 voru aðeins nokkrir hestar eftir. Átak var gert í að varðveita hestinn og eru nú til 70-80 hross í Færeyjum. [1] Heimild |   Wikimedia Commons er með margmiðlunarefni sem te
            Read more