Prestaties bewaken met DMV's - Azure SQL Database (2023)

  • Artikel
  • 23 minuten om te lezen

Van toepassing op:Prestaties bewaken met DMV's - Azure SQL Database (1) Azure SQL Database-Azure SQL Managed Instance Prestaties bewaken met DMV's - Azure SQL Database (2)

  • Azure SQL Database
  • Azure SQL Managed Instance

Microsoft Azure SQL Database maakt een subset van dynamische beheerweergaven mogelijk om prestatieproblemen te diagnosticeren, die kunnen worden veroorzaakt door geblokkeerde of langlopende query's, resourceknelpunten, slechte queryplannen, enzovoort. Dit artikel bevat informatie over het detecteren van veelvoorkomende prestatieproblemen met behulp van dynamische beheerweergaven.

Dit artikel gaat over Azure SQL Database, zie ook Microsoft Azure SQL Managed Instance prestaties bewaken met behulp van dynamische beheerweergaven.

Machtigingen

In Azure SQL Database, afhankelijk van de rekengrootte en implementatieoptie, is voor het uitvoeren van een query op een DMV mogelijk de machtiging VIEW DATABASE STATE of VIEW SERVER STATE 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 langer dan 80% is, kunt u de volgende stappen voor probleemoplossing overwegen:

Het CPU-probleem is nu opgetreden

Als er momenteel een probleem optreedt, zijn er twee mogelijke scenario's:

Veel afzonderlijke query's die cumulatief hoge CPU verbruiken

Gebruik de volgende query om de belangrijkste queryhashes 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 tORDER 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_textFROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS STORDER BY cpu_time DESC;GO

Het CPU-probleem is in het verleden opgetreden

Als het probleem in het verleden is opgetreden 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. De standaardconfiguraties van Query Store gebruiken een granulariteit van 1 uur. 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 id if not parameterized or not parameterized properly-- it grabs a sample query text by minWITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, 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 JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id=p.query_id JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id 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_millisec, avg_cpu_millisec, max_cpu_millisec, 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_millisec DESC, query_hash ASC) AS RN FROM AggregatedCPU)SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, 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.RNFROM OrderedCPU AS ODWHERE OD.RN<=15ORDER BY total_cpu_millisec DESC;

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.

Voor meer informatie over het verwerken van CPU-prestatieproblemen in Azure SQL Database, raadpleegt u Diagnose en probleemoplossing voor hoge CPU op Azure SQL Database.

IO-prestatieproblemen identificeren

Bij het identificeren van IO-prestatieproblemen zijn de belangrijkste wachttypen die verband houden met IO-problemen:

Als het IO-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.

Io-gebruik van gegevens identificeren en registreren

Gebruik de volgende query om het io-gebruik van gegevens te identificeren en te registreren. Als de gegevens of logboek-IO hoger is dan 80%, betekent dit dat gebruikers de beschikbare IO hebben gebruikt voor de servicelaag Azure SQL Database.

SELECT end_time, avg_data_io_percent, avg_log_write_percentFROM sys.dm_db_resource_statsORDER BY end_time DESC;

Als de IO-limiet is bereikt, hebt u twee opties:

  • Optie 1: De rekengrootte of servicelaag upgraden
  • Optie 2: De query's identificeren en afstemmen die de meeste IO verbruiken.

Buffergerelateerde IO weergeven met behulp van de Query Store

Voor optie 2 kunt u de volgende query gebruiken voor Query Store voor io die betrekking heeft op buffers om de afgelopen twee uur bijgehouden activiteit weer te geven:

-- top queries that waited on buffer-- note these are finished queriesWITH 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 JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id=p.query_id JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id 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 RN FROM Aggregated)SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RNFROM Ordered AS ODWHERE OD.RN<=15ORDER BY total_wait_time_ms DESC;GO

Totaal aantal IO-logboeken weergeven voor WRITELOG-wachttijden

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

