I have been experienced that my DB is experiencing a lot of performance issues and after some time I have observer that an SSIS jobs takes extremely long time to inserting new rows through SSIS OLE DB Destination . I have checked the following things so far: Data access mode : Table or view fast mode with marked: "Table lock" and "Check constraints", "Row per batch" is empty and Maximum insert commit : 2147483647 Number of rows for inserting have been a really small number <100 so I dont really understand that it takes 20-30 minutes for this. I think its the insert which is slow because all previous data tasks are marked green check so I believe its correct. Have read that this could be because of the index in the tabel but how this can be checked to confirm or exclude?
Is it possible to change it so that Insert has higher priority then Select so that Insert are not blocked
If you enable the database option READ COMMITTED SNAPSHOT then the SELECT will not require locks or block the INSERT. Also you could run the SELECT under SNAPSHOT transaction isolation level.