State "Waiting for table flush" in processlist

Question

If I try to run queries (even as easy as select id from table limit 1 ) on some specific tables in a schema (only a few of them have this problem) I get stuck. When looking at the processlist, the state is "Waiting for table flush". Any suggestion about how do I unlock these tables so that I can query them?

Answer 1

For MySQL

1. Identify the processes causing issues.

   • This command will help to detect processes waiting for disk I/O in D state:

     watch "ps -eo pid,user,state,command | awk '\$3 == /D/ { print \$0 }'"

   • You can also seek processes with long runtime like this:

     SHOW FULL PROCESSLIST\G

2. Fix the processes or related queries you previously detected.

3. If you need to kill the detected processes, you can find their ids with the previous full processlist and execute the command: KILL <pid>;

In case you want to fix the same issue For SQL :

1. Find wich processes are causing issues. This query will list them:

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 
    qs.total_worker_time DESC

2. Fix the processes or related queries you previously detected.

3. If you need to kill the detected processes, you can find their ids with the previous full processlist and execute the command:

KILL <SPID>
GO
EXEC sp_who2
GO

You can find more alternatives and details at the following source questions/answers/comments:

https://serverfault.com/questions/316922/how-to-detect-the-process-and-mysql-query-that-makes-high-load-on-server How do I find out what is hammering my SQL Server?

You also can use step by step SQL related article: https://www.wearediagram.com/blog/terminating-sql-server-blocking-processes