Finding a SQL Server process percentage complete with DMVs

Problem

Some tasks that are run in SQL Sever take a long time to run and it is sometimes difficult to tell whether these tasks are progressing or not.  One common way of determining that status is to look at the data returned from sp_who2 or sp_lock to ensure that things are still working and the process is not hung.  With SQL Server several dynamic management views have been added, so let’s take a look at some of these and how they can assist.

Solution

As mentioned above using sp_who2 or using the GUI tools you can get an idea of what is running and the current status of these tasks.  With SQL Server several dynamic management views have been added and we will take a look at two of these so you can gain some additional insight into your processing.

The two DMVs that we will look at are:

• dm_exec_requests
• dm_exec_sessions

Data can be selected from these DMVs directly using a SELECT statement.

Let’s take a look at running a DBCC CHECKDB which often takes a lot of time to process and see what information we get back from sp_who2 as well as these new DMVs.  We will run this against the AdventureWorks database.

To do this we open a new query window and then run the following:

use AdventureWorks
GO 
DBCC CHECKDB

When looking at the DMVs and the output from sp_who2 we get the results listed below.  When I ran this my session_id was 68, so you will need to use the session_id that is being used to run the DBCC command.  This can be found on the bottom left of the query window.

SELECT * FROM sys.dm_exec_sessions WHERE session_id = 68

Note: I pivoted the data so it was easier to read.

SELECT * FROM sys.dm_exec_requests WHERE session_id = 68

Note: I pivoted the data so it was easier to read.

sp_who2 68

Note: I pivoted the data so it was easier to read.

From the above output we can get a handle on the different things that are occurring.  The data from sp_who2 is helpful, but it is very limited.  The one thing we can see is that the DBCC has spawned multiple threads to run the command, but not a lot more.

One thing to note from the sys.dm_exec_requests is the percent_complete column (this is highlighted above).  This gives you an idea where things are as well as an idea that things are progressing as long as this value continues to increase.

Next Steps

• As you can see these two DMVs give you much more insight then sp_who2 gives you.  So next time you are monitoring SQL Server , don’t forget that these DMVs exist.
• Take a look at these other DMV tips Dynamic Management Views and Functions – SQL Server Exposed