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
Is HostGator storing my password in plaintext?
Tiptoe or tiphoof? Adjusting words to better fit fantasy races
Cynical novel that describes an America ruled by the media, arms manufacturers, and ethnic figureheads
Will it be accepted, if there is no ''Main Character" stereotype?
Bash method for viewing beginning and end of file
Hostile work environment after whistle-blowing on coworker and our boss. What do I do?
What is the oldest known work of fiction?
There is only s̶i̶x̶t̶y one place he can be
Why is delta-v is the most useful quantity for planning space travel?
What is the opposite of 'gravitas'?
How does it work when somebody invests in my business?
Are there any comparative studies done between Ashtavakra Gita and Buddhim?
Using parameter substitution on a Bash array
Print name if parameter passed to function
Have I saved too much for retirement so far?
How to be diplomatic in refusing to write code that breaches the privacy of our users
What would happen if the UK refused to take part in EU Parliamentary elections?
What will be the benefits of Brexit?
How do I rename a LINUX host without needing to reboot for the rename to take effect?
Is the destination of a commercial flight important for the pilot?
Go Pregnant or Go Home
Why "be dealt cards" rather than "be dealing cards"?
Is exact Kanji stroke length important?
Where in the Bible does the greeting ("Dominus Vobiscum") used at Mass come from?
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
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