Udostępnij za pośrednictwem

Analizowanie danych monitorowania obserwatora bazy danych (wersja zapoznawcza)

  • Artykuł

Dotyczy: Azure SQL Database Azure SQL Managed Instance

Oprócz używania pulpitów nawigacyjnych w witrynie Azure Portal lub tworzenia wizualizacji do wyświetlania i analizowania danych monitorowania SQL w usłudze Power BI, Grafana, Azure Data Explorer lub Analiza w czasie rzeczywistym w usłudze Microsoft Fabric możesz wykonywać zapytania bezpośrednio względem magazynu danych monitorowania.

Ten artykuł zawiera przykłady zapytań języka KQL i języka T-SQL , które ułatwiają rozpoczęcie analizowania zebranych danych monitorowania.

Analizowanie danych monitorowania przy użyciu języka KQL

Aby przeanalizować zebrane dane monitorowania, zalecaną metodą jest użycie język zapytań Kusto (KQL). Język KQL jest optymalny do wykonywania zapytań dotyczących danych telemetrycznych, metryk i dzienników. Zapewnia rozbudowaną obsługę wyszukiwania tekstu i analizowania, operatorów szeregów czasowych i funkcji, analizy i agregacji oraz wielu innych konstrukcji językowych, które ułatwiają analizę danych.

Język KQL jest koncepcyjnie podobny do języka SQL. Działa na jednostkach schematu, takich jak tabele i kolumny, i obsługuje operacje relacyjne, takie jak projekt, ograniczanie, sprzężenie i podsumowywanie, odpowiadające SELECTklauzulom , JOINWHERE, i GROUP BY w języku SQL.

Aby napisać i wykonać zapytania KQL, możesz użyć narzędzia Kusto Explorer lub internetowego interfejsu użytkownika usługi Azure Data Explorer. Kusto Explorer to w pełni funkcjonalne oprogramowanie klasyczne systemu Windows, a internetowy interfejs użytkownika usługi Azure Data Explorer umożliwia wykonywanie zapytań KQL i wizualizowanie wyników w przeglądarce na dowolnej platformie.

Za pomocą tych narzędzi można również wykonywać zapytania dotyczące bazy danych w analizie czasu rzeczywistego w usłudze Microsoft Fabric. Aby nawiązać połączenie, dodaj nowe połączenie przy użyciu identyfikatora URI zapytania bazy danych analizy w czasie rzeczywistym. Ponadto, jeśli używasz analizy w czasie rzeczywistym, możesz analizować dane monitorowania przy użyciu zestawów zapytań KQL. Zestaw zapytań KQL można zapisać jako artefakt z możliwością udostępniania sieci szkieletowej i użyć go do tworzenia raportów usługi Power BI.

Jeśli dopiero zaczynasz korzystać z języka KQL, następujące zasoby mogą pomóc w rozpoczęciu pracy:

Poniższe przykłady mogą pomóc w pisaniu własnych zapytań KQL w celu wyświetlania i analizowania zebranych danych monitorowania SQL. Możesz również użyć tych przykładów jako punktu wyjścia do tworzenia własnych wizualizacji danych i pulpitów nawigacyjnych.

Używanie języka KQL do wykonywania zapytań dotyczących zużycia zasobów w czasie

W tym przykładzie zapytanie zwraca metryki użycia zasobów (procesor CPU, procesy robocze, przepływność zapisu dziennika itp.) dla podstawowej repliki bazy danych, elastycznej puli lub wystąpienia zarządzanego SQL w ciągu ostatniej godziny. Oprócz zwracania zestawu wyników wizualizuje go jako wykres czasu.

W tym i innych przykładach zmień zmienne w instrukcjach let , aby odpowiadały nazwam serwera, bazy danych, elastycznej puli lub wystąpienia zarządzanego SQL. Aby użyć innego interwału czasu, zmień zmienną duration . Aby uzyskać więcej informacji, zobacz literały przedziału czasu.

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;

Wyświetlanie właściwości bazy danych, elastycznej puli lub wystąpienia zarządzanego SQL za pomocą języka KQL

W tym przykładzie zapytanie zwraca zestaw wszystkich baz danych, elastycznych pul lub wystąpień zarządzanych SQL, z których zebrano co najmniej jeden przykład w odpowiednim zestawie danych Właściwości w ciągu ostatniego dnia. Innymi słowy, każdy wiersz reprezentuje cel monitorowania z ostatnio obserwowanymi właściwościami.

Funkcja arg_max() agreguje dane, aby zwrócić najnowszy wiersz dla określonego zestawu kolumn identyfikujących cel. Na przykład w przypadku baz danych Azure SQL Database ten zestaw to 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;

Wyświetlanie statystyk środowiska uruchomieniowego zapytań przy użyciu języka KQL

To zapytanie zwraca zapytania zużywające najwięcej zasobów w infrastrukturze Azure SQL. Zmień zmienną na rangę zapytań według dowolnej metryki magazynu zapytań, w tym czasu procesora CPU, czasu, czasu, liczby wykonań itp. Można również ustawić zmienne do filtrowania według interwału czasu, typu wykonywania zapytania i tekstu zapytania. Ustaw zmienne, aby skupić się na określonym serwerze logicznym, elastycznej puli, wystąpieniu zarządzanym SQL lub bazie danych.

Zapytanie używa zestawu danych statystyk środowiska uruchomieniowego zapytania, aby zwrócić liczbę najważniejszych zapytań i uwzględnia ich klasyfikację według każdej innej metryki zużycia zasobów.

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;

Używanie języka KQL do analizowania liczników wydajności w czasie

W tym przykładzie zapytanie zwraca wartości licznika wydajności dla przedziału czasu, który rozpoczyna się 30 minut przed określonym czasem zakończenia.

W tym przykładzie użyto skumulowanych liczników wydajności, takich jak Total request count i Query optimizations/sec. Skumulowany oznacza, że wartość licznika stale rośnie w miarę wykonywania działań zapytań SQL. Zapytanie w tym przykładzie oblicza różnicę lub różnicę między wartością licznika w każdej próbce a jej wartością w poprzednim przykładzie w celu uzyskania liczby żądań i optymalizacji, które wystąpiły od poprzedniego przykładu, a następnie wizualizuje te metryki na wykresie czasowym.

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;

Poniższy przykład dotyczy liczników wydajności punktu w czasie, które zgłaszają ostatnio obserwowaną wartość, taką jak Active memory grants count, Pending memory grants counti Processes blocked. Interwał czasu to ostatnie 30 minut.

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;

W poniższym przykładzie użyto zestawu danych Liczniki wydajności (szczegółowe) do wykresu wykorzystania procesora CPU dla pul zasobów użytkowników i wewnętrznych pul zasobów i grup obciążeń w usłudze Azure SQL Database. Aby uzyskać więcej informacji, zobacz Użycie zasobów według obciążeń użytkownika i procesów wewnętrznych.

Obciążenia użytkownika są uruchamiane w SloSharedPool1 pulach zasobów lub UserPool , podczas gdy wszystkie inne pule zasobów są używane dla różnych obciążeń systemowych.

Podobnie obciążenia użytkowników są uruchamiane w grupach obciążeń o nazwie rozpoczynających się od UserPrimaryGroup.DBId, podczas gdy wszystkie inne grupy obciążeń są używane dla różnych obciążeń systemowych. Na przykład zapytania monitorowania obserwatora bazy danych są uruchamiane w SQLExternalMonitoringGroup grupie obciążeń.

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;

Analizowanie skumulowanych oczekiwań w czasie za pomocą języka KQL

W tym przykładzie pokazano, jak utworzyć wykres najważniejszych typów oczekiwania SQL w przedziale czasu. Zapytanie oblicza skumulowany czas oczekiwania dla każdego typu oczekiwania w milisekundach na sekundę czasu, który upłynął. Zmienne zapytania można dostosować, aby ustawić czas rozpoczęcia i zakończenia interwału, liczbę najważniejszych typów oczekiwania do uwzględnienia oraz krok między punktami danych na wykresie.

Zapytanie używa dwóch technik w celu zwiększenia wydajności:

  • Operator KQL partycji ze strategią shuffle rozłożenia przetwarzania zapytań na wiele węzłów klastra, jeśli jest obecny.
  • Funkcja materialize() do utrwalania pośredniego zestawu wyników, który jest ponownie używany do obliczania pierwszych oczekiwań i tworzenia szeregów czasowych do utworzenia wykresu.
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;

Analizowanie danych monitorowania przy użyciu języka T-SQL

Jeśli znasz już język T-SQL, możesz od razu rozpocząć wykonywanie zapytań i analizowanie danych monitorowania SQL bez konieczności nauki języka KQL. Jednak język KQL jest zalecanym językiem do wykonywania zapytań dotyczących danych w usłudze Azure Data Explorer lub analizy w czasie rzeczywistym, ponieważ zapewnia niezrównaną obsługę wykonywania zapytań dotyczących danych telemetrycznych.

Możesz nawiązać połączenie z usługą Azure Data Explorer lub bazą danych analizy w czasie rzeczywistym z poziomu programu SQL Server Management Studio (SSMS), narzędzia Azure Data Studio i innych typowych narzędzi. Możesz wykonać zapytanie względem eksploratora danych platformy Azure lub bazy danych KQL tak, jakby była to baza danych SQL Server lub Azure SQL Database. Aby uzyskać więcej informacji, zobacz Query data in Azure Data Explorer using SQL Server emulation (Wykonywanie zapytań o dane w usłudze Azure Data Explorer przy użyciu emulacji programu SQL Server).

