query timeout

Question

Today the Dynamic could not load data, and in splunk logs, i notice high frequency query timeout error. In SSMS, I notice high Parrellel redo tran turn wait type. I tried to reboot mssql and quick fix the issue. but may i know what is the possible cause.

Answer 1

Usually such issues are due to poor database design, improper indexing and/or poorly written queries.

In such situation, you rarely have any choice but to roll up the sleeves and start performance tuning.

If you are on SQL 2016 or later, get a head start with enabling Query Store for the database. After a few hours, find the Query Store node in Object Explorer. Go there are and explorer the reports and start digging.

Answer 2

Hi @Elon Musk

This issue usually occurs when a query that needs to request memory for sorting or hashing operations does not get enough memory in the allotted time.

It might because SQL Server has experienced memory pressure across the entire SQL range, and even a small number of memory requests is difficult to satisfy. Or, SQL Server may not have a great memory pressure, but the user suddenly sent one or several extremely complex statements that require a large amount of memory, which suddenly made SQL Server memory resources very tight.

Here are the general steps to help resolve this memory error.

• Avoid SQL Server's memory being overrun by Windows or other applications. Verify that other applications or services are consuming memory on this server. Reconfigure less important applications or services so that they consume less memory.
• Start collecting performance monitor counters for SQL Server: Buffer Manager, SQL Server: Memory Manager.
• Check the dynamic management view sys.dm_exec_query_memory_grants to return information about queries that have already received memory grants, or queries that are still waiting to be executed.

SELECT getdate() as runtime , 
       session_id,scheduler_id,DOP,request_time,grant_time,
       requested_memory_kb,granted_memory_kb,used_memory_kb,
       timeout_sec,query_cost,timeout_sec,resource_semaphore_id,
       wait_order,is_next_candidate, wait_time_ms,
       REPLACE (REPLACE (cast(s2.text as varchar(4000)), CHAR(10), ' '), CHAR(13), ' ')  AS sql_statement
from sys.dm_exec_query_memory_grants 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

This gives you an opportunity to catch statements that are causing SQL Server memory pressure. If you can adjust the design, you may be able to solve the problem without upgrading the hardware.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.