0

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
  • 13
  • 3
  • Please add tags to your post regarding which database system you're using (in assuming Microsoft SQL Server) and which version. – J.D. Dec 06 '21 at 12:31
  • Yes, SQL Server 2019, I will add tags – gk1993_aal Dec 06 '21 at 12:32
  • Do you observer blocking during the insert? – Dan Guzman Dec 06 '21 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 06 '21 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 06 '21 at 13:22
  • Thanks. Is it possible to change it so that Insert has higher priority then Select so that Insert are not blocked ? – gk1993_aal Dec 06 '21 at 13:37
  • Has the target table an ID column with a SEQUENCE as default? If yes, check the cache property of the SEQUENCE object (I once had the case, that the SELECT of a few million rows took maybe 2 minutes, but the INSERT 40 minutes, until I found, that the sequence had a cache value of only 20...) – Thomas Franz Dec 06 '21 at 17:25
  • Are you running the SSIS on the server? If you are loading a CSV via SSIS and have the CSV file on your laptop, while you are connected through a slow VPN connection to your companies LAN, it has to move the data from / to your laptop (if you are running SSIS local) – Thomas Franz Dec 06 '21 at 17:27
  • BTW: what happens if you are inserting into a new, empty table (with or without indexes / IDs / default columns etc.)? Faster / slower / same? This may help to find the reason / culprint. – Thomas Franz Dec 06 '21 at 17:28

1 Answers1

0

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.

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
  • 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? – gk1993_aal Dec 06 '21 at 14:00
  • 1
    The best starting point for understanding the performance of your workload is Query Store: https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 – David Browne - Microsoft Dec 06 '21 at 14:35
  • @gk1993_aal you could test this by creating a copy of the table in a different database and experimenting with it. – Sir Swears-a-lot Dec 14 '21 at 09:17