Inserting in db is extremely slow - SSIS OLE DB Destination

Question

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?

• gk1993_aal: Yes, SQL Server 2019, I will add tags
• gk1993_aal: 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: Is it possible to change it so that Insert has higher priority then Select so that Insert are not blocked ?

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.

• gk1993_aal: 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?
• David Browne - Microsoft: The best starting point for understanding the performance of your workload is Query Store: learn.microsoft.com/en-us/sql/relational-databases/performance/…