A slow query on MS-SQL-Server 2019 cannot be optimized

Question

In the following thread I received some important and valuable information on how I can improve a slow query on MS-SQL-Server 2019.

However, that didn't do very much.

First I deleted all non-clustered indexes and replaced them with clustered indexex. With the clustered index, some queries seem to be at least a little faster.

First question: It always seemed to me as if MS-SQL had not used the non-clustered index at all because creating a non-clustered index did not improve performance. Is that possible?

I kept getting the tip from you that I should absolutely avoid data conversions when accessing the index.

I tried that in a view. In the following script, the date values ​​are ALL nvarchars. However, the script runs for 78 minutes! Without the SUBSTRINGs it doesn't run any faster.

Second question: Is there a reason why this script has been running for so long?

The following query is the old version of the same script, just with different tables, but the table structure and the amount of data is roughly the same:

The script from the Second Execution Plan runs for 7 minutes, although here "DATETIME date values" ​​are linked with "NVARCHAR date values". I don't understand why this script runs so fast, although it is obvious that type conversions must take place here when the index is accessed.

The latter query is then integrated as a view in the following script:

This insert runs for 29 minutes. Previously, on MS-SQL 2008, the same insert ran for 10 minutes.

In this script I also removed the data conversions when accessing the index, but that did not bring any performance gain at all.

This script always runs for about 30 minutes, no matter what I do, I can't get it any faster.

Third question: Do you see a bottleneck in the third execution plan?

After all, you can see in all three execution plans that clustered index accesses take place.

Answer 1

After carefully reviewing your fourth question on the same topic, I'm afraid my diagnosis is exactly the same I provided in your first question. Let's look at your first query here.

You can see it executes in 29 seconds. But let's look at compile time (In SSMS: F4 to enable properties and click on the SELECT node):

The number above is in miliseconds. Converting to minutes it's 77. Adding both times together you get close to observed 78 minutes runtime.

Let's look at the second, "good" query:

6 minutes compilation time.

Now you might ask yourself is this a lot? The answer is yes in both cases. I would consider compilation time in SECONDS slow. Your cases are patological.

I cannot answer what causes it. I will only give some ideas you could try. First a question: is the problem affecting other queries in your system? If yes then maybe something on the instance is wrongly configured. Do you have any funny trace flags enabled?

Your issue actually existed in original database. It must have been carried over. Like schema. Script out all the tables from the query and everything related to it. Is there anything funny? 100 statistics on one table? 100 indexes? hypotethical indexes?

Try cutting up the query. Remove one join or predicate at the time. You will get garbage results, but at some point the reduced query should start compiling in normal times. That will give you a clue what could be causing it.

That's all I got. Good luck.

Answer 2

Your second execution plan has parallelism the whole way through while the first doesn't.

The 2 arrows over each operator denote parallelism - go faster stripes as I believe Erik D calls them.

You can check this earlier answer to see if you have one of the reasons mentioned here that prevent you getting a parallel plan

SQL not engaging parallelism for extremely large query