Delen via

Bewakingsgegevens van database-watcher analyseren (preview)

  • Artikel

van toepassing op:Azure SQL DatabaseAzure SQL Managed Instance-

Naast het gebruik van dashboards in Azure Portal, of het bouwen van visualisaties om SQL-bewakingsgegevens in Power BI-, Grafana-, Azure Data Explorer- of Real-Time Analytics in Microsoft Fabricte bekijken en te analyseren, kunt u rechtstreeks een query uitvoeren op het bewakingsgegevensarchief.

Dit artikel bevat voorbeelden van KQL- en T-SQL--query's waarmee u aan de slag kunt met het analyseren van verzamelde bewakingsgegevens.

KQL gebruiken om bewakingsgegevens te analyseren

Voor het analyseren van verzamelde bewakingsgegevens is de aanbevolen methode om de Kusto Query Language (KQL) te gebruiken. KQL is optimaal voor het uitvoeren van query's op telemetrie, metrische gegevens en logboeken. Het biedt uitgebreide ondersteuning voor tekst zoeken en parseren, tijdreeksoperators en functies, analyse en aggregatie, en vele andere taalconstructies die gegevensanalyse mogelijk maken.

KQL is conceptueel vergelijkbaar met SQL. Het werkt op schema-entiteiten, zoals tabellen en kolommen, en ondersteunt relationele bewerkingen zoals project, beperken, samenvoegen en samenvatten, die overeenkomen met de SELECT, JOIN, WHEREen GROUP BY-componenten in SQL.

Als u KQL-query's wilt schrijven en uitvoeren, kunt u Kusto Explorer- of de Azure Data Explorerwebgebruikersinterface gebruiken. Kusto Explorer is een volledig functionele Windows-bureaubladsoftware, terwijl u met de webinterface van Azure Data Explorer KQL-query's kunt uitvoeren en resultaten kunt visualiseren in de browser op elk platform.

U kunt deze hulpprogramma's ook gebruiken om een query uit te voeren op een database in Real-Time Analytics in Microsoft Fabric. Als u verbinding wilt maken, voegt u een nieuwe verbinding toe met behulp van de query-URI- van uw Real-Time Analytics-database. Als u Real-Time Analytics gebruikt, kunt u bovendien bewakingsgegevens analyseren met behulp van KQL-querysets. Een KQL-queryset kan worden opgeslagen als een deelbaar Fabric-artefact en wordt gebruikt om Power BI-rapporten te maken.

Als u niet bekend bent met KQL, kunt u aan de slag met de volgende bronnen:

Met de volgende voorbeelden kunt u uw eigen KQL-query's schrijven om verzamelde SQL-bewakingsgegevens weer te geven en te analyseren. U kunt deze voorbeelden ook gebruiken als uitgangspunt bij het bouwen van uw eigen gegevensvisualisaties en dashboards.

KQL gebruiken om een query uit te voeren op resourceverbruik in de loop van de tijd

In dit voorbeeld retourneert de query metrische gegevens over resourceverbruik (CPU, werkrollen, schrijfdoorvoer voor logboeken, enzovoort) voor de primaire replica van een database, een elastische pool of een beheerd SQL-exemplaar gedurende het afgelopen uur. Naast het retourneren van de resultatenset, wordt deze gevisualiseerd als een tijddiagram.

In deze en andere voorbeelden moet je de variabelen in de instructies wijzigen zodat ze overeenkomen met de namen van je server, database, elastische pool of SQL Managed Instance. Als u een ander tijdsinterval wilt gebruiken, wijzigt u de duration variabele. Zie letterlijke tijdsperiodevoor meer informatie.

let logicalServer = @"your-server-name";
let databaseName = @"your-database-name";
let replicaType = "Primary";
let duration = 1h;
sqldb_database_resource_utilization
| where sample_time_utc > ago(duration)
| where logical_server_name =~ logicalServer
| where database_name =~ databaseName
| where replica_type =~ replicaType
| project sample_time_utc,
          avg_cpu_percent,
          avg_instance_cpu_percent,
          avg_data_io_percent,
          avg_log_write_percent,
          max_worker_percent
| sort by sample_time_utc desc
| render timechart;

KQL gebruiken om eigenschappen van databases, elastische pools of sql managed instance weer te geven

In dit voorbeeld retourneert de query een set van alle databases, elastische pools of SQL managed instances waarvan ten minste één voorbeeld uit de bijbehorende Eigenschappengegevensset is verzameld in de afgelopen dag. Met andere woorden, elke rij vertegenwoordigt een bewakingsdoel met de meest recent waargenomen eigenschappen.

De functie arg_max() voegt gegevens samen om de meest recente rij te retourneren voor de opgegeven set kolommen waarmee een doel wordt geïdentificeerd. Voor Azure SQL-databases is deze set bijvoorbeeld logical_server_name, database_name, replica_type.

let duration = 1d;
sqldb_database_properties
| where sample_time_utc > ago(duration)
| summarize arg_max(sample_time_utc, *) by logical_server_name, database_name, replica_type
| project-rename last_sample_time_utc = sample_time_utc
| sort by tolower(logical_server_name) asc,
          tolower(database_name) asc,
          case(
              replica_type == "Primary", 0,
              replica_type == "Geo-replication forwarder", 1,
              replica_type == "Named secondary", 2,
              replica_type == "HA secondary", 3,
              4) asc;

