Query does nothing except context switches
I'd like to understand what could have been happening during the 30 seconds when it seems to have been doing nothing.


If a query isn't using CPU, it's in a wait, which are tracked as the system-level in sys.dm_os_wait_stats.

SQL Server 2016 introduces a DMV for session-level wait stats with sys.dm_exec_session_wait_stats.

SQL Server 2017 introduces wait tracking at the query level in Query Store with sys.query_store_wait_stats.

Earlier versions can use Extended Events to see the waits for a particular session.  See eg: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/20/troubleshooting-slow-running-query-using-extended-events-wait-info-event/