Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different.

For this blog post I decided to use Brent Ozar’s famous sp_BlitzWho command (in expert mode) coupled with Azure Logic Apps. At a high level it is simple. At a specific time trigger the execution of sp_BlitzWho stored procedure and query it for later use.

Before I begin I would like to mention Azure Logic Apps. It simplifies how you build automated scalable workflows that integrate apps and data across cloud services and on-premises systems. Important components such as triggers and actions are key to fulfill your logic. I linked to Microsoft documentation in the above paragraph.

What does my workflow look like?

You can set the recurrence to be a specific time or tell it to check whatever frequency you require.

I set mine to a time stamp such as: 2019-02-06T11:05:00Z which you would enter as the start time which will get triggered automatically.

If successful you will see the below which is the result of the SQL query being executed above.

Based on the variables assigned expect the stored procedure to create a table for you to query.

SELECT *
  FROM [dbo].[BrentO]
  where database_name = 'Testdb'
  and session_cpu >= 500

This is a pretty good way to schedule and execute a data gathering command at times when you might be sleeping. Hopefully you have found it useful. You can add your own twists to it from sending an automated email after the table has been created to even running parallel branches.

Another possible technique would be where you could put a delay into workflow after the first execution and write the data out to a different table for the second execution so you could perform deltas during that delay.