On last Sunday of the last 2 months at 9.47AM we are getting blocking in SQL server. It last 2-3 hours and then disappears on its own. We don't get any blocking at all any other times of the month.
How can I check what is happening at that particular time?
I tried the query below, but it does not show anything being executed that day at that particular time. The last entry is 35 minutes before the problem starts and then the next entry is 30 minutes after the problem started.
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
I also checked scheduled jobs in SQL Server Agent, but nothing that is not normally running (backups, cdc, index rebuilds etc) is on.
Therefore, my question is: how can I find which external process is using the database at the particular time on the last Sunday of the month? I would be grateful for any tips and suggestions.
sys.dm_exec_query_stats only shows cached execution plans. That's unlikely to help you.
Instead, since it's predictable when this happens, you can use sys.dm_exec_connections to get the active connections (you can then use task manager to the process using the same ports - if it's running on a machine you have access to). To find what kind of query is executing, use sys.dm_exec_requests.
This has to be run while the actual load is happening - so you'll probably want to either do it manually when the problem occurs, or you'll need to schedule its execution and log the results.