تحديد خطط الاستعلام الإشكالية

مكتمل

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

دعونا نستكشف كل من هذه السيناريوهات بمزيد من التفصيل.

قيود مكونات الأجهزة

لا تظهر قيود الأجهزة عادة أثناء تنفيذ استعلام واحد ولكنها تصبح واضحة ضمن حمل الإنتاج عندما تكون مؤشرات ترابط وحدة المعالجة المركزية والذاكرة محدودة. يمكن الكشف عن خلاف وحدة المعالجة المركزية من خلال مراقبة عداد مراقبة الأداء "% وقت المعالج"، والذي يقيس استخدام وحدة المعالجة المركزية للخادم. في SQL Server، يمكن أن تشير أنواع انتظار SOS_SCHEDULER_YIELDوCXPACKET إلى ضغط وحدة المعالجة المركزية. يمكن أن يؤدي ضعف أداء نظام التخزين إلى إبطاء حتى عمليات تنفيذ الاستعلام الفردي المحسنة. يتم تعقب أداء التخزين بشكل أفضل على مستوى نظام التشغيل باستخدام عدادات Disk Seconds/Read مراقبة الأداء و Disk Seconds/Write، والتي تقيس أوقات إكمال عملية الإدخال/الإخراج. يسجل SQL Server أداء تخزين ضعيفا إذا استغرق الإدخال/الإخراج أكثر من 15 ثانية. يمكن أن تشير الانتظارات PAGEIOLATCH_SH العالية في SQL Server إلى مشكلات في أداء التخزين. عادة ما يتم تقييم أداء الأجهزة في وقت مبكر من عملية استكشاف الأخطاء وإصلاحها نظرا لسهولة تقييمها.

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

بنيات الاستعلام دون المستوى الأمثل

تؤدي قواعد البيانات الارتباطية أفضل أداء عند تنفيذ العمليات المستندة إلى المجموعة، والتي تعالج البيانات (INSERTو UPDATEDELETEو وSELECT) في مجموعات، ما ينتج إما قيمة واحدة أو مجموعة نتائج. البديل هو المعالجة المستندة إلى الصف، باستخدام المؤشرات أو التكرارات الحلقية أثناء، ما يزيد التكلفة خطيا مع عدد الصفوف المتأثرة - وهو مقياس إشكالي مع نمو وحدات تخزين البيانات.

يعد الكشف عن الاستخدام دون المستوى الأمثل للعمليات المستندة إلى الصف مع المؤشرات أو حلقات WHILE أمرا مهما، ولكن هناك أنماط أخرى مضادة ل SQL Server يجب التعرف عليها. تسببت الدالات ذات القيمة الجدولية (TVFs)، وخاصة الملفات التلفزيونية متعددة العبارات، في أنماط خطة التنفيذ الإشكالية قبل SQL Server 2017. غالبا ما يستخدم المطورون ملفات TVF متعددة العبارات لتنفيذ استعلامات متعددة داخل دالة واحدة وتجميع النتائج في جدول واحد. ومع ذلك، يمكن أن يؤدي استخدام TVFs إلى عقوبات الأداء.

يحتوي SQL Server على نوعين من TVFs: المضمنة ومتعددة العبارات. يتم التعامل مع الملفات التلفزيونية المضمنة مثل طرق العرض، بينما يتم التعامل مع الملفات التلفزيونية متعددة العبارات مثل الجداول أثناء معالجة الاستعلام. نظرا لأن TVFs ديناميكية وتفتقر إلى الإحصائيات، يستخدم SQL Server عدد صفوف ثابت لتقدير تكلفة خطة الاستعلام. يمكن أن يكون هذا جيدا لعدد الصفوف الصغيرة، ولكنه غير فعال لآلاف الصفوف أو ملايين الصفوف.

وهناك نمط آخر مضاد هو استخدام الدوال العددية، التي لها مشاكل مماثلة في التقدير والتنفيذ. قامت Microsoft بإجراء تحسينات كبيرة على الأداء باستخدام معالجة الاستعلام الذكي، ضمن مستويات التوافق 140 و150.

قابلية SARGability

يشير مصطلح SARGable في قواعد البيانات الارتباطية إلى دالة تقييم (WHERE عبارة) منسقة لاستخدام فهرس لتسريع تنفيذ الاستعلام. تسمى دالات التقييم بالتنسيق الصحيح "Search Arguments" أو SARGs. في SQL Server، يعني استخدام SARG أن المحسن يقيم باستخدام فهرس غير متفاوت المسافات على العمود المشار إليه في SARG لعملية SEEK ، بدلا من مسح الفهرس أو الجدول بأكمله لاسترداد قيمة.

لا يضمن وجود SARG استخدام مؤشر SEEK. لا يزال بإمكان خوارزميات التكلفة الخاصة بالمحسن تحديد أن الفهرس مكلف للغاية، خاصة إذا كان SARG يشير إلى نسبة كبيرة من الصفوف في جدول. يعني عدم وجود SARG أن المحسن لن يقيم SEEK على فهرس غير متفاوت المسافات.

