February 10, 2014 at 9:20 am
The table has the index for months...
Current existing index
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_ProductKey] ON [dbo].[PurchaseOrderDetail]
(
[ProductKey] ASC
)
but the missing index report run by the DBA and my running sp_BlitzIndex shows the following:
DBA missing index
CREATE INDEX missing_index_7023 ON [xxxxxx].[dbo].[PurchaseOrderDetail] ([ProductKey])
sp_BlitzIndex
DatabaseSchemaTableMagic Benefit NumberMissing Index DetailsAvg Query CostEst Index ImprovementSeeksScansCompilesEquality ColumnsInequality ColumnsIncluded ColumnsEstimated ImpactCreate TSQLMore InfoDisplay Order
xxxxxxxdboPurchaseOrderDetail222961EQUALITY: [ProductKey] INCLUDES: [PurchaseOrderDetailID], [PurchaseOrderID] 0.695.73883011[ProductKey]NULL[PurchaseOrderDetailID], [PurchaseOrderID]3883 uses; Impact: 95.7%; Avg query cost: 0.6CREATE INDEX [ix_PurchaseOrderDetail_ProductKey_includes] ON [Nucleus].[dbo].[PurchaseOrderDetail] ([ProductKey]) INCLUDE ([PurchaseOrderDetailID], [PurchaseOrderID]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);EXEC dbo.sp_BlitzIndex @DatabaseName='Nucleus', @SchemaName='dbo', @TableName='PurchaseOrderDetail';1
sp_BLitz index seems to indicate that the missing index has an include of 2 fields. The current existing index shows that is in used ( user seeks)
TableNameIndexNameColumnNameIndex_IDuser_seeksuser_scansuser_lookupsuser_updates
PurchaseOrderDetailIX_PurchaseOrderDetail_ProductKeyProductKey2339120068317
Does this mean that I have to create 2 indexes?
I am trying to not have too many indexes... What are your thoughts?
Thank you very much in advance.
February 10, 2014 at 10:17 am
The included columns are there in order to omit bookmark lookups which could be costly and in some scenarios could lead to deadlocks. But it all depends upon your workload.
As far as creating two indexes is concerned, no, you don't need two indexes. Since you already have the index on the same column, you should only recreate the existing index with the included columns as suggested. Hope it helps.
February 11, 2014 at 1:29 pm
Thanks! I will probably just keep the existing one and ignore the one flagged as missing for now.
February 11, 2014 at 2:45 pm
You need to run these commands (or the equivalent). Run them online if possible, and using tempdb for sort if possible:
DROP INDEX [IX_PurchaseOrderDetail_ProductKey] ON [dbo].[PurchaseOrderDetail]
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_ProductKey]
ON [dbo].[PurchaseOrderDetail] ( [ProductKey] )
INCLUDE ( [PurchaseOrderDetailID], [PurchaseOrderID] )
WITH ( FILLFACTOR = 100 /*, ONLINE = ON*/ /*, SORT_IN_TEMPDB = ON */ )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 11, 2014 at 2:53 pm
Is PurchaseOrderDetailID the clustered key?
If it is, is it necessary to have it in the include statement, since the clustered key is always a part of a non clustered index anyway?
February 11, 2014 at 3:00 pm
May I ask why the covering index is a better choice? Thanks!
February 11, 2014 at 3:17 pm
The single index with the included columns can handle queries for both ProductKey alone and for ProductKey and one/both of the included columns. That means you only need one index instead of two. The overhead of a second index is almost certainly worse here than just using the slightly larger index for queries that need only ProductKey.
Btw, even if one of those IDs is the clustering key, or part of the clustering key, it doesn't hurt to explicitly include it in the index. And it could be vital if the existing clustering key is changed, as it explicitly documents that this index needs that column included in it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 11, 2014 at 5:34 pm
Thank you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply