0

this is a script that does the following:

create a database called "radha" for testing purposes,

create the table ProductItemDetailsDenorm

create the clustered primary key

create set of non-clustered indexes on table ProductItemDetailsDenorm

use master
go

create database radhe
go

USE [radhe]
GO

/****** Object:  Table [dbo].[ProductItemDetailsDenorm]    Script Date: 17/05/2016 11:42:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductItemDetailsDenorm]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductItemDetailsDenorm](
    [ProductURLID] [int] NOT NULL,
    [ClassID] [int] NOT NULL,
    [SegmentID] [int] NOT NULL,
    [GroupID] [int] NOT NULL,
    [ItemNo] [varchar](10) NOT NULL,
    [Tier1] [varchar](10) NOT NULL,
    [Tier2] [varchar](10) NOT NULL,
    [Tier3] [varchar](10) NOT NULL,
    [LanguageID] [smallint] NOT NULL,
    [GenderCode] [char](1) NOT NULL,
    [URL] [nvarchar](250) NOT NULL,
    [SeasonItemID] [varchar](5) NOT NULL,
    [SeasonID] [char](1) NOT NULL,
    [Tier1Descr] [nvarchar](250) NOT NULL,
    [Tier2Descr] [nvarchar](250) NOT NULL,
    [Tier3Descr] [nvarchar](250) NOT NULL,
    [Tier2Sort] [int] NOT NULL,
    [Tier3Sort] [int] NOT NULL,
    [WasPrice] [decimal](18, 4) NOT NULL,
    [Price] [decimal](18, 4) NOT NULL,
    [PricePreTax] [decimal](18, 4) NOT NULL,
    [PriceTaxRateApplied] [decimal](18, 4) NOT NULL,
    [SaleDiscount] [decimal](18, 4) NOT NULL,
    [FullDescription] [varchar](10) NOT NULL,
    [ZoomImageData] [nvarchar](10) NOT NULL,
    [ProductWarning] [nvarchar](250) NOT NULL,
    [AdditionalDetailsText] [nvarchar](max) NOT NULL,
    [AdditionalMeasurementText] [nvarchar](max) NOT NULL,
    [HemmingT1] [varchar](10) NOT NULL,
    [SortOrder] [smallint] NULL,
    [ParentGroupID] [int] NOT NULL,
 CONSTRAINT [PK_ProductItemDetailsDenorm] PRIMARY KEY CLUSTERED 
(
    [ClassID] ASC,
    [SegmentID] ASC,
    [GroupID] ASC,
    [ParentGroupID] ASC,
    [ProductURLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

SET ANSI_PADDING ON
GO

ALTER AUTHORIZATION ON [dbo].[ProductItemDetailsDenorm] TO  SCHEMA OWNER 
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IDX_ClassID_SegmentID_GroupID_ItemNo_LanguageIDTier2_Tier3]    Script Date: 17/05/2016 11:42:50 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ProductItemDetailsDenorm]') AND name = N'IDX_ClassID_SegmentID_GroupID_ItemNo_LanguageIDTier2_Tier3')
CREATE NONCLUSTERED INDEX [IDX_ClassID_SegmentID_GroupID_ItemNo_LanguageIDTier2_Tier3] ON [dbo].[ProductItemDetailsDenorm]
(
    [ClassID] ASC,
    [SegmentID] ASC,
    [GroupID] ASC,
    [ItemNo] ASC,
    [LanguageID] ASC,
    [Tier2] ASC,
    [Tier3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IDX_ItemNo]    Script Date: 17/05/2016 11:42:50 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ProductItemDetailsDenorm]') AND name = N'IDX_ItemNo')
CREATE NONCLUSTERED INDEX [IDX_ItemNo] ON [dbo].[ProductItemDetailsDenorm]
(
    [ItemNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IDX_URL]    Script Date: 17/05/2016 11:42:50 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ProductItemDetailsDenorm]') AND name = N'IDX_URL')
CREATE NONCLUSTERED INDEX [IDX_URL] ON [dbo].[ProductItemDetailsDenorm]
(
    [URL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_ProductItemDetailsDenorm_ClassID_SegmentID_GroupID_Tier1_Tier2_LanguageID_INCL]    Script Date: 17/05/2016 11:42:50 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ProductItemDetailsDenorm]') AND name = N'IX_ProductItemDetailsDenorm_ClassID_SegmentID_GroupID_Tier1_Tier2_LanguageID_INCL')
CREATE NONCLUSTERED INDEX [IX_ProductItemDetailsDenorm_ClassID_SegmentID_GroupID_Tier1_Tier2_LanguageID_INCL] ON [dbo].[ProductItemDetailsDenorm]
(
    [ClassID] ASC,
    [SegmentID] ASC,
    [GroupID] ASC,
    [Tier1] ASC,
    [Tier2] ASC,
    [LanguageID] ASC
)
INCLUDE (   [URL]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE NONCLUSTERED INDEX IDX_Tier1_Tier2_Tier3_LanguageID_INC_ClassID_SegmentID_GroupID_URL_SeasonItemID_Tier1Descr 
ON [dbo].[ProductItemDetailsDenorm] 
([Tier1], [Tier2], [Tier3], [LanguageID]) 
INCLUDE (ClassID,SegmentID,GroupID,URL,SeasonItemID,Tier1Descr) --

CREATE NONCLUSTERED INDEX IDX_SegmentID_GroupID_Tier1_LanguageID_SeasonItemID_INC_Tier2_Tier3_ParentGroupID 
ON [dbo].[ProductItemDetailsDenorm] 
([SegmentID], [GroupID], [Tier1], [LanguageID], [SeasonItemID]) 
INCLUDE (Tier2,Tier3,ParentGroupID) --

CREATE NONCLUSTERED INDEX IDX_ClassID_Tier1_LanguageIDItemNo_INC_Tier2_Tier3_WasPrice_Price 
ON [dbo].[ProductItemDetailsDenorm] ([ClassID], [Tier1], [LanguageID], [ItemNo]) 
INCLUDE (Tier2,Tier3,WasPrice,Price) --

CREATE NONCLUSTERED INDEX IDX_ProductURLID 
ON [dbo].[ProductItemDetailsDenorm] ([ProductURLID]) --

CREATE NONCLUSTERED INDEX IDX_ClassID_GroupID_Tier1_LanguageID_INC_SegmentID_SeasonItemID_ProductWarning 
ON [dbo].[ProductItemDetailsDenorm] ([ClassID], [GroupID], [Tier1], [LanguageID]) 
INCLUDE (SegmentID,SeasonItemID,ProductWarning) --

CREATE NONCLUSTERED INDEX [ix_ProductItemDetailsDenorm_ClassID_Tier1_LanguageID_ItemNo_includes] 
ON [dbo].[ProductItemDetailsDenorm] ([ClassID], [Tier1], [LanguageID], [ItemNo]) 
INCLUDE (SegmentID,GroupID,Tier2,Tier3,GenderCode,URL,SeasonItemID,SeasonID,Tier1Descr,Tier2Descr,
         Tier3Descr,Tier2Sort,Tier3Sort,WasPrice,Price,PricePreTax,PriceTaxRateApplied,SaleDiscount,FullDescription,
         ZoomImageData,ProductWarning,AdditionalDetailsText,AdditionalMeasurementText,HemmingT1,SortOrder) --

CREATE NONCLUSTERED INDEX IDX_ClassID_GroupID_Tier1_LanguageID_SeasonItemID_INC_SegmentID_ProductWarning 
ON [dbo].[ProductItemDetailsDenorm] ([ClassID], [GroupID], [Tier1], [LanguageID], [SeasonItemID]) 
INCLUDE (SegmentID,ProductWarning) --

 CREATE NONCLUSTERED INDEX IDX_ClassID_Tier1_LanguageIDItemNo_INC_many_cols_heavy_index
ON [dbo].[ProductItemDetailsDenorm] 
([ClassID], [Tier1], [LanguageID], [ItemNo]) 
INCLUDE (SegmentID,GroupID,Tier2,Tier3,GenderCode,URL,SeasonItemID,SeasonID,
          Tier1Descr,Tier2Descr,Tier3Descr,Tier2Sort,Tier3Sort,WasPrice,Price,PricePreTax,PriceTaxRateApplied,
          SaleDiscount,FullDescription,ZoomImageData,ProductWarning,AdditionalDetailsText,
          AdditionalMeasurementText,HemmingT1,SortOrder) WITH (ONLINE = ON)


 CREATE NONCLUSTERED INDEX IDX_ClassID_SegmentID_GroupID_Tier1_Tier3_LanguageID_INC_Tier2_URL 
ON [dbo].[ProductItemDetailsDenorm] 
([ClassID], [SegmentID], [GroupID], [Tier1], [Tier3], [LanguageID]) 
INCLUDE (Tier2,URL) WITH (ONLINE = ON)


CREATE NONCLUSTERED INDEX IDX_ClassID_Tier1_Tier2_LanguageID_INC_all_columns
ON [dbo].[ProductItemDetailsDenorm] ([ClassID], [Tier1], [Tier2], [LanguageID]) 
INCLUDE (SegmentID,GroupID,ItemNo,Tier3,GenderCode,URL,SeasonItemID,SeasonID,Tier1Descr,Tier2Descr,Tier3Descr,Tier2Sort,Tier3Sort,WasPrice,Price,PricePreTax,PriceTaxRateApplied,SaleDiscount,FullDescription,ZoomImageData,ProductWarning,AdditionalDetailsText,AdditionalMeasurementText,HemmingT1,SortOrder,ParentGroupID) WITH (ONLINE = ON)


CREATE NONCLUSTERED INDEX IDX_ItemNo_LanguageID ON [dbo].[ProductItemDetailsDenorm] ([ItemNo], [LanguageID]) 
INCLUDE (Tier1) 
--WITH (ONLINE = ON, drop_existing=on)


CREATE NONCLUSTERED INDEX IDX_Tier3_INC_ClassID_SegmentID_GroupID_Tier1_Tier2_SeasonItemID 
ON [dbo].[ProductItemDetailsDenorm] ([Tier3]) 
INCLUDE (ClassID,SegmentID,GroupID,Tier1,Tier2,SeasonItemID) WITH (ONLINE = ON)


CREATE NONCLUSTERED INDEX IDX_Tier3Tier1_Tier2_INC_ClassID_SegmentID_GroupID_URL 
ON [dbo].[ProductItemDetailsDenorm] ([Tier3], [Tier1], [Tier2]) 
INCLUDE (ClassID,SegmentID,GroupID,URL,SeasonItemID) 
--WITH (ONLINE = ON, drop_existing=on)


CREATE NONCLUSTERED INDEX IDX_Tier2_Tier3_INC_Tier1 
ON [dbo].[ProductItemDetailsDenorm] ([Tier2], [Tier3]) INCLUDE (Tier1) WITH (ONLINE = ON)

CREATE NONCLUSTERED INDEX IDX_Tier2_Tier3Tier1_INC_ClassID_SegmentID_GroupID_URL 
ON [dbo].[ProductItemDetailsDenorm] ([Tier2], [Tier3], [Tier1]) 
INCLUDE (ClassID,SegmentID,GroupID,URL,SortOrder) 
--WITH (ONLINE = ON, drop_existing=on)

CREATE NONCLUSTERED INDEX IDX_ClassID_SegmentID_GroupID_Tier1_Tier3_LanguageIDTier2_INC_URL_SeasonItemID_Tier1Descr_Tier2Descr 
ON [dbo].[ProductItemDetailsDenorm] 
([ClassID], [SegmentID], [GroupID], [Tier1], [Tier3], [LanguageID], [Tier2]) 
INCLUDE (URL,SeasonItemID,Tier1Descr,Tier2Descr) 

-------------

I am working on managing the overlapping and duplicated indexes for the table ProductItemDetailsDenorm.

This table has 500K rows. It is updated only once a day, and not many records, few thousands on average. It is used a lot for reading, in many different stored procedures.

when I run my script to find overlapping indexes below:

Finding and Eliminating Duplicate or Overlapping Indexes

use radhe
go

;WITH CTE_INDEX_DATA AS (
       SELECT
              SCHEMA_DATA.name AS schema_name,
              TABLE_DATA.name AS table_name,
              INDEX_DATA.name AS index_name,
              STUFF((SELECT  ', ' + COLUMN_DATA_KEY_COLS.name + ' ' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END -- Include column order (ASC / DESC)

                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_KEY_COLS
                                                ON T.object_id = INDEX_DATA_KEY_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS
                                                ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_KEY_COLS
                                                ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
                                  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS key_column_list ,
          STUFF(( SELECT  ', ' + COLUMN_DATA_INC_COLS.name
                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_INC_COLS
                                                ON T.object_id = INDEX_DATA_INC_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS
                                                ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_INC_COLS
                                                ON T.object_id = COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
                                  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS include_column_list,
       INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)
       FROM sys.indexes INDEX_DATA
       INNER JOIN sys.tables TABLE_DATA
       ON TABLE_DATA.object_id = INDEX_DATA.object_id
       INNER JOIN sys.schemas SCHEMA_DATA
       ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
       WHERE TABLE_DATA.is_ms_shipped = 0
       AND INDEX_DATA.type_desc IN ('NONCLUSTERED', 'CLUSTERED')
)
SELECT
       *
FROM CTE_INDEX_DATA DUPE1
WHERE EXISTS
(SELECT * FROM CTE_INDEX_DATA DUPE2
 WHERE DUPE1.schema_name = DUPE2.schema_name
 AND DUPE1.table_name = DUPE2.table_name
 AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
 AND DUPE1.index_name <> DUPE2.index_name)

this is the result:

enter image description here

I can see that the index on row 1 is contained in the index on row 8. index on row 2 is contained in index on row 4 based on the included columns and so on...

questions:

1) performance in retrieving data from this particular table is a must, this database has much more reading than writing, is it ok to leave the overlapped indexes if I can see that they have been used?

2) How can I improve this script so that I will have a column called [contained] that would show the list of indexes where the current index is contained?

for example on the first line it would have on the [contained] column "IDX_ItemNoLanguageID", which is the name of the index on the 8th row.

  • 5
    Instead of reinventing the wheel .. Look t sp_blitzindex from brentozar.com – Kin Shah May 17 '16 at 12:30
  • @kin, thanks, very useful as always. However, I have been using Brent's tools and still I like to have my own so that I can compare. I also use DTA. question 1) is it ok to leave the overlapped indexes if I can see that they have been used? – Marcello Miorelli May 17 '16 at 14:29
  • 1
    if the indexes are exactly same, you can drop the dupe. If they are not, the optimizer might use them depending on the queries you are running. – Kin Shah May 17 '16 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.