تحديث الإحصائيات على النسخ المتماثلة الثانوية لمجموعة التوفر

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

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

لكن توقف عن الشكوى ، فإليك الفكرة الرئيسية: عزيزي Microsoft ، دعونا نستخدم الإشارات لتحديث الإحصائيات ... حسنًا ، ونقوم بالكثير عليها.

هناك دائمًا طريقة أو شيء من هذا القبيل

* دائما تقريبا

دعونا سرد التفاصيل الأساسية المعروفة لحل ممكن على Enterprise Edition MS SQL Server:

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

دعونا نفعل ذلك

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

في ما يلي نص برمجي لإنشاء جدول وإدراج مليون صف فيه:

DROP TABLE IF EXISTS dbo.SampleDataTable; CREATE TABLE dbo.SampleDataTable ( C1 BIGINT NOT NULL, C2 BIGINT NOT NULL, CONSTRAINT PK_SampleDataTable PRIMARY KEY (C1) ); INSERT INTO dbo.SampleDataTable WITH (TABLOCK) SELECT t.RN, t.RN FROM ( SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN FROM sys.objects t1 CROSS JOIN sys.objects t2 CROSS JOIN sys.objects t3 CROSS JOIN sys.objects t4 CROSS JOIN sys.objects t5 ) t OPTION (MAXDOP 1); 

الآن لنقم بإنشاء إحصائيات ST_SampleDataTable_C2 للعمود c2

 CREATE STATISTICS ST_SampleDataTable_C2 ON dbo.SampleDataTable(C2); 

وبعد ذلك ، سأقوم بإدخال 1000 صف ، والتي ستكون مهمة جدًا وبسببها أحتاج إلى تحديث الإحصاءات.

 set nocount on; INSERT INTO dbo.SampleDataTable WITH (TABLOCK) SELECT 10000000 + t.RN, 999999999 FROM ( SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN FROM sys.objects t1 CROSS JOIN sys.objects t2 CROSS JOIN sys.objects t3 CROSS JOIN sys.objects t4 CROSS JOIN sys.objects t5 ) t OPTION (MAXDOP 1); 

لدي الآن 1000 إدخال ، في العمود C2 ، القيمة هي 999999999. وهذا بالتأكيد يعني مشكلة مفتاح التصاعدي وأحتاج حقًا إلى تحديث الإحصائيات ... على النسخة المتماثلة بحيث لا أجهد الخادم الرئيسي مع الحسابات و منعه من خدمة العملاء.

باستخدام أمر SHOW_STATISTICS DBCC القديم الجيد ، دعونا نفحص إحصائياتنا.

 DBCC SHOW_STATISTICS('dbo.SampleDataTable', 'ST_SampleDataTable_C2') 

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

أيضًا ، يمكننا أن نرى تدفق الإحصاءات باستخدام المعلمة STATS_STREAM للأمر DBCC SHOW_STATISTICS:

 DBCC SHOW_STATISTICS('dbo.SampleDataTable', 'ST_SampleDataTable_C2') WITH STATS_STREAM; 

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

على جديلة

دعنا ننسخ طاولتنا على نسخة طبق الأصل إلى tempdb (على الرغم من أن AG الخاص بي في وضع متزامن ، يمكن تنفيذ نفس الشيء في غير متزامن ، فقط البيانات قد تأتي مع تأخير طفيف).

 use TempDB; DROP TABLE IF EXISTS dbo.SampleDataTable; CREATE TABLE dbo.SampleDataTable ( C1 BIGINT NOT NULL, C2 BIGINT NOT NULL, CONSTRAINT PK_SampleDataTable PRIMARY KEY (C1) ); INSERT INTO dbo.SampleDataTable SELECT C1, C2 FROM AvGroupDb.dbo.SampleDataTable; 

نحن الآن على استعداد لتحديث الإحصائيات بمسح كامل في tempdb على النسخة المتماثلة.

 use TempDB; UPDATE STATISTICS ST_SampleDataTable_C2 ON dbo.SampleDataTable(C2) WITH FULLSCAN; 

( ملاحظة المترجم - نسيت Nico إنشاء إحصائيات ، ويستخدم بناء الجملة غير الصحيح لعملية UPDATE STATISTICS ، بدلاً من UPDATE ، يجب إنشاء CREATE ، بمعنى أنه لا يتم تحديث الإحصائيات ، ولكن يتم إنشاؤها )

ارجع إلى DBCC SHOW_STATISTICS وانظر إليه:

 DBCC SHOW_STATISTICS('dbo.SampleDataTable', 'ST_SampleDataTable_C2') 

يبدو مختلفًا تمامًا عما كان عليه على الخادم الرئيسي - 3 أسطر فقط مقابل 178 ، ولكنه يصف البيانات تمامًا - لدينا مليون سطر فريد و 1000 سطر بنفس قيمة عمود C2 - الرسم البياني جيد بقدر الإمكان .

دعونا نلقي نظرة على تدفق الإحصاءات:

 DBCC SHOW_STATISTICS('dbo.SampleDataTable', 'ST_SampleDataTable_C2') WITH STATS_STREAM; 

لست بحاجة إلى أن تكون عبقريًا لتلاحظ أن الدفق يبدو مختلفًا تمامًا - فنرى 5689A0C6 حرفًا في الدفق المحدث ، بينما في الأصل ، بين كل هذه الأصفار التي شاهدناها EDF10EB4.

دعنا نركز على تصدير هذه البيانات إلى ملف نصي في مكان ما خارج SQL Server ونفعل ذلك بمساعدة الأمر BCP الرائع ، الذي يتطلب تمكين CMDSHELL (ملاحظة: ربما لا تريد ذلك على خادم الإنتاج الخاص بك):

 EXEC xp_cmdshell 'BCP "DBCC SHOW_STATISTICS(''AvGroupDb.dbo.SampleDataTable'', ''ST_SampleDataTable_C2'') WITH STATS_STREAM" queryout \\SharedServer\Tempdb\stats.txt -c -T'; 

وهنا هو مدى حجم ملف stats.txt في الكرة لدينا:



فقط بضع كيلو بايت! من السهل أن تنقل ، وسهلة لإدارة.

العودة إلى الخادم الرئيسي

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

 CREATE TABLE dbo.TempStats( Stats_Stream VARBINARY(MAX), Rows BIGINT, DataPages BIGINT ); 

دعنا نستورد البيانات من ملفنا النصي في جدولنا المؤقت الجديد ونرى ما استوردناه:

 BULK INSERT dbo.TempStats FROM '\\SharedServer\Tempdb\stats.txt' SELECT * FROM dbo.TempStats; 

يمكننا أن نرى نفس البيانات التي قمنا بحسابها على النسخة المتماثلة ، لكن هذه البيانات موجودة بالفعل على الخادم الرئيسي لدينا ، وكل ما يتبقى لنا هو تحديث الإحصاءات الخاصة بنا منها في الجدول. يمكن إجراء هذه العملية باستخدام عملية UPDATE STATISTICS باستخدام المعلمة WITH STATS_STREAM = ...

 DECLARE @script NVARCHAR(MAX) SELECT @script = 'UPDATE STATISTICS dbo.SampleDataTable(ST_SampleDataTable_C2) WITH STATS_STREAM = ' + CONVERT(nvarchar(max), [Stats_Stream],1) FROM dbo.TempStats PRINT @script; EXECUTE sp_executesql @script; 

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

 UPDATE STATISTICS dbo.SampleDataTable(ST_SampleDataTable_C2) WITH STATS_STREAM = 

إن تشغيل DBCC SHOW_STATISTICS على الخادم الرئيسي يعطيني النتيجة ذاتها التي كنت آملها - تمامًا كما رأينا في النسخة المتماثلة. الدائرة مغلقة.

 DBCC SHOW_STATISTICS('dbo.SampleDataTable', 'ST_SampleDataTable_C2'); 

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

ليس السيناريو الأساسي لذلك.

إذا كان لديك العديد من AGs بين النسخ المتماثلة نفسها ، حيث تعد النسخة المتماثلة الرئيسية في AG واحدة والأخرى هي الرئيسية في الثانية ، فيمكنك إدراج بيانات BLOB في دفق البيانات بين النسخ المتماثلة وإضافة قاعدة بيانات صغيرة تحتوي على البيانات المرسلة.



انظر الى الصورة إذا كان لدينا AGs (AG1 و AG2) موجودان على خوادم مختلفة ولدينا جدول محدد على Server1 في AG1 نريد تحديث الإحصائيات له ، ثم على Server2 يمكننا نسخ هذا الجدول (دعنا نسميها dbo.MyTable ) في tempdb ، قم بتحديث واستخدام AG2 إرسال الكائن مع تدفق الإحصاءات مرة أخرى إلى Server1 ، حيث يمكنك ببساطة استيراد الإحصائيات من هذا التدفق إلى الإحصائيات التي نحتاج إليها.

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

مكان للشك

قد يكون لديك بعض الاعتراضات ، على سبيل المثال:

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

AG ردود الفعل القناة

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



وهو ما يمثل الآلية الكاملة لقناة التعليقات الحالية. تؤكد النسخة المتماثلة ، باستخدام الخطوة 12 وما تلاها ، للخادم الأساسي أنه تم حفظ المعلومات. يمكن استخدام نفس القناة لإرسال كائن تدفق إحصائيات بعد إعادة العد على نسخة متماثلة. بالطبع ، لن نضطر إلى استخدام tempdb لهذا الغرض ، لكننا ننشئ كائنًا في الذاكرة داخل قاعدة البيانات لا يجب تخزينه بشكل دائم (انظر إلى جداول OLTP Schema-Only الخاصة بك في الذاكرة فقط ، أو فكر في جداول NOLOGGING في Oracle) ، يجب إزالتها في نهاية العملية - سيكون ذلك رائعًا حقًا.

الأفكار العامة

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

 UPDATE STATISTICS dbo.MyAwesomeTable(HugeImportantStatOnC17) WITH FULLSCAN, SECONDARY 

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

أنا متأكد من أن هذه الميزة ستشجع العديد من مستخدمي Enterprise Edition على الترحيل إلى الإصدار الجديد من SQL Server ، والذي سيسمح بتوزيع العمليات الثقيلة بين النسخ المتماثلة.

بالنسبة للوضع الحالي - أرى بالضبط كيف يمكنك أتمتة هذا الحل باستخدام Powershell.

مايكروسوفت ، حان دورك! ؛)

التصويت للميزة المقترحة هنا .

ملاحظة المترجم: أي اقتراحات وتعليقات على الترجمة والتصميم مرحب بها ، كالعادة.

عادة ما كنت أسمي النسخة المتماثلة الأساسية في الترجمة "الخادم الأساسي" ، والنسخة المتماثلة الثانوية - مجرد نسخة طبق الأصل. ربما هذا ليس صحيحًا تمامًا ، لكن أذني تؤلم أقل من النسخ المتماثلة "الأولية" و "الثانوية" على msdn.