다음을 통해 공유

데이터베이스 Watcher 모니터링 데이터 분석(프리뷰)

  • 아티클

적용 대상: Azure SQL 데이터베이스 Azure SQL Managed Instance

Azure Portal의 대시보드를 사용하거나 시각적 개체를 빌드하여 Power BI, Grafana, Azure Data Explorer 또는 Real-Time Analytics in Microsoft Fabric의 실시간 분석에서 SQL 모니터링 데이터를 확인하고 분석하는 것 외에도 모니터링 데이터 저장소를 직접 쿼리할 수 있습니다.

이 문서에는 수집된 모니터링 데이터 분석을 시작하는 데 도움이 되는 KQL 및 T-SQL 쿼리 예시가 포함되어 있습니다.

KQL을 사용한 모니터링 데이터 분석

수집된 모니터링 데이터를 분석하기 위해 권장되는 방법은 KQL(Kusto 쿼리 언어)을 사용하는 것입니다. KQL은 원격 분석, 메트릭 및 로그를 쿼리하는 데 최적입니다. 그리고 텍스트 검색 및 구문 분석, 시계열 연산자 및 함수, 분석 및 집계, 데이터 분석을 용이하게 하는 기타 여러 언어 구성에 대한 광범위한 지원을 제공합니다.

KQL은 개념적으로 SQL과 유사합니다. 테이블 및 열과 같은 스키마 엔터티에서 작동하며 SQL의 SELECT, JOIN, WHERE 및 GROUP BY 절에 해당하는 프로젝트, 제한, 조인, 요약과 같은 관계형 연산을 지원합니다.

KQL 쿼리를 작성하고 실행하려면 Kusto Explorer 또는 Azure Data Explorer 웹 UI를 사용할 수 있습니다. Kusto Explorer는 완전한 기능을 갖춘 Windows 데스크탑 소프트웨어이며, Azure Data Explorer 웹 UI를 사용하면 KQL 쿼리를 실행하고 모든 플랫폼의 브라우저에서 결과를 시각화할 수 있습니다.

이러한 도구를 사용하여 Microsoft Fabric의 실시간 분석에서 데이터베이스를 쿼리할 수도 있습니다. 연결하려면 실시간 분석 데이터베이스의 쿼리 URI를 사용하여 새 연결을 추가합니다. 또한 실시간 애널리틱스를 사용하는 경우 KQL 쿼리 집합을 사용하여 모니터링 데이터를 분석할 수도 있습니다. KQL 쿼리 집합을 공유 가능한 패브릭 아티팩트로 저장하여 Power BI 보고서를 만드는 데 사용할 수 있습니다.

KQL을 처음 사용하는 경우 다음 리소스를 참조하면 시작하는 데 도움이 될 수 있습니다.

다음 예시는 수집된 SQL 모니터링 데이터를 보고 분석하는 고유한 KQL 쿼리를 작성하는 데 도움이 될 수 있습니다. 이러한 예시를 데이터 시각화 및 대시보드 구축의 출발점으로 사용할 수도 있습니다.

KQL을 사용하여 시간에 따른 리소스 사용량 쿼리

이 예시에서 쿼리는 지난 1시간 동안 데이터베이스의 주 복제본, Elastic Pool 또는 SQL Managed Instance에 대한 리소스 사용 메트릭(CPU, 작업자, 로그 쓰기 처리량 등)을 반환합니다. 그리고 결과 집합을 반환할 뿐만 아니라 시간 차트로 시각화합니다.

이 예시와 다른 예시에서는 서버, 데이터베이스, Elastic Pool 또는 SQL Managed Instance의 이름과 일치하도록 let 문의 변수를 변경합니다. 다른 시간 간격을 사용하려면 duration 변수를 변경합니다. 자세한 내용은 시간 범위 리터럴을 참조하세요.

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을 사용하여 데이터베이스, Elastic Pool 또는 SQL Managed Instance 속성 보기

이 예시에서 쿼리는 지난 1일 동안 해당 속성 데이터 세트의 샘플이 1개 이상 수집된 모든 데이터베이스, Elastic Pool 또는 SQL Managed Instance의 집합을 반환합니다. 즉, 각 행은 가장 최근에 관찰된 속성을 가진 모니터링 대상을 나타냅니다.

arg_max() 함수는 데이터를 집계하여 대상을 식별하는 지정된 열 집합에 대한 최신 행을 반환합니다. 예를 들어 Azure SQL 데이터베이스의 경우 이 집합은 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을 사용하여 런타임 통계 쿼리 보기

이 쿼리는 Azure SQL 자산의 상위 리소스 사용 쿼리를 반환합니다. 변수를 변경하여 CPU 시간, 경과 시간, 실행 횟수 등 모든 쿼리 저장소 메트릭을 기준으로 쿼리의 순위를 매길 수 있습니다. 시간 간격, 쿼리 실행 유형 및 쿼리 텍스트를 기준으로 필터링하도록 변수를 설정할 수도 있습니다. 변수를 설정하여 특정 논리 서버, Elastic Pool, SQL Managed Instance 또는 데이터베이스에 초점을 맞출 수 있습니다.

이 쿼리는 쿼리 런타임 통계 데이터 세트를 사용하여 지정한 상위 쿼리 수를 반환하며, 다른 모든 리소스 사용 메트릭별 순위를 포함합니다.

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을 사용하여 시간에 따른 성능 카운터 분석

이 예시에서 쿼리는 지정된 종료 시간 30분 전에 시작되는 시간 간격에 대한 성능 카운터 값을 반환합니다.

이 예시에서는 Total request count 및 Query optimizations/sec 같은 누적 성능 카운터를 사용합니다. 누적은 SQL 쿼리 작업이 발생할 때 카운터 값이 계속 증가한다는 것을 의미합니다. 이 예시의 쿼리는 각 샘플의 카운터 값과 이전 샘플의 값 사이의 차이, 즉 델타를 계산하여 이전 샘플 이후 발생한 요청 및 최적화 수를 구한 다음 이러한 메트릭을 시간 차트에 시각화합니다.

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;

다음 예는 Active memory grants count, Pending memory grants count, Processes blocked 같이 가장 최근에 관찰된 값을 보고하는 특정 시점 성능 카운터의 경우입니다. 시간 간격은 지난 30분입니다.

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;

다음 예에서는 성능 카운터(세부 정보) 데이터 세트를 사용하여 Azure SQL 데이터베이스의 사용자 및 내부 리소스 풀과 워크로드 그룹에 대한 CPU 사용률을 차트에 표시합니다. 자세한 내용은 사용자 워크로드 및 내부 프로세스별 리소스 사용량을 참조하세요.

사용자 워크로드는 SloSharedPool1 또는 UserPool 리소스 풀에서 실행되고, 다른 모든 리소스 풀은 다양한 시스템 워크로드에 사용됩니다.

마찬가지로 사용자 워크로드는 UserPrimaryGroup.DBId(으)로 시작하는 워크로드 그룹에서 실행되고, 다른 모든 워크로드 그룹은 다양한 시스템 워크로드에 사용됩니다. 예를 들어, 데이터베이스 Watcher 모니터링 쿼리는 SQLExternalMonitoringGroup 작업 그룹에서 실행되고 있습니다.

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을 사용하여 시간 경과에 따른 누적 대기 분석

이 예시에서는 시간 간격에 따라 상위 SQL 대기 형식을 차트로 표시하는 방법을 보여줍니다. 이 쿼리는 각 대기 유형에 대한 누적 대기 시간을 경과 시간 1초당 밀리초 단위로 계산합니다. 쿼리 변수를 조정하여 간격 시작 및 종료 시간, 포함할 상위 대기 유형 수, 차트에서 데이터 요소 사이의 단계를 설정할 수 있습니다.

이 쿼리는 다음의 두 가지 기법을 사용하여 성능을 개선합니다.

  • 여러 클러스터 노드에 쿼리 처리를 분산하는 shuffle 전략이 있는 경우에는 partition KQL 연산자.
  • 상위 대기 시간 계산 및 차트에 표시할 시계열을 작성하는 데 재사용되는 중간 결과 집합을 유지하는 materialize() 함수.
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을 사용하여 모니터링 데이터 분석

T-SQL에 이미 익숙한 경우 KQL을 학습하지 않고도 SQL 모니터링 데이터를 즉시 쿼리하고 분석할 수 있습니다. 하지만 원격 분석 데이터 쿼리에 대한 탁월한 지원을 제공하기 때문에 Azure Data Explorer 또는 실시간 분석에서 데이터를 쿼리하는 데 권장되는 언어는 KQL입니다.

SQL Server Management Studio(SSMS), Azure Data Studio 및 기타 일반 도구에서 Azure Data Explorer 또는 Real-Time Analytics 데이터베이스에 연결할 수 있습니다. Azure Data Explorer 또는 KQL 데이터베이스를 마치 SQL Server 또는 Azure SQL 데이터베이스처럼 쿼리할 수 있습니다. 자세한 내용은 SQL Server 에뮬레이션을 사용하여 Azure Data Explorer에서 데이터 쿼리를 참조하세요.

참고 항목

Azure Data Explorer 및 실시간 분석에서 모든 T-SQL 구문이 지원되는 것은 아닙니다. 자세한 내용은 T-SQL을 사용하여 데이터 쿼리를 참조하세요.

SQL에서 Kusto 쿼리 언어로 변환 치트 시트는 T-SQL 지원이 필요에 따라 충분하지 않다고 판단되거나 고급 분석 기능을 사용하기 위해 T-SQL 쿼리를 KQL로 변환하려는 경우 도움이 될 수 있습니다.

다음 예시에서는 T-SQL을 사용하여 데이터베이스 Watcher 데이터 저장소에서 모니터링 데이터를 쿼리하는 방법을 보여 줍니다.

T-SQL을 사용하여 시간에 따른 리소스 사용량 분석

이 예시에서 쿼리는 지난 1시간 동안 데이터베이스의 주 복제본, Elastic Pool 또는 SQL Managed Instance에 대한 리소스 사용 메트릭(CPU, 작업자, 로그 쓰기 처리량 등)을 반환합니다.

이 예시와 다른 예시에서는 서버, 데이터베이스, Elastic Pool 또는 SQL Managed Instance의 이름과 일치하도록 DECLARE 문에서 변수를 변경합니다.

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을 사용하여 데이터베이스, Elastic Pool 또는 SQL Managed Instance 속성 보기

이 예시에서 쿼리는 지난 24시간 동안 해당 속성 데이터 세트의 샘플이 1개 이상 수집된 모든 데이터베이스, Elastic Pool 또는 SQL Managed Instance의 집합을 반환합니다. 즉, 각 행은 가장 최근에 관찰된 속성을 가진 모니터링 대상을 나타냅니다.

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을 사용하여 런타임 통계 쿼리 보기

이 쿼리는 Azure SQL 자산 전체에서 사용되는 상위 리소스 쿼리를 반환합니다. @TopQueriesBy 변수를 변경하여 CPU 시간, 경과 시간, 실행 횟수 등을 포함한 모든 쿼리 저장소 메트릭별로 상위 쿼리를 찾을 수 있습니다. 또한 특정 쿼리의 시간 간격, 쿼리 실행 유형, 쿼리 해시를 기준으로 필터링하도록 변수를 설정하거나 특정 논리 서버, Elastic Pool 또는 SQL Managed Instance의 데이터베이스에 초점을 맞추도록 변수를 설정할 수도 있습니다.

이 쿼리는 쿼리 런타임 통계 데이터 세트를 사용하여 사용자가 지정한 상위 쿼리를 반환합니다. 또한 다른 모든 리소스 사용량 메트릭별로 순위를 반환합니다.

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을 사용하여 시간 경과에 따른 성능 카운터 분석

이 예시에서 쿼리는 지난 30분 동안의 성능 카운터 값을 반환합니다.

이 예시에서는 Total request count 및 Query optimizations/sec 같은 누적 성능 카운터를 사용합니다. 누적은 쿼리 작업이 발생할 때 카운터 값이 계속 증가한다는 것을 의미합니다. 이 쿼리는 LAG() 분석 함수를 사용하여 각 샘플의 카운터 값과 이전 샘플의 값 사이의 차이 또는 델타를 계산하여 이전 샘플 이후 발생한 요청 및 최적화 횟수를 구합니다.

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을 사용하여 지정 시간 성능 카운터 분석

다음 예시는 Active memory grants count, Pending memory grants count, 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을 사용하여 시간에 따른 누적 대기 분석

이 예시에서 쿼리는 30분 간격 동안 평균 누적 대기 시간으로 상위 10개 대기 유형을 반환합니다. 누적은 쿼리가 매초 모든 요청이 각 대기 유형에 따라 대기한 총 시간(밀리초)을 계산한다는 의미입니다. 여러 요청이 동시에 실행(및 대기)될 수 있으므로 각 초의 누적 대기 시간은 1초 이상이 될 수 있습니다.

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;

관련 콘텐츠