مشاركة عبر

استكشاف مشكلات الذاكرة وإصلاحها باستخدام قاعدة بيانات Azure SQL

  • مقالة

ينطبق على: قاعدة بيانات Azure SQL

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

ملاحظة

تُركز هذه المقالة على قاعدة بياناتAzure SQL. لمزيد من المعلومات حول استكشاف مشكلات الذاكرة وإصلاحها في SQL Server، راجع MSSQLSERVER_701.

جرب سبل التحقيق التالية استجابة إلى:

  • رمز الخطأ 701 المقترن برسالة خطأ "There is insufficient system memory in resource pool '%ls' to run this query"
  • رمز الخطأ 802 المقترن برسالة خطأ "There is insufficient memory available in the buffer pool"

عرض أحداث خارج الذاكرة

إذا واجهت أخطاء نفاد الذاكرة، فراجع sys.dm_os_out_of_memory_events. تم تقديم هذا العرض في يناير 2022، ويتضمن معلومات متنبأ بها بسبب نفاد الذاكرة يتم تحديدها بواسطة خوارزمية إرشادية ويتم توفيرها بدرجة محدودة من الثقة.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

التحقيق في تخصيص الذاكرة

إذا استمر ظهور أخطاء نفاد الذاكرة في قاعدة بيانات Azure SQL، ففكر على الأقل في زيادة هدف مستوى الخدمة لقاعدة البيانات في مدخل Azure مؤقتاً. إذا استمر ظهور أخطاء نفاذ الذاكرة، فاستخدم الاستعلامات التالية للبحث عن حالات غريبة من التخصيص المرتفع من ذاكرة الاستعلام، والتي قد تساهم في تحقق حالة عدم كفاية الذاكرة. قم بتشغيل الاستعلامات من المثال التالي في قاعدة البيانات التي تعاني من المشكلة (ليس في master قاعدة بيانات الخادم المنطقي الخاص بـ Azure SQL).

استخدم DMV لعرض أحداث خارج الذاكرة

بدءاً من أبريل 2022، تمت إضافة طريقة عرض الإدارة الديناميكية الجديدة (DMV) للسماح برؤية الأحداث وأسباب أحداث نفاد الذاكرة (OOM) في Azure SQL Databasesys.dm_os_out_of_memory_events. لمزيد من المعلومات، راجع sys.dm_os_out_of_memory_events.

استخدام DMVs لعرض مخصص الذاكرة

ابدأ بتحقيق واسع النطاق، إذا شهدت مؤخراً خطأ نفاد الذاكرة، بعرض تخصيص لوحدات مخصص الذاكرة. مخصصات الذاكرة هي ميزة داخلية في مشغل قاعدة بيانات Azure SQL. قد تكون أكبر مخصصات ذاكرة من حيث الصفحات المخصصة لها مفيدة في تحديد نوع الاستعلام أو ميزة SQL Server التي تستهلك معظم الذاكرة.

SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY pages_kb DESC;
GO
SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY virtual_memory_committed_kb DESC;
  • بعض أشهر مخصصات الذاكرة، مثل MEMORYCLERK_SQLQERESERVATIONS، يتم حلها بشكل أفضل من خلال تحديد الاستعلامات المستحوذة على أكبر قدر من تخصيص الذاكرة وتحسين أدائها مع فهرسة أفضل وضبط الفهرس.
  • وبينما لا تكون هناك أي علاقة بين OBJECTSTORE_LOCK_MANAGER بتخصيص الذاكرة، فإنه من المتوقع أن تكون عالية عندما تطالب الاستعلامات بالعديد من التأمينات، على سبيل المثال، بسبب تعطيل تصاعد التأمين أو تنفيذ معاملات كبيرة جداً.
  • ومن المتوقع أن تحقق بعض المخصصات أعلى استهلاك: حيث يكون MEMORYCLERK_SQLBUFFERPOOL غالباً هو أعلى مخصص ذاكرة دائماً، بينما يكون CACHESTORE_COLUMNSTOREOBJECTPOOL مرتفعاً عند استخدام مؤشرات تخزين الأعمدة. ومن المتوقع أن تحقق تلك المخصصات أعلى استهلاكاً.

لمزيد من المعلومات حول أنواع مخصصات الذاكرة، راجع sys.dm_os_memory_clerks.

استخدام DMVs للتحقيق في الاستعلامات النشطة

في معظم الحالات، لا يكون الاستعلام الفاشل سبباً في هذا الخطأ.

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

--Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;

قد تقرر استخدام عبارة KILL لإيقاف الاستعلام الجاري تنفيذه حالياً والذي يحجز أو يكون في انتظار تخصيص ذاكرة ضخم. استخدم هذه العبارة بعناية، خاصة عند تشغيل العمليات المهمة للأعمال. للحصول على مزيدٍ من المعلومات، راجع KILL (Transact-SQL).

استخدام مخزن الاستعلام للتحقيق في الاستخدام السابق لذاكرة الاستعلام

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

