It would appear that entity framework automatically creates an index for every foreign key on a table. I literally have about 50 indexes that have never been read in two years of database usage. I'd like to delete them since there is a insert/update cost for indexes.
Anyone else encountered this?
Thanks!
If there have been zero reads in the past 2 years and tons of writes during that timeframe, yes delete them.
I always start out with indexing foreign keys as those are often used in joins, but then I go back after some time has passed and review which are not needed. I use the missing indexes report and the unused indexes report to help make these decisions.
Did you check system uses as well as user uses? Typically SQL uses such indexes to verify FK constraints, and without it would have to do table scans. I think those get recorded as system uses, although not 100% sure.
Hi Scott,
I hadn't checked that. I modified the query to include system_seeks, system_lookups, and system_scans and other than a few system_scans they are all 0. I feel better having checked that so thank you.
You're welcome. I'm a DBA, so I have to be nit picky when it comes to SQL stats 