Query goes crazy.

Question

Post reply

Query goes crazy.

Robert-378556

SSCertifiable

Points: 5917
More actions
September 22, 2023 at 4:02 pm

#4291966
Hi. I have a query that returns few rows (out of 7 table join, tables have milions of records).
On our server it's fast, takes 46ms to complete. On client's server it takes 25-60 sec. The execution plan is similar in relations and indexes used, but almost every node has info like this:
```
cost 10% 
5.492s 
3533121 of 26 
(13388926%)
```
while on our server it's normal
```
cost 10% 
0.0s 
1 of 26 (4%)
```
Our server is on premise, while client's is in cloud, if it matters.
This craziness starts with "distinct sort" node. Like it rematerialized every row while accessing rows for sort. Out of memory? out of tempdb space?
- This topic was modified 1 year, 2 months ago by Robert-378556.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Have you looked to see if the indexes on the table are the same? Have you updated statistics lately on either?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 2

Thank you for your reply. The list of indexes is the same, they're supposed to be the same, but I can't check it directly. Indexes and statistics were rebuilt and it was a little better, still hundreds of times slower than on our server. Plan cache was cleared too.

The execution plan looks the same regarding positions of tables in the graph, always index seek, only used indexes are different. The node with (13388926%) shows 2 rewinds, other have 0. If not from rewinds, where does this huge percentage come from? It would be too easy, if the client gave me direct access to the database....

This reply was modified 1 year, 2 months ago by Robert-378556.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

I have no real idea except to say that yours isn't the first post where On-prem performance blew the doors off of cloud performance in similar tests. You might want to find out what the specs are on the client's cloud system. There may also be settings changes (MaxDop, CTFP, etc).

You've also clearly identified a huge difference by identifying that the used indexes are different.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 4

yours isn't the first post where On-prem performance blew the doors off of cloud performance in similar tests

Not even a slightest idea why would it produce execution stats like this: 3533121 of 26 (13388926%)?

As if for some reason it switches to RBAR on top table and rematerializes the rest of join for each row. Damn, when I rethink about it, it could be the case. The node from plan where it starts to look crazy, uses an index where the search column is not an index column, but included column, so the index is not searchable, so it scans the index (though it says index seek) and for each potential match of included column value retries matching the rest. RBAR. In our database this node uses the other index where the search column is first index column.

Humor is a good ignition device for new ideas and makes you feel good, if it proves correct, it's double feel good.