Query to identify queries executed in a specific Azure SQL DB

Matthews, Darren (Capita Retain) 0 Reputation points
2024-05-08T12:31:13.2233333+00:00

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.

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 29,482 Reputation points Microsoft Employee
    2024-05-29T19:41:45.87+00:00

    @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;


  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.