كيفية ضبط Microsoft SQL Server للأداء

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

سأصف في هذه المقالة إجراءً خطوة بخطوة لضبط الأداء الأساسي على أحد خوادم قواعد البيانات الأعلى في السوق: Microsoft SQL Server (اختصارًا SQL Server).

# 1 العثور على الجناة

كما هو الحال مع أي برنامج آخر ، نحتاج إلى فهم أن SQL Server هو برنامج كمبيوتر معقد. إذا كانت لدينا مشكلة معها ، فنحن بحاجة إلى اكتشاف سبب عدم تشغيلها كما نتوقع.

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

• إعدادات الأجهزة والتثبيت ، التي قد تحتاج إلى تصحيح لأن احتياجات SQL Server محددة
• إذا قدمنا ​​كود T-SQL الصحيح لتطبيق SQL Server

على الرغم من أن SQL Server هو برنامج مملوك ، فقد قدمت Microsoft الكثير من الطرق لفهمه واستخدامه بكفاءة.

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

يمكن حساب إحصائيات الانتظار باستخدام sys.dm_os_wait_stats طريقة عرض الإدارة الديناميكية (DMV) ، والتي توفر معلومات إضافية حول حالتها الحالية. هناك العديد من البرامج النصية على الإنترنت للاستعلام عن هذا العرض ، لكن المفضل لدي هو نص Paul Randal لأنه سهل الفهم ولديه جميع المعلمات المهمة لمراقبة إحصائيات الانتظار:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

عندما ننفذ هذا البرنامج النصي ، نحتاج إلى التركيز على الصفوف العلوية للنتيجة لأنه تم تعيينها أولاً وتمثل الحد الأقصى لنوع الانتظار.

نحتاج إلى فهم أنواع الانتظار حتى نتمكن من اتخاذ القرارات الصحيحة. للتعرف على أنواع الانتظار المختلفة ، يمكننا الانتقال إلى وثائق Microsoft الممتازة.

لنأخذ مثالاً حيث لدينا الكثير من PAGEIOLATCH_XX . هذا يعني أن الخيط ينتظر قراءة صفحة البيانات من القرص إلى المخزن المؤقت ، وهو ليس سوى كتلة ذاكرة. يجب أن نتأكد من أننا نفهم ما يحدث. هذا لا يعني بالضرورة ضعف نظام الإدخال / الإخراج الفرعي أو عدم وجود ذاكرة كافية ، وزيادة الذاكرة والنظام الفرعي للإدخال / الإخراج ستحل المشكلة ، ولكن بشكل مؤقت فقط. للعثور على حل دائم ، نحتاج إلى معرفة سبب قراءة الكثير من البيانات من القرص: ما أنواع أوامر SQL التي تسبب ذلك؟ هل نقرأ الكثير من البيانات بدلاً من قراءة بيانات أقل باستخدام عوامل التصفية ، مثل جمل where ؟ هل يحدث عدد كبير جدًا من قراءات البيانات بسبب عمليات مسح الجدول أو عمليات مسح الفهرس؟ هل يمكننا تحويلها إلى طلبات فهرسة من خلال تنفيذ أو تعديل الفهارس الموجودة؟ هل نكتب استعلامات SQL يساء فهمها بواسطة SQL Optimizer (برنامج آخر داخل برنامج خادم SQL الخاص بنا)؟

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

# 2 العثور على استفسارات إشكالية

كما ذكرنا أعلاه ، فإن أول شيء يمكننا القيام به هو البحث عن الاستعلامات التي بها مشكلات. سيجد رمز T-SQL التالي أسوأ 20 استعلامًا أداءً:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

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

# 3 ضبط الاستعلامات

يعد ضبط استعلام T-SQL مفهومًا مهمًا. الشيء الأساسي الذي يجب فهمه هو مدى قدرتنا على كتابة استعلامات T-SQL وتنفيذ الفهارس ، حتى يتمكن مُحسِّن SQL من العثور على خطة مُحسَّنة للقيام بما أردنا القيام به. مع كل إصدار جديد من SQL Server ، نحصل على مُحسِّن أكثر تعقيدًا يغطي أخطائنا في كتابة استعلامات SQL غير المحسّنة ، كما سيصلح أي أخطاء متعلقة بالمُحسِّن السابق. ولكن ، بغض النظر عن مدى ذكاء المُحسِّن ، إذا لم نتمكن من إخباره بما نريد (من خلال كتابة استعلامات T-SQL مناسبة) ، فلن يتمكن مُحسِّن SQL من القيام بعمله.

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

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

لا يمكنني تغطية جميع جوانب خطة التنفيذ هنا ، ولكن على المستوى الأساسي يمكنني شرح الأشياء التي نحتاج إلى أخذها في الاعتبار.

