January 5, 2017 at 8:52 am
Afternoon Guys,
I suspect the answer to my question will be along the lines of YMMV, but I'll ask it anyway 🙂
When looking through some execution plans earlier for some queries that were flagged up as Big IO consumers, I noticed that two separate indexes were suggested by the optimizer. As follows, these are for two separate queries that get run fairly requently:
--63% Est. Improvement
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ACTIVITY_GEN] ([ACT_TYPE],[APPTYPE_ID])
INCLUDE ([REFERENCE],[START_DATE],[START_TIME])
--21% Est. Improvement
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ACTIVITY_GEN] ([ACT_TYPE],[APPTYPE_ID])
INCLUDE ([REFERENCE],[STATUS])
So my question is that there isn't much different about them except within the INCLUDE list... in these types of cases were two indexes have been suggested with different INCLUDEs can I combine the includes into one... Ultimately, ending up with something like this:
CREATE NONCLUSTERED INDEX idx_MySuggestion
ON [dbo].[ACTIVITY_GEN] ([ACT_TYPE],[APPTYPE_ID])
INCLUDE ([REFERENCE],[STATUS],[REFERENCE],[START_DATE],[START_TIME])
Or does that not quite work how I think it works. Or want it to work.
I've not tested any of the indexes yet so this might all be for nought but even separate from this case I'd like to know the answer/best practice should one exist.
Cheers
Alex
January 5, 2017 at 9:09 am
alex.sqldba (1/5/2017)
Afternoon Guys,I suspect the answer to my question will be along the lines of YMMV, but I'll ask it anyway 🙂
When looking through some execution plans earlier for some queries that were flagged up as Big IO consumers, I noticed that two separate indexes were suggested by the optimizer. As follows, these are for two separate queries that get run fairly requently:
--63% Est. ImprovementCREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ACTIVITY_GEN] ([ACT_TYPE],[APPTYPE_ID])
INCLUDE ([REFERENCE],[START_DATE],[START_TIME])
--21% Est. ImprovementCREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ACTIVITY_GEN] ([ACT_TYPE],[APPTYPE_ID])
INCLUDE ([REFERENCE],[STATUS])
So my question is that there isn't much different about them except within the INCLUDE list... in these types of cases were two indexes have been suggested with different INCLUDEs can I combine the includes into one... Ultimately, ending up with something like this:
CREATE NONCLUSTERED INDEX idx_MySuggestionON [dbo].[ACTIVITY_GEN] ([ACT_TYPE],[APPTYPE_ID])
INCLUDE ([REFERENCE],[STATUS],[REFERENCE],[START_DATE],[START_TIME])
Or does that not quite work how I think it works. Or want it to work.
I've not tested any of the indexes yet so this might all be for nought but even separate from this case I'd like to know the answer/best practice should one exist.
Cheers
Alex
The missing indexes subsystem is basically built to cover a query with a nonclustered index. It also doesn't check existing indexes nor does it care about how much you may update the rows (and thus incur significant extra work). It will duplicate/overlap like a big dog.
If you intend to create these indexes you should absolutely combine them into one. But there is no need to put Reference in the INCLUDE twice. Note that it also doesn't matter what order you put the INCLUDEs in.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 5, 2017 at 10:21 am
alex.sqldba (1/5/2017)
...So my question is that there isn't much different about them except within the INCLUDE list... in these types of cases were two indexes have been suggested with different INCLUDEs can I combine the includes into one...Alex
also please note that the missing index notices you get in execution plans or the dynamic management views DO NOT give you an order the key columns of the index should be in:
https://technet.microsoft.com/en-us/library/ms345485(v=sql.105).aspx#Anchor_2
it's best to analyze usage of this table, and consider the columns that are most frequently referenced in joins or WHERE clauses, and have the best selectivity first. That isn't determined for you.
January 5, 2017 at 10:27 am
Brilliant, thanks Kevin!
January 5, 2017 at 10:28 am
Cheers Chris! Interest link too! Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply