Excessive indexes. Please advise me.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Excessive indexes. Please advise me.

Author

Topic

mcolli00
Starting Member

19 Posts

Posted - 2010-12-02 : 14:54:57

Hi all! I have a database containing tables with excessive indexes. I am not sure which indexes are safe to keep since I am new at this. Right now, I am dropping the ones containing duplicate keys and those with no user_seek or user_scan activity. Would you suggest any other criteria so that I can simplify the indexes even further? I am new to tuning and want to learn to do it correctly the first time in. Below is a preview of the indexes on one of my tables. Thanks so much for any suggestions!!! MC

index_name index_description index_keys
_dta_index_f_facility_20_192576320__K1_2_3_4_8_9_10_11_12 nonclustered located on PRIMARY fd_pk
_dta_index_f_facility_20_192576320__K1_2_8 nonclustered located on PRIMARY fd_pk
_dta_index_f_facility_20_192576320__K1_3_4 nonclustered located on PRIMARY fd_pk
_dta_index_f_facility_20_192576320__K1_3_4_17 nonclustered located on PRIMARY fd_pk
_dta_index_f_facility_20_192576320__K1_K2_9 nonclustered located on PRIMARY fd_pk, fd_clnt_fk
_dta_index_f_facility_20_192576320__K1_K4_18 nonclustered located on PRIMARY fd_pk, fd_name
_dta_index_f_facility_20_192576320__K1_K4_3_18 nonclustered located on PRIMARY fd_pk, fd_name
_dta_index_f_facility_20_192576320__K18_K1_K2_K20_K25_K19_K17_K3_K4_K5_K6_K7_K8_K9_K10_K11_12_13_14_15_16_21_22_23_24 nonclustered located on PRIMARY fd_fdt_fk, fd_pk, fd_clnt_fk, fd_rts_fk, fd_country_fk, fd_cst_fk, fd_dist_fk, fd_code, fd_name, fd_phoneMain, fd_phoneFax, fd_manager, fd_address1, fd_address2, fd_city, fd_state
_dta_index_f_facility_20_192576320__K18_K1_K4_K3 nonclustered located on PRIMARY fd_fdt_fk, fd_pk, fd_name, fd_code
_dta_index_f_facility_20_192576320__K2_K18_K1_K25_K19_K17_K20_3_4_5_6_7_8_9_10_11_12_13_14_15_16_21_22_23_24 nonclustered located on PRIMARY fd_clnt_fk, fd_fdt_fk, fd_pk, fd_country_fk, fd_cst_fk, fd_dist_fk, fd_rts_fk
_dta_index_f_facility_20_192576320__K2_K3_1_4_17 nonclustered located on PRIMARY fd_clnt_fk, fd_code
f_facility_covering_01 nonclustered located on PRIMARY fd_pk, fd_clnt_fk, fd_dist_fk, fd_fdt_fk
f_facility_fd_pk_WNK_01 nonclustered located on PRIMARY fd_pk
f_facility_fd_pk_WNK_02 nonclustered located on PRIMARY fd_pk
idx_facilityCLNT clustered located on PRIMARY fd_clnt_fk, fd_pk
idx_facilityCODE nonclustered located on PRIMARY fd_code
IX_f_facility_cstfk nonclustered located on PRIMARY fd_cst_fk
IX_f_facility_distfk nonclustered located on PRIMARY fd_dist_fk
IX_f_facility_fdtfk nonclustered located on PRIMARY fd_fdt_fk
PK_f_facility nonclustered, unique, primary key located on PRIMARY fd_pk

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-02 : 15:04:37

Run this and show us the output: http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

Do not drop the indexes even after you've viewed the report. We'll need to talk about the output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mcolli00
Starting Member

19 Posts

Posted - 2010-12-02 : 15:31:29

This is what I got. That was strange that it didn't show facility table.

name indexname index_id reads writes rows reads_per_write drop statement
f_pm_datetable SAIDX_05_f_pm_datetable_3 24 0 0 48105 100 DROP INDEX [SAIDX_05_f_pm_datetable_3] ON [dbo].[f_pm_datetable]
f_pm_datetable f_pm_datetable_pm_rts_fk 25 0 0 48105 100 DROP INDEX [f_pm_datetable_pm_rts_fk] ON [dbo].[f_pm_datetable]
f_webSAUserDataAccess f_webSAUserDataAccess_user_fk_tag_fk 4 0 0 242013 100 DROP INDEX [f_webSAUserDataAccess_user_fk_tag_fk] ON [dbo].[f_webSAUserDataAccess]
f_worktableL idx_woLclntfk 2 0 0 1876331 100 DROP INDEX [idx_woLclntfk] ON [dbo].[f_worktableL]
f_worktableL _dta_index_f_worktableL_20_1631865426__K4_K3_K2_K20_1_9_11_12_13_14_15_16_17_18_19_22_28 3 0 0 1876331 100 DROP INDEX [_dta_index_f_worktableL_20_1631865426__K4_K3_K2_K20_1_9_11_12_13_14_15_16_17_18_19_22_28] ON [dbo].[f_worktableL]
f_worktableL _dta_index_f_worktableL_20_1631865426__K2_K3_K1_K5_9_20 6 0 0 1876331 100 DROP INDEX [_dta_index_f_worktableL_20_1631865426__K2_K3_K1_K5_9_20] ON [dbo].[f_worktableL]
f_worktableL SAIDX_02_f_worktableL_18 7 0 0 1876331 100 DROP INDEX [SAIDX_02_f_worktableL_18] ON [dbo].[f_worktableL]
f_worktableL _dta_index_f_worktableL_5_524685067__K3_K28_K5_K2_K6_K7_K4_1_8_9_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_29_ 30 0 0 1876331 100 DROP INDEX [_dta_index_f_worktableL_5_524685067__K3_K28_K5_K2_K6_K7_K4_1_8_9_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_29_] ON [dbo].[f_worktableL]
f_part _dta_index_f_part_20_1486224645__K24_K1_K30_K42_K44_K46_K23_2_3_4_5_6_7_8_9_10_11_12_13_14_15_16_17_18_20_21_22_25_26_28_29_31_ 6 0 0 58541 100 DROP INDEX [_dta_index_f_part_20_1486224645__K24_K1_K30_K42_K44_K46_K23_2_3_4_5_6_7_8_9_10_11_12_13_14_15_16_17_18_20_21_22_25_26_28_29_31_] ON [dbo].[f_part]
f_part _dta_index_f_part_20_1486224645__K3_K17_1 17 0 0 58541 100 DROP INDEX [_dta_index_f_part_20_1486224645__K3_K17_1] ON [dbo].[f_part]
f_part f_part_covering_01 24 0 0 58541 100 DROP INDEX [f_part_covering_01] ON [dbo].[f_part]
f_worktable SAIDX_f_worktable_13 12 0 0 1543293 100 DROP INDEX [SAIDX_f_worktable_13] ON [dbo].[f_worktable]
f_worktable _dta_index_f_worktable_20_1750869900__K3_K11_K1_K20_K9_K8_K4_K10_K7_K6_K5_K13_K2_K24_K30_K21_17_25_27 19 0 0 1543293 100 DROP INDEX [_dta_index_f_worktable_20_1750869900__K3_K11_K1_K20_K9_K8_K4_K10_K7_K6_K5_K13_K2_K24_K30_K21_17_25_27] ON [dbo].[f_worktable]
f_worktable _dta_index_f_worktable_20_1750869900__K1_K20_10_32 22 0 0 1543293 100 DROP INDEX [_dta_index_f_worktable_20_1750869900__K1_K20_10_32] ON [dbo].[f_worktable]
f_worktable _dta_index_f_worktable_5_1184163414__K20_K11_K8_K7_1_2_3_4_5_6_10_13_30_35 23 0 0 1543293 100 DROP INDEX [_dta_index_f_worktable_5_1184163414__K20_K11_K8_K7_1_2_3_4_5_6_10_13_30_35] ON [dbo].[f_worktable]
f_worktable SAIDX_06_f_worktable_12 41 0 0 1543293 100 DROP INDEX [SAIDX_06_f_worktable_12] ON [dbo].[f_worktable]
f_worktable _dta_index_f_worktable_20_1750869900__K11_K4_K10_K3_1_2_5_6_7_8_13_20_30_35 43 0 0 1543293 100 DROP INDEX [_dta_index_f_worktable_20_1750869900__K11_K4_K10_K3_1_2_5_6_7_8_13_20_30_35] ON [dbo].[f_worktable]
f_worktable idx_worktableFB 53 0 0 1543293 100 DROP INDEX [idx_worktableFB] ON [dbo].[f_worktable]
f_worktableTask SAIDX_01_f_worktableTask_58 2 0 0 1559349 100 DROP INDEX [SAIDX_01_f_worktableTask_58] ON [dbo].[f_worktableTask]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K1_K2_K20_K3_K25 13 0 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K1_K2_K20_K3_K25] ON [dbo].[f_worktableTask]
f_worktableTask f_worktableTask_covering_03 14 0 0 1559349 100 DROP INDEX [f_worktableTask_covering_03] ON [dbo].[f_worktableTask]
f_worktableTask SAIDX_01_f_worktableTask_59 24 0 0 1559349 100 DROP INDEX [SAIDX_01_f_worktableTask_59] ON [dbo].[f_worktableTask]
f_losttime IX_f_losttime_cefk 6 0 0 22261 100 DROP INDEX [IX_f_losttime_cefk] ON [dbo].[f_losttime]
f_equipmentWorkRCenterLink _dta_index_f_equipmentWorkRCenterLink_20_1516688601__K9_K2 3 0 0 46202 100 DROP INDEX [_dta_index_f_equipmentWorkRCenterLink_20_1516688601__K9_K2] ON [dbo].[f_equipmentWorkRCenterLink]
f_equipment IX_f_equipment_fcfk 3 0 0 45225 100 DROP INDEX [IX_f_equipment_fcfk] ON [dbo].[f_equipment]
f_equipment IX_f_equipment_tgfk 14 0 0 45225 100 DROP INDEX [IX_f_equipment_tgfk] ON [dbo].[f_equipment]
f_equipment _dta_index_f_equipment_20_1722905655__K95_K3_K98_K1_2_4_5_6_7_8_9_10_13_20_21_77_92_93_122_123_124_125_126 24 0 0 45225 100 DROP INDEX [_dta_index_f_equipment_20_1722905655__K95_K3_K98_K1_2_4_5_6_7_8_9_10_13_20_21_77_92_93_122_123_124_125_126] ON [dbo].[f_equipment]
f_equipment _dta_index_f_equipment_20_1722905655__K6_K1_K95_K8_K7_K5_K4_2_9_10_92 25 0 0 45225 100 DROP INDEX [_dta_index_f_equipment_20_1722905655__K6_K1_K95_K8_K7_K5_K4_2_9_10_92] ON [dbo].[f_equipment]
f_equipment _dta_index_f_equipment_5_1888725781__K7_K2_K6_K3_1_4_5_8_9_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_ 61 0 0 45225 100 DROP INDEX [_dta_index_f_equipment_5_1888725781__K7_K2_K6_K3_1_4_5_8_9_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_] ON [dbo].[f_equipment]
f_worktableTaskSchedule SAIDX_f_worktableTaskSchedule_33 6 0 0 570519 100 DROP INDEX [SAIDX_f_worktableTaskSchedule_33] ON [dbo].[f_worktableTaskSchedule]
f_worktableTaskSchedule f_worktableTaskSchedule_wos_wk_fk 14 0 0 570519 100 DROP INDEX [f_worktableTaskSchedule_wos_wk_fk] ON [dbo].[f_worktableTaskSchedule]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K2_K3_K1 17 2 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K2_K3_K1] ON [dbo].[f_worktableTask]
f_jobRecordWorkRCenterLink IX_f_jobRecordWorkRCenterLink_fofk 3 3 0 121822 100 DROP INDEX [IX_f_jobRecordWorkRCenterLink_fofk] ON [dbo].[f_jobRecordWorkRCenterLink]
f_worktableTask IX_f_worktableTask_wofk 26 4 0 1559349 100 DROP INDEX [IX_f_worktableTask_wofk] ON [dbo].[f_worktableTask]
f_vendorWorkRCenterLink _dta_index_f_vendorWorkRCenterLink_20_684685637__K1 3 7 0 363690 100 DROP INDEX [_dta_index_f_vendorWorkRCenterLink_20_684685637__K1] ON [dbo].[f_vendorWorkRCenterLink]
f_worktable idx_woWCCompdatetableComposite 40 10 0 1543293 100 DROP INDEX [idx_woWCCompdatetableComposite] ON [dbo].[f_worktable]
f_worktable idx_woRCComposite 32 11 0 1543293 100 DROP INDEX [idx_woRCComposite] ON [dbo].[f_worktable]
f_worktableTask idx_worktableTaskTASK 23 13 0 1559349 100 DROP INDEX [idx_worktableTaskTASK] ON [dbo].[f_worktableTask]
f_worktable f_worktable_wo_pk_wo_tag_fk_wo_clnt_fk_wo_itemType_fk 28 13 0 1543293 100 DROP INDEX [f_worktable_wo_pk_wo_tag_fk_wo_clnt_fk_wo_itemType_fk] ON [dbo].[f_worktable]
f_webSAReportColumnLangstrings _dta_index_f_webSAReportColumnLangstrings_20_1212231769__K2_K1_3 3 15 0 20690 100 DROP INDEX [_dta_index_f_webSAReportColumnLangstrings_20_1212231769__K2_K1_3] ON [dbo].[f_webSAReportColumnLangstrings]
f_webSAPageFields IX_f_webSAPageFields_sfldfk 7 16 0 17055 100 DROP INDEX [IX_f_webSAPageFields_sfldfk] ON [dbo].[f_webSAPageFields]
f_worktableTask _dta_index_f_worktableTask_5_1504164554__K2_19 58 17 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_5_1504164554__K2_19] ON [dbo].[f_worktableTask]
f_webSAPageFields _dta_index_f_webSAPageFields_20_1167251859__K1_K2_6_10 14 20 0 17055 100 DROP INDEX [_dta_index_f_webSAPageFields_20_1167251859__K1_K2_6_10] ON [dbo].[f_webSAPageFields]
f_purchasetableDetail f_purchasetableDetail_covering_01 8 21 0 128179 100 DROP INDEX [f_purchasetableDetail_covering_01] ON [dbo].[f_purchasetableDetail]
f_worktable idx_wowcclntfk2 39 21 0 1543293 100 DROP INDEX [idx_wowcclntfk2] ON [dbo].[f_worktable]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K2_K25_K1_K3_K20_4_5_7_17 15 21 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K2_K25_K1_K3_K20_4_5_7_17] ON [dbo].[f_worktableTask]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K1_K3_K2_4 11 21 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K1_K3_K2_4] ON [dbo].[f_worktableTask]
f_worktableTask f_worktableTask_covering_02 20 21 0 1559349 100 DROP INDEX [f_worktableTask_covering_02] ON [dbo].[f_worktableTask]
f_worktable f_worktable_covering_02 27 21 0 1543293 100 DROP INDEX [f_worktable_covering_02] ON [dbo].[f_worktable]
f_part _dta_index_f_part_20_1486224645__K1_K30_K42_K24_2_3_4_7_9_14_15_16_21_22_32_38 26 21 0 58541 100 DROP INDEX [_dta_index_f_part_20_1486224645__K1_K30_K42_K24_2_3_4_7_9_14_15_16_21_22_32_38] ON [dbo].[f_part]
f_purchasetable f_purchasetable_covering_01 5 21 0 44693 100 DROP INDEX [f_purchasetable_covering_01] ON [dbo].[f_purchasetable]
f_worktableL f_worktableL_fwor_wos_fk 9 21 0 1876331 100 DROP INDEX [f_worktableL_fwor_wos_fk] ON [dbo].[f_worktableL]
f_part f_part_pa_pk_WNK_01 21 21 0 58541 100 DROP INDEX [f_part_pa_pk_WNK_01] ON [dbo].[f_part]
f_worktableL f_worktableL_covering_01 5 21 0 1876331 100 DROP INDEX [f_worktableL_covering_01] ON [dbo].[f_worktableL]
f_worktableTrade _dta_index_f_worktableTrade_20_684841802__K2_K3 9 21 0 1561212 100 DROP INDEX [_dta_index_f_worktableTrade_20_684841802__K2_K3] ON [dbo].[f_worktableTrade]
f_worktableTaskSchedule _dta_index_f_worktableTaskSchedule_20_1797490078__K5_K1_K6_K4 8 21 0 570519 100 DROP INDEX [_dta_index_f_worktableTaskSchedule_20_1797490078__K5_K1_K6_K4] ON [dbo].[f_worktableTaskSchedule]
f_worktablePart f_worktablePart_f_woTask_fk 13 21 0 183664 100 DROP INDEX [f_worktablePart_f_woTask_fk] ON [dbo].[f_worktablePart]
f_worktableTaskSchedule _dta_index_f_worktableTaskSchedule_20_1797490078__K5_K1_K6_2_12 16 27 0 570519 100 DROP INDEX [_dta_index_f_worktableTaskSchedule_20_1797490078__K5_K1_K6_2_12] ON [dbo].[f_worktableTaskSchedule]
f_worktableTrade _dta_index_f_worktableTrade_20_684841802__K2_K1_K5_3 10 27 0 1561212 100 DROP INDEX [_dta_index_f_worktableTrade_20_684841802__K2_K1_K5_3] ON [dbo].[f_worktableTrade]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K2_K13_1_3_4_5_6_7_8_9_10_11_12_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29 16 27 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K2_K13_1_3_4_5_6_7_8_9_10_11_12_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29] ON [dbo].[f_worktableTask]
f_request f_request_log_pk_WNK_01 3 31 0 26898 100 DROP INDEX [f_request_log_pk_WNK_01] ON [dbo].[f_request]
f_worktableTask _dta_index_f_worktableTask_5_1504164554__K2_18 59 40 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_5_1504164554__K2_18] ON [dbo].[f_worktableTask]
f_worktableTask f_worktableTask_covering_01 12 42 0 1559349 100 DROP INDEX [f_worktableTask_covering_01] ON [dbo].[f_worktableTask]
f_part _dta_index_f_part_20_1486224645__K1_K42_2_3_4_7_16_17_21 22 42 0 58541 100 DROP INDEX [_dta_index_f_part_20_1486224645__K1_K42_2_3_4_7_16_17_21] ON [dbo].[f_part]
f_worktable _dta_index_f_worktable_20_1750869900__K1_2 20 52 0 1543293 100 DROP INDEX [_dta_index_f_worktable_20_1750869900__K1_2] ON [dbo].[f_worktable]
f_webSAPageFields IX_f_webSAPageFields_pagefk 2 58 0 17055 100 DROP INDEX [IX_f_webSAPageFields_pagefk] ON [dbo].[f_webSAPageFields]
f_part _dta_index_f_part_20_1486224645__K1_K42_K30_2_3_4_7_9_16_17_21_22_31_32 23 63 0 58541 100 DROP INDEX [_dta_index_f_part_20_1486224645__K1_K42_K30_2_3_4_7_9_16_17_21_22_31_32] ON [dbo].[f_part]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K1_K25_K20_K3_K2 9 63 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K1_K25_K20_K3_K2] ON [dbo].[f_worktableTask]
f_onhandAdjustment f_onhandAdjustment_covering_01 4 63 0 251303 100 DROP INDEX [f_onhandAdjustment_covering_01] ON [dbo].[f_onhandAdjustment]
f_worktable _dta_index_f_worktable_20_1750869900__K1_2_3_4_5_6_7_8_9_10_11_13_17_20_24_25_26_29_30_31_53 21 69 0 1543293 100 DROP INDEX [_dta_index_f_worktable_20_1750869900__K1_2_3_4_5_6_7_8_9_10_11_13_17_20_24_25_26_29_30_31_53] ON [dbo].[f_worktable]
f_worktableTask _dta_index_f_worktableTask_20_581485746__K2_K1_K6_K3_4_5_10 21 84 0 1559349 100 DROP INDEX [_dta_index_f_worktableTask_20_581485746__K2_K1_K6_K3_4_5_10] ON [dbo].[f_worktableTask]
f_worktable f_worktable_covering_04 25 105 0 1543293 100 DROP INDEX [f_worktable_covering_04] ON [dbo].[f_worktable]
f_webSAPageFields _dta_index_f_webSAPageFields_20_1167251859__K2_K8_K1_K3_K4_K5_K6_K7_K9_K10_K11_K12_K13_K14_K15_K16_17_18_19_20_21_22_23_24_25_ 10 107 0 17055 100 DROP INDEX [_dta_index_f_webSAPageFields_20_1167251859__K2_K8_K1_K3_K4_K5_K6_K7_K9_K10_K11_K12_K13_K14_K15_K16_17_18_19_20_21_22_23_24_25_] ON [dbo].[f_webSAPageFields]
f_webSAPageFieldLangstrings _dta_index_f_webSAPageFieldLangstrings_20_159196213__K2_K1_3_4_5 8 122 0 53753 100 DROP INDEX [_dta_index_f_webSAPageFieldLangstrings_20_159196213__K2_K1_3_4_5] ON [dbo].[f_webSAPageFieldLangstrings]
f_worktable f_worktable_covering_01 34 977 0 1543293 100 DROP INDEX [f_worktable_covering_01] ON [dbo].[f_worktable]
f_worktable _dta_index_f_worktable_5_1184163414__K62_K20_K1_K10_2_3_4_5_6_7_8_9_11_12_13_14_15_16_17_18_19_21_22_23_24_25_26_27_28_29_30_ 16 1611 0 1543293 100 DROP INDEX [_dta_index_f_worktable_5_1184163414__K62_K20_K1_K10_2_3_4_5_6_7_8_9_11_12_13_14_15_16_17_18_19_21_22_23_24_25_26_27_28_29_30_] ON [dbo].[f_worktable]
f_Email IX_f_Email_ldsfk 3 2587 0 112418 100 DROP INDEX [IX_f_Email_ldsfk] ON [dbo].[f_Email]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-02 : 15:45:20

Let's run this instead:



select name, indexname, reads, writes, rows, reads_per_write from 
(SELECT 
o.name
, indexname=i.name
, i.index_id   
, reads=user_seeks + user_scans + user_lookups   
, writes =  user_updates   
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
	WHEN s.user_updates < 1 THEN 100
	ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
  END AS reads_per_write
FROM sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()   
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000) t
where reads = 0
ORDER BY reads

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mcolli00
Starting Member

19 Posts

Posted - 2010-12-02 : 16:02:56

Here it is. A bit smaller...

name indexname reads writes rows reads_per_write
f_pm_datetable TMAIDX_05_f_pm_datetable_3 0 0 48105 100
f_pm_datetable f_pm_datetable_pm_rts_fk 0 0 48105 100
f_webSAUserDataAccess f_webSAUserDataAccess_user_fk_tag_fk 0 0 242013 100
f_worktableL idx_wolaborclntfk 0 0 1876331 100
f_worktableL _dta_index_f_worktableL_20_1631865426__K4_K3_K2_K20_1_9_11_12_13_14_15_16_17_18_19_22_28 0 0 1876331 100
f_worktableL _dta_index_f_worktableL_20_1631865426__K2_K3_K1_K5_9_20 0 0 1876331 100
f_worktableL TMAIDX_02_f_worktableL_18 0 0 1876331 100
f_worktableL _dta_index_f_worktableL_5_524685067__K3_K28_K5_K2_K6_K7_K4_1_8_9_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_29_ 0 0 1876331 100
f_part _dta_index_f_part_20_1486224645__K24_K1_K30_K42_K44_K46_K23_2_3_4_5_6_7_8_9_10_11_12_13_14_15_16_17_18_20_21_22_25_26_28_29_31_ 0 0 58541 100
f_part _dta_index_f_part_20_1486224645__K3_K17_1 0 0 58541 100
f_part f_part_covering_01 0 0 58541 100
f_worktable TMAIDX_f_worktable_13 0 0 1543293 100
f_worktable _dta_index_f_worktable_20_1750869900__K3_K11_K1_K20_K9_K8_K4_K10_K7_K6_K5_K13_K2_K24_K30_K21_17_25_27 0 0 1543293 100
f_worktable _dta_index_f_worktable_20_1750869900__K1_K20_10_32 0 0 1543293 100
f_worktable _dta_index_f_worktable_5_1184163414__K20_K11_K8_K7_1_2_3_4_5_6_10_13_30_35 0 0 1543293 100
f_worktable TMAIDX_06_f_worktable_12 0 0 1543293 100
f_worktable _dta_index_f_worktable_20_1750869900__K11_K4_K10_K3_1_2_5_6_7_8_13_20_30_35 0 0 1543293 100
f_worktable idx_workorderFB 0 0 1543293 100
f_worktableTask TMAIDX_01_f_worktableTask_58 0 0 1559349 100
f_worktableTask _dta_index_f_worktableTask_20_581485746__K1_K2_K20_K3_K25 0 0 1559349 100
f_worktableTask f_worktableTask_covering_03 0 0 1559349 100
f_worktableTask TMAIDX_01_f_worktableTask_59 0 0 1559349 100
f_losttime IX_f_losttime_cefk 0 0 22261 100
f_equipmentWorkRCenterLink _dta_index_f_equipmentWorkRCenterLink_20_1516688601__K9_K2 0 0 46202 100
f_equipment IX_f_equipment_fcfk 0 0 45225 100
f_equipment IX_f_equipment_tgfk 0 0 45225 100
f_equipment _dta_index_f_equipment_20_1722905655__K95_K3_K98_K1_2_4_5_6_7_8_9_10_13_20_21_77_92_93_122_123_124_125_126 0 0 45225 100
f_equipment _dta_index_f_equipment_20_1722905655__K6_K1_K95_K8_K7_K5_K4_2_9_10_92 0 0 45225 100
f_equipment _dta_index_f_equipment_5_1888725781__K7_K2_K6_K3_1_4_5_8_9_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_ 0 0 45225 100
f_worktableTaskSchedule TMAIDX_f_worktableTaskSchedule_33 0 0 570519 100
f_worktableTaskSchedule f_worktableTaskSchedule_wos_wk_fk 0 0 570519 100

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-02 : 16:09:24

Those are the indexes that you should consider dropping as they haven't been used since SQL Server was started (since last reboot or since last restart of the service). BUT! Be careful though as you need to ensure that every single possible query that will ever run on your system has been run when you run this report, otherwise you could end up removing a much needed query that hasn't run yet.

I once dropped an index that said 0 reads and a few days later, a monthly report launched that caused the CPU to go to 100% due to a table scan (missing index, the one that I had dropped). The monthly report had not been run when I had ran the DMV report, so that's why the index showed as 0 reads. The index is highly needed for the report!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mcolli00
Starting Member

19 Posts

Posted - 2010-12-02 : 16:26:37

Thanks for helping! Let me ask you another question. Is it considered a best practice to have a nonclustered index on column1 and then another on column1 and column2? I am asking since I see this frequently...

index(column1)
index(column1, column2)
index(column1,column2,column3)
index(column1,column2,column3,column4)

Thanks
MC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-02 : 16:31:29

It is a bad practice in general. The first 3 indexes are considered duplicates of the last index. The wider the index gets though, the more it could be a problem and not preferred by the optimizer. The best practice are only general guidelines and should be tested. If any of the best practices come up as being slow, then alter it. Such as if your index4 is slower than your index1 for a particular query, then consider having both.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mcolli00
Starting Member

19 Posts

Posted - 2010-12-02 : 16:47:14

Thanks so much for your help today!

MC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-02 : 17:04:04

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sachin.Nand

2937 Posts

Posted - 2010-12-03 : 04:18:29

Also beware on order of columns in the index.If you query your table with some filter like this
...where colmn2=somevalue and column1=somevalue then optmizer wont be able to use any of indexes you have created.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-12-03 : 05:36:53

"where colmn2=somevalue and column1=somevalue then optmizer wont be able to use any of indexes you have created."

That will use a "column1, column2" index, or a "column2, column1" index (all things being equal)

But maybe that was not your point?

Sachin.Nand

2937 Posts

Posted - 2010-12-03 : 05:56:04

quote:
That will use a "column1, column2" index, or a "column2, column1" index

No I doubt it will.It go for an Index Scan if the indexes are defined like this
index(column1, column2)

PBUH

Kristen
Test

22859 Posts

Posted - 2010-12-03 : 07:22:12

OK, just to make sure I understand your point.

You are saying that if I use


where column2=somevalue and column1=somevalue

that will NOT use:


index(column1, column2)

but if I turn the WHERE clause round:


where column1=somevalue and column2=somevalue

then that will use the index?

If so then that is not my understanding of things - I would expect the query optimiser to use that index whichever order I put my columns in the WHERE clause

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 07:46:56

quote:
Originally posted by Kristen

OK, just to make sure I understand your point.

You are saying that if I use

where column2=somevalue and column1=somevalue

that will NOT use:

index(column1, column2)

but if I turn the WHERE clause round:

where column1=somevalue and column2=somevalue

Nope - you're right. The order of the columns in the where clause (or join) doesn't (or shouldn't) make any difference as to whether an index is used. It may do in odd cases but that would be considered a bug.
You can tell whether an index is used by looking at the query plan.
then that will use the index?

If so then that is not my understanding of things - I would expect the query optimiser to use that index whichever order I put my columns in the WHERE clause

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sachin.Nand

2937 Posts

Posted - 2010-12-03 : 07:47:51

No Sorry I meant it to be something different.I wanted to say if you have a query with filter ....where column2=somevalue and column2 is part of composite index say index(column1, column2) the optimizer wont use the index.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources