Is it possible to execute DMV queries between certain time periods?

Cataster 641 Reputation points
2021-06-24T17:05:57.097+00:00

We have a DMV query that executes every 10 mins and inserts usage statistics, like SESSION_CURRENT_DATABASE, SESSION_LAST_COMMAND_STARTTIME, etc.. and supposedly has been running fine for the last 2 years.

Today we were notified by data hyperingestion team that the last records shown were from 6/10. So we found out the job has been stuck for 14 days not executing new statistics since. We've immediately restarted the job and it's been executing successfully since the morning, but basically we've lost the data during this 14 days period. Is there a way for us to execute this DMV query between 6/10-6/24 on the $SYSTEM.DISCOVER to recover these past 14 days of data?

Or all hope's lost?

DMV query

SELECT [SESSION_ID]
      ,[SESSION_SPID]
      ,[SESSION_CONNECTION_ID]
      ,[SESSION_USER_NAME]
      ,[SESSION_CURRENT_DATABASE]
      ,[SESSION_USED_MEMORY]
      ,[SESSION_PROPERTIES]
      ,[SESSION_START_TIME]
      ,[SESSION_ELAPSED_TIME_MS]
      ,[SESSION_LAST_COMMAND_START_TIME]
      ,[SESSION_LAST_COMMAND_END_TIME]
      ,[SESSION_LAST_COMMAND_ELAPSED_TIME_MS]
      ,[SESSION_IDLE_TIME_MS]
      ,[SESSION_CPU_TIME_MS]
      ,[SESSION_LAST_COMMAND_CPU_TIME_MS]
      ,[SESSION_READS]
      ,[SESSION_WRITES]
      ,[SESSION_READ_KB]
      ,[SESSION_WRITE_KB]
      ,[SESSION_COMMAND_COUNT]
FROM $SYSTEM.DISCOVER_SESSIONS
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,243 questions
0 comments No comments
{count} votes

1 additional answer

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2021-06-24T17:52:27.587+00:00

    Is there a way for us to execute this DMV query between 6/10-6/24

    Obviously you can. You only need a time Machine and go a few days back. Maybe Doctor Emmett from Back to the Future can help in this task.
    109019-image.png

    How exactly do you want to execute a query 14 days ago when we are in the present?!?

    You request is totally not clear.

    If you provide more information about what data is missing and which DMV you used then we might find a way to pull some of the data from other places like the transaction log file, but most of the dat provided by Dynamic Management Views (DMV) is something you get dynamically in specific point of time when you execute the query

    1 person found this answer helpful.