Random long run of the SQL Query

Question

Hello,

I have a database where I have a select query which runs slowly just for some parameters. The database has actual statistics (full scan), I removed old execution plans (did not restart, just remove these plans), no missing indexes were found but for some parameters (especially parameter P4 and 2 values for this parameter), this command runs for several minutes, but for other it takes a few seconds.
Execution plan can be found here:
https://www.brentozar.com/pastetheplan/?id=HkWJSj6SO

I know, that the SQL is not ideal and also join over 6 tables is not ideal, but it is hard to say, that the design is not optimal when there is problem with just 2 parameters. I suspect an issue with parameter sniffing, but I am not sure about that (how to solved). I would use a workaround where I would generate a special table with agregated results, but I hope, that there exists a solution for this issue/situation. Can you please provide any ideas how to do that?

Thank you for any help or any guide to solve this issue.

Regards
Ondrej

Answer 1

That was only an estimated plan. We could say a little more if we got the actual execution plan, preferably one for "fast" parameters and one for "slow".

The query itself is very difficult to read since it all one long line.

By the way, have you ruled out that this is not an issue caused by blocking?

Answer 2

It looks like you are running the RTM+GDR1 version of SQL 2019. I would highly suggest you install the current CU and retest. There have been many fixes which may change your results.

https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

Answer 3

As Tom points out, you are behind with versions, and you should install CU10, which is the most recent Cumulative Update. I would not really expect this to address the issue, since performance fixes are typically hidden by trace flag 4199.

There are two slow versions of the SELECT COUNT(*) query, but there is no fast plan to compare them to. And as I pointed out last time, the query is about unreadable, since there are no line breaks in it. I did notice one thing, though, when I scrolled through it, though: there is an OR condition. The optimizer rarely does a good job with OR, and rewriting with UNION often pays off.

As for the other query, I notice that this is unparameterised SQL which is bad in itself. It means that for every new set of parameter values there will be new compilation and a new cache entry, so this is a resource waster. In theory, though, you should get the best plan for the parameters you have, but apparently that does not always happen.

In any case, I would first clean up this query by parameterising it. This can certainly lead to parameter sniffing issues.

It also seems to me that this query is bigger than the query text seems to suggest. That is, the plan have more operators than I would expect, and I see table names not appearing in the query. Am I right to assume there there are one or more views in the query?

Answer 4

Hello @Erland Sommarskog ,

Thank you again for your answer. I pasted the "original" and formatted SQL query here:
https://pastebin.com/RKpNmagv

The "complication" here is that developers use Hibernate framework to generate structure and SQL queries. I can fix some issues with manually added indexes, but this does not solve all the issues (as you can see). That is why the SQL queries look weird. Even in Hibernate, you can use manual queries, but this is not the way developers want to go. I will try to discuss it with them. Maybe they will change the opinion. Also will look for a fast execution plan.

I pasted the other query like it was sent to me. I also expected, that these unparameterized queries will get the best execution plan for them, but this did not happen as you see.
Formated SQL query can be seen here:
https://pastebin.com/PHgRvFYf

Following your recommendation, I parameterized the SQL. Here is how the parameterized SQL looks like:
https://pastebin.com/Yv2vvDzX

And then I called it like this:
https://pastebin.com/tAjd8x41

Plan for @P3 = 2134 (duration 3m 9s)
https://www.brentozar.com/pastetheplan/?id=BJeZDTfUO

Plan for @P3 = 2192 (duration 2s)
https://www.brentozar.com/pastetheplan/?id=ry6x8TzId

The only difference is, that one call uses WHERE porovnaniv0_.ucokr = N'2134' and the other one WHERE porovnaniv0_.ucokr = N'2192' (parameter @P3).

It also seems to me that this query is bigger than the query text seems to suggest. That is, the plan have more operators than I would expect, and I see table names not appearing in the query. Am I right to assume there there are one or more views in the query?

Yes, object porovnani is a view and it consists of tables ciselnik, tridyim, porovnat and majetek1. Here is a definition:
https://pastebin.com/SvVW9gnC

Answer 5

I was not able to access the Pastebin links. Both browser and my firewall raised alerts about invalid certificate, so I did not want to go there.

I notice that in the fast plan, there is an index scan against the porovonat table which retrieves zero rows, and likewise only a handful of rows are returned majetek1 table. It suggests there is a quite different data profile depending on the values.

Hibernate, you say? Looks like you have an uphill battle. It is not tenable to have something that through a secret sauces that generates SQL which has its own secret sauce to produce query plans. ORM may be good for quick development of small systems, but as the systems grow in complexity, you grow out of it.

Anyway, what is the data type and collation of the column porovonat.druh?