تتضمن أمثلة التعبيرات غير القابلة ل SARGable تلك التي تحتوي على LIKE عبارة باستخدام حرف بدل في بداية السلسلة، مثل WHERE lastName LIKE '%SMITH%'. تحدث دالات التقييم الأخرى غير القابلة ل SARGable عند استخدام الدالات على عمود، مثل WHERE CONVERT(CHAR(10), CreateDate,121) = '2020-03-22'. يتم تحديد هذه الاستعلامات عادة عن طريق فحص خطط التنفيذ لمسح الفهرس أو الجدول حيث يجب أن تحدث عمليات البحث بخلاف ذلك.

يوجد فهرس في عمود المدينة يتم استخدامه في WHERE عبارة الاستعلام وأثناء استخدامه في خطة التنفيذ هذه أعلاه، يمكنك أن ترى أن الفهرس يتم مسحه ضوئيا، مما يعني أن الفهرس بأكمله قيد القراءة. الوظيفة LEFT في المسند تجعل هذا التعبير غير قابل للسرعة. لن يتم تقييم المحسن باستخدام البحث عن الفهرس في الفهرس في عمود المدينة .

يمكن كتابة هذا الاستعلام لاستخدام دالة تقييم قابلة ل SARGable. سيقوم المحسن بعد ذلك بتقييم SEEK على الفهرس في عمود المدينة . في هذه الحالة، سيقرأ عامل تشغيل البحث عن الفهرس مجموعة أصغر من الصفوف.

يؤدي تغيير LEFT الدالة إلى إلى LIKE بحث فهرس.

ملاحظة

LIKE الكلمة الأساسية، في هذا المثال، لا تحتوي على حرف بدل على اليسار، لذلك فهي تبحث عن المدن التي تبدأ ب M. إذا كانت "ذات وجهين" أو بدأت بحرف بدل ('%M%' أو '%M') فسيكون غير قابل للسارغ. تقدر عملية البحث بإرجاع 1267 صفا، أو ما يقرب من 15% من التقدير للاستعلام مع دالة التقييم غير القابلة لل SARGable.

تتعامل بعض الأنماط المضادة لتطوير قاعدة البيانات الأخرى مع قاعدة البيانات كخدمة بدلا من مخزن بيانات. يمكن أن يؤدي استخدام قاعدة بيانات لتحويل البيانات إلى JSON أو معالجة السلاسل أو إجراء عمليات حسابية معقدة إلى الاستخدام المفرط لوحدة المعالجة المركزية وزيادة زمن الانتقال. يمكن أن تؤدي الاستعلامات التي تحاول استرداد جميع السجلات ثم إجراء الحسابات في قاعدة البيانات إلى استخدام الإدخال/ الإخراج والمعالج بشكل مفرط. من الناحية المثالية، يجب استخدام قاعدة البيانات لعمليات الوصول إلى البيانات وبنيات قاعدة البيانات المحسنة مثل التجميع.

الفهارس المفقودة

تنشأ مشاكل الأداء الأكثر شيوعا لمسؤولي قاعدة البيانات من عدم وجود فهارس مفيدة، ما يتسبب في قراءة المحرك لصفحات أكثر مما هو ضروري لإرجاع نتائج الاستعلام. بينما تستهلك الفهارس الموارد (تؤثر على أداء الكتابة واستهلاك المساحة)، فإن مكاسب الأداء الخاصة بها غالبا ما تفوق تكاليف الموارد الإضافية. يمكن تحديد خطط التنفيذ مع هذه المشكلات بواسطة عامل تشغيل الاستعلام Clustered Index Scan أو مزيج من البحث عن فهرس غير متفاوت المسافات والبحث عن المفتاح، مما يشير إلى الأعمدة المفقودة في فهرس موجود.

يساعد محرك قاعدة البيانات من خلال الإبلاغ عن الفهارس المفقودة في خطط التنفيذ. تتوفر أسماء الفهارس الموصى بها وتفاصيلها من خلال طريقة عرض sys.dm_db_missing_index_detailsالإدارة الديناميكية . تشبه sys.dm_db_index_usage_stats طرق عرض الإدارة الديناميكية الأخرى وتسلط sys.dm_db_index_operational_stats الضوء على استخدام الفهارس الموجودة.

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

الإحصائيات المفقودة وغير المحدثة

يعد فهم أهمية إحصائيات العمود والفهرس لمحسن الاستعلام أمرا بالغ الأهمية. من الضروري أيضا التعرف على الشروط التي يمكن أن تؤدي إلى إحصائيات قديمة وكيف يمكن أن تظهر هذه المشكلة في SQL Server. تقدم Azure SQL افتراضيا تعيين إحصائيات التحديث التلقائي إلى ON. قبل SQL Server 2016، كان السلوك الافتراضي لإحصائيات التحديث التلقائي هو عدم تحديث الإحصائيات حتى يساوي عدد التعديلات على الأعمدة في الفهرس حوالي 20% من عدد الصفوف في جدول. قد يؤدي هذا السلوك إلى تعديلات كبيرة في البيانات تغير أداء الاستعلام دون تحديث الإحصائيات، مما يؤدي إلى خطط دون المستوى الأمثل استنادا إلى إحصائيات قديمة.