يعود نموذج الاستعلام التالي في قاعدة بيانات Azure SQL بمعلومات مهمة عن عمليات تنفيذ الاستعلام المسجلة في مخزن الاستعلام. استهداف أعلى الاستعلامات استهلاكاً التي تم تحديدها لفحصها وضبط أدائها، وتقييم ما إذا كانت تنفذ على النحو المنشود أم لا. لاحظ عامل تصفية الوقت في qsp.last_execution_time لتقييد النتائج على الحديث منها فقط. يمكنك ضبط جملة TOP للحصول على نتائج أكثر أو أقل اعتمادا على البيئة الخاصة بك.

SELECT TOP 10 PERCENT --limit results
  a.plan_id, query_id, plan_group_id, query_sql_text
, query_plan = TRY_CAST(query_plan as XML)
, avg_query_max_used_memory
, min_query_max_used_memory
, max_query_max_used_memory
, last_query_max_used_memory 
, last_execution_time  
, query_count_executions
    FROM (
    SELECT 
      qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    , last_execution_time = MAX(qsp.last_execution_time)
    , query_count_executions = SUM(qsrs.count_executions) 
    , avg_query_max_used_memory = AVG(qsrs.avg_query_max_used_memory)
    , min_query_max_used_memory = MIN(qsrs.min_query_max_used_memory)
    , max_query_max_used_memory = MAX(qsrs.max_query_max_used_memory)
    , last_query_max_used_memory = MAX(qsrs_latest.last_query_max_used_memory) --only from latest result
    FROM sys.query_store_plan AS qsp 
    INNER JOIN sys.query_store_query AS qsq
        ON qsp.query_id = qsq.query_id
    INNER JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id 
    INNER JOIN sys.query_store_runtime_stats AS qsrs
        ON qsp.plan_id = qsrs.plan_id 
    INNER JOIN (SELECT plan_id
            , last_query_max_used_memory 
            , rownum = ROW_NUMBER() OVER (PARTITION BY plan_id ORDER BY last_execution_time DESC)
            FROM sys.query_store_runtime_stats qsrs) AS qsrs_latest
        ON qsrs_latest.plan_id = qsp.plan_id
        AND qsrs_latest.rownum = 1 --use latest last_query_max_used_memory per plan_id
    WHERE DATEADD(hour, -24, sysdatetime()) < qsp.last_execution_time --past 24 hours only
    AND qsrs_latest.last_query_max_used_memory > 0
    GROUP BY qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    ) AS a
ORDER BY max_query_max_used_memory DESC, avg_query_max_used_memory DESC;

الأحداث الممتدة

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

هناك طريقتان لالتقاط التتبع في SQL Server؛ وهما الأحداث الممتدة (XEvents) وتتبع أثار ملفات التعريف. ومع ذلك، فإن SQL Server Profiler هو تقنية تتبع قديمة، لا تدعمها قاعدة بَيانات SQL Azure. تُعد الأحداث الممتدة تقنية تتبع حديثة تسمح بمزيد من التنوع ولها تأثير منخفض على النظام الخاضع للملاحظة، وتم دَمج واجهته في Management Studio (SSMS). لمزيد من المعلومات حول الاستعلام عن الأحداث الممتدة في Azure SQL Database، راجع الأحداث الممتدة في Azure SQL Database.

راجع المستند الذي يشرح كيفية استخدام Extended Events New Session Wizard في Management Studio. وبالنسبة لقواعد بيانات SQL Azure، يوفر SSMS مجلد فرعي للأحداث الممتدة تحت كل قاعدة بيانات في مستكشف عناصر SQL Server. استخدم جلسة عمل أحداث ممتدة لالتقاط هذه الأحداث المفيدة، وتحديد الاستعلامات التي تم إنشاؤها:

  • أخطاء الفئة:

    • error_reported
    • exchange_spill
    • hash_spill_details

  • تنفيذ الفئة:

    • excessive_non_grant_memory_used

  • ذاكرة الفئة:

    • query_memory_grant_blocking
    • query_memory_grant_usage

قد يشكل التقاط عناصر تخصيص الذاكرة أو تمديد تخصيص الذاكرة أو التخصيص الزائد للذاكرة دليلاً محتملاً للاستعلام الذي يستنزف فجأة ذاكرة أكثر مما كان عليه في الماضي، وتفسيراً محتملاً لخطأ ناشئ في الذاكرة في حمل عمل موجود.

نفاذ ذاكرة OLTP الداخلية بالذاكرة

قد تواجه Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation إذا كنت تستخدم In-Memory OLTP. تقليل مقدار البيانات في جداول محسنة للذاكرة والمعلمات التي تم تحسينها من قبل الذاكرة وقيمة الجدول، أو قم بتحجيم قاعدة البيانات إلى هدف خدمة أعلى للحصول على ذاكرة أكثر. للمزيد من المعلومات حول مشكلات نفاد الذاكرة في In-Memory OLTP لخادم SQL Server، راجع حل مشكلات نفاد الذاكرة.

الحصول على دعم قاعدة بيانات Azure SQL

إذا استمرت أخطاء الذاكرة في قاعدة بيانات Azure SQL، فقم بتقديم طلب دعم Azure عن طريق تحديد Get Support على موقع دعم Azure.

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