Suggestion on Missing Index CreationHow does SQL Server determine key column order in missing index requests?SQL Server: index creation dateDynamic temporary index creationParent-Child Tree Hierarchical ORDERAdding index to large mysql tablesFirebird Index Creation FailsMySQL Index Creation Internalsdeteriorating stored procedure running timesClustered Index Creationsp_BlitzIndex finding “Unknown” indexClustered index scan appears to be costed too low with row count spool
Bash method for viewing beginning and end of file
What is the intuitive meaning of having a linear relationship between the logs of two variables?
If you attempt to grapple an opponent that you are hidden from, do they roll at disadvantage?
Is there a problem with hiding "forgot password" until it's needed?
Curses work by shouting - How to avoid collateral damage?
Tiptoe or tiphoof? Adjusting words to better fit fantasy races
How does it work when somebody invests in my business?
What will be the benefits of Brexit?
Short story about space worker geeks who zone out by 'listening' to radiation from stars
is this a spam?
Was the picture area of a CRT a parallelogram (instead of a true rectangle)?
Modify casing of marked letters
Is there an Impartial Brexit Deal comparison site?
Ways to speed up user implemented RK4
Greatest common substring
Your magic is very sketchy
What is the oldest known work of fiction?
Efficiently merge handle parallel feature branches in SFDX
What is the term when two people sing in harmony, but they aren't singing the same notes?
Applicability of Single Responsibility Principle
There is only s̶i̶x̶t̶y one place he can be
Why "be dealt cards" rather than "be dealing cards"?
Can criminal fraud exist without damages?
Is expanding the research of a group into machine learning as a PhD student risky?
Suggestion on Missing Index Creation
How does SQL Server determine key column order in missing index requests?SQL Server: index creation dateDynamic temporary index creationParent-Child Tree Hierarchical ORDERAdding index to large mysql tablesFirebird Index Creation FailsMySQL Index Creation Internalsdeteriorating stored procedure running timesClustered Index Creationsp_BlitzIndex finding “Unknown” indexClustered index scan appears to be costed too low with row count spool
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:
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
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
|
show 4 more comments
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:
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
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
1
Silly question: why not create a clustered index? it looks like if you created one onA, 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
|
show 4 more comments
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:
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
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
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)
sql-server index sp-blitzindex
sql-server index sp-blitzindex
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
asked yesterday
Learning_DBAdminLearning_DBAdmin
380114
380114
1
Silly question: why not create a clustered index? it looks like if you created one onA, 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
|
show 4 more comments
1
Silly question: why not create a clustered index? it looks like if you created one onA, 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
|
show 4 more comments
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.
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
add a comment |
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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.
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
add a comment |
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.
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
add a comment |
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.
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
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.
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
answered yesterday
Brent OzarBrent Ozar
35.5k19108241
35.5k19108241
add a comment |
add a comment |
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
-index, sp-blitzindex, sql-server
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