KQL gebruiken om queryruntimestatistieken weer te geven

Deze query retourneert de belangrijkste resourceverbruikende query's in uw Azure SQL-estate. Wijzig een variabele om query's te rangschikken op basis van een Query Store metrische gegevens, waaronder CPU-tijd, verstreken tijd, uitvoeringsaantal, enzovoort. U kunt ook variabelen instellen om te filteren op een tijdsinterval, queryuitvoeringstype en querytekst. Stel variabelen in om te focussen op een specifieke logische server, elastische pool, SQL Managed Instance of database.

De query maakt gebruik van de query runtime statistiekendataset om het aantal topquery's te retourneren dat u opgeeft, en omvat hun rangschikking op basis van alle andere meetgegevens voor hulpbronnenverbruik.

let topQueriesBy = "cpu_time"; // Set to one of the following metrics to return the top resource consuming queries:
// count_executions, duration, cpu_time, logical_io_reads, logical_io_writes, physical_io_reads, 
// num_physical_io_reads, clr_time, dop, query_max_used_memory, rowcount, log_bytes_used, tempdb_space_used 
let topQueries = 10; // Set the number of top queries to return
let endTime = now();
let startTime = endTime - 1d;
let logicalServerName = @""; // Optionally filter by logical server name
let elasticPoolName = @""; // Optionally filter by elastic pool name, if any databases are in elastic pools
let databaseName = @""; // Optionally filter by database name
let executionType = ""; // Optionally filter by execution type. Use Regular, Aborted, Exception.
let queryHash = ""; // Optionally filter by query hash (example: 0xBAAA461A6C93EA88)
let queryTextFragment = ""; // Optionally filter by a query text fragment
sqldb_database_query_runtime_stats
| where interval_start_time >= startTime and interval_end_time <= endTime
| where isempty(executionType) or execution_type_desc =~ executionType
| where isempty(logicalServerName) or logical_server_name =~ logicalServerName
| where isempty(elasticPoolName) or elastic_pool_name =~ elasticPoolName
| where isempty(databaseName) or database_name =~ databaseName
| summarize dcount_logical_servers = dcount(logical_server_name),
            any_logical_server_name = take_any(logical_server_name),
            dcount_elastic_pools = dcount(strcat(logical_server_name, "|", elastic_pool_name)),
            any_elastic_pool_name = take_any(elastic_pool_name),
            dcount_databases = dcount(strcat(logical_server_name, "|", database_name)),
            any_database_name = take_any(database_name),
            dcount_sql_module_name = dcount(sql_module_name),
            any_sql_module_name = take_any(sql_module_name),
            dcount_context_settings_id = dcount(context_settings_id),
            any_context_settings_id = take_any(context_settings_id),
            query_sql_text = take_any(query_sql_text),
            count_executions = sum(toreal(count_executions)),
            count_successful_executions = sumif(toreal(count_executions), execution_type_desc == "Regular"),
            count_aborted_executions = sumif(toreal(count_executions), execution_type_desc == "Aborted"),
            count_exception_executions = sumif(toreal(count_executions), execution_type_desc == "Exception"),
            duration_us = sum(avg_duration_us * count_executions),
            cpu_time_us = sum(avg_cpu_time_us * count_executions),
            logical_io_reads = sum(avg_logical_io_reads * count_executions),
            logical_io_writes = sum(avg_logical_io_writes * count_executions),
            physical_io_reads = sum(avg_physical_io_reads * count_executions),
            num_physical_io_reads = sum(avg_num_physical_io_reads * count_executions),
            clr_time_us = sum(avg_clr_time_us * count_executions),
            dop = sumif(avg_dop * count_executions, is_parallel_plan),
            query_max_used_memory = sum(avg_query_max_used_memory * count_executions),
            rowcount = sum(avg_rowcount * count_executions),
            log_bytes_used = sum(avg_log_bytes_used * count_executions),
            tempdb_space_used = sum(avg_tempdb_space_used * count_executions)
            by query_hash
| project logical_server_name = iif(dcount_logical_servers == 1, any_logical_server_name, strcat(any_logical_server_name, " (+", tostring(dcount_logical_servers - 1), ")")),
          elastic_pool_name = iif(dcount_elastic_pools == 1, any_elastic_pool_name, strcat(any_elastic_pool_name, " (+", tostring(dcount_elastic_pools - 1), ")")),
          database_name = iif(dcount_databases == 1, any_database_name, strcat(any_database_name, " (+", tostring(dcount_databases - 1), ")")),
          query_sql_text,
          count_executions,
          count_successful_executions,
          count_aborted_executions,
          count_exception_executions,
          duration_us,
          cpu_time_us,
          logical_io_reads,
          logical_io_writes,
          physical_io_reads,
          num_physical_io_reads,
          clr_time_us,
          dop,
          query_max_used_memory_kb = query_max_used_memory * 8,
          rowcount,
          log_bytes_used,
          tempdb_space_used_kb = tempdb_space_used * 8,
          sql_module_name = iif(dcount_sql_module_name == 1, any_sql_module_name, strcat(any_sql_module_name, " (+", tostring(dcount_sql_module_name - 1), ")")),
          context_settings_id = iif(dcount_context_settings_id == 1, tostring(any_context_settings_id), strcat(any_context_settings_id, " (+", tostring(dcount_context_settings_id - 1), ")")),
          query_hash
| sort by count_executions desc | extend count_executions_rank = row_rank_dense(count_executions)
| sort by duration_us desc | extend duration_rank = row_rank_dense(duration_us)
| sort by cpu_time_us desc | extend cpu_time_rank = row_rank_dense(cpu_time_us)
| sort by logical_io_reads desc | extend logical_io_reads_rank = row_rank_dense(logical_io_reads)
| sort by logical_io_writes desc | extend logical_io_writes_rank = row_rank_dense(logical_io_writes)
| sort by physical_io_reads desc | extend physical_io_reads_rank = row_rank_dense(physical_io_reads)
| sort by num_physical_io_reads desc | extend num_physical_io_reads_rank = row_rank_dense(num_physical_io_reads)
| sort by clr_time_us desc | extend clr_time_rank = row_rank_dense(clr_time_us)
| sort by dop desc | extend dop_rank = row_rank_dense(dop)
| sort by query_max_used_memory_kb desc | extend query_max_used_memory_rank = row_rank_dense(query_max_used_memory_kb)
| sort by rowcount desc | extend rowcount_rank = row_rank_dense(rowcount)
| sort by log_bytes_used desc | extend log_bytes_used_rank = row_rank_dense(log_bytes_used)
| sort by tempdb_space_used_kb desc | extend tempdb_space_used_rank = row_rank_dense(tempdb_space_used_kb)
| sort by case(
              topQueriesBy =~ "count_executions", toreal(count_executions),
              topQueriesBy =~ "duration", toreal(duration_us),
              topQueriesBy =~ "cpu_time", toreal(cpu_time_us),
              topQueriesBy =~ "logical_io_reads", toreal(logical_io_reads),
              topQueriesBy =~ "logical_io_writes", toreal(logical_io_writes),
              topQueriesBy =~ "physical_io_reads", toreal(physical_io_reads),
              topQueriesBy =~ "num_physical_io_reads", toreal(num_physical_io_reads),
              topQueriesBy =~ "clr_time", toreal(clr_time_us),
              topQueriesBy =~ "dop", toreal(dop),
              topQueriesBy =~ "query_max_used_memory", toreal(query_max_used_memory_kb),
              topQueriesBy =~ "rowcount", toreal(rowcount),
              topQueriesBy =~ "log_bytes_used", toreal(log_bytes_used),
              topQueriesBy =~ "tempdb_space_used", toreal(tempdb_space_used_kb),
              real(null)
              ) desc,
          count_executions desc
| project-away count_executions
| where isempty(queryHash) or query_hash == queryHash
| where isempty(queryTextFragment) or query_sql_text contains queryTextFragment
| take topQueries;

KQL gebruiken om prestatiemeteritems in de loop van de tijd te analyseren

In dit voorbeeld retourneert de query prestatiemeteritems voor een tijdsinterval dat 30 minuten voor de opgegeven eindtijd begint.

In dit voorbeeld worden cumulatieve prestatiemeteritems zoals Total request count en Query optimizations/secgebruikt. Cumulatief betekent dat de tellerwaarde steeds toeneemt naarmate de SQL-queryactiviteit plaatsvindt. De query in dit voorbeeld berekent het verschil of de delta tussen de tellerwaarde in elk voorbeeld en de bijbehorende waarde in het vorige voorbeeld om het aantal aanvragen en optimalisaties te verkrijgen dat is opgetreden sinds het vorige voorbeeld en visualiseert deze metrische gegevens vervolgens in een tijddiagram.

let logicalServer = @"your-server-name";
let databaseName = @"your-database-name";
let replicaType = "Primary";
let endTime = datetime("2023-12-19 22:10:00");
let startTime = endTime - 30m;
sqldb_database_performance_counters_common
| where sample_time_utc between (startTime .. endTime)
| where logical_server_name =~ logicalServer
| where database_name =~ databaseName
| where replica_type =~ replicaType
| where cntr_type == 272696576 // restrict to cumulative counters
| where object_name =~ "Workload Group Stats" and counter_name in ("Total request count","Query optimizations/sec")
| project replica_id, sample_time_utc, object_name, counter_name, cntr_value
| sort by replica_id asc, counter_name asc, sample_time_utc asc
| extend delta_cntr_value = iif(cntr_value >= prev(cntr_value) and counter_name == prev(counter_name) and replica_id == prev(replica_id), cntr_value - prev(cntr_value), real(null)),
         delta_sample_time_utc = iif(sample_time_utc >= prev(sample_time_utc), datetime_diff("Millisecond", sample_time_utc, prev(sample_time_utc)), long(null))
| where isnotempty(delta_sample_time_utc)
| extend value = delta_cntr_value / delta_sample_time_utc * 1000
| summarize requests_per_sec = take_anyif(value, counter_name =~ "Total request count"),
            query_optimizations_per_sec = take_anyif(value, counter_name =~ "Query optimizations/sec")
            by sample_time_utc
