2 more things:

- forget the word "DISTINCT". Use Group by when needed (I bet you don't need it in your query);

- if you have an index rebuilding routine, use that...

The query is a mess.

its difficult to understand the requirements from the code because it lacks of logic, not to mention some constructions in the query eliminate others.

My guess would...

Whoever removed my post - are you sure it was really a spam?

In the very first reply Phil suggested “you probably need to rethink”. And I just pointed out that...

Using a function to parse string arrays stored in 2 or more different tables containing homogeneous data - how many more wrong approaches can you fit into such a simple...

The problem is that a typical developer uses relational database as document storage.

Thanks to MS providing embedded support to XML, JSON, Columnsstore, etc.

Obviously, a relational database is not the most...

The query does not have any WHERE condition, so there is nothing to SEEK there.

Aggregation query still has to go through every row in the table to return the result...

To avoid fragmentation you need to answer the question - why does it happen?

you might need to change the indexing strategy on the table, so adding new records want cause...

First, you trigger must be INSTEAD OF, not FOR.

FOR triggers are executed after insert is done, so, it there is a constraint - it will raise the error before the...

How about "non-structured data"?

If you think about it...

If the issue existed on physical server as well, and you're on SQL2017 now, then Grant's advice is the one to follow.

You may also wish to trace tempdb usage. Might...

Well, I...

Is your SQL Server installed on physical or virtual machine?

That leads us back to the question I asked that hasn't yet been answered... before the OP makes any "plans", the first thing that should be done is an...

You should take all those varchar(max) columns off the table and put it into kinda old good EAV table:

DocumentId (fk to the main table)

InfoType NOT NULL

infoContents VARCHAR](MAX) NOT NULL

From what I saw last years, 95% of that increased workload are table scans, hash joins, endless data replications due to horrible indexing strategies and overall dumb database design.

at least...