Microsoft Copilot skills in Azure SQL Database (SQLExternalMonitoring_Copilot)

Check for any problems in the database.

SELECT 
  @@SERVERNAME AS Server, 
  @@VERSION AS SQLServerVersion, 
  d.name AS DBName, 
  d.recovery_model_Desc AS RecoveryModel, 
  d.Compatibility_level AS CompatiblityLevel, 
  d.create_date, 
  d.state_desc, 
  slo.* 
FROM sys.databases AS d 
INNER JOIN sys.database_service_objectives AS slo 
  ON d.database_id = slo.database_id;

Check top blocking sessions.

WITH cteHead AS ( 
  SELECT 
    sess.session_id, 
    req.request_id, 
    LEFT(ISNULL(req.wait_type, ''), 50) AS 'wait_type', LEFT(ISNULL(req.wait_resource, ''), 40) AS 'wait_resource', 
    LEFT(req.last_wait_type, 50) AS 'last_wait_type', 
    req.wait_time, 
    req.blocking_session_id, 
    LEFT(req.[status], 15) AS 'request_status', 
    sess.open_transaction_count AS 'open_tran_count', 
    req.command, 
    req.plan_handle, 
    req.[sql_handle], 
    conn.most_recent_sql_handle, 
    LEFT(sess.[status], 15) AS 'session_status', 
    req.query_hash, 
    req.query_plan_hash 
  FROM sys.dm_exec_sessions AS sess 
  LEFT OUTER JOIN sys.dm_exec_requests AS req 
    ON sess.session_id = req.session_id 
  LEFT OUTER JOIN sys.dm_exec_connections AS conn 
    ON conn.session_id = sess.session_id 
), 
cteBlockingHierarchy AS ( 
  SELECT 
    head.session_id AS head_blocker_session_id, 
    head.session_id AS session_id, 
    head.blocking_session_id, 
    head.request_status, 
    head.open_tran_count, 
    head.wait_type, 
    head.wait_time, 
    head.wait_resource, 
    head.[sql_handle], 
    head.most_recent_sql_handle, 
    0 AS [level] 
  FROM cteHead AS head 
  WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0) 
        AND head.session_id IN ( 
          SELECT DISTINCT blocking_session_id 
          FROM cteHead 
          WHERE blocking_session_id != 0 
        ) 
  UNION ALL 
  SELECT 
    h.head_blocker_session_id, 
    blocked.session_id, 
    blocked.blocking_session_id, 
    blocked.request_status, 
    blocked.open_tran_count, 
    blocked.wait_type, 
    blocked.wait_time, 
    blocked.wait_resource, 
    h.[sql_handle], 
    h.most_recent_sql_handle, 
    [level] + 1 
  FROM cteHead AS blocked 
  INNER JOIN cteBlockingHierarchy AS h 
    ON h.session_id = blocked.blocking_session_id 
       AND h.session_id != blocked.session_id 
  WHERE (h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE','CXPACKET') 
         OR h.wait_type IS NULL) 
) 
SELECT 
  bh.session_id AS blocked_session_id, 
  bh.blocking_session_id AS blocker_session_id, 
  bh.request_status, 
  bh.wait_type, 
  bh.wait_resource, 
  txt.[text] AS blocker_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text( 
  ISNULL([sql_handle], most_recent_sql_handle) 
) AS txt;

Show me all the queries in my workload that have anti-patterns in them.

WITH AggregatedCPU AS ( 
  SELECT 
    '0x'+ CONVERT(VARCHAR(16), CONVERT(VARBINARY(8), q.query_hash), 2) 
      AS query_hash, 
    ROUND( 
      SUM(count_executions * avg_cpu_time / 1000.0), 
      3) AS total_cpu_ms, 
    ROUND( 
      SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions), 
      3) AS avg_cpu_ms, 
    ROUND(MAX(rs.max_cpu_time / 1000.00), 3) AS max_cpu_ms, 
    ROUND(MAX(max_logical_io_reads), 3) AS max_logical_reads, 
    COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, 
    COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Aborted' THEN count_executions 
        ELSE 0 
      END) AS aborted_execution_count, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Regular' THEN count_executions 
        ELSE 0 
      END) AS regular_execution_count, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Exception' THEN count_executions 
        ELSE 0 
      END) AS exception_execution_count, 
    SUM(count_executions) AS total_executions 
  FROM sys.query_store_query_text AS qt 
  INNER JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id 
  INNER JOIN sys.query_store_plan AS p 
    ON q.query_id = p.query_id 
  INNER JOIN sys.query_store_runtime_stats AS rs 
    ON rs.plan_id = p.plan_id 
  INNER JOIN sys.query_store_runtime_stats_interval AS rsi 
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id 
  WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception') 
        AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE()) 
  GROUP BY query_hash 
), 
OrderedCPU AS ( 
  SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN 
  FROM AggregatedCPU 
) 
SELECT * 
FROM OrderedCPU AS OD 
WHERE OD.RN <= 10 
ORDER BY total_cpu_ms DESC;

What is the name of the logical server for this database?

SELECT 
  @@SERVERNAME AS Server, 
  @@VERSION AS SQLServerVersion, 
  d.name AS DBName, 
  d.recovery_model_Desc AS RecoveryModel, 
  d.Compatibility_level AS CompatiblityLevel, 
  d.create_date, 
  d.state_desc, 
  slo.* 
FROM sys.databases AS d 
INNER JOIN sys.database_service_objectives AS slo 
  ON d.database_id = slo.database_id;

What's the compatibility level of this database?

SELECT 
  @@SERVERNAME AS Server, 
  @@VERSION AS SQLServerVersion, 
  d.name AS DBName, 
  d.recovery_model_Desc AS RecoveryModel, 
  d.Compatibility_level AS CompatiblityLevel, 
  d.create_date, 
  d.state_desc, 
  slo.* 
FROM sys.databases AS d 
INNER JOIN sys.database_service_objectives AS slo 
  ON d.database_id = slo.database_id;

Who are currently actively connected to the database?

SELECT 
  @@Servername AS Server, 
  DB_NAME(database_id) AS DatabaseName, 
  COUNT(database_id) AS Connections, 
  PROGRAM_NAME, 
  Login_name AS LoginName, 
  MIN(Login_Time) AS Login_Time, 
  MIN(COALESCE(last_request_end_time, last_request_start_time)) AS Last_Batch 
FROM sys.dm_exec_sessions 
WHERE database_id > 0 
      AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' ) 
GROUP BY 
  database_id, 
  login_name, 
  PROGRAM_NAME 
ORDER BY Connections DESC;

Why is automatic plan correction failing?

SELECT 
  name, 
  desired_state_desc, 
  actual_state_desc, 
  reason_desc 
FROM sys.database_automatic_tuning_options;

Why is my database slow?

SELECT 
  AVG(avg_cpu_percent) AS 'Average CPU Percent', 
  MAX(avg_cpu_percent) AS 'Maximum CPU Percent', 
  AVG(avg_instance_cpu_percent) AS 'Average Instance CPU Percent', 
  MAX(avg_instance_cpu_percent) AS 'Maximum Instance CPU Percent', 
  SUM(CASE 
        WHEN avg_cpu_percent >= 80 THEN 1 
        ELSE 0 
      END) / 4. AS 'Minutes with CPU Percent over 80%' 
FROM sys.dm_db_resource_stats 
WHERE end_time >= DATEADD(MINUTE, -60, GETDATE());

WITH cteHead AS ( 
  SELECT 
    sess.session_id, 
    req.request_id, 
    LEFT(ISNULL(req.wait_type, ''), 50) AS 'wait_type', LEFT(ISNULL(req.wait_resource, ''), 40) AS 'wait_resource', 
    LEFT(req.last_wait_type, 50) AS 'last_wait_type', 
    req.wait_time, 
    req.blocking_session_id, 
    LEFT(req.[status], 15) AS 'request_status', 
    sess.open_transaction_count AS 'open_tran_count', 
    req.command, 
    req.plan_handle, 
    req.[sql_handle], 
    conn.most_recent_sql_handle, 
    LEFT(sess.[status], 15) AS 'session_status', 
    req.query_hash, 
    req.query_plan_hash 
  FROM sys.dm_exec_sessions AS sess 
  LEFT OUTER JOIN sys.dm_exec_requests AS req 
    ON sess.session_id = req.session_id 
  LEFT OUTER JOIN sys.dm_exec_connections AS conn 
    ON conn.session_id = sess.session_id 
), 
cteBlockingHierarchy AS ( 
  SELECT 
    head.session_id AS head_blocker_session_id, 
    head.session_id AS session_id, 
    head.blocking_session_id, 
    head.request_status, 
    head.open_tran_count, 
    head.wait_type, 
    head.wait_time, 
    head.wait_resource, 
    head.[sql_handle], 
    head.most_recent_sql_handle, 
    0 AS [level] 
  FROM cteHead AS head 
  WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0) 
        AND head.session_id IN ( 
          SELECT DISTINCT blocking_session_id 
          FROM cteHead 
          WHERE blocking_session_id != 0 
        ) 
  UNION ALL 
  SELECT 
    h.head_blocker_session_id, 
    blocked.session_id, 
    blocked.blocking_session_id, 
    blocked.request_status, 
    blocked.open_tran_count, 
    blocked.wait_type, 
    blocked.wait_time, 
    blocked.wait_resource, 
    h.[sql_handle], 
    h.most_recent_sql_handle, 
    [level] + 1 
  FROM cteHead AS blocked 
  INNER JOIN cteBlockingHierarchy AS h 
    ON h.session_id = blocked.blocking_session_id 
       AND h.session_id != blocked.session_id 
  WHERE (h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE','CXPACKET') 
         OR h.wait_type IS NULL) 
) 
SELECT 
  bh.session_id AS blocked_session_id, 
  bh.blocking_session_id AS blocker_session_id, 
  bh.request_status, 
  bh.wait_type, 
  bh.wait_resource, 
  txt.[text] AS blocker_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text( 
  ISNULL([sql_handle], most_recent_sql_handle) 
) AS txt;

Why is the CPU usage high on this database?

WITH AggregatedCPU AS ( 
  SELECT 
    '0x'+ CONVERT(VARCHAR(16), CONVERT(VARBINARY(8), q.query_hash), 2) 
      AS query_hash, 
    ROUND( 
      SUM(count_executions * avg_cpu_time / 1000.0), 
      3) AS total_cpu_ms, 
    ROUND( 
      SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions), 
      3) AS avg_cpu_ms, 
    ROUND(MAX(rs.max_cpu_time / 1000.00), 3) AS max_cpu_ms, 
    ROUND(MAX(max_logical_io_reads), 3) AS max_logical_reads, 
    COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, 
    COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Aborted' THEN count_executions 
        ELSE 0 
      END) AS aborted_execution_count, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Regular' THEN count_executions 
        ELSE 0 
      END) AS regular_execution_count, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Exception' THEN count_executions 
        ELSE 0 
      END) AS exception_execution_count, 
    SUM(count_executions) AS total_executions 
  FROM sys.query_store_query_text AS qt 
  INNER JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id 
  INNER JOIN sys.query_store_plan AS p 
    ON q.query_id = p.query_id 
  INNER JOIN sys.query_store_runtime_stats AS rs 
    ON rs.plan_id = p.plan_id 
  INNER JOIN sys.query_store_runtime_stats_interval AS rsi 
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id 
  WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception') 
        AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE()) 
  GROUP BY query_hash 
), 
OrderedCPU AS ( 
  SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN 
  FROM AggregatedCPU 
) 
SELECT * 
FROM OrderedCPU AS OD 
WHERE OD.RN <= 10 
ORDER BY total_cpu_ms DESC;


SELECT TOP 10 
  req.session_id, 
  req.status, 
  req.start_time, 
  ROUND(req.cpu_time, 3) AS 'cpu_time_ms', 
  '0x'+ CONVERT(VARCHAR(16), CONVERT(VARBINARY(8), req.query_hash), 2) AS query_hash, 
  req.logical_reads, 
  req.dop,s.login_name, 
  s.host_name, 
  s.program_name, 
  OBJECT_NAME(st.objectid, st.dbid) AS 'object_name', 
  REPLACE( 
    REPLACE( 
      SUBSTRING( 
        st.text, 
        (req.statement_start_offset / 2) + 1, 
        (CASE req.statement_end_offset 
           WHEN -1 THEN DATALENGTH(st.text) 
           ELSE req.statement_end_offset 
         END - req.statement_start_offset) / 2 + 1), 
      CHAR(10), ' '), 
    CHAR(13), ' ') AS statement_text 
FROM sys.dm_exec_requests AS req 
INNER JOIN sys.dm_exec_sessions AS s 
  ON req.session_id = s.session_id 
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st 
WHERE req.session_id <> @@SPID 
ORDER BY req.cpu_time DESC;