I have run a database engine tuning advisor session, DTA, to optimize my database.
The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example on my table ProductIcon below:
CREATE STATISTICS [_dta_stat_172123954_1_2_3_4_5_12] ON [dbo].[ProductIcon] ([ClassID], [SegmentID], [GroupID], [Tier1], [LanguageID], [Active]) go CREATE STATISTICS [_dta_stat_172123954_11_1_2_3_4_5_12] ON [dbo].[ProductIcon] ([IconTypeID], [ClassID], [SegmentID], [GroupID], [Tier1], [LanguageID], [Active]) go
this is the table definition:
IF OBJECT_ID('[dbo].[ProductIcon]') IS NOT NULL DROP TABLE [dbo].[ProductIcon] GO CREATE TABLE [dbo].[ProductIcon] ( [ClassID] INT NOT NULL, [SegmentID] INT NOT NULL, [GroupID] INT NOT NULL, [Tier1] VARCHAR(10) NOT NULL, [LanguageID] SMALLINT NOT NULL CONSTRAINT [DF_ProductIcon_LanguageID] DEFAULT ((1)), [Image] VARCHAR(50) NOT NULL CONSTRAINT [DF_ProductIcon_Image] DEFAULT (''), [Descr] VARCHAR(100) NOT NULL CONSTRAINT [DF_ProductIcon_Descr] DEFAULT (''), [SortOrder] INT NOT NULL CONSTRAINT [DF__ProductIc__SortO__6A358A53] DEFAULT ((1)), [URL] NVARCHAR(512) NULL, [LinkJSClick] NVARCHAR(512) NULL, [IconTypeID] INT NULL, [Active] BIT NOT NULL CONSTRAINT [DF_ProductIcon_Active] DEFAULT ((1)), [Tier2] VARCHAR(10) NOT NULL CONSTRAINT [DF_ProductIcon_Tier2] DEFAULT (''), CONSTRAINT [PK_ProductIcon] PRIMARY KEY CLUSTERED ([ClassID] asc, [SegmentID] asc, [GroupID] asc, [Tier1] asc, [LanguageID] asc, [SortOrder] asc, [Tier2] asc))
If I knew exactly how the stats would be used I would probably be able to decide whether or not I should keep both or one of them.
Is there any way I can have this vision so that I can make my decision as to which one(s) of them I should keep?
we are still on sql 2005 for this one.
This table currently does not have any other index , however the following index is appointed as missing:
CREATE NONCLUSTERED INDEX IDX_IconTypeID_Active_INC_1_2_3_4_5_6 ON [dbo].[ProductIcon] ([IconTypeID], [Active]) INCLUDE (ClassID,SegmentID,GroupID,Tier1,Descr,Tier2)