راقب حمل عمل تجمع SQL المخصص لـ Azure Synapse Analytics باستخدام DMVs

توضح هذه المقالة كيفية استخدام طرق عرض الإدارة الديناميكية (DMVs) لمراقبة حمل العمل الخاص بك بما في ذلك التحقيق في تنفيذ الاستعلام في تجمع SQL مخصص.

الأذونات

للاستعلام عن DMVs في هذه المقالة، تحتاج إما إلى عرض حالة قاعدة البيانات أو إذن CONTROL. عادة ما يكون منح VIEW DATABASE STATE الإذن المفضل لأنه أكثر تقييداً.

GRANT VIEW DATABASE STATE TO myuser;

مراقبة الاتصالات

يتم تسجيل جميع عمليات تسجيل الدخول إلى مستودع البيانات الخاص بك على sys.dm_pdw_exec_sessions. يحتوي DMV هذا على آخر 10000 تسجيل دخول. session_id هو المفتاح الأساسي ويتم تعيينه بالتتابع لكل تسجيل دخول جديد.

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

مراقبة تنفيذ الاستعلام

يتم تسجيل جميع الاستعلامات التي يتم تنفيذها على مجموعة SQL في sys.dm_pdw_exec_requests. يحتوي DMV هذا على آخر 10000 استعلامات تم تنفيذها. request_id يحدد كل استعلام بشكل فريد وهو المفتاح الأساسي ل DMV هذا. request_id يتم تعيين بالتتابع لكل استعلام جديد وبادئة ب QID، والذي يرمز إلى معرف الاستعلام. يظهر الاستعلام عن DMV هذا لمعطى session_id جميع الاستعلامات لتسجيل دخول معين.

ملاحظة

تستخدم الإجراءات المخزنة معرفات طلب متعددة. يتم تعيين معرفات الطلب بترتيب تسلسلي.

فيما يلي الخطوات التي يجب اتباعها للتحقيق في خطط وأوقات تنفيذ الاستعلام لاستعلام معين.

الخطوة 1: تحديد الاستعلام الذي ترغب في التحقيق فيه

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

من نتائج الاستعلام السابقة، لاحظ مُعرف الطلب من الاستعلام الذي ترغب في التحقيق فيه.

يمكن وضع الاستعلامات في الحالة Suspended في قائمة الانتظار نظراً لوجود عدد كبير من الاستعلامات قيد التشغيل النشطة. تظهر هذه الاستعلامات أيضاً في sys.dm_pdw_waits. في هذه الحالة، ابحث عن حالات انتظار مثل UserConcurrencyResourceType. للحصول على معلومات بشأن حدود التزامن، راجع حدود الذاكرة والتزامن أو فئات الموارد لإدارة حمل العمل. يمكن أن تنتظر الاستعلامات أيضاً لأسباب أخرى مثل أقفال الكائنات. إذا كان الاستعلام الخاص بك ينتظر أحد الموارد، فراجع التحقيق في الاستعلامات التي تنتظر الموارد بشكل أكبر في هذه المقالة.

لتبسيط البحث عن استعلام في الجدول sys.dm_pdw_exec_requests ، استخدم LABEL لتعيين تعليق للاستعلام، والذي يمكن البحث عنه في sys.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: التحقيق في خطة الاستعلام

استخدم «معرف الطلب» لاسترداد الاستعلامات التي تم من خلالها توزيع خطة SQL ‏(DSQL) من sys.dm_pdw_request_steps

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

عندما تستغرق خطة DSQL وقتاً أطول من المتوقع، يمكن أن يكون السبب خطة معقدة تحتوي على العديد من خطوات DSQL أو مجرد خطوة واحدة تستغرق وقتاً طويلاً. إذا كانت الخطة عبارة عن العديد من الخطوات مع العديد من عمليات النقل، ففكر في تحسين توزيعات الجدول لتقليل حركة البيانات. تشرح مقالة توزيع الجدول سبب وجوب نقل البيانات لحل استعلام. تشرح المقالة أيضاً بعض إستراتيجيات التوزيع لتقليل حركة البيانات.

للتحقيق في مزيد من التفاصيل حول خطوة واحدة، افحص operation_type عمود خطوة الاستعلام طويلة الأمد ولاحظ فهرس الخطوة:

• لعمليات SQL (OnOperation، RemoteOperation، ReturnOperation)، تابع الخطوة 3
• بالنسبة لعمليات نقل البيانات (ShuffleMoveOperation، BroadcastMoveOperation، TrimMoveOperation، PartitionMoveOperation، MoveOperation، CopyOperation)، تابع الخطوة 4.

الخطوة 3: التحقيق في SQL على قواعد البيانات الموزعة

استخدم مُعرف الطلب وفهرس الخطوة لاسترداد التفاصيل من sys.dm_pdw_sql_requests، والتي تحتوي على معلومات تنفيذ خطوة الاستعلام على جميع قواعد البيانات الموزعة.

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

عند تشغيل خطوة الاستعلام، يمكن استخدام DBCC PDW_SHOWEXECUTIONPLAN لاسترداد خطة SQL Server المقدرة من ذاكرة التخزين المؤقت لخطة SQL Server للخطوة التي تعمل على توزيع معين.

-- 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: التحقيق في حركة البيانات على قواعد البيانات الموزعة

استخدم مُعرف الطلب وفهرس الخطوة لاسترداد معلومات بشأن خطوة نقل البيانات التي تعمل على كل توزيع من sys.dm_pdw_dms_workers.

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

• total_elapsed_time تحقق من العمود لمعرفة ما إذا كان توزيع معين يستغرق وقتا أطول بكثير من الآخرين لحركة البيانات.
• بالنسبة للتوزيع طويل الأمد، تحقق من rows_processed العمود لمعرفة ما إذا كان عدد الصفوف التي يتم نقلها من هذا التوزيع أكبر بكثير من غيرها. إذا كان الأمر كذلك، فقد يشير هذا الاكتشاف إلى انحراف في بياناتك الأساسية. أحد أسباب انحراف البيانات هو التوزيع على عمود به العديد من القيم الفارغة (كل صفوفها ستهبط في نفس التوزيع). امنع الاستعلامات البطيئة عن طريق تجنب التوزيع على هذه الأنواع من الأعمدة أو تصفية الاستعلام لإزالة القيم الفارغة عندما يكون ذلك ممكناً.

إذا كان الاستعلام قيد التشغيل، يمكنك استخدام DBCC PDW_SHOWEXECUTIONPLAN لاسترداد خطة SQL Server المقدرة من ذاكرة التخزين المؤقت لخطة SQL Server لخطوة SQL قيد التشغيل حالياً ضمن توزيع معين.

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

مراقبة استعلامات الانتظار

إذا اكتشفت أن الاستعلام الخاص بك لا يحرز تقدماً لأنه ينتظر مورداً، فإليك استعلام يعرض جميع الموارد التي ينتظرها الاستعلام.

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

إذا كان الاستعلام ينتظر الموارد من استعلام آخر بنشاط، فستكون الحالة AcquireResources. إذا كان الاستعلام يحتوي على جميع الموارد المطلوبة، فسيتم منح الحالة.

مراقبة tempdb

tempdb يتم استخدام قاعدة البيانات للاحتفاظ بنتائج وسيطة أثناء تنفيذ الاستعلام. يمكن أن يؤدي الاستخدام العالي لقاعدة tempdb البيانات إلى بطء أداء الاستعلام. لكل DW100c تم تكوينه، يتم تخصيص 399 غيغابايت من tempdb المساحة (سيكون DW1000c 3.99 تيرابايت من إجمالي tempdb المساحة). فيما يلي تلميحات لمراقبة tempdb الاستخدام وتقليل tempdb الاستخدام في استعلاماتك.

مراقبة tempdb مع طرق العرض

لمراقبة tempdb الاستخدام، قم أولا بتثبيت طريقة العرض microsoft.vw_sql_requests من مجموعة أدوات Microsoft لتجمع SQL. يمكنك بعد ذلك تنفيذ الاستعلام التالي لمشاهدة الاستخدام لكل عقدة tempdb لجميع الاستعلامات المنفذة:

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

ملاحظة

تستخدم tempdbحركة البيانات . لتقليل استخدام أثناء حركة البيانات، تأكد من tempdb أن الجدول يستخدم استراتيجية توزيع توزع البيانات بالتساوي. استخدم Azure Synapse SQL Distribution Advisor للحصول على توصيات حول طريقة إلغاء الفرز المناسبة لأحمال العمل الخاصة بك. استخدم مجموعة أدوات Azure Synapse للمراقبة tempdb باستخدام استعلامات T-SQL.

إذا كان لديك استعلام يستهلك كمية كبيرة من الذاكرة أو تلقيت رسالة خطأ تتعلق بتخصيص tempdb، فقد يكون ذلك بسبب عبارة CREATE TABLE AS SELECT (CTAS) أو INSERT SELECT كبيرة جدا تعمل بالفشل في عملية حركة البيانات النهائية. يمكن تعريف هذا عادة على أنه عملية ShuffleMove في خطة الاستعلام الموزعة مباشرة قبل INSERT SELECT النهائي. استخدم sys.dm_pdw_request_steps لمراقبة عمليات ShuffleMove.

التخفيف الأكثر شيوعا هو تقسيم عبارة CTAS أو INSERT SELECT إلى عبارات تحميل متعددة بحيث لا يتجاوز حجم البيانات حد 399 غيغابايت لكل 100DWUc tempdb . يمكنك أيضا توسيع نطاق نظام المجموعة الخاص بك إلى حجم أكبر لزيادة مقدار tempdb المساحة لديك.

بالإضافة إلى عبارات CTAS وINSERT SELECT، يمكن أن تمتد الاستعلامات الكبيرة والمعقدة التي تعمل بذاكرة غير كافية إلى التسبب في tempdb فشل الاستعلامات. ضع في اعتبارك التشغيل مع فئة موارد أكبر لتجنب الامتداد إلى tempdb.

مراقبة الذاكرة

يمكن أن تكون الذاكرة السبب الجذري لبطء الأداء ومشاكل نفاد الذاكرة. ضع في اعتبارك تحجيم مستودع البيانات إذا وجدت أن استخدام الذاكرة SQL Server يصل إلى حدوده خلال تنفيذ الاستعلام.

يرجع الاستعلام التالي SQL Server استخدام الذاكرة وضغط الذاكرة لكل عقدة:

-- 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)'

مراقبة حجم سجل المعاملات

يقوم الاستعلام التالي بإرجاع حجم سجل المعاملات على كل توزيع. إذا كان أحد ملفات السجل يصل إلى 160 جيجابايت، فيجب أن تفكر في توسيع نطاق المثيل أو الحد من حجم المعاملة الخاصة بك.

-- 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)'

مراقبة التراجع عن سجل المعاملات

إذا فشلت الاستعلامات الخاصة بك أو استغرقت وقتاً طويلاً للمتابعة، يمكنك التحقق والمراقبة إذا كانت لديك أي معاملات تتراجع.

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

يوفر الاستعلام التالي تقديرا تقريبيا لتقدم التحميل الخاص بك. يعرض الاستعلام فقط الملفات التي تتم معالجتها حالياً.

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

مراقبة حظر الاستعلام

يوفر الاستعلام التالي أفضل 500 استعلام محظور في البيئة.

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

استرداد نص الاستعلام من الاستعلامات قيد الانتظار والحظر

يوفر الاستعلام التالي نص الاستعلام ومعرفه للاستعلامات قيد الانتظار والحظر لاستكشاف الأخطاء وإصلاحها بسهولة.

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

الخطوات التالية

• لمزيد من المعلومات بشأن DMVs، راجع طرق عرض النظام.