DMV'leri kullanarak Azure Synapse Analytics ayrılmış SQL havuzu iş yükünüzü izleme

Bu makalede, ayrılmış bir SQL havuzunda sorgu yürütmeyi araştırma da dahil olmak üzere iş yükünüzü izlemek için Dinamik Yönetim Görünümlerinin (DMV) nasıl kullanılacağı açıklanmaktadır.

İzinler

Bu makaledeki DMV'leri sorgulamak için VERITABANı DURUMUNU GÖRÜNTÜLE veya CONTROL iznine sahip olmanız gerekir. Genellikle VIEW DATABASE STATE verilmesi çok daha kısıtlayıcı olduğundan tercih edilen izindir.

GRANT VIEW DATABASE STATE TO myuser;

Bağlantıları izleme

Veri ambarınızdaki tüm oturum açma işlemleri sys.dm_pdw_exec_sessions günlüğe kaydedilir. Bu DMV son 10.000 oturum açma bilgisini içerir. session_id birincil anahtardır ve her yeni oturum açma işlemi için sırayla atanır.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

Sorgu yürütmeyi izleme

SQL havuzunda yürütülen tüm sorgular sys.dm_pdw_exec_requests günlüğe kaydedilir. Bu DMV yürütülen son 10.000 sorguyu içerir. her request_id sorguyu benzersiz olarak tanımlar ve bu DMV'nin birincil anahtarıdır. request_id her yeni sorgu için sıralı olarak atanır ve ön ekinde sorgu kimliği anlamına gelen QID bulunur. Belirli bir DMV için bu DMV sorgulanması, belirli session_id bir oturum açma için tüm sorguları gösterir.

Belirli bir sorgu için sorgu yürütme planlarını ve zamanlarını araştırmak için izleyebileceğiniz adımlar aşağıdadır.

1. Adım: Araştırmak istediğiniz sorguyu tanımlama

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

-- Find top 10 queries longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Yukarıdaki sorgu sonuçlarından, araştırmak istediğiniz sorgunun İstek Kimliği'ne dikkat edin.

Askıya alındı durumundaki sorgular, çok sayıda etkin çalışan sorgu nedeniyle kuyruğa alınabilir. Bu sorgular sys.dm_pdw_waits de görünür. Bu durumda UserConcurrencyResourceType gibi beklemeleri arayın. Eşzamanlılık sınırları hakkında bilgi için bkz . Bellek ve eşzamanlılık sınırları veya iş yükü yönetimi için kaynak sınıfları. Sorgular, nesne kilitleri gibi diğer nedenleri de bekleyebilir. Sorgunuz bir kaynak bekliyorsa, bu makalenin devamında kaynakları bekleyen sorguları araştırma bölümüne bakın.

sys.dm_pdw_exec_requests tablosundaki bir sorgunun aramasını basitleştirmek için LABEL işlevini kullanarak sorgunuza görünümde aranabilecek bir açıklama atayınsys.dm_pdw_exec_requests.

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

2. Adım: Sorgu planını araştırma

sorgunun dağıtılmış SQL (DSQL) planını sys.dm_pdw_request_steps almak için İstek Kimliğini kullanın

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

Bir DSQL planı beklenenden uzun sürdüğünde, bunun nedeni birçok DSQL adımı içeren karmaşık bir plan veya yalnızca bir adımın uzun sürmesi olabilir. Plan birkaç taşıma işlemiyle birçok adımdan oluşursa, veri taşımayı azaltmak için tablo dağıtımlarınızı iyileştirmeyi göz önünde bulundurun. Tablo dağıtımı makalesinde, sorguyu çözmek için verilerin neden taşınması gerektiği açıklanmaktadır. Makalede ayrıca veri taşımayı en aza indirmeye yönelik bazı dağıtım stratejileri açıklanmaktadır.

Tek bir adımla ilgili diğer ayrıntıları araştırmak için, uzun süre çalışan sorgu adımının sütununu inceleyin operation_type ve Adım Dizini'ne dikkat edin:

• SQL işlemleri için (OnOperation, RemoteOperation, ReturnOperation), ADIM 3 ile devam edin
• Veri Taşıma işlemleri (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation) için ADIM 4 ile devam edin.

3. Adım: Dağıtılmış veritabanlarında SQL'i araştırma

tüm dağıtılmış veritabanlarındaki sorgu adımının yürütme bilgilerini içeren sys.dm_pdw_sql_requests ayrıntılarını almak için İstek Kimliği ve Adım Dizini'ni kullanın.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

Sorgu adımı çalışırken DBCC PDW_SHOWEXECUTIONPLAN, belirli bir dağıtımda çalışan adımın SQL Server plan önbelleğinden SQL Server tahmini planını almak için kullanılabilir.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

4. Adım: Dağıtılmış veritabanlarında veri hareketini araştırma

sys.dm_pdw_dms_workers her dağıtımda çalışan bir veri taşıma adımı hakkında bilgi almak için İstek Kimliği ve Adım Dizini'ni kullanın.

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

• Belirli bir dağıtımın total_elapsed_time veri taşıma için diğerlerinden önemli ölçüde uzun sürdüğünü görmek için sütunu denetleyin.
• Uzun süre çalışan dağıtımda, bu dağıtımdan taşınan satır sayısının diğerlerinden önemli ölçüde büyük olup olmadığını görmek için sütunu denetleyin rows_processed . Bu durumda, bu bulgu temel alınan verilerinizin dengesiz olduğunu gösterebilir. Veri dengesizliği için bir neden, birçok NULL değeri olan bir sütuna (satırların tümü aynı dağıtıma eklenecek olan) dağıtmaktır. Bu tür sütunlarda dağıtım yapmaktan kaçınarak veya mümkün olduğunda NUL'leri ortadan kaldırmak için sorgunuzu filtreleyerek yavaş sorguları önleyin.

Sorgu çalışıyorsa DBCC PDW_SHOWEXECUTIONPLAN kullanarak, belirli bir dağıtımda çalışmakta olan SQL Adımı için SQL Server plan önbelleğinden SQL Server tahmini planını alabilirsiniz.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Bekleyen sorguları izleme

Sorgunuzun bir kaynağı beklediği için ilerleme kaydedemediğini fark ederseniz, burada sorgunun beklediği tüm kaynakları gösteren bir sorgu bulabilirsiniz.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

Sorgu etkin olarak başka bir sorgudaki kaynakları bekliyorsa, durum AcquireResources olur. Sorgu gerekli tüm kaynaklara sahipse, durum Verildi olur.

Tempdb'yi izleme

Veritabanı tempdb , sorgu yürütme sırasında ara sonuçları tutmak için kullanılır. Veritabanının tempdb yüksek kullanımı sorgu performansının yavaşlanmasına neden olabilir. Yapılandırılan her DW100c için 399 GB tempdb alan ayrılır (DW1000c'nin toplam tempdb alanı 3,99 TB olur). Aşağıda kullanımı izlemeye tempdb ve sorgularınızdaki kullanımı azaltmaya tempdb yönelik ipuçları yer almaktadır.

Görünümlerle tempdb'i izleme

Kullanımı izlemek tempdb için öncelikle SQL için Microsoft Toolkit havuzundan microsoft.vw_sql_requests görünümünü yükleyin. Ardından, yürütülen tüm sorguların düğüm başına kullanımını görmek tempdb için aşağıdaki sorguyu yürütebilirsiniz:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Büyük miktarda bellek tüketen bir sorgunuz varsa veya ayırmasıyla tempdbilgili bir hata iletisi aldıysanız, bunun nedeni son veri taşıma işleminde başarısız olan çok büyük BIR CREATE TABLE AS SELECT (CTAS) veya INSERT SELECT deyiminin çalışması olabilir. Bu genellikle son INSERT SELECT'in hemen öncesinde dağıtılmış sorgu planında ShuffleMove işlemi olarak tanımlanabilir. ShuffleMove işlemlerini izlemek için sys.dm_pdw_request_steps kullanın.

En yaygın risk azaltma, CTAS veya INSERT SELECT deyiminizi birden çok yük deyimine bölerek veri hacminin 100DWUc tempdb sınırı başına 399 GB'ı aşmamasıdır. Ayrıca sahip olduğunuz alanı artırmak tempdb için kümenizi daha büyük bir boyuta ölçeklendikleyebilirsiniz.

CTAS ve INSERT SELECT deyimlerine ek olarak, yetersiz bellekle çalışan büyük, karmaşık sorgular içine tempdb taşarak sorguların başarısız olmasına neden olabilir. içine taşmasını önlemek için daha büyük bir kaynak sınıfıyla çalıştırmayı tempdbgöz önünde bulundurun.

Belleği izleme

Bellek, yavaş performans ve yetersiz bellek sorunlarının kök nedeni olabilir. Sorgu yürütme sırasında SQL Server bellek kullanımının sınırlarına ulaştığını fark ederseniz veri ambarınızı ölçeklendirmeyi göz önünde bulundurun.

Aşağıdaki sorgu, düğüm başına SQL Server bellek kullanımını ve bellek baskısını döndürür:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

İşlem günlüğü boyutunu izleme

Aşağıdaki sorgu, her dağıtımda işlem günlüğü boyutunu döndürür. Günlük dosyalarından biri 160 GB'a ulaşıyorsa örneğinizin ölçeğini artırmayı veya işlem boyutunuzu sınırlamayı düşünmelisiniz.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

İşlem günlüğünü geri alma işlemini izleme

Sorgularınız başarısız oluyorsa veya devam etmek uzun sürüyorsa geri dönen işlemleriniz olup olmadığını denetleyip izleyebilirsiniz.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

PolyBase yükünü izleme

Aşağıdaki sorgu, yükünüzün ilerleme durumuyla ilgili yaklaşık bir tahmin sağlar. Sorgu yalnızca işlenmekte olan dosyaları gösterir.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Sorgu engellemelerini izleme

Aşağıdaki sorgu, ortamda en çok engellenen 500 sorguyu sağlar.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
FROM
    sys.dm_pdw_waits waiting
    INNER JOIN sys.dm_pdw_waits blocking
    ON waiting.object_type = blocking.object_type
    AND waiting.object_name = blocking.object_name
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

Bekleyen ve engelleyen sorgulardan sorgu metni alma

Aşağıdaki sorgu, kolayca sorun gidermeye yönelik bekleyen ve engelleyen sorgular için sorgu metnini ve tanımlayıcısını sağlar.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
FROM sys.dm_pdw_waits waiting
       INNER JOIN sys.dm_pdw_waits blocking
       ON waiting.object_type = blocking.object_type
       AND waiting.object_name = blocking.object_name
       INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
       ON blocking.request_id = blocking_exec_request.request_id
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

Sonraki adımlar

• DMV'ler hakkında daha fazla bilgi için bkz . Sistem görünümleri.