Patience and sys.dm_exec_requests

I’m not always the most patient person in the world. I’m better now, but when I first started as a DBA, I’d have a tendency to think that a process had hung when it was actually processing away. Because of that, I made a lot of those mistakes of stopping and restarting a process (especially restores and DBCC CHECKDB commands) when in fact, they were probably working just fine.

Sys.dm_exec_requests is a DMV that returns a row for every request that is currently executing. The command column will hold the command that you’re concerned about. The amazing part here is that it has a percent_complete column. For example:

SELECT percent_complete
  FROM sys.dm_exec_requests
  WHERE LOWER(command) = N'dbcc checkdb';

You can also enter the session ID (SPID):

SELECT percent_complete
  FROM sys.dm_exec_requests
  WHERE session_id = 59;

If the percent_complete column continues to increase over time, you can know that the process is making progress. Keep in mind that you can’t necessarily extrapolate the time it will finish from the start_time and percent_complete columns, or expect that estimated_completion_time is accurate. For example, during a DBCC CHECKDB operation, if corruption is found at any level, a deeper check might be required and that would extend the time it takes to run the CHECKDB. If you do make the lovely mistake of, say, killing a query that had been running forever, you can also get an idea of just how much has to rollback now. Yay.

To be clear, the percent_complete command isn’t populated for everything. Here’s the list from the MSDN article on sys.dm_exec_requests:

You’ll notice that SELECT/INSERT/UPDATE/DELETE statements are not included in this list. So you can’t use it to figure out how long a query is going to run.

I’ve focused on percent_complete, because that’s what saves me from the crazy house, but there is a lot of great information to be found in this DMV. You can capture both the SQL and Plan handles. You can see what the last wait type was and how long it spent on that wait type.

Keeping an eye on that DMV lets me know that the process is progressing and has kept me from overreacting and canceling processes that seem to be stuck, but aren’t. I’ve since used that on long-running processes like backups and restores. It also provides a way to tell users that, yes, this is running, and it’s roughly x% done.

Lori Edwards

Lori (@loriedwards) has been with SentryOne since 2013, and has recently transitioned from her role as Senior Solutions Engineer to Training Manager. She is responsible for all learning strategy and architecture, including building, executing, measuring, and evaluating training for SentryOne. Lori is also currently serving as a Director at Large for PASS.

Lori shares tips and tidbits for DBAs picked up over years of working as a DBA, as well as stories about her job here. Whether you are a new DBA who wants to learn or someone interested in learning about what it is like to work for SentryOne, be sure to check out Lori’s blog.