| sort by sample_time_utc desc
| project sample_time_utc, requests_per_sec, query_optimizations_per_sec
| render timechart;

Het volgende voorbeeld is voor prestatiemeteritems voor een bepaald tijdstip waarmee de laatst waargenomen waarde wordt weergegeven, zoals Active memory grants count, Pending memory grants counten Processes blocked. Het tijdsinterval is de afgelopen 30 minuten.

let logicalServer = @"your-server-name";
let databaseName = @"your-database-name";
let replicaType = "Primary";
let duration = 30m;
sqldb_database_performance_counters_common
| where sample_time_utc > ago(duration)
| where logical_server_name =~ logicalServer
| where database_name =~ databaseName
| where replica_type =~ replicaType
| where cntr_type == 65792 // restrict to point-in-time counters
| where (object_name =~ "General Statistics" and counter_name in ("Processes blocked"))
        or
        (object_name =~ "Resource Pool Stats" and counter_name in ("Active memory grants count","Pending memory grants count"))
| project sample_time_utc, counter_name, cntr_value
| render timechart;

In het volgende voorbeeld wordt de prestatiecounters (gedetailleerd) dataset gebruikt om het CPU-gebruik voor gebruikers- en interne resource-pools en workloadgroepen in Azure SQL Database in kaart te brengen. Zie Resourceverbruik door gebruikersworkloads en interne processenvoor meer informatie.

De gebruikersworkloads worden uitgevoerd in de SloSharedPool1 of UserPool resourcegroepen, terwijl alle andere resourcegroepen worden gebruikt voor verschillende systeemworkloads.

Op dezelfde manier worden de gebruikersworkloads uitgevoerd in de workloadgroepen die beginnen met UserPrimaryGroup.DBId, terwijl alle andere workloadgroepen worden gebruikt voor verschillende systeemworkloads. Database Watcher-bewakingsqueries worden bijvoorbeeld uitgevoerd in de SQLExternalMonitoringGroup-werkgroep.

let logicalServer = @"your-server-name";
let databaseName = @"your-database-name";
let replicaType = "Primary";
let endTime = datetime("2023-12-19 22:10:00");
let startTime = endTime - 30m;
sqldb_database_performance_counters_detailed
| where sample_time_utc between (startTime .. endTime)
| where logical_server_name =~ logicalServer
| where database_name =~ databaseName
| where replica_type =~ replicaType
| where cntr_type == 537003264 // restrict to ratio percentage counters
| where object_name =~ "Resource Pool Stats" and counter_name in ("CPU usage %")
| project sample_time_utc, resource_pool = instance_name, cpu_percentage = cntr_value
| render timechart;

let logicalServer = @"your-server-name";
let databaseName = @"your-database-name";
let replicaType = "Primary";
let endTime = datetime("2023-12-19 22:10:00");
let startTime = endTime - 30m;
sqldb_database_performance_counters_detailed
| where sample_time_utc between (startTime .. endTime)
| where logical_server_name =~ logicalServer
| where database_name =~ databaseName
| where replica_type =~ replicaType
| where cntr_type == 537003264 // restrict to ratio percentage counters
| where object_name =~ "Workload Group Stats" and counter_name in ("CPU usage %")
| project sample_time_utc, workload_group = instance_name, cpu_percentage = cntr_value
| render timechart;

KQL gebruiken om cumulatieve wachttijden in de loop van de tijd te analyseren

In dit voorbeeld ziet u hoe u de belangrijkste SQL-wachttypen in een tijdsinterval weergeeft. De query berekent de cumulatieve wachttijd voor elk wachttype, in milliseconden per seconde van verstreken tijd. U kunt queryvariabelen aanpassen om de begin- en eindtijd van het interval, het aantal topwachttijden dat moet worden opgenomen, en de stap tussen de gegevenspunten in de grafiek in te stellen.

De query maakt gebruik van twee technieken om de prestaties te verbeteren:

  • De partitie KQL-operator met de shuffle strategie om, indien aanwezig, queryverwerking over meerdere clusterknooppunten te verdelen.
  • De materialiseer() functie om een tussenliggende resultatenset te behouden die opnieuw wordt gebruikt voor het berekenen van de bovenste wachttijden en voor het bouwen van de tijdreeks die moet worden weergegeven.
