Delen via


Prestaties van Microsoft Azure SQL Database bewaken met dynamische beheerweergaven

Van toepassing op: Azure SQL Database

Met Microsoft Azure SQL Database kan een subset van dynamische beheerweergaven prestatieproblemen vaststellen, die kunnen worden veroorzaakt door geblokkeerde of langdurige query's, resourceknelpunten, slechte queryplannen en meer.

Dit artikel bevat informatie over het detecteren van veelvoorkomende prestatieproblemen door query's uit te voeren op dynamische beheerweergaven via T-SQL. U kunt elk queryprogramma gebruiken, zoals:

Bevoegdheden

In Azure SQL Database, afhankelijk van de rekengrootte en implementatieoptie, is het mogelijk dat voor het uitvoeren van query's op een DMV de machtiging VIEW DATABASE STATE of VIEW SERVER STATE is vereist. De laatste machtiging kan worden verleend via lidmaatschap van de ##MS_ServerStateReader## serverfunctie.

Als u de VIEW DATABASE STATE-machtiging wilt verlenen aan een specifieke databasegebruiker, voert u de volgende query uit als voorbeeld:

GRANT VIEW DATABASE STATE TO database_user;

Als u lidmaatschap wilt verlenen aan de ##MS_ServerStateReader## serverfunctie voor een aanmelding voor de logische server in Azure, maakt u verbinding met de master database en voert u de volgende query uit als voorbeeld:

ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login];

In een exemplaar van SQL Server en in Azure SQL Managed Instance retourneren dynamische beheerweergaven serverstatusgegevens. In Azure SQL Database retourneren ze alleen informatie met betrekking tot uw huidige logische database.

CPU-prestatieproblemen identificeren

Als het CPU-verbruik gedurende langere perioden hoger is dan 80%, kunt u de volgende stappen voor probleemoplossing overwegen of het CPU-probleem zich nu voordoet of in het verleden heeft plaatsgevonden.

Het CPU-probleem treedt nu op

Als het probleem zich op dit moment voordoet, zijn er twee mogelijke scenario's:

Veel afzonderlijke query's die cumulatief hoge CPU verbruiken
  • Gebruik de volgende query om topquery-hashes te identificeren:

    PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';
    SELECT TOP 10 GETDATE() runtime, *
    FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"
        FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text
              FROM sys.dm_exec_requests AS req
                    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats
        GROUP BY query_hash) AS t
    ORDER BY Total_Request_Cpu_Time_Ms DESC;
    
Langlopende query's die CPU verbruiken, worden nog steeds uitgevoerd
  • Gebruik de volgende query om deze query's te identificeren:

    PRINT '--top 10 Active CPU Consuming Queries by sessions--';
    SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE 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), ' '), 1, 512) AS statement_text
    FROM sys.dm_exec_requests AS req
        CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
    ORDER BY cpu_time DESC;
    GO
    

Het CPU-probleem is in het verleden opgetreden

Als het probleem zich in het verleden heeft voorgedaan en u de hoofdoorzaakanalyse wilt uitvoeren, gebruikt u Query Store. Gebruikers met databasetoegang kunnen T-SQL gebruiken om query's uit te voeren op Query Store-gegevens. Standaardconfiguraties van Query Store gebruiken een granulariteit van 1 uur.

  1. Gebruik de volgende query om te kijken naar activiteit voor query's met een hoog CPU-verbruik. Deze query retourneert de top 15 CPU-verbruikende query's. Vergeet niet om het volgende te wijzigen rsi.start_time >= DATEADD(hour, -2, GETUTCDATE():

    -- Top 15 CPU consuming queries by query hash
    -- Note that a query hash can have many query ids if not parameterized or not parameterized properly
    WITH AggregatedCPU
    AS (
        SELECT q.query_hash
            ,SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms
            ,SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms
            ,MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms
            ,MAX(max_logical_io_reads) 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
            ,MIN(qt.query_sql_text) AS sampled_query_text
        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 q.query_hash
        )
        ,OrderedCPU
    AS (
        SELECT query_hash
            ,total_cpu_ms
            ,avg_cpu_ms
            ,max_cpu_ms
            ,max_logical_reads
            ,number_of_distinct_plans
            ,number_of_distinct_query_ids
            ,total_executions
            ,Aborted_Execution_Count
            ,Regular_Execution_Count
            ,Exception_Execution_Count
            ,sampled_query_text
            ,ROW_NUMBER() OVER (
                ORDER BY total_cpu_ms DESC
                    ,query_hash ASC
                ) AS query_hash_row_number
        FROM AggregatedCPU
        )
    SELECT OD.query_hash
        ,OD.total_cpu_ms
        ,OD.avg_cpu_ms
        ,OD.max_cpu_ms
        ,OD.max_logical_reads
        ,OD.number_of_distinct_plans
        ,OD.number_of_distinct_query_ids
        ,OD.total_executions
        ,OD.Aborted_Execution_Count
        ,OD.Regular_Execution_Count
        ,OD.Exception_Execution_Count
        ,OD.sampled_query_text
        ,OD.query_hash_row_number
    FROM OrderedCPU AS OD
    WHERE OD.query_hash_row_number <= 15 --get top 15 rows by total_cpu_ms
    ORDER BY total_cpu_ms DESC;
    
  2. Zodra u de problematische query's hebt geïdentificeerd, is het tijd om deze query's af te stemmen om het CPU-gebruik te verminderen. Als u geen tijd hebt om de query's af te stemmen, kunt u er ook voor kiezen om de SLO van de database bij te werken om het probleem te omzeilen.

Zie Problemen met hoge CPU in Azure SQL Database vaststellen en oplossen voor meer informatie over het afhandelen van CPU-prestatieproblemen in Azure SQL Database.

I/O-prestatieproblemen identificeren

Bij het identificeren van prestatieproblemen met opslaginvoer/uitvoer (I/O), zijn de belangrijkste wachttypen die zijn gekoppeld aan I/O-problemen:

  • PAGEIOLATCH_*

    Voor I/O-problemen met gegevensbestanden (inclusief PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Als de naam van het wachttype IO bevat, verwijst deze naar een I/O-probleem. Als er geen IO in de wachtnaam van de paginavergrendeling staat, verwijst deze naar een ander type probleem (bijvoorbeeld tempdb conflicten).

  • WRITE_LOG

    Voor I/O-problemen met transactielogboeken.

Als het I/O-probleem zich op dit moment voordoet

Gebruik de sys.dm_exec_requests of sys.dm_os_waiting_tasks om de wait_type en wait_timete zien.

I/O-gebruik van gegevens en logboeken identificeren

Gebruik de volgende query om gegevens te identificeren en I/O-gebruik te registreren. Als de gegevens of logboek-I/O hoger zijn dan 80%, betekent dit dat gebruikers de beschikbare I/O voor de Azure SQL Database-servicelaag hebben gebruikt.

SELECT
    database_name = DB_NAME()
,   UTC_time = end_time
,   'CPU Utilization In % of Limit'           = rs.avg_cpu_percent
,   'Data IO In % of Limit'                   = rs.avg_data_io_percent
,   'Log Write Utilization In % of Limit'     = rs.avg_log_write_percent
,   'Memory Usage In % of Limit'              = rs.avg_memory_usage_percent 
,   'In-Memory OLTP Storage in % of Limit'    = rs.xtp_storage_percent
,   'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
,   'Concurrent Sessions in % of Limit'       = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs  --past hour only
ORDER BY  rs.end_time DESC;

Zie de sectie Resourcegebruik bewaken verderop in dit artikel voor meer voorbeeldensys.dm_db_resource_stats.

Als de I/O-limiet is bereikt, hebt u twee opties:

  • De rekengrootte of servicelaag upgraden
  • Identificeer en stem de query's af die de meeste I/O gebruiken.

Voor optie 2 kunt u de volgende query voor Query Store gebruiken voor buffergerelateerde I/O om de laatste twee uur bijgehouden activiteit weer te geven:

-- Top queries that waited on buffer
-- Note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_desc
                    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_wait_stats AS waits ON waits.plan_id=p.plan_id
                         INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
                    WHERE wait_category_desc='Buffer IO' AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
                    GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS query_hash_row_number
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.query_hash_row_number
FROM Ordered AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_wait_time_ms
ORDER BY total_wait_time_ms DESC;
GO
Totale logboek-I/O weergeven voor WRITELOG-wachttijden

Als het wachttype is WRITELOG, gebruikt u de volgende query om de totale logboek-I/O per instructie weer te geven:

-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
           SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms,
           MAX(max_logical_io_reads) 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,
           MIN(qt.query_sql_text) AS sampled_query_text
    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 q.query_hash),
     OrderedLogUsed
AS (SELECT query_hash,
           total_log_bytes_used,
           number_of_distinct_plans,
           number_of_distinct_query_ids,
           total_executions,
           Aborted_Execution_Count,
           Regular_Execution_Count,
           Exception_Execution_Count,
           sampled_query_text,
           ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS query_hash_row_number
    FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
       OD.number_of_distinct_plans,
       OD.number_of_distinct_query_ids,
       OD.total_executions,
       OD.Aborted_Execution_Count,
       OD.Regular_Execution_Count,
       OD.Exception_Execution_Count,
       OD.sampled_query_text,
       OD.query_hash_row_number
FROM OrderedLogUsed AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_log_bytes_used
ORDER BY total_log_bytes_used DESC;
GO

Tempdb-prestatieproblemen identificeren

Bij het identificeren van I/O-prestatieproblemen zijn de belangrijkste wachttypen die zijn gekoppeld aan tempdb problemen PAGELATCH_* (niet PAGEIOLATCH_*). PAGELATCH_* Wachttijden betekenen echter niet altijd dat u conflicten hebttempdb. Dit wachten kan ook betekenen dat er een conflict voor een gegevenspagina voor gebruikersobjecten is opgetreden vanwege gelijktijdige aanvragen voor dezelfde gegevenspagina. Als u nog meer conflicten wilt bevestigen tempdb , gebruikt u sys.dm_exec_requests om te bevestigen dat de wait_resource waarde begint met 2:x:y waar 2 de database-id is tempdb , x de bestands-id is en y de pagina-id is.

Voor tempdb conflicten is een algemene methode het verminderen of herschrijven van toepassingscode tempdbdie afhankelijk is van . Algemene tempdb gebruiksgebieden zijn onder andere:

  • Tijdelijke tabellen
  • Tabelvariabelen
  • Tabelwaardeparameters
  • Gebruik van versieopslag (gekoppeld aan langlopende transacties)
  • Query's met queryplannen die gebruikmaken van sorteringen, hash joins en spools

Zie tempdb in Azure SQL voor meer informatie.

Topquery's die gebruikmaken van tabelvariabelen en tijdelijke tabellen

Gebruik de volgende query om de belangrijkste query's te identificeren die gebruikmaken van tabelvariabelen en tijdelijke tabellen:

SELECT plan_handle, execution_count, query_plan
INTO #tmpPlan
FROM sys.dm_exec_query_stats
     CROSS APPLY sys.dm_exec_query_plan(plan_handle);
GO

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') AS 'Database'
, stmt.stmt_details.value('@Schema', 'varchar(max)') AS 'Schema'
, stmt.stmt_details.value('@Table', 'varchar(max)') AS 'table'
INTO #tmp2
FROM
    (SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p
        CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);
GO

SELECT t.plan_handle, [Database], [Schema], [table], execution_count
FROM
    (SELECT DISTINCT plan_handle, [Database], [Schema], [table]
     FROM #tmp2
     WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t
        INNER JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;
GO
DROP TABLE #tmpPlan
DROP TABLE #tmp2

Langlopende transacties identificeren

Gebruik de volgende query om langlopende transacties te identificeren. Langdurige transacties verhinderen het opschonen van permanente versieopslag (PVS). Zie Problemen met versneld databaseherstel oplossen voor meer informatie.

SELECT DB_NAME(dtr.database_id) 'database_name',
       sess.session_id,
       atr.name AS 'tran_name',
       atr.transaction_id,
       transaction_type,
       transaction_begin_time,
       database_transaction_begin_time, 
       transaction_state,
       is_user_transaction,
       sess.open_transaction_count,
       TRIM(REPLACE(
                REPLACE(
                            SUBSTRING(
                                        SUBSTRING(
                                                    txt.text,
                                                    (req.statement_start_offset / 2) + 1,
                                                    ((CASE req.statement_end_offset
                                                            WHEN -1 THEN
                                                                DATALENGTH(txt.text)
                                                            ELSE
                                                                req.statement_end_offset
                                                        END - req.statement_start_offset
                                                    ) / 2
                                                    ) + 1
                                                ),
                                        1,
                                        1000
                                    ),
                            CHAR(10),
                            ' '
                        ),
                CHAR(13),
                ' '
            )
            ) Running_stmt_text,
       recenttxt.text 'MostRecentSQLText'
FROM sys.dm_tran_active_transactions AS atr
    INNER JOIN sys.dm_tran_database_transactions AS dtr
        ON dtr.transaction_id = atr.transaction_id
    LEFT JOIN sys.dm_tran_session_transactions AS sess
        ON sess.transaction_id = atr.transaction_id
    LEFT JOIN sys.dm_exec_requests AS req
        ON req.session_id = sess.session_id
           AND req.transaction_id = sess.transaction_id
    LEFT JOIN sys.dm_exec_connections AS conn
        ON sess.session_id = conn.session_id
    OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt
    OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS recenttxt
WHERE atr.transaction_type != 2
      AND sess.session_id != @@spid
ORDER BY start_time ASC;

Problemen met wachttijden voor geheugentoekenning identificeren

Als uw belangrijkste wachttype is RESOURCE_SEMAPHORE en u geen probleem hebt met hoog CPU-gebruik, is er mogelijk een probleem met de geheugentoekenning.

Bepalen of een RESOURCE_SEMAPHORE wachten een topwacht is

Gebruik de volgende query om te bepalen of een RESOURCE_SEMAPHORE wachttijd een topwacht is. Indicatief zou ook een stijgende wachttijdsrang zijn van RESOURCE_SEMAPHORE in de recente geschiedenis. Zie Problemen met trage prestaties of onvoldoende geheugen oplossen die worden veroorzaakt door geheugentoelagen in SQL Server voor meer informatie over het oplossen van wachtproblemen met geheugentoelagen.

SELECT wait_type,
       SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
    INNER JOIN sys.dm_exec_sessions AS sess
        ON req.session_id = sess.session_id
WHERE is_user_process = 1
GROUP BY wait_type
ORDER BY SUM(wait_time) DESC;
Instructies voor hoog geheugengebruik identificeren

Als er onvoldoende geheugenfouten optreden in Azure SQL Database, raadpleegt u sys.dm_os_out_of_memory_events. Zie Problemen met onvoldoende geheugen oplossen met Azure SQL Database voor meer informatie.

Wijzig eerst het onderstaande script om relevante waarden van start_time en end_timebij te werken. Voer vervolgens de volgende query uit om instructies te identificeren die veel geheugen verbruiken:

SELECT IDENTITY(INT, 1, 1) rowId,
    CAST(query_plan AS XML) query_plan,
    p.query_id
INTO #tmp
FROM sys.query_store_plan AS p
    INNER JOIN sys.query_store_runtime_stats AS r
        ON p.plan_id = r.plan_id
    INNER JOIN sys.query_store_runtime_stats_interval AS i
        ON r.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE start_time > '2018-10-11 14:00:00.0000000'
      AND end_time < '2018-10-17 20:00:00.0000000';
GO
;WITH cte
AS (SELECT query_id,
        query_plan,
        m.c.value('@SerialDesiredMemory', 'INT') AS SerialDesiredMemory
    FROM #tmp AS t
        CROSS APPLY t.query_plan.nodes('//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]') AS m(c) )
SELECT TOP 50
    cte.query_id,
    t.query_sql_text,
    cte.query_plan,
    CAST(SerialDesiredMemory / 1024. AS DECIMAL(10, 2)) SerialDesiredMemory_MB
FROM cte
    INNER JOIN sys.query_store_query AS q
        ON cte.query_id = q.query_id
    INNER JOIN sys.query_store_query_text AS t
        ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;
De tien belangrijkste actieve geheugentoelagen identificeren

Gebruik de volgende query om de tien belangrijkste actieve geheugentoelagen te identificeren:

SELECT TOP 10
    CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime,
       r.session_id,
       r.blocking_session_id,
       r.cpu_time,
       r.total_elapsed_time,
       r.reads,
       r.writes,
       r.logical_reads,
       r.row_count,
       wait_time,
       wait_type,
       r.command,
       OBJECT_NAME(txt.objectid, txt.dbid) 'Object_Name',
       TRIM(REPLACE(REPLACE(SUBSTRING(SUBSTRING(TEXT, (r.statement_start_offset / 2) + 1, 
        (  (
            CASE r.statement_end_offset
                WHEN - 1
                    THEN DATALENGTH(TEXT)
                ELSE r.statement_end_offset
                END - r.statement_start_offset
            ) / 2
        ) + 1), 1, 1000), CHAR(10), ' '), CHAR(13), ' ')) AS stmt_text,
       mg.dop,                                               --Degree of parallelism
       mg.request_time,                                      --Date and time when this query requested the memory grant.
       mg.grant_time,                                        --NULL means memory has not been granted
       mg.requested_memory_kb / 1024.0 requested_memory_mb,  --Total requested amount of memory in megabytes
       mg.granted_memory_kb / 1024.0 AS granted_memory_mb,   --Total amount of memory actually granted in megabytes. NULL if not granted
       mg.required_memory_kb / 1024.0 AS required_memory_mb, --Minimum memory required to run this query in megabytes.
       max_used_memory_kb / 1024.0 AS max_used_memory_mb,
       mg.query_cost,                                        --Estimated query cost.
       mg.timeout_sec,                                       --Time-out in seconds before this query gives up the memory grant request.
       mg.resource_semaphore_id,                             --Non-unique ID of the resource semaphore on which this query is waiting.
       mg.wait_time_ms,                                      --Wait time in milliseconds. NULL if the memory is already granted.
       CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
           WHEN 1 THEN
               'Yes'
           WHEN 0 THEN
               'No'
           ELSE
               'Memory has been granted'
       END AS 'Next Candidate for Memory Grant',
       qp.query_plan
FROM sys.dm_exec_requests AS r
    INNER JOIN sys.dm_exec_query_memory_grants AS mg
        ON r.session_id = mg.session_id
           AND r.request_id = mg.request_id
    CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY mg.granted_memory_kb DESC;

Verbindingen bewaken

U kunt de weergave sys.dm_exec_connections gebruiken om informatie op te halen over de verbindingen die tot stand zijn gebracht met een specifieke database of elastische pool en de details van elke verbinding. Daarnaast is de weergave sys.dm_exec_sessions handig bij het ophalen van informatie over alle actieve gebruikersverbindingen en interne taken.

Huidige sessies weergeven

De volgende query haalt informatie op over de huidige verbinding. Als u alle sessies wilt weergeven, verwijdert u de WHERE component.

U ziet alle uitvoersessies op de database alleen als u de machtiging DATABASESTATUS WEERGEVEN hebt voor de database bij het uitvoeren van de sys.dm_exec_requests en sys.dm_exec_sessions weergaven. Anders ziet u alleen de huidige sessie.

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
    INNER JOIN sys.dm_exec_sessions AS s
        ON c.session_id = s.session_id
WHERE c.session_id = @@SPID; --Remove to view all sessions, if permissions allow

Resourcegebruik bewaken

U kunt het resourcegebruik van Azure SQL Database op queryniveau bewaken met behulp van SQL Database Query Performance Insight in Azure Portal of de Query Store.

U kunt ook het gebruik bewaken met behulp van deze weergaven:

sys.dm_db_resource_stats

U kunt de sys.dm_db_resource_stats weergave in elke database gebruiken. In sys.dm_db_resource_stats de weergave ziet u recente gegevens over resourcegebruik ten opzichte van de servicelaag. Gemiddelde percentages voor CPU, gegevens-I/O, schrijfbewerkingen in logboeken en geheugen worden elke 15 seconden geregistreerd en worden gedurende 1 uur bijgehouden.

Omdat deze weergave een gedetailleerder overzicht biedt van het gebruik van resources, gebruikt sys.dm_db_resource_stats u eerst voor een analyse van de huidige status of probleemoplossing. In deze query ziet u bijvoorbeeld het gemiddelde en maximale resourcegebruik voor de huidige database in het afgelopen uur:

SELECT
    Database_Name = DB_NAME(),
    tier_limit = COALESCE(rs.dtu_limit, cpu_limit), --DTU or vCore limit
    AVG(avg_cpu_percent) AS 'Average CPU use in percent',
    MAX(avg_cpu_percent) AS 'Maximum CPU use in percent',
    AVG(avg_data_io_percent) AS 'Average data IO in percent',
    MAX(avg_data_io_percent) AS 'Maximum data IO in percent',
    AVG(avg_log_write_percent) AS 'Average log write use in percent',
    MAX(avg_log_write_percent) AS 'Maximum log write use in percent',
    AVG(avg_memory_usage_percent) AS 'Average memory use in percent',
    MAX(avg_memory_usage_percent) AS 'Maximum memory use in percent'
FROM sys.dm_db_resource_stats AS rs --past hour only
GROUP BY rs.dtu_limit, rs.cpu_limit;  

Zie de voorbeelden in sys.dm_db_resource_stats voor andere query's.

sys.resource_stats

De sys.resource_stats weergave in de master database bevat aanvullende informatie waarmee u de prestaties van uw database kunt bewaken op de specifieke servicelaag en rekenkracht. De gegevens worden om de 5 minuten verzameld en worden ongeveer 14 dagen bewaard. Deze weergave is handig voor een langetermijnanalyse van de wijze waarop uw database gebruikmaakt van resources.

In de volgende grafiek ziet u het cpu-resourcegebruik voor een Premium-database met de P2-rekenkracht voor elk uur in een week. Deze grafiek begint op een maandag, toont vijf werkdagen en geeft vervolgens een weekend weer, wanneer er veel minder gebeurt in de toepassing.

A screenshot of a sample graph of database resource use.

Uit de gegevens heeft deze database momenteel een piekbelasting van meer dan 50 procent CPU-gebruik ten opzichte van de P2-rekenkracht (dinsdagochtend). Als CPU de dominante factor is in het resourceprofiel van de toepassing, kunt u besluiten dat P2 de juiste rekenkracht is om ervoor te zorgen dat de workload altijd past. Als u verwacht dat een toepassing na verloop van tijd groeit, is het een goed idee om een extra resourcebuffer te hebben, zodat de toepassing nooit de limiet op prestatieniveau bereikt. Als u de rekenkracht verhoogt, kunt u helpen bij het voorkomen van door de klant zichtbare fouten die kunnen optreden wanneer een database niet over voldoende vermogen beschikt om aanvragen effectief te verwerken, met name in latentiegevoelige omgevingen. Een voorbeeld is een database die ondersteuning biedt voor een toepassing die webpagina's schildert op basis van de resultaten van databaseaanroepen.

Andere toepassingstypen kunnen dezelfde grafiek anders interpreteren. Als een toepassing bijvoorbeeld elke dag loongegevens probeert te verwerken en dezelfde grafiek heeft, kan dit soort 'batchtaak'-model prima werken met een P1-rekenkracht. De P1-rekenkracht heeft 100 DTU's vergeleken met 200 DTU's bij de P2-rekenkracht. De P1-rekenkracht biedt de helft van de prestaties van de P2-rekenkracht. Dus 50 procent van het CPU-gebruik in P2 is gelijk aan 100 procent CPU-gebruik in P1. Als de toepassing geen time-outs heeft, maakt het mogelijk niet uit of het 2 uur of 2,5 uur duurt voordat een taak is voltooid, als deze vandaag wordt uitgevoerd. Een toepassing in deze categorie kan waarschijnlijk een P1-rekenkracht gebruiken. U kunt profiteren van het feit dat er perioden zijn gedurende de dag waarop het resourcegebruik lager is, zodat elke 'grote piek' later in de dag kan overlopen in een van de dalen. De P1-rekenkracht kan goed zijn voor dat soort toepassingen (en geld besparen), zolang de taken elke dag op tijd kunnen worden voltooid.

De database-engine toont verbruikte resourcegegevens voor elke actieve database in de sys.resource_stats weergave van de master database op elke server. De gegevens in de tabel worden geaggregeerd voor intervallen van vijf minuten. Met de servicelagen Basic, Standard en Premium kunnen de gegevens langer dan 5 minuten in de tabel worden weergegeven, zodat deze gegevens nuttiger zijn voor historische analyses in plaats van bijna realtime-analyse. Voer een query uit in de sys.resource_stats weergave om de recente geschiedenis van een database te bekijken en te controleren of de reservering die u hebt gekozen, de gewenste prestaties heeft geleverd wanneer dat nodig is.

Notitie

In Azure SQL Database moet u zijn verbonden met de master database om een query uit te voeren sys.resource_stats in de volgende voorbeelden.

In dit voorbeeld ziet u hoe de gegevens in deze weergave worden weergegeven:

SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'userdb1'
ORDER BY start_time DESC;

In het volgende voorbeeld ziet u verschillende manieren waarop u de sys.resource_stats catalogusweergave kunt gebruiken voor informatie over hoe uw database gebruikmaakt van resources:

  1. Als u wilt kijken naar het resourcegebruik van de afgelopen week voor de gebruikersdatabase userdb1, kunt u deze query uitvoeren, waarbij u uw eigen databasenaam vervangt:

    SELECT *
    FROM sys.resource_stats
    WHERE database_name = 'userdb1' 
        AND start_time > DATEADD(day, -7, GETDATE())
    ORDER BY start_time DESC;
    
  2. Als u wilt evalueren hoe goed uw workload past bij de rekenkracht, moet u inzoomen op elk aspect van de metrische resourcegegevens: CPU, leesbewerkingen, schrijfbewerkingen, aantal werkrollen en aantal sessies. Hier volgt een herziene query voor sys.resource_stats het rapporteren van de gemiddelde en maximumwaarden van deze metrische resourcegegevens voor elke servicelaag waarvoor de database is ingericht:

    SELECT rs.database_name
    ,    rs.sku
    ,    storage_mb                           = MAX(rs.Storage_in_megabytes)
    ,    'Average CPU Utilization In %'       = AVG(rs.avg_cpu_percent)            
    ,    'Maximum CPU Utilization In %'       = MAX(rs.avg_cpu_percent)            
    ,    'Average Data IO In %'               = AVG(rs.avg_data_io_percent)        
    ,    'Maximum Data IO In %'               = MAX(rs.avg_data_io_percent)        
    ,    'Average Log Write Utilization In %' = AVG(rs.avg_log_write_percent)           
    ,    'Maximum Log Write Utilization In %' = MAX(rs.avg_log_write_percent)           
    ,    'Average Requests In %'              = AVG(rs.max_worker_percent)    
    ,    'Maximum Requests In %'              = MAX(rs.max_worker_percent)    
    ,    'Average Sessions In %'              = AVG(rs.max_session_percent)    
    ,    'Maximum Sessions In %'              = MAX(rs.max_session_percent)    
    FROM sys.resource_stats AS rs
    WHERE rs.database_name = 'userdb1' 
    AND rs.start_time > DATEADD(day, -7, GETDATE())
    GROUP BY rs.database_name, rs.sku;
    
  3. Met deze informatie over de gemiddelde en maximumwaarden van elke metrische resource kunt u beoordelen hoe goed uw workload past in de rekengrootte die u hebt gekozen. Normaal gesproken geven gemiddelde waarden sys.resource_stats u een goede basislijn die u kunt gebruiken voor de doelgrootte. Het moet uw primaire meetstick zijn.

    • Voor DTU-aankoopmodeldatabases :

      U kunt bijvoorbeeld de Standard-servicelaag met S2-rekenkracht gebruiken. Het gemiddelde gebruikspercentage voor CPU- en I/O-lees- en schrijfbewerkingen is lager dan 40 procent, het gemiddelde aantal werkrollen is lager dan 50 en het gemiddelde aantal sessies is lager dan 200. Uw workload kan in de S1-rekenkracht passen. U kunt eenvoudig zien of uw database past in de werkrol- en sessielimieten. Als u wilt zien of een database in een lagere rekenkracht past, deelt u het DTU-nummer van de lagere rekenkracht door het DTU-getal van uw huidige rekenkracht en vermenigvuldigt u het resultaat met 100:

      S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40

      Het resultaat is het relatieve prestatieverschil tussen de twee rekengrootten in percentage. Als uw resourcegebruik deze hoeveelheid niet overschrijdt, kan uw workload in de lagere rekenkracht passen. U moet echter alle bereiken met resourcegebruikswaarden bekijken en bepalen hoe vaak uw databaseworkload in de lagere rekenkracht past. Met de volgende query wordt het passend percentage per resourcedimensie uitgevoerd op basis van de drempelwaarde van 40 procent die we in dit voorbeeld hebben berekend:

       SELECT database_name,
           100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent'
       FROM sys.resource_stats
       WHERE start_time > DATEADD(day, -7, GETDATE())
       AND database_name = 'sample' --remove to see all databases
       GROUP BY database_name;
      

      Op basis van uw databaseservicelaag kunt u bepalen of uw workload in de lagere rekenkracht past. Als uw databaseworkloaddoelstelling 99,9 procent is en de voorgaande query waarden retourneert die groter zijn dan 99,9 procent voor alle drie de resourcedimensies, past uw workload waarschijnlijk in de lagere rekenkracht.

      Als u het passend percentage bekijkt, krijgt u ook inzicht in of u naar de volgende hogere rekenkracht moet gaan om aan uw doelstelling te voldoen. Bijvoorbeeld het CPU-gebruik voor een voorbeelddatabase in de afgelopen week:

      Gemiddeld CPU-percentage Maximum CPU-percentage
      24.5 100.00

      De gemiddelde CPU is ongeveer een kwart van de limiet van de rekenkracht, die goed in de rekenkracht van de database past.

    • Voor DTU-aankoopmodel - en vCore-aankoopmodeldatabases :

      De maximumwaarde laat zien dat de database de limiet van de rekenkracht bereikt. Moet u naar de volgende hogere rekenkracht gaan? Bekijk hoe vaak uw workload 100 procent bereikt en vergelijk deze vervolgens met de doelstelling van uw databaseworkload.

       SELECT database_name,
           100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent'
       FROM sys.resource_stats
       WHERE start_time > DATEADD(day, -7, GETDATE())
       AND database_name = 'sample'  --remove to see all databases
       GROUP BY database_name;
      

      Deze percentages zijn het aantal steekproeven dat uw workload onder de huidige rekenkracht past. Als deze query een waarde retourneert die kleiner is dan 99,9 procent voor een van de drie resourcedimensies, heeft uw steekproefgemiddelde gemiddelde workload de limieten overschreden. Overweeg om over te stappen op de volgende hogere rekenkracht of gebruik technieken voor het afstemmen van toepassingen om de belasting van de database te verminderen.

    Notitie

    Voor elastische pools kunt u afzonderlijke databases in de pool bewaken met de technieken die in deze sectie zijn beschreven. U kunt de pool ook als geheel bewaken. Zie Een elastische pool bewaken en beheren voor meer informatie.

Maximum aantal gelijktijdige aanvragen

Als u het huidige aantal gelijktijdige aanvragen wilt zien, voert u deze query uit op uw gebruikersdatabase:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R;

Als u de workload van een database wilt analyseren, wijzigt u deze query om te filteren op de specifieke database die u wilt analyseren. Werk eerst de naam van de database bij naar MyDatabase de gewenste database en voer vervolgens de volgende query uit om het aantal gelijktijdige aanvragen in die database te vinden:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R
    INNER JOIN sys.databases AS D 
        ON D.database_id = R.database_id
AND D.name = 'MyDatabase';

Dit is slechts een momentopname op een bepaald moment. Als u meer inzicht wilt krijgen in uw workload en gelijktijdige aanvraagvereisten, moet u in de loop van de tijd veel voorbeelden verzamelen.

Maximum aantal gelijktijdige aanmeldingsevenementen

U kunt uw gebruikers- en toepassingspatronen analyseren om een idee te krijgen van de frequentie van aanmeldingsevenementen. U kunt ook echte laadbewerkingen uitvoeren in een testomgeving om ervoor te zorgen dat u niet aan deze of andere limieten komt die in dit artikel worden besproken. Er is geen enkele query of dynamische beheerweergave (DMV) waarmee u gelijktijdige aanmeldingsaantallen of geschiedenis kunt weergeven.

Als meerdere clients dezelfde verbindingsreeks gebruiken, verifieert de service elke aanmelding. Als 10 gebruikers tegelijkertijd verbinding maken met een database met dezelfde gebruikersnaam en hetzelfde wachtwoord, zijn er 10 gelijktijdige aanmeldingen. Deze limiet geldt alleen voor de duur van de aanmelding en verificatie. Als dezelfde 10 gebruikers opeenvolgend verbinding maken met de database, is het aantal gelijktijdige aanmeldingen nooit groter dan 1.

Notitie

Deze limiet is momenteel niet van toepassing op databases in elastische pools.

Maximum aantal sessies

Als u het aantal huidige actieve sessies wilt zien, voert u deze query uit op uw database:

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;

Als u een SQL Server-workload analyseert, wijzigt u de query zodat deze zich op een specifieke database richt. Deze query helpt u bij het bepalen van de mogelijke sessiebehoeften voor de database als u overweegt deze naar Azure te verplaatsen. Werk eerst de naam van de database bij naar MyDatabase de gewenste database en voer vervolgens de volgende query uit:

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections AS C
    INNER JOIN sys.dm_exec_sessions AS S 
        ON (S.session_id = C.session_id)
    INNER JOIN sys.databases AS D 
        ON (D.database_id = S.database_id)
WHERE D.name = 'MyDatabase';

Opnieuw retourneren deze query's een aantal punten in de tijd. Als u in de loop van de tijd meerdere voorbeelden verzamelt, hebt u het beste inzicht in uw sessiegebruik.

U kunt historische statistieken over sessies ophalen door een query uit te voeren op de sys.resource_stats catalogusweergave en de active_session_count kolom te bekijken.

Grootten van databases en objecten berekenen

Met de volgende query wordt de grootte van uw database geretourneerd (in megabytes):

-- Calculates the size of the database.
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Met de volgende query wordt de grootte van afzonderlijke objecten geretourneerd (in megabytes):

-- Calculates the size of individual database objects.
SELECT o.name, SUM(ps.reserved_page_count) * 8.0 / 1024 AS size_mb
FROM sys.dm_db_partition_stats AS ps 
    INNER JOIN sys.objects AS o 
        ON ps.object_id = o.object_id
GROUP BY o.name
ORDER BY size_mb DESC;

Queryprestaties bewaken

Trage of langdurige query's kunnen aanzienlijke systeembronnen verbruiken. In deze sectie ziet u hoe u dynamische beheerweergaven gebruikt om enkele veelvoorkomende problemen met queryprestaties te detecteren met behulp van de sys.dm_exec_query_stats dynamische beheerweergave. De weergave bevat één rij per query-instructie in het cacheplan en de levensduur van de rijen is gekoppeld aan het plan zelf. Wanneer een plan uit de cache wordt verwijderd, worden de bijbehorende rijen uit deze weergave verwijderd.

Topquery's zoeken op CPU-tijd

Het volgende voorbeeld retourneert informatie over de top 15 query's gerangschikt op gemiddelde CPU-tijd per uitvoering. In dit voorbeeld worden de query's samengevoegd op basis van hun query-hash, zodat logisch equivalente query's worden gegroepeerd op basis van het cumulatieve resourceverbruik.

SELECT TOP 15 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
     MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
        SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                WHEN -1 THEN DATALENGTH(ST.text)
                ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
    ) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
Queryplannen bewaken voor cumulatieve CPU-tijd

Een inefficiënt queryplan kan ook het CPU-verbruik verhogen. In het volgende voorbeeld wordt bepaald welke query gebruikmaakt van de meest cumulatieve CPU in de recente geschiedenis.

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
FROM
    (SELECT TOP 15
            qs.plan_handle,
            qs.total_worker_time
        FROM
            sys.dm_exec_query_stats AS qs
        ORDER BY qs.total_worker_time desc
    ) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;

Geblokkeerde query's bewaken

Trage of langlopende query's kunnen bijdragen aan overmatig resourceverbruik en kunnen het gevolg zijn van geblokkeerde query's. De oorzaak van de blokkering kan een slecht toepassingsontwerp zijn, slechte queryplannen, het ontbreken van nuttige indexen, enzovoort.

U kunt de sys.dm_tran_locks weergave gebruiken om informatie op te halen over de huidige vergrendelingsactiviteit in de database. Zie bijvoorbeeld sys.dm_tran_locks. Zie Azure SQL-blokkeringsproblemen begrijpen en oplossen voor meer informatie over het oplossen van blokkeringsproblemen.

Impasses bewaken

In sommige gevallen kunnen twee of meer query's elkaar blokkeren, wat resulteert in een impasse.

U kunt een uitgebreide gebeurtenissen maken om een database in Azure SQL Database te traceren om impasse-gebeurtenissen vast te leggen en vervolgens gerelateerde query's en hun uitvoeringsplannen te vinden in Query Store. Meer informatie in Analyseren en impasses voorkomen in Azure SQL Database, waaronder een lab om een impasse in AdventureWorksLT te veroorzaken. Meer informatie over de typen resources die kunnen vastlopen.

Volgende stappen