DB doesn't return a response for a very long time without errors

[John_Wick]

Hello everyone
I often encounter a problem and I think it is not unique. The problem sometimes happens on a prod environment. The usual select * from the database freezes on routine tasks. DBA say that there are no problems on their part. This usually happens with several campaigns on the schedule at the same time.

How to set, for example, read timeout or something else so that such requests fall with an error, and not remain dead?

Or does anyone have any idea why this happens at all?

DB - SQLServer via SAS ACCESS to SQLServer.

Usually this request takes 1-3 minutes and the data did not differ from those previously:

MPRINT(EXEC_MACRO.MAEXP.MAUSREXP.MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES.MGETVARTYPESQL):
MPRINT(EXEC_MACRO.MAEXP.MAUSREXP.MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES):
SQLSRV: Called SQLTables with schema of cdm
NOTE: 2 table(s) not displayed/returned because the name is too long.
SQLSRV: AUTOCOMMIT is NO for connection 5
SQLSRV: AUTOCOMMIT turned ON for connection id 5с

SQLSRV_14: Prepared: on connection 5
SELECT * FROM "cdm"."MA CONTACT HISTORY"

NOTE: No rows were selected.
MPRINT (EXEC_MACRO.MAEXP .MAUSREXP .MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES) :
NOTE: PROCEDURE SQL used (Total process time):
                   real time 2:08:24.06

                   user cpu time 0.13 seconds
                   system cpu time 0.05 seconds
                   memory 5593.12k

                   OS Memory 39616.00k
                   Timestamp 17.02.2023 09:21:40
                   Step Count 435 Switch Count 0

                   Page Faults 0

[SASKiwi]

Since it appears you are using a SAS product which is automatically generating SQL you would be best to open a Tech Support track for this issue.

[Sajid01]

Hello
I see this in the log "NOTE: 2 table(s) not displayed/returned because the name is too long."

SAS has limit of 32 character  for table names whereas SQL server has a limit of 128 Character for table names.

Please review this.

[Tom]

What are these macros you are running? 

MPRINT(EXEC_MACRO.MAEXP.MAUSREXP.MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES.MGETVARTYPESQL):

Perhaps one of them is very slow?