Why is an index created more than one time in SQL via Entity Framework
I have written a service, which imports every night more than 960.000 rows to a SQL Database. This data will be used for other operations few hours later.
The application is a .Net application, with Entity Framework on it. Import is being made with EntityFramework BulkExtensions.
After 3-4 Imports, I receive TimeoutExceptions.
Some experts from my company reviewed my whole code. From Code-Side everything seems to be ok.
Now I have taken a look to the SQL Server. It is running a SQL Server 2014 Professional.
After running the query:
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID WHERE indexstats.avg_fragmentation_in_percent > 30 AND i.index_id = indexstats.index_id
I got following Result:
Now my question is, why is Index2 being created more than 1 time?
I have expected to get only one row for Table 2 with one Index (IX_Index2).
One Solution collect form web for “Why is an index created more than one time in SQL via Entity Framework”
This doesn’t answer the question directly I’m afraid.
Regardless of your above query, you may want to consider dropping or disabling your index prior to the data load and then rebuilding / enabling it after the data load. This will probably be more efficient as otherwise the index will get updated as the data is being imported.
This article provides some simple code to show you how to do this if you’re not already doing so:
To disable in index, issue an ALTER INDEX command.
ALTER INDEX IX_IndexName ON Schema.TableName DISABLE; GO
If you want to re-enable the index, you need to rebuild it – there is
no ALTER INDEX…ENABLE command. The rebuild works like this:
ALTER INDEX IX_IndexName ON Schema.TableName REBUILD; GO
To drop a clustered or nonclustered index, issue a DROP INDEX command.
DROP INDEX IndexName ON Schema.TableName; GO