let logicalServer = @"your-server-name";
let databaseName = @"your-database-name";
let replicaType = "Primary";
let endTime = datetime("2023-12-19 22:10:00");
let startTime = endTime - 30m;
let top_wait_types = 10;
let chart_step = 30s;
let wait_type_sample = materialize (
sqldb_database_wait_stats
| where sample_time_utc between (startTime .. endTime)
| where logical_server_name =~ logicalServer
| where database_name =~ databaseName
| where replica_type =~ replicaType
| project replica_id, sample_time_utc, wait_type, wait_time_ms
| partition hint.strategy=shuffle by wait_type
(
sort by replica_id asc, sample_time_utc asc
| extend delta_wait_time_ms = iif(wait_time_ms >= prev(wait_time_ms) and replica_id == prev(replica_id), wait_time_ms - prev(wait_time_ms), long(null)),
         delta_sample_time_utc = iif(sample_time_utc >= prev(sample_time_utc), datetime_diff("Millisecond", sample_time_utc, prev(sample_time_utc)), long(null))
| where isnotempty(delta_sample_time_utc)
| extend wait_ms_per_s = toreal(delta_wait_time_ms) / delta_sample_time_utc * 1000
| project sample_time_utc, wait_type, wait_ms_per_s
)
);
let top_wait = (
wait_type_sample
| summarize total_wait_ms_per_s = sum(wait_ms_per_s) by wait_type
| top top_wait_types by total_wait_ms_per_s desc
| project-away total_wait_ms_per_s
);
wait_type_sample
| join kind=inner top_wait on wait_type
| project-away wait_type1
| make-series wait_ms_per_s = avgif(wait_ms_per_s, isfinite(wait_ms_per_s)) default = long(null) on sample_time_utc from startTime to endTime step chart_step by wait_type
| project wait_type, sample_time_utc, wait_ms_per_s
| render timechart;

T-SQL gebruiken om bewakingsgegevens te analyseren

Als u al bekend bent met T-SQL, kunt u direct beginnen met het uitvoeren van query's en het analyseren van SQL-bewakingsgegevens zonder KQL te hoeven leren. KQL- is echter de aanbevolen taal voor het opvragen van gegevens in Azure Data Explorer of Real-Time Analytics, omdat deze ongeëvenaarde ondersteuning biedt voor het uitvoeren van query's op telemetriegegevens.

U kunt verbinding maken met uw Azure Data Explorer- of Real-Time Analytics-database vanuit SQL Server Management Studio (SSMS), Visual Studio Code met de SQL Server mssql-extensie en andere veelgebruikte hulpprogramma's. U kunt een query uitvoeren op een Azure Data Explorer- of KQL-database alsof het een SQL Server of een Azure SQL-database is. Voor meer informatie, zie query's uitvoeren op gegevens in Azure Data Explorer met behulp van SQL Server-emulatie.

Notitie

Niet elke T-SQL-constructie wordt ondersteund in Azure Data Explorer en Real-Time Analytics. Zie Gegevens opvragen met T-SQL-voor meer informatie.

De cheatsheet voor SQL naar Kusto Query Language kunt u helpen uw T-SQL-query's te vertalen naar KQL als u merkt dat T-SQL-ondersteuning onvoldoende is voor uw behoeften, of als u uw T-SQL-query's wilt converteren naar KQL om de geavanceerde analytische mogelijkheden te gebruiken.

In de volgende voorbeelden ziet u hoe u query's uitvoert op bewakingsgegevens in het database-watcher-gegevensarchief met behulp van T-SQL.

T-SQL gebruiken om resourceverbruik in de loop van de tijd te analyseren

In dit voorbeeld retourneert de query metrische gegevens over resourceverbruik (CPU, werkrollen, schrijfdoorvoer voor logboeken, enzovoort) voor de primaire replica van een database, een elastische pool of een beheerd SQL-exemplaar gedurende het afgelopen uur.

Wijzig in deze en andere voorbeelden de variabelen in de instructie DECLARE zodat deze overeenkomen met de namen van uw server, database, elastische pool of sql managed instance.

DECLARE @LogicalServerName sysname = 'your-server-name',
        @DatabaseName sysname = 'your-database-name',
        @ReplicaType sysname = 'Primary',
        @DurationMinutes int = 60;

SELECT sample_time_utc,
       avg_cpu_percent,
       avg_instance_cpu_percent,
       avg_data_io_percent,
       avg_log_write_percent,
       max_worker_percent
FROM sqldb_database_resource_utilization
WHERE sample_time_utc > DATEADD(minute, -@DurationMinutes, SYSUTCDATETIME())
      AND
      logical_server_name = @LogicalServerName
      AND
      database_name = @DatabaseName
      AND
      replica_type = @ReplicaType
ORDER BY sample_time_utc DESC;

T-SQL gebruiken om de eigenschappen van de database, elastische pool of sql managed instance weer te geven

In dit voorbeeld retourneert de query een set van alle databases, elastische pools of met SQL beheerde exemplaren waarvan ten minste één voorbeeld in de bijbehorende Eigenschappengegevensset is verzameld in de afgelopen 24 uur. Met andere woorden, elke rij vertegenwoordigt een bewakingsdoel met de meest recent waargenomen eigenschappen.

DECLARE @DurationHours int = 24;

SELECT p.sample_time_utc,
       p.logical_server_name,
       p.database_name,
       p.replica_type,
       p.database_id,
       p.elastic_pool_name,
       p.service_tier,
       p.service_level_objective,
       p.logical_cpu_count,
       p.database_engine_memory_mb,
       p.compatibility_level,
       p.updateability,
       p.database_engine_build_time,
       p.database_engine_start_time_utc
FROM sqldb_database_properties AS p
INNER JOIN (
           SELECT logical_server_name,
                  database_name,
                  replica_type,
                  MAX(sample_time_utc) AS last_sample_time_utc
           FROM sqldb_database_properties
           WHERE sample_time_utc > DATEADD(hour, -@DurationHours, SYSUTCDATETIME())
           GROUP BY logical_server_name,
                    database_name,
                    replica_type
           ) AS ls