قبل SQL Server 2016، يمكن استخدام علامة التتبع 2371 لتغيير العدد المطلوب من التعديلات على قيمة ديناميكية، لذلك كلما زاد الجدول، انخفضت النسبة المئوية لتعديلات الصفوف اللازمة لتشغيل تحديث الإحصائيات. تدعم الإصدارات الأحدث من SQL Server وقاعدة بيانات Azure SQL ومثيل Azure SQL المدار هذا السلوك بشكل افتراضي. تظهر دالة sys.dm_db_stats_properties الإدارة الديناميكية آخر مرة تم فيها تحديث الإحصائيات وعدد التعديلات منذ التحديث الأخير، مما يسمح لك بتحديد الإحصائيات التي قد تحتاج إلى تحديثات يدوية بسرعة.

خيارات محسن ضعيفة

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

في المثال أدناه من قاعدة بيانات AdventureWorks2017 ، يتم استخدام تلميح استعلام لإخبار محسن قاعدة البيانات باستخدام اسم مدينة سياتل دائما. لن يضمن هذا التلميح أفضل خطة تنفيذ لجميع قيم المدينة، ولكنه يمكن التنبؤ به. سيتم استخدام قيمة "سياتل" ل @city_name فقط أثناء التحسين. أثناء التنفيذ، يتم استخدام القيمة (‘Ascheim’) الفعلية المتوفرة.

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

كما هو الحال في المثال، يستخدم الاستعلام تلميحا (عبارة #D0) لإخبار المحسن باستخدام قيمة متغيرة معينة لإنشاء خطة التنفيذ الخاصة به.

شم المعلمة

يقوم SQL Server بذاكرة التخزين المؤقت لخطط تنفيذ الاستعلام لاستخدامها في المستقبل. نظرا لأن عملية استرداد خطة التنفيذ تستند إلى قيمة تجزئة استعلام، يجب أن يكون نص الاستعلام متطابقا لكل تنفيذ للاستعلام للخطة المخزنة مؤقتا لاستخدامها. لدعم قيم متعددة في نفس الاستعلام، يستخدم العديد من المطورين المعلمات، التي تم تمريرها من خلال الإجراءات المخزنة، كما هو ملاحظ في المثال التالي:

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

يمكن أيضا تحديد معلمات الاستعلامات بشكل صريح باستخدام الإجراء sp_executesql. ومع ذلك، يتم وضع معلمات صريحة للاستعلامات الفردية من خلال التطبيق مع شكل ما (اعتمادا على واجهة برمجة التطبيقات) من PREPARE و EXECUTE. عندما ينفذ محرك قاعدة البيانات هذا الاستعلام لأول مرة، فإنه يحسن الاستعلام استنادا إلى القيمة الأولية للمعلمة، في هذه الحالة، 42. يسمح هذا السلوك، المسمى بشم المعلمة، بتقليص حمل العمل الإجمالي لاستعلامات التحويل البرمجي على الخادم. ومع ذلك، إذا كان هناك انحراف في البيانات، فقد يختلف أداء الاستعلام بشكل كبير.

على سبيل المثال، يحتوي الجدول الذي يحتوي على 10 ملايين سجل، و99% من هذه السجلات على معرف 1، أما% 1 الأخرى فهي أرقام فريدة، ويستند الأداء إلى المعرف الذي تم استخدامه في البداية لتحسين الاستعلام. يشير هذا الأداء المتقلب بشدة إلى انحراف البيانات وليس مشكلة متأصلة في شم المعلمة. هذا السلوك هو مشكلة أداء شائعة إلى حد ما يجب أن تكون على علم بها. يجب أن تفهم خيارات التخفيف من حدة المشكلة. هناك بعض الطرق لمعالجة هذه المشكلة، ولكن كل منها يأتي بمفاضلات:

• استخدم التلميح RECOMPILE في الاستعلام الخاص بك، أو WITH RECOMPILE خيار التنفيذ في الإجراءات المخزنة. يؤدي هذا التلميح إلى إعادة التحويل البرمجي للاستعلام أو الإجراء في كل مرة يتم تنفيذها، ما سيزيد من استخدام وحدة المعالجة المركزية على الخادم ولكنه سيستخدم دائما قيمة المعلمة الحالية.
• يمكنك استخدام تلميح الاستعلام #D0. يؤدي هذا التلميح إلى اختيار المحسن عدم التعرف على المعلمات ومقارنة القيمة مع المدرج التكراري لبيانات العمود. لن يحصل هذا الخيار على أفضل خطة ممكنة ولكنه سيسمح بخطة تنفيذ متسقة.
• أعد كتابة الإجراء أو الاستعلامات عن طريق إضافة منطق حول قيم المعلمات إلى RECOMPILE فقط للمعلمات المزعجة المعروفة. في المثال أدناه، إذا كانت المعلمة SalesPersonID NULL، يتم تنفيذ الاستعلام باستخدام OPTION (RECOMPILE).

CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

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