• نحتاج أولاً إلى معرفة المشغلين الذين يتحملون معظم تكلفة الاستعلام.
• إذا كان المشغل يتحمل الكثير من التكلفة ، فنحن بحاجة لمعرفة سبب ذلك. في معظم الأحيان ، ستتطلب عمليات الفحص تكلفة أكثر مما تطلبه. نحتاج إلى فحص سبب إجراء فحص معين (فحص الجدول أو مسح الفهرس) بدلاً من البحث عن فهرس. يمكننا حل هذه المشكلة عن طريق تطبيق الفهارس المناسبة على أعمدة الجدول ، ولكن كما هو الحال مع أي برنامج معقد ، لا يوجد حل ثابت. على سبيل المثال ، إذا كان الجدول صغيرًا ، فإن عمليات الفحص تكون أسرع من عمليات البحث.
• هناك ما يقرب من 78 عاملًا ، يمثلون الإجراءات والقرارات المختلفة لخطة تنفيذ SQL Server. نحتاج إلى دراستها بعمق من خلال الرجوع إلى وثائق Microsoft ، حتى نتمكن من فهمها بشكل أفضل واتخاذ الإجراءات المناسبة.

# 4 إعادة استخدام خطة التنفيذ

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

يمكننا استخدام الاستعلام أدناه لمعرفة عدد مرات إعادة استخدام خطة التنفيذ ، حيث usecounts عدد مرات إعادة استخدام الخطة:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

أفضل طريقة لإعادة استخدام خطة التنفيذ هي تنفيذ الإجراءات المخزنة ذات المعلمات. عندما لا نكون في وضع يسمح لنا بتنفيذ الإجراءات المخزنة ، يمكننا استخدام sp_executesql ، والتي يمكن استخدامها بدلاً من ذلك لتنفيذ عبارات T-SQL عندما يكون التغيير الوحيد في عبارات SQL هو قيم المعلمات. على الأرجح سيعيد SQL Server استخدام خطة التنفيذ التي أنشأها في التنفيذ الأول.

مرة أخرى ، كما هو الحال مع أي برنامج كمبيوتر معقد ، لا يوجد حل ثابت. في بعض الأحيان يكون من الأفضل تجميع الخطة مرة أخرى.

دعنا نفحص المثالين التاليين من طلبات البحث:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

لنفترض أن لدينا فهرسًا غير متفاوت في عمود name ونصف الجدول يحتوي على قيمة sri وعدد قليل من الصفوف لها pal في عمود name . بالنسبة للاستعلام الأول ، سيستخدم SQL Server فحص الجدول لأن نصف الجدول له نفس القيم. ولكن بالنسبة للاستعلام الثاني ، من الأفضل استخدام فحص الفهرس لأن عددًا قليلاً فقط من الصفوف لها قيمة pal .

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

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

• عندما تتغير الفهارس المستخدمة بواسطة الاستعلام أو يتم إسقاطها
• عندما تتغير إحصائيات أو هيكل أو مخطط الجدول المستخدم بواسطة الاستعلام
• عندما نستخدم خيار "إعادة الترجمة"
• عندما يكون هناك عدد كبير من عمليات الإدراج أو التحديثات أو الحذف
• عندما نمزج DDL و DML في استعلام واحد

# 5 إزالة الفهارس غير الضرورية

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

يوفر لنا خادم SQL dm_db_index_usage_stats DMV للعثور على إحصائيات الفهرس. عندما نقوم بتشغيل كود T-SQL أدناه ، نحصل على إحصائيات الاستخدام لفهارس مختلفة. إذا وجدنا فهارس لم يتم استخدامها على الإطلاق ، أو نادرًا ما يتم استخدامها ، فيمكننا إسقاطها للحصول على الأداء.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

# 6 تثبيت خادم SQL وإعداد قاعدة البيانات

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

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

# 7 لا تفرط في تحميل خادم SQL

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

• إنتاج
• التطور
• اختبارات
• تحليلي

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

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

# 8 سجل المعاملات و tempdb والذاكرة

يحتاج ملف السجل إلى مساحة خالية كافية للعمليات العادية لأن عملية النمو التلقائي في ملف السجل تستغرق وقتًا طويلاً ويمكن أن تجبر العمليات الأخرى على الانتظار حتى تكتمل. لمعرفة حجم ملف السجل لكل قاعدة بيانات ومقدار استخدامها ، يمكننا استخدام DBCC SQLPERF(logspace) .

أفضل طريقة لإعداد tempdb هي وضعه على قرص منفصل. نحتاج إلى الحفاظ على الحجم الأولي كبيرًا بقدر ما يمكننا تحمله لأنه عندما يصل إلى حالة النمو التلقائي ، سينخفض ​​الأداء.

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

خاتمة:

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

مزيد من القراءة على مدونة Toptal Engineering:

• حل الاختناقات مع فهارس وأقسام SQL
• Oracle إلى SQL Server و SQL Server إلى Oracle Migration Guide