question

Tokenaton avatar image
Tokenaton asked

Select with a particular value goes in timeout

Hi, I've a strange situation, this is not the first time I saw that. In such a situation it seems that sql server not execute a SELECT statement in time going in a timeout.

The strange thing is that same query with different value, not sintax, and same client works like a charm. Is it possible for the underscore character ? but other client have the same special char in their name and it works !!

See below:


--This one go in timeout

SELECT DisplayOrderInfo FROM dbo.ORDERS_IN RIGHT JOIN

(SELECT TOP(1) OrderRowInsertedGuid FROM PACKAGE_RECORD WHERE DeviceName = 'B2O_GLME_MASTER_L3' ORDER BY VerificationDate desc) t1

ON dbo.ORDERS_IN.InsertedGuid = t1.OrderRowInsertedGuid

WHERE dbo.ORDERS_IN.State = 2 and Suspended = 0


--This one work like a charm, the only difference is the DeviceName

SELECT DisplayOrderInfo FROM dbo.ORDERS_IN RIGHT JOIN

(SELECT TOP(1) OrderRowInsertedGuid FROM PACKAGE_RECORD WHERE DeviceName = 'B2O_GLME-MASTER_L3' ORDER BY VerificationDate desc) t1

ON dbo.ORDERS_IN.InsertedGuid = t1.OrderRowInsertedGuid

WHERE dbo.ORDERS_IN.State = 2 and Suspended = 0


there are two other client with different name and with 1 million record in join that react in a nanosecond, I've tried to delete completly the records on the right join part but no difference select still go in timeout, In the end I've changed the name of the device to work


This is the versione of Sql Server, but in the past I've seen a similar situation on other sql server

--select @@VERSION

Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) Nov 16 2019 01:14:50 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

selectjointimeout-expiredvalue
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

It's likely generating a execution plan that suits one value and not the other. How up-to-date are the statistics on that column? Try updating those and see if the execution performance changes.

6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tokenaton avatar image Tokenaton commented ·

Tks for the quick response, the statistics are up-to-date at 01/21/2021 not so bad I think ?

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·

They could still be bad if there has been enough data movement. Try updating stats first. Then look at the execution plans for each query and see if there is any difference.

0 Likes 0 ·
Tokenaton avatar image Tokenaton commented ·

So, stats wont update, execution plan does not appear since query take long long time

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·

Why won't the stats update?
An estimated execution plan is good enough for what you need - no need to run the query

0 Likes 0 ·
Tokenaton avatar image Tokenaton commented ·

In SSMS stats property update statistics does not worked, editing index and saving without any change worked. Execution plan and query execution worked great. Great and tanks a lot


0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.