I want to build a usage scanner for SQL Server to gather information about which user performed which query, how much time it took, etc., similar to Snowflake.

In Snowflake, there’s the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY table, and in Teradata, we have DBQ queries. I used system tables like sys.dm_exec_requests, sys.dm_exec_query_stats, and sys.dm_exec_sessions, but they only provide data for the current day.

My question is how can I get this type of information to get usage level information?

I cannot see audit table in which I have this type of information.

Help is really appreciated.