ON p.logical_server_name = ls.logical_server_name
   AND
   p.database_name = ls.database_name
   AND
   p.replica_type = ls.replica_type
   AND
   p.sample_time_utc = ls.last_sample_time_utc
WHERE p.sample_time_utc > DATEADD(hour, -@DurationHours, SYSUTCDATETIME())
ORDER BY LOWER(logical_server_name) ASC,
         LOWER(database_name) ASC,
         CASE replica_type
              WHEN 'Primary' THEN 0
              WHEN 'Geo-replication forwarder' THEN 1
              WHEN 'Named secondary' THEN 2
              WHEN 'HA secondary' THEN 3
         END ASC;

T-SQL gebruiken om queryruntimestatistieken weer te geven

Deze query retourneert de belangrijkste resourceverbruikende query's in uw Azure SQL-estate. Wijzig de variabele @TopQueriesBy om de belangrijkste query's te vinden op basis van een Query Store metrische gegevens, waaronder CPU-tijd, verstreken tijd, uitvoeringsaantal, enzovoort. U kunt ook variabelen instellen om te filteren op een tijdsinterval, queryuitvoeringstype en query-hash van een specifieke query, of om u te richten op databases van een specifieke logische server, elastische pool of sql managed instance.

De query maakt gebruik van de queryruntimestatistiekengegevensset om de belangrijkste query's te retourneren die u opgeeft. Het geeft ook hun rang terug bij elk ander meetformulier voor hulpbronnenverbruik.

DECLARE @EndTime datetime2 = SYSUTCDATETIME(),
        @StartTime datetime2 = DATEADD(hour, -24, SYSUTCDATETIME()),
        /* 
        Set the next variable to one of the following metrics to return the top resource consuming queries:
        executions, cpu_time, duration, logical_io_reads, physical_io_reads, num_physical_io_reads, 
        clr_time, query_max_used_memory, log_bytes_used, tempdb_space_used, row_count, dop
        */
        @TopQueriesBy varchar(30) = 'cpu_time',
        @TopQueries int = 10,
        @LogicalServerName sysname = '', -- Optionally filter by logical server name
        @ElasticPoolName sysname = '', -- Optionally filter by elastic pool name, if any databases are in elastic pools
        @DatabaseName sysname = '', -- Optionally filter by database name
        @ExecutionType varchar(30) = '', -- Optionally filter by execution type. Use Regular, Aborted, Exception.
        @QueryHash varchar(18) = ''; -- Optionally filter by query hash (example: 0xBAAA461A6C93EA88)

SELECT TOP (@TopQueries) 
       CONCAT(logical_server_name, IIF(count_logical_servers > 1, CONCAT(' (+', CAST(count_logical_servers - 1 AS varchar(11)), ')'), '')) AS logical_server_name,
       CONCAT(database_name, IIF(count_databases > 1, CONCAT(' (+', CAST(count_databases - 1 AS varchar(11)), ')'), '')) AS database_name,
       query_sql_text,
       CONCAT(CAST(query_id AS varchar(11)), IIF(count_queries > 1, CONCAT(' (+', CAST(count_queries - 1 AS varchar(11)), ')'), '')) AS query_id,
       CONCAT(CAST(plan_id AS varchar(11)), IIF(count_plans > 1, CONCAT(' (+', CAST(count_plans - 1 AS varchar(11)), ')'), '')) AS plan_id,
       regular_executions,
       aborted_executions,
       exception_executions,
       cpu_time_us,
       duration_us,
       logical_io_reads,
       physical_io_reads,
       num_physical_io_reads,
       clr_time_us,
       query_max_used_memory_kb,
       log_bytes_used,
       tempdb_space_used_kb,
       row_count,
       dop,
       query_hash,
       executions_rank,
       cpu_time_rank,
       duration_rank,
       logical_io_reads_rank,
       physical_io_reads_rank,
       num_physical_io_reads_rank,
       clr_time_rank,
       query_max_used_memory_rank,
       log_bytes_used_rank,
       tempdb_space_used_rank,
       row_count_rank,
       dop_rank
FROM (
     SELECT *,
            DENSE_RANK() OVER (ORDER BY executions DESC) AS executions_rank,
            DENSE_RANK() OVER (ORDER BY cpu_time_us DESC) AS cpu_time_rank,
            DENSE_RANK() OVER (ORDER BY duration_us DESC) AS duration_rank,
            DENSE_RANK() OVER (ORDER BY logical_io_reads DESC) AS logical_io_reads_rank,
            DENSE_RANK() OVER (ORDER BY physical_io_reads DESC) AS physical_io_reads_rank,
            DENSE_RANK() OVER (ORDER BY num_physical_io_reads DESC) AS num_physical_io_reads_rank,
            DENSE_RANK() OVER (ORDER BY clr_time_us DESC) AS clr_time_rank,
            DENSE_RANK() OVER (ORDER BY query_max_used_memory_kb DESC) AS query_max_used_memory_rank,
            DENSE_RANK() OVER (ORDER BY log_bytes_used DESC) AS log_bytes_used_rank,
            DENSE_RANK() OVER (ORDER BY tempdb_space_used_kb DESC) AS tempdb_space_used_rank,
            DENSE_RANK() OVER (ORDER BY row_count DESC) AS row_count_rank,
            DENSE_RANK() OVER (ORDER BY dop DESC) AS dop_rank
     FROM (
          SELECT query_hash,
                 COUNT(DISTINCT(logical_server_name)) AS count_logical_servers,
                 MAX(logical_server_name) AS logical_server_name,
                 COUNT(DISTINCT(database_name)) AS count_databases,
                 MAX(database_name) AS database_name,
                 COUNT(DISTINCT(query_id)) AS count_queries,
                 MAX(query_id) AS query_id,
                 COUNT(DISTINCT(plan_id)) AS count_plans,
                 MAX(plan_id) AS plan_id,
                 MAX(query_sql_text) AS query_sql_text,
                 SUM(IIF(execution_type_desc = 'Regular', count_executions, 0)) AS regular_executions,
                 SUM(IIF(execution_type_desc = 'Aborted', count_executions, 0)) AS aborted_executions,
                 SUM(IIF(execution_type_desc = 'Exception', count_executions, 0)) AS exception_executions,
                 SUM(count_executions) AS executions,
                 SUM(avg_cpu_time_us * count_executions) AS cpu_time_us,
                 SUM(avg_duration_us * count_executions) AS duration_us,
                 SUM(avg_logical_io_reads * count_executions) AS logical_io_reads,
                 SUM(avg_physical_io_reads * count_executions) AS physical_io_reads,
                 SUM(avg_num_physical_io_reads * count_executions) AS num_physical_io_reads,
                 SUM(avg_clr_time_us * count_executions) AS clr_time_us,
                 SUM(avg_query_max_used_memory * count_executions) * 8 AS query_max_used_memory_kb,
                 SUM(avg_log_bytes_used * count_executions) AS log_bytes_used,
                 SUM(avg_tempdb_space_used * count_executions) * 8 AS tempdb_space_used_kb,
                 SUM(avg_rowcount * count_executions) AS row_count,
                 SUM(IIF(is_parallel_plan = 1, avg_dop * count_executions, NULL)) AS dop
          FROM sqldb_database_query_runtime_stats
          WHERE interval_start_time >= @StartTime AND interval_end_time <= @EndTime
                AND
                (@ExecutionType = '' OR LOWER(execution_type_desc) = LOWER(@ExecutionType))
                AND
                (@LogicalServerName = '' OR LOWER(logical_server_name) = LOWER(@LogicalServerName))
                AND
                (@ElasticPoolName = '' OR LOWER(elastic_pool_name) = LOWER(@ElasticPoolName))
                AND
                (@DatabaseName = '' OR LOWER(database_name) = LOWER(@DatabaseName))
          GROUP BY query_hash
          ) AS rsa
     ) AS rsar
WHERE @QueryHash = '' OR LOWER(query_hash) = LOWER(@QueryHash)
ORDER BY CASE @TopQueriesBy
              WHEN 'executions' THEN executions_rank
              WHEN 'cpu_time' THEN cpu_time_rank
              WHEN 'duration' THEN duration_rank
              WHEN 'logical_io_reads' THEN logical_io_reads_rank
              WHEN 'physical_io_reads' THEN physical_io_reads_rank
              WHEN 'num_physical_io_reads' THEN num_physical_io_reads_rank
              WHEN 'clr_time' THEN clr_time_rank
              WHEN 'query_max_used_memory' THEN query_max_used_memory_rank
              WHEN 'log_bytes_used' THEN log_bytes_used_rank
              WHEN 'tempdb_space_used' THEN tempdb_space_used_rank
              WHEN 'row_count' THEN row_count_rank
              WHEN 'dop' THEN dop_rank
         END ASC;

T-SQL gebruiken om prestatiemeteritems in de loop van de tijd te analyseren

In dit voorbeeld retourneert de query prestatiemeteritemwaarden voor de afgelopen 30 minuten.

In dit voorbeeld worden cumulatieve prestatiemeteritems zoals Total request count en Query optimizations/secgebruikt. Cumulatief betekent dat de tellerwaarde steeds toeneemt naarmate de queryactiviteit plaatsvindt. De query maakt gebruik van de LAG() analytische functie om het verschil of de delta te berekenen tussen de tellerwaarde in elke steekproef en de bijbehorende waarde in het vorige voorbeeld om het aantal aanvragen en optimalisaties te verkrijgen dat is opgetreden sinds de vorige steekproef.

DECLARE @LogicalServerName sysname = 'your-server-name',
        @DatabaseName sysname = 'your-database-name',
        @ReplicaType sysname = 'Primary',
        @DurationMinutes int = 30;

SELECT sample_time_utc,
       SUM(IIF(
              counter_name = 'Total request count',
              CAST((cntr_value - prev_cntr_value) AS decimal) / DATEDIFF(millisecond, prev_sample_time_utc, sample_time_utc) * 1000,
              NULL
              )) AS requests_per_second,
       SUM(IIF(
              counter_name = 'Query optimizations/sec',
              CAST((cntr_value - prev_cntr_value) AS decimal) / DATEDIFF(millisecond, prev_sample_time_utc, sample_time_utc) * 1000,
              NULL
              )) AS query_optimizations_per_second
FROM (
     SELECT sample_time_utc,
            LAG(sample_time_utc) OVER (PARTITION BY replica_id, object_name, counter_name ORDER BY sample_time_utc ASC) AS prev_sample_time_utc,
            counter_name,
            cntr_value,
            LAG(cntr_value) OVER (PARTITION BY replica_id, object_name, counter_name ORDER BY sample_time_utc ASC) AS prev_cntr_value
     FROM sqldb_database_performance_counters_common
     WHERE sample_time_utc > DATEADD(minute, -@DurationMinutes, SYSUTCDATETIME())
           AND
           logical_server_name = @LogicalServerName
           AND
           database_name = @DatabaseName
           AND
           replica_type = @ReplicaType
           AND
           cntr_type = 272696576 /* restrict to cumulative counters */
           AND
           object_name = 'Workload Group Stats'
           AND
           counter_name IN ('Total request count','Query optimizations/sec')
     ) AS pc
WHERE cntr_value >= prev_cntr_value
      AND
      sample_time_utc >= prev_sample_time_utc
GROUP BY sample_time_utc
ORDER BY sample_time_utc DESC;

T-SQL gebruiken om prestatiemeteritems voor een bepaald tijdstip te analyseren

Het volgende voorbeeld is voor prestatiemeteritems voor een bepaald tijdstip waarmee de laatst waargenomen waarde wordt weergegeven, zoals Active memory grants count, Pending memory grants counten Processes blocked.

DECLARE @LogicalServerName sysname = 'your-server-name',
        @DatabaseName sysname = 'your-database-name',
        @ReplicaType sysname = 'Primary',
        @DurationMinutes int = 30;

SELECT sample_time_utc,
       SUM(IIF(
              counter_name = 'Processes blocked',
              cntr_value,
              NULL
              )) AS processes_blocked,
       SUM(IIF(
              counter_name = 'Active memory grants count',
              cntr_value,
              NULL
              )) AS active_memory_grants,
       SUM(IIF(
              counter_name = 'Pending memory grants count',
              cntr_value,
              NULL
              )) AS pending_memory_grants
FROM (
     SELECT sample_time_utc,
            counter_name,
            cntr_value
     FROM sqldb_database_performance_counters_common
     WHERE sample_time_utc > DATEADD(minute, -@DurationMinutes, SYSUTCDATETIME())
         AND
         logical_server_name = @LogicalServerName
         AND
         database_name = @DatabaseName
         AND
         replica_type = @ReplicaType
         AND
         cntr_type = 65792 /* restrict to point-in-time counters */
         AND
         (
         (object_name = 'General Statistics' AND counter_name IN ('Processes blocked'))
         OR
         (object_name = 'Resource Pool Stats' AND counter_name IN ('Active memory grants count','Pending memory grants count'))
         )
     ) AS pc
GROUP BY sample_time_utc
ORDER BY sample_time_utc DESC;

T-SQL gebruiken om cumulatieve wachttijden na verloop van tijd te analyseren

In dit voorbeeld retourneert de query de tien belangrijkste wachttypen op basis van de gemiddelde cumulatieve wachttijd gedurende een interval van 30 minuten. Cumulatief betekent dat de query de totale tijd berekent, in milliseconden, die is besteed aan het wachten voor elk wachttype voor alle aanvragen per seconde. Omdat meerdere aanvragen gelijktijdig kunnen worden uitgevoerd (en kunnen wachten), kan de cumulatieve wachttijd in elke seconde meer dan één seconde zijn.

DECLARE @LogicalServerName sysname = 'your-server-name',
        @DatabaseName sysname = 'your-database-name',
        @ReplicaType sysname = 'Primary',
        @DurationMinutes int = 30;

SELECT TOP (10) wait_type,
                SUM(CAST((wait_time_ms - prev_wait_time_ms) AS decimal)) * 1000
                /
                SUM(DATEDIFF(millisecond, prev_sample_time_utc, sample_time_utc))
                AS wait_time_ms_per_sec
FROM (
     SELECT sample_time_utc,
            LAG(sample_time_utc) OVER (PARTITION BY replica_id, wait_type ORDER BY sample_time_utc ASC) AS prev_sample_time_utc,
            wait_type,
            wait_time_ms,
            LAG(wait_time_ms) OVER (PARTITION BY replica_id, wait_type ORDER BY sample_time_utc ASC) AS prev_wait_time_ms
     FROM sqldb_database_wait_stats
     WHERE sample_time_utc > DATEADD(minute, -@DurationMinutes, SYSUTCDATETIME())
         AND
         logical_server_name = @LogicalServerName
         AND
         database_name = @DatabaseName
         AND
         replica_type = @ReplicaType
     ) AS w
WHERE sample_time_utc >= prev_sample_time_utc
      AND
      wait_time_ms >= prev_wait_time_ms
GROUP BY wait_type
ORDER BY wait_time_ms_per_sec DESC;

Verwante inhoud