Archive for the ‘sp_whoisactive’ Category

Joining sys.dm_exec_requests to sp_whoisactive results

October 28, 2015 1 comment

Previously, I've blogged about returning formatted estimated_completion_time values from sys.dm_exec_requests and getting sortable output from sp_whoisactive. In this post I combine the two, joining the two result sets together. In my environment, we use Ola Hallengren's Maintenance Solution to run our backups. Because the backup job defaults to run backups for all user databases, it can be difficult to tell which database is currently being backed up.


This procedure takes the output from sp_whoisactive and joins it to sys.dm_exec_requests on session_id, returning the estimated completion time and current backup statement in a single pass. Here is a sample result set from the procedure (Click to Enlarge):


The sql_command column contains the full command being run, in this example Ola Hallengren's DatabaseBackup procedure. The sql_text column contains the actual SQL statement being run, BACKUP DATABASE [AdventureWorks2012] TO DISK.... The DatabaseBackup procedure runs database backups in alphabetical order based on the database name, so this allows tracking a job progress.

Many thanks to Adam Machanic (b|t) for sp_whoisactive, Ola Hallengren (b|t) for his Maintenance Solution, and Aaron Bertrand (b|t) for his advice on formatting the estimated completion time more elegantly. I finally had an opportunity to use it.

I hope you can put this procedure to good use. Let me know if you have any questions.

The August Chicago SQL Server User Group Meeting

August 10, 2012 Leave a comment

Last night was the August meeting of the Chicago SQL Server User Group. It was the second meeting Aaron Lowe and I have run. Ira Whiteside of Melissa Data was kind enough to sponsor the meeting and was scheduled to present. Unfortunately, Ira ran into car problems on the way down and could not make it. In Ira's absence, Aaron and I decided that we would each do a presentation. Aaron presented on Writing Professional Database Code, which was excellent. Statement terminators and version control for database code are two of Aaron's suggestions I will bring back to my team.

I started off the meeting with a short presentation introducing Adam Machanic's sp_whoisactive. If you do any troubleshooting of SQL Server issues, sp_whoisactive is a tool you should be using. It consolidates data from sp_who2, sys.dm_exec_requests and a number of other DMOs to give you a quick and complete picture of how each process is performing on your system. You can pull the execution plan for each process and have the option to generate deltas for a number of metrics over a period of time. The best part about the procedure is that it is free for download. The fact that Adam Machanic provides the code for free is a testament to the strength of the SQL Server community.

If you live in the Chicago area and are not registered for the user group, you can do so here. You will receive e-mail notification for upcoming meetings. We are looking for group members to present at upcoming meetings. If you are interested, you can e-mail me here. I hope to see you at an upcoming meeting.

Categories: CSSUG, DMO, sp_whoisactive Tags: ,