-- Top transaction log consumers-- Adjust the time window by changing-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())WITH AggregatedLogUsedAS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec, SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, 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 JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id 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), OrderedLogUsedAS (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 RN 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.RNFROM OrderedLogUsed AS ODWHERE OD.RN <= 15ORDER BY total_log_bytes_used DESC;GO

Prestatieproblemen identificeren tempdb

Bij het identificeren van IO-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
  • Versiearchiefgebruik (gekoppeld aan langlopende transacties)
  • Query's met queryplannen die gebruikmaken van sorteringen, hash joins en spools

Belangrijkste query'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_planINTO #tmpPlanFROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle);GOWITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') 'Database', stmt.stmt_details.value('@Schema', 'varchar(max)') 'Schema', stmt.stmt_details.value('@Table', 'varchar(max)') 'table'INTO #tmp2FROM(SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);GOSELECT t.plan_handle, [Database], [Schema], [table], execution_countFROM(SELECT DISTINCT plan_handle, [Database], [Schema], [table] FROM #tmp2 WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;

Langlopende transacties identificeren

Gebruik de volgende query om langlopende transacties te identificeren. Langlopende transacties voorkomen dat versieopslag wordt opgeschoond.

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 recenttxtWHERE atr.transaction_type != 2 AND sess.session_id != @@spidORDER 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, hebt u mogelijk een probleem met wachtende geheugentoekenning.

Bepalen of een RESOURCE_SEMAPHORE wachttijd een topwacht is

Gebruik de volgende query om te bepalen of een RESOURCE_SEMAPHORE wachttijd een topwacht is

SELECT wait_type, SUM(wait_time) AS total_wait_time_msFROM sys.dm_exec_requests AS req JOIN sys.dm_exec_sessions AS sess ON req.session_id = sess.session_idWHERE is_user_process = 1GROUP BY wait_typeORDER 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.

Gebruik de volgende query om instructies te identificeren die veel geheugen verbruiken:

SELECT IDENTITY(INT, 1, 1) rowId, CAST(query_plan AS XML) query_plan, p.query_idINTO #tmpFROM sys.query_store_plan AS p JOIN sys.query_store_runtime_stats AS r ON p.plan_id = r.plan_id JOIN sys.query_store_runtime_stats_interval AS i ON r.runtime_stats_interval_id = i.runtime_stats_interval_idWHERE start_time > '2018-10-11 14:00:00.0000000' AND end_time < '2018-10-17 20:00:00.0000000';GO;WITH cteAS (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_MBFROM cte JOIN sys.query_store_query AS q ON cte.query_id = q.query_id JOIN sys.query_store_query_text AS t ON q.query_text_id = t.query_text_idORDER BY SerialDesiredMemory DESC;

De 10 belangrijkste actieve geheugentoelagen identificeren

Gebruik de volgende query om de 10 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), ' ' ) ) 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_planFROM sys.dm_exec_requests AS r 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 qpORDER BY mg.granted_memory_kb DESC;

De grootte van de database 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 DatabaseSizeInMBFROM sys.database_filesWHERE type_desc = 'ROWS';GO

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

-- Calculates the size of individual database objects.SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024FROM sys.dm_db_partition_stats, sys.objectsWHERE sys.dm_db_partition_stats.object_id = sys.objects.object_idGROUP BY sys.objects.name;GO

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 sys.dm_exec_sessions weergave handig bij het ophalen van informatie over alle actieve gebruikersverbindingen en interne taken.

Met de volgende query wordt informatie over de huidige verbinding opgehaald:

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_timeFROM sys.dm_exec_connections AS cJOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_idWHERE c.session_id = @@SPID;

Notitie

Wanneer u de sys.dm_exec_requestssys.dm_exec_sessions viewsmachtiging DATABASESTATUS WEERGEVEN voor de database uitvoert, ziet u alle uitvoeringssessies in de database. Anders ziet u alleen de huidige sessie.

Resourcegebruik bewaken

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

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

  • sys.dm_db_resource_stats
  • sys.resource_stats

sys.dm_db_resource_stats

U kunt de sys.dm_db_resource_stats weergave in elke database gebruiken. De sys.dm_db_resource_stats weergave toont recente resourcegebruiksgegevens ten opzichte van de servicelaag. Gemiddelde percentages voor CPU, gegevens-IO, schrijfbewerkingen in logboeken en geheugen worden elke 15 seconden vastgelegd en worden gedurende 1 uur bewaard.

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

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

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 de rekengrootte. De gegevens worden elke 5 minuten verzameld en worden ongeveer 14 dagen bewaard. Deze weergave is handig voor een historische analyse op langere termijn van de wijze waarop uw database resources gebruikt.

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

Prestaties bewaken met DMV's - Azure SQL Database (3)

Uit de gegevens heeft deze database momenteel een piek-CPU-belasting van iets meer dan 50 procent CPU-gebruik ten opzichte van de P2-rekenkracht (middag op dinsdag). 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 zich kunnen voordoen wanneer een database niet voldoende vermogen heeft 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 salarisgegevens 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 wanneer resourcegebruik lager is, zodat elke 'grote piek' later op 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 kunnen worden voltooid.

De database-engine bevat 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 5 minuten. Met de servicelagen Basic, Standard en Premium kunnen de gegevens meer dan 5 minuten in de tabel worden weergegeven, zodat deze gegevens nuttiger zijn voor historische analyse in plaats van in bijna realtime-analyse. Voer een query uit in de sys.resource_stats weergave om de recente geschiedenis van een database te bekijken en om 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_statsWHERE database_name = 'resource1'ORDER BY start_time DESC;

Prestaties bewaken met DMV's - Azure SQL Database (4)

In het volgende voorbeeld ziet u verschillende manieren waarop u de sys.resource_stats catalogusweergave kunt gebruiken om informatie te krijgen over de manier waarop uw database resources gebruikt:

  1. Als u wilt kijken naar het resourcegebruik van de afgelopen week voor de database userdb1, kunt u deze query uitvoeren:

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

    SELECT 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 physical data IO use in percent', max(avg_data_io_percent) AS 'Maximum physical data IO use 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(max_session_percent) AS 'Average % of sessions', max(max_session_percent) AS 'Maximum % of sessions', avg(max_worker_percent) AS 'Average % of workers', max(max_worker_percent) AS 'Maximum % of workers'FROM sys.resource_statsWHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE());
  3. Met deze informatie over de gemiddelde en maximumwaarden van elke resourcemetrie kunt u beoordelen hoe goed uw workload past in de rekengrootte die u hebt gekozen. Meestal geven gemiddelde waarden u sys.resource_stats een goede basislijn om te gebruiken op basis van de doelgrootte. Het moet uw primaire meetstok zijn. U kunt bijvoorbeeld de Standard-servicelaag gebruiken met S2-rekenkracht. De gemiddelde gebruikspercentages voor LEES- en I/O-lees- en schrijfbewerkingen liggen onder de 40 procent, het gemiddelde aantal werknemers is lager dan 50 en het gemiddelde aantal sessies is lager dan 200. Uw workload past mogelijk in de rekengrootte S1. 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 met betrekking tot CPU, lees- en schrijfbewerkingen, deelt u het DTU-nummer van de lagere rekenkracht op basis van het DTU-getal van uw huidige rekenkracht en vermenigvuldigt u het resultaat vervolgens 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, past uw workload mogelijk in de lagere rekenkracht. U moet echter alle bereiken met waarden voor resourcegebruik 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 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 database_name = 'sample' AND start_time > DATEADD(day, -7, GETDATE());

    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-percentageMaximum CPU-percentage
    24.5100,00

    De gemiddelde CPU is ongeveer een kwart van de limiet van de rekengrootte, die goed in de rekenkracht van de database past. De maximumwaarde laat echter zien dat de database de limiet van de rekengrootte bereikt. Moet u naar de volgende hogere rekenkracht gaan? Bekijk hoe vaak uw workload 100 procent bereikt en vergelijk deze vervolgens met uw databaseworkloaddoelstelling.

     SELECT 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 database_name = 'sample' AND start_time > DATEADD(day, -7, GETDATE());

    Als deze query een waarde retourneert die kleiner is dan 99,9 procent voor een van de drie resourcedimensies, kunt u overwegen om over te stappen op de volgende hogere rekenkracht of door toepassingsafstemmingstechnieken te gebruiken om de belasting van de database te verminderen.

  4. In deze oefening wordt ook rekening gebracht met de toename van uw verwachte workload in de toekomst.

Voor elastische pools kunt u afzonderlijke databases in de pool bewaken met de technieken die in deze sectie zijn beschreven. U kunt echter ook de groep 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 Transact-SQL-query uit op uw database:

SELECT COUNT(*) AS [Concurrent_Requests]FROM sys.dm_exec_requests 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. Als u bijvoorbeeld een database met de naam MyDatabasehebt, retourneert deze Transact-SQL-query het aantal gelijktijdige aanvragen in die database:

SELECT COUNT(*) AS [Concurrent_Requests]FROM sys.dm_exec_requests RINNER JOIN sys.databases D ON D.database_id = R.database_idAND 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 aanmeldingen

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

Als meerdere clients dezelfde connection string 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 is alleen van toepassing op de duur van de aanmelding en verificatie. Als dezelfde tien 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 Transact-SQL-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. Met deze query kunt u de mogelijke sessiebehoeften voor de database bepalen als u deze naar Azure wilt verplaatsen.

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

Nogmaals, deze query's retourneren 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 weergave en de active_session_count kolom te bekijken.

Queryprestaties bewaken

Trage of langlopende query's kunnen aanzienlijke systeembronnen verbruiken. In deze sectie ziet u hoe u dynamische beheerweergaven gebruikt om enkele veelvoorkomende problemen met queryprestaties te detecteren.

Belangrijkste N-query's zoeken

In het volgende voorbeeld wordt informatie geretourneerd over de vijf belangrijkste query's die zijn gerangschikt op gemiddelde CPU-tijd. 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 5 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_textFROM sys.dm_exec_query_stats AS QSCROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_statsGROUP BY query_stats.query_hashORDER BY 2 DESC;

Geblokkeerde query's bewaken

Trage of langlopende query's kunnen bijdragen aan overmatig resourceverbruik en zijn het gevolg van geblokkeerde query's. De oorzaak van de blokkering kan slecht toepassingsontwerp zijn, slechte queryplannen, het ontbreken van nuttige indexen, enzovoort. U kunt de weergave sys.dm_tran_locks gebruiken om informatie te krijgen over de huidige vergrendelingsactiviteit in de database. Zie bijvoorbeeld sys.dm_tran_locks. Zie Begrijpen en oplossen Azure SQL blokkeringsproblemen voor meer informatie over het oplossen van problemen.

Impasses bewaken

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

U kunt een database met uitgebreide gebeurtenissen maken in Azure SQL Database om impasse-gebeurtenissen vast te leggen en vervolgens gerelateerde query's en hun uitvoeringsplannen te vinden in Query Store. Meer informatie over analyseren en voorkomen van impasses in Azure SQL Database.

Queryplannen bewaken

Een inefficiënt queryplan kan ook het CPU-verbruik verhogen. In het volgende voorbeeld wordt de weergave sys.dm_exec_query_stats gebruikt om te bepalen welke query gebruikmaakt van de meest cumulatieve CPU.

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 50 qs.plan_handle, qs.total_worker_time FROM sys.dm_exec_query_stats qsORDER BY qs.total_worker_time desc) AS highest_cpu_queriesCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qORDER BY highest_cpu_queries.total_worker_time DESC;

Zie ook

  • Dynamische beheerweergaven en -functies (Transact-SQL)
  • Systeem dynamische beheerweergaven

Volgende stappen

  • Inleiding tot Azure SQL Database en Azure SQL Managed Instance
  • Problemen met hoog CPU-gebruik in Azure SQL Database vaststellen en oplossen
  • Toepassingen en databases afstemmen op prestaties in Azure SQL Database en Azure SQL Managed Instance
  • Problemen met Azure SQL databaseblokkering begrijpen en oplossen
  • Impasses in Azure SQL Database analyseren en voorkomen
Top Articles
Latest Posts
Article information

Author: Dong Thiel

Last Updated: 03/03/2023

Views: 5896

Rating: 4.9 / 5 (59 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Dong Thiel

Birthday: 2001-07-14

Address: 2865 Kasha Unions, West Corrinne, AK 05708-1071

Phone: +3512198379449

Job: Design Planner

Hobby: Graffiti, Foreign language learning, Gambling, Metalworking, Rowing, Sculling, Sewing

Introduction: My name is Dong Thiel, I am a brainy, happy, tasty, lively, splendid, talented, cooperative person who loves writing and wants to share my knowledge and understanding with you.