SQLTeam.com | Weblogs | Forums

Is it safe to delete auto-generated entity framework indexes?


#1

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!


#2

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.


#3

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.


#4

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.


#5

You're welcome. I'm a DBA, so I have to be nit picky when it comes to SQL stats :grinning: