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?
1 Answer
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.
answered Dec 6, 2021 at 13:55
-
Thank you David Can you tell how can I confirm that there is no issue with the index so that I am sure before I change theses settings? Commented Dec 6, 2021 at 14:00
-
1The best starting point for understanding the performance of your workload is Query Store: learn.microsoft.com/en-us/sql/relational-databases/performance/… Commented Dec 6, 2021 at 14:35
-
@gk1993_aal you could test this by creating a copy of the table in a different database and experimenting with it. Commented Dec 14, 2021 at 9:17
sp_whoto 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.