check if someone had DENY'ed create tables in TEMPDB- or even deny'ed other permissions that could cause the issue.

It is the Portuguese for AI

how many rows of tbl A (18900000  total) have the ReferenceNum = 0 ?

and why aren't you filtering them when doing the insert into the temp table?

I also see, dev team is using DISTINCT operator as a common practice. Is there any proper ways to re-write to the queries to avoid the usage of distinct...

You said in your narrative in your original post that "it reads last 30 mins of data from the tables involved" and yet ALL of your...

before we can even suggest anything else...

1 - WHY are they returning 40 Million rows on a function call? what purpose does this accomplish?

ask the devs what is this output...

• This reply was modified 1 year, 12 months ago by frederico_fonseca.
• This reply was modified 1 year, 12 months ago by frederico_fonseca. Reason: add table name to proposed indexes

can you also get a ACTUAL execution plan of doing those sqls

and there is some duplication of access to table C_B_PARTY

take following snippet.

take following snippet.
first INNER join...

• This reply was modified 1 year, 12 months ago by frederico_fonseca.

If you want a load into a new table, then create the empty table AND create the Clustered Index.  Then, forget SSIS... use BULK INSERT with the TABLOCK option...

helps to read the documentation.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

search for tuple-mover

Sql server edition (express, standard, enterprise) - developer --- if you are using this for Production usage you CAN NOT use developer license - if it is only for development...

those files you are getting.... can you answer the following.

• how many files per load
• how many rows per file (max, min and average size)
• how often you get them.

...

• This reply was modified 2 years ago by frederico_fonseca.

why are you dropping and recreating the indexes? what are you trying to accomplish with it?

(for Phil - OP has DROP_EXISTING = ON so indexes are there already while import...

as Ant-Green mentioned above you need to use a SSMS version compatible with SQL 2012 - SSMS 19 only supports 2014 or higher and you are trying to connect to...

you will need to speak with vendor - and see if the application is already prepared to work with SQL Server  instead of Sybase. It may well be just a...

read this for an explanation https://stackoverflow.com/questions/46312573/ssrs-the-restrictions-imposed-by-the-constrained-flag-in-the-strtoset-function