Query to identify queries executed in a specific Azure SQL DB

Question

Hi All,

I am trying to monitor queries running on Azure SQL Databases on a Premium tier Elastic Pool with ReadScale replication enabled. We redirect reporting for our OLTP system against the replicas (using ApplicationIntent = ReadOnly) which means Query Store does not capture the SQL. I have a Runbook to connect to all the DBs on a schedule and query DMVs to try to get the data I want. Here's the simplified query...

SELECT 
  s.*,
  t.*
FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as t
--  CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) as p -- Wanted plans too but commented out for now!
WHERE 1 = 1
  AND s.last_execution_time >= CAST(DATEADD(MINUTE, -40, GETUTCDATE()) AS DATETIME) -- Capture last 40 mins as this will run every 30 mins
ORDER BY s.last_execution_time DESC;

My problem is as the Runbook loops round each target DB I'm getting results from every DB on the server, from every DB. What I really want is DB1 to show queries that ran in DB1, DB2 to show queries that ran in DB2, etc.

If I run this query in master I only get the queries that ran in master, and sys.dm_exec_sql_text.dbid seems to be too limiting, removing ad-hoc queries that I want to see.

Any suggestions appreciated,

Thanks.

Answer 1

@Matthews, Darren (Capita Retain) Can you try as below

SELECT

r.session_id,

r.status,

r.command,

s.*,

t.*

FROM sys.dm_exec_requests r

JOIN sys.dm_exec_query_stats s ON r.plan_handle = s.plan_handle

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as t

WHERE r.database_id = DB_ID('YourDatabaseName') -- Replace with your target database name

AND s.last_execution_time >= CAST(DATEADD(MINUTE, -40, GETUTCDATE()) AS DATETIME)

ORDER BY s.last_execution_time DESC;