question

Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 asked ErlandSommarskog answered

Random long run of the SQL Query

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


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Ondrej-1562 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @ErlandSommarskog,

The issue happens even when the SQL query has been run outside of business hours alone so there was no blocking operation.

I need to collect execution plans on the fly, so not easy to catch the plans, but here are examples of bad performing ones.
https://www.brentozar.com/pastetheplan/?id=rys4I5b8d
https://www.brentozar.com/pastetheplan/?id=HJ-KvqWLu

It looks it is related to the ucokr value because we found another example.
ucokr = 2134 (duration: less than 1s)
https://www.brentozar.com/pastetheplan/?id=HkQeFcZI_

ucokr= 2192 (duration: stopped after 4 minutes without result)
https://www.brentozar.com/pastetheplan/?id=rkjLY5bUO

Maybe if we find what is the issue for the slow and fast example above, it will solve all the other similar issues.

Thank you
Ondrej

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered Ondrej-1562 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @TomPhillips-1744,

Thank you for your recommendation. I will arrange a maintenance window for it.

regards
Ondrej

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 answered

Hello @ErlandSommarskog,

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 answered

Hello @ErlandSommarskog,

Thank you for your answer.

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.

Yes. The "organization unit" 2134 "owns" a majority of records. Other units like e.g. 2192 has fewer records (all other 6-7 units have together less than the 2134 unit itself).

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.

I think, the same, but they started with it when the product was small and it is "locked" in a project, and removal would need quite a lot of work. That's why they don't want to do it.

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

Collation is Czech_CI_AS, data type is (varchar(2),null)

Thank you
Ondrej
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I think, the same, but they started with it when the product was small and it is "locked" in a project, and removal would need quite a lot of work. That's why they don't want to do it.

Maybe they have learnt a lesson for the next project then!

But for performance issues like these, there is really not a choice. The query has to be crafted manually to get it under control.

Collation is Czech_CI_AS, data type is (varchar(2),null)

is there an index on the column?

The reason I asked is that are implicit conversion to nvarchar. This is a quite common problem with ORMs. The database is designed to use varchar, but the ORM does everything in Unicode. (But reportedly, Hibernate has a switch to control this.)

If you have an SQL collation, this leads to a performance disaster, because the index cannot be seeked. For a Windows collation a seek is still possible. However, the operator in question is a scan. That cold be that there is no index on druh - or that it is not selective to be useful.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 answered ErlandSommarskog commented

Hello @ErlandSommarskog,

is there an index on the column?

No, there is not an index. There are the following indexes (columns it includes can be estimated from their names).
Here is the definition of the index I added:

 CREATE NONCLUSTERED INDEX [ix_druh] ON [dbo].[porovnat]
 (
     [druh] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
 GO

88229-image.png
I added the index, updated statistics on the table and run the command again, but unfortunately, no change even worse. I stopped it after 4 minutes without results.
https://www.brentozar.com/pastetheplan/?id=rkBqgW8Id


Thank you
Ondrej


image.png (20.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yeah, I know too little about the data to know whether an index on druh would be useful at all. I just noticed the implicit conversions, and decided to ask, as it could be a problem. But that was very much a shot in the dark.

I'm afraid that the query is a little too complex to help with through a forum, even if we would eventually get access to the view definition. We can point out things that stand out, but to find the best indexes it is necessary to have intimate knowledge about the data, and even then it can be bit of trial and error.

0 Votes 0 ·
Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 answered ErlandSommarskog commented

Hello @ErlandSommarskog,

Thank you for your answer. Here is a definition of the view.
https://pastebin.com/0sfU1YRX
As I mentioned earlier, the data is not evenly distributed. There is one organizational unit that has more records than the rest of the others. What should be the steps in this situation to solve it?
Maybe I not mentioned the situation, that this query is a search query. Hibernate build the query based on the search condition and not all possible values are used every time. Would it make sense to force the use of one SQL with a full list of parameters but only with some of them with a value?

Thank you
Ondrej

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer. Here is a definition of the view.

https://pastebin.com/0sfU1YRX

The connection is still insecure.

As I mentioned earlier, the data is not evenly distributed. There is one organizational unit that has more records than the rest of the others. What should be the steps in this situation to solve it?

Uneven distributions can certainly be a challenge, not the least for parameterised queries, since the same plan is not good for all values. If you have a case where one unit overshadows everything else, it certainly can make sense to inline that particular value, so that you get different values.

Now, as I understood it, you get problems even with a hard-coded value. If some of the problems are due to how Hibernates generates the query, you already know the answer: don't use Hibernate for this query.






0 Votes 0 ·
Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 answered

Hello @ErlandSommarskog,

Thank you for your answer.

Let's try Github Gist. I posted there a definition of the view.
https://gist.github.com/xzizka/e59f4463e79aef66714c84aeed7159f1

Now, as I understood it, you get problems even with a hard-coded value. If some of the problems are due to how Hibernates generates the query, you already know the answer: don't use Hibernate for this query.

If I understand your recommendation... Rework the database structure and use manually written queries. Is that correct?

Thank you


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.