SQL Server Performance Forum – Threads Archive

How to determine if indexes are used?

Hi, Currently, i have a table with 24 indexes (!!!), and most of them are composite indexes. How do i determine whether all the indexes are used? I suspect only a few of them are used, and many of them can be dropped, since they’re ‘covered’ by another index, but i’m not completely sure. Here is the reverse engineered script: CREATE CLUSTERED INDEX [DIXI_UNIT] ON [dbo].[UNIT]([xfr_insert_pid]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT] ON [dbo].[UNIT]([serial_number], [part_number], [lifecycle], [site_num]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_order_key] ON [dbo].[UNIT]([order_key], [site_num]) ON [PRIMARY]
GO CREATE INDEX [DIXU_UNIT] ON [dbo].[UNIT]([xfr_update_pid], [xfr_insert_pid]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_LOT] ON [dbo].[UNIT]([lot_key], [site_num]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_PARENT_UNIT] ON [dbo].[UNIT]([parent_unit_key], [site_num]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_ORDER_ITEM] ON [dbo].[UNIT]([order_item_key], [site_num]) ON [PRIMARY]
GO CREATE INDEX [DIX_ORIGINAL_UNIT] ON [dbo].[UNIT]([original_site_num], [original_unit_key]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_CONTAINER] ON [dbo].[UNIT]([container_key], [container_type]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_REVIVE_HISTORY] ON [dbo].[UNIT]([historical_unit_key], [historical_site_num]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_TOBJ_STATUS_KEY] ON [dbo].[UNIT]([tobj_status_key], [site_num]) ON [PRIMARY]
GO CREATE INDEX [DIX_UNIT_PRE_SERIALIZED] ON [dbo].[UNIT]([pre_serialized_lot_key], [pre_serialized_lot_site_num]) ON [PRIMARY]
GO CREATE INDEX [UNIT77] ON [dbo].[UNIT]([original_site_num], [original_unit_key], [tobj_status_key]) ON [PRIMARY]
GO /****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC (‘CREATE STATISTICS [hind_1404584092_68A_29A_30A] ON [dbo].[UNIT] ([tobj_status_key], [original_site_num], [original_unit_key]) ‘)
GO /****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC (‘CREATE STATISTICS [hind_1404584092_1A_2A] ON [dbo].[UNIT] ([unit_key], [site_num]) ‘)
GO CREATE INDEX [FIDX_UNIT_uda0] ON [dbo].[UNIT]([uda_0]) ON [PRIMARY]
GO Please help!! Raymond

I’ve read the article
http://www.sql-server-performance.com/indexes_not_equal.asp Following the article, and on this index that i have on one of my tables: CREATE INDEX [DIX_UNIT_TOBJ_STATUS_KEY] ON [dbo].[UNIT]([tobj_status_key], [site_num]) ON [PRIMARY] Doing a dbcc show_statistics, i get the following results:
Statistics for INDEX ‘DIX_UNIT_TOBJ_STATUS_KEY’. All density Average Length Columns 1.0954499E-6 4.0 tobj_status_key
1.0954499E-6 8.0 tobj_status_key, xfr_insert_pid (2 row(s) affected) Does this mean the column ‘site_num’ is not in use? Raymond

Run DBCC SHOW_STATISTICS on site_num and see. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Tried to, but i got the error message: Server: Msg 2767, Level 16, State 1, Line 1
Could not locate statistics ‘SITE_NUM’ in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I don’t have an index just for the column ‘site_num’… Raymond

Based on my earlier results, how do i interpret this: Statistics for INDEX ‘DIX_UNIT_TOBJ_STATUS_KEY’. All density Average Length Columns 1.0954499E-6 4.0 tobj_status_key
1.0954499E-6 8.0 tobj_status_key, xfr_insert_pid (2 row(s) affected)
My index is:
CREATE INDEX [DIX_UNIT_TOBJ_STATUS_KEY] ON [dbo].[UNIT]([tobj_status_key], [site_num]) ON [PRIMARY]
Raymond

The following articles will help you:
http://www.sql-server-performance.com/indexes_not_equal.asp
http://www.mssqlcity.com/Articles/Adm/IndexStat70.htm Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

i’ve read both the articles, but they don’t explain why the column ‘xfr_insert_pid’ appear in the summary report, even though that column is not part of the index? and the column ‘site_num’ that is part of the index does not appear in the summary?
Statistics for INDEX ‘DIX_UNIT_TOBJ_STATUS_KEY’.
All density Average Length Columns
1.0954499E-6 4.0 tobj_status_key
1.0954499E-6 8.0 tobj_status_key, xfr_insert_pid
(2 row(s) affected)
My index is:
CREATE INDEX [DIX_UNIT_TOBJ_STATUS_KEY] ON [dbo].[UNIT]([tobj_status_key], [site_num]) ON [PRIMARY]
Raymond

]]>