Troubleshooting Live Queries

During our career as a database professional, we likely encounter cases where a runaway query takes hours to complete or doesn’t even complete by any reasonable time measurement. How do we troubleshoot cases such as this?

A query execution plan can help provide a conclusive explanation of query performance issues. But to get a query execution plan there is one requirement a long-running query can’t easily meet: query completion.

If the query takes a long time to complete or never actually does, then how can we troubleshoot these cases? And what happens if we take that production query back to our development server and it runs fine? That means there is a set of conditions that can only be reproduced in the production server, be that the size of the database, the data distribution statistics, or even the availability of resources such as memory or CPU. Therefore, the ability to analyze a query execution plan while the query is executing...