Uwaga

Nie każda konstrukcja języka T-SQL jest obsługiwana w usłudze Azure Data Explorer i analizie w czasie rzeczywistym. Aby uzyskać szczegółowe informacje, zobacz Wykonywanie zapytań dotyczących danych przy użyciu języka T-SQL.

Ściągawka sql do język zapytań Kusto może pomóc w przetłumaczeniu zapytań T-SQL na język KQL, jeśli okaże się, że obsługa języka T-SQL jest niewystarczająca dla Twoich potrzeb lub jeśli chcesz przekonwertować zapytania T-SQL na język KQL, aby korzystać z zaawansowanych funkcji analitycznych.

W poniższych przykładach pokazano, jak wykonywać zapytania dotyczące danych monitorowania w magazynie danych obserwatora bazy danych przy użyciu języka T-SQL.

Analizowanie zużycia zasobów w czasie przy użyciu języka T-SQL

W tym przykładzie zapytanie zwraca metryki użycia zasobów (procesor CPU, procesy robocze, przepływność zapisu dziennika itp.) dla podstawowej repliki bazy danych, elastycznej puli lub wystąpienia zarządzanego SQL w ciągu ostatniej godziny.

W tym i innych przykładach zmień zmienne w instrukcji DECLARE tak, aby odpowiadały nazwam serwera, bazy danych, elastycznej puli lub wystąpienia zarządzanego SQL.

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;

Używanie języka T-SQL do wyświetlania właściwości bazy danych, elastycznej puli lub wystąpienia zarządzanego SQL

W tym przykładzie zapytanie zwraca zestaw wszystkich baz danych, pul elastycznych lub wystąpień zarządzanych SQL, z których zebrano co najmniej jeden przykład w odpowiednim zestawie danych Właściwości w ciągu ostatnich 24 godzin. Innymi słowy, każdy wiersz reprezentuje cel monitorowania z ostatnio obserwowanymi właściwościami.

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;

Wyświetlanie statystyk środowiska uruchomieniowego zapytań przy użyciu języka T-SQL

To zapytanie zwraca zapytania zużywające najwięcej zasobów w infrastrukturze Azure SQL. Zmień zmienną, @TopQueriesBy aby znaleźć najważniejsze zapytania według dowolnej metryki magazynu zapytań, w tym czasu procesora CPU, czasu, czasu, liczby wykonań itp. Można również ustawić zmienne do filtrowania według interwału czasu, typu wykonywania zapytania i skrótu zapytania określonego zapytania lub skoncentrować się na bazach danych z określonego serwera logicznego, elastycznej puli lub wystąpienia zarządzanego SQL.

Zapytanie używa zestawu danych statystyk środowiska uruchomieniowego zapytania w celu zwrócenia najważniejszych zapytań, które określisz. Zwraca również ich rangę według każdej innej metryki zużycia zasobów.

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;

Używanie języka T-SQL do analizowania liczników wydajności w czasie

W tym przykładzie zapytanie zwraca wartości licznika wydajności z ostatnich 30 minut.

W tym przykładzie użyto skumulowanych liczników wydajności, takich jak Total request count i Query optimizations/sec. Skumulowany oznacza, że wartość licznika stale rośnie wraz z działaniem zapytania. Zapytanie używa funkcji analitycznej LAG(), aby obliczyć różnicę lub różnicę między wartością licznika w każdej próbce a jej wartością w poprzednim przykładzie w celu uzyskania liczby żądań i optymalizacji, które wystąpiły od poprzedniego przykładu.

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;

Używanie języka T-SQL do analizowania liczników wydajności punktu w czasie

Następnym przykładem są liczniki wydajności punktu w czasie, które zgłaszają ostatnio obserwowaną wartość, taką jak Active memory grants count, Pending memory grants counti 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;

Używanie języka T-SQL do analizowania skumulowanych oczekiwań w czasie

W tym przykładzie zapytanie zwraca 10 pierwszych typów oczekiwania według średniego skumulowanego czasu oczekiwania w 30-minutowym interwale. Skumulowany oznacza, że zapytanie oblicza całkowity czas (w milisekundach) spędził oczekiwanie pod każdym typem oczekiwania według wszystkich żądań w każdej sekundzie. Ponieważ wiele żądań może być wykonywanych (i czekać) jednocześnie, skumulowany czas oczekiwania w każdej sekundzie może być więcej niż jedna sekunda.

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;

Powiązana zawartość