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?

  • Please add tags to your post regarding which database system you're using (in assuming Microsoft SQL Server) and which version.
    – J.D.
    Dec 6, 2021 at 12:31
  • Yes, SQL Server 2019, I will add tags
    – gk1993_aal
    Dec 6, 2021 at 12:32
  • Do you observer blocking during the insert?
    – Dan Guzman
    Dec 6, 2021 at 12:50
  • @DanGuzman, The select queies I strongly believe are being blocked as I have experienced by my self that the select queries are just executing for long time without any data to be displayed which normally takes 5 sec. I do not know whether inserting are being blocked if select queries are running at same time, can that happen?
    – gk1993_aal
    Dec 6, 2021 at 12:55
  • @gk1993_aal, SELECT queries can block inserts and visa-versa. You can execute sp_who to identify blocking/blocked sessions or other methods, such as in this answer. Maybe you should schedule the package to run during periods of low activity.
    – Dan Guzman
    Dec 6, 2021 at 13:22

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.