🚧 🌯 🏛️ varchar (max) -varchar (max) وفي الإنتاج 💪🏿 👶 👍🏾

لقد شاركت مؤخرًا في مناقشة حول تأثير تحديد الأطوال في أعمدة nvarchar على الأداء. كانت الحجج معقولة من كلا الجانبين ، وبما أن لدي وقت فراغ ، قررت أن أختبر قليلاً. كانت النتيجة هذا المنشور.

المفسد - ليس بهذه البساطة.

تم إجراء جميع الاختبارات على SQL Server 2014 Developer Edition ، وتم الحصول على نفس النتائج تقريبًا على SQL Server 2016 (مع اختلافات طفيفة). يجب أن يكون ما يلي ملائمًا لـ SQL Server 2005-2016 (والاختبار مطلوب في 2017/2019 ، حيث ظهرت منح الذاكرة التكييفية هناك ، والتي يمكن أن تصحح الوضع إلى حد ما).

نحتاج إلى إجراء مخزن من Erik Darling sp_pressure_detector ، والذي يسمح لك بالحصول على الكثير من المعلومات حول الحالة الحالية للنظام وSQL Query Stress هو أداة مفتوحة المصدر رائعة جدًا Adam Machanic / Erik Ejlskov Jensen لاختبار التحميل MS SQL Server.

عن ماذا نتحدث

السؤال الذي أحاول الإجابة عليه هو ما إذا كان اختيار طول حقل varchar (n) يؤثر على الأداء (فيما يلي ببساطة varchar في كل مكان ، على الرغم من أن كل شيء له صلة أيضًا بـ nvarchar) ، أو يمكنك استخدام varchar (max) وليس البخار ، لأنه إذا كان طول السلسلة <8000 (4000 حرف nvarchar) ، ثم يتم تخزين varchar (max) و varchar (N) IN-ROW.

موقف الطبخ

create table ##v10  (i int, d datetime, v varchar(10));
create table ##v100 (i int, d datetime, v varchar(100));
create table ##vmax (i int, d datetime, v varchar(max));

نقوم بإنشاء 3 جداول من ثلاثة حقول ، والفرق فقط في طول varchar: 10/100 / max. واملأها بنفس البيانات:

;with x as (select 1 x union all select 1)
, xx as (select 1 x from x x1, x x2)
, xxx as (select 1 x from xx x1, xx x2, xx x3)
, xxxx as (
	select row_number() over(order by (select null)) i
		, dateadd(second, row_number() over(order by (select null)), '20200101') d
		, cast (row_number() over(order by (select null)) as varchar(10))  v 		
	from xxx x1, xxx x2, xxx x3
) --262144 
insert into ##v10			--varchar(10)
select i, d, v from xxxx;	

insert into ##v100			--varchar(100)
select i, d, v from ##v10;

insert into ##vmax			--varchar(max)
select i, d, v from ##v10;

ونتيجة لذلك ، سيحتوي كل جدول على 262144 صفًا. يحتوي العمود I (عدد صحيح) على أرقام غير متكررة من 1 إلى 262145 ؛ d (datetime) التواريخ الفريدة و v (varchar) - طاقم العمل (I as varchar (10)). لجعله أشبه بالحياة الواقعية ، أنشئ فهرس كتلة فريدًا على i:

create unique clustered index #cidx10 on ##v10(i);
create unique clustered index #cidx100 on ##v100(i);
create unique clustered index #cidxmax on ##vmax(i);

اذهب

أولاً ، دعنا نرى خطط تنفيذ الطلبات المختلفة.

أولاً ، تحقق من أن التحديد حسب حقل varchar لا يعتمد على طوله (إذا تم تخزين <8000 حرفًا هناك). ندرج خطة تنفيذ صالحة وننظر:

select * from ##v10 where v = '123';
select * from ##v100 where v = '123';
select * from ##vmax where v = '123';

من الغريب أن الفرق ، على الرغم من صغره ، موجود. تقوم خطة الاستعلام باستخدام varchar (max) أولاً بتحديد جميع الصفوف ثم تصفيتها ، ويقوم varchar (10) و varchar (100) بالتحقق من التطابقات عند مسح فهرس المجموعة. وبسبب هذا ، يستغرق المسح 3 مرات أطول تقريبًا - 0.068 ثانية مقابل 0.022 لـ varchar (10).

الآن دعونا نرى ما يحدث إذا قمنا ببساطة بعرض عمود varchar وحددنا البيانات عن طريق مفتاح فهرس الكتلة:

select * from ##v10  where i between 200000 and 201000;
select * from ##v100 where i between 200000 and 201000;
select * from ##vmax where i between 200000 and 201000;

كل شيء واضح هنا - لا يوجد فرق لمثل هذه الطلبات.

و الان الى الجزء الممتع. في الطلب السابق ، حصلنا على 1001 صف فقط ، والآن نريد تصنيفها حسب العمود غير المفهرس. نحاول:

select * from ##v10  where i between 200000 and 201000 order by d;
select * from ##v100 where i between 200000 and 201000 order by d;
select * from ##vmax where i between 200000 and 201000 order by d;

أوه ، وما هو الأصفر؟

مضحك ، أي طلب وطلب وتلقى 6.5 ميغا بايت من ذاكرة الوصول العشوائي للفرز ، واستخدم فقط 96 كيلو بايت. وكم سيكون أسوأ إذا كان هناك المزيد من الخطوط. حسنًا ، فليكن 1000 ، ولكن 100000:

ولكن هنا أكثر خطورة. علاوة على ذلك ، فإن الطلب الأول الذي يعمل مع أصغر varchar (10) غير راضٍ أيضًا عن شيء:

على اليسار ، هناك تحذير من الطلب الأخير: تم طلب 500 ميجابايت ، وتم استخدام 9.5 ميجابايت فقط. وعلى اليمين يوجد تحذير للفرز: تم طلب 8840 كيلوبايت ، ولكن لم يكن هناك ما يكفي منها وتم كتابة وقراءة 360 صفحة أخرى (8 كيلوبايت لكل منها) من tempdb.

وهنا يطرح السؤال: WTF؟

الجواب هو كيف يعمل محسن استعلام SQL Server. لفرز شيء ما ، تحتاج أولاً إلى وضع شيء ما في الذاكرة. كيف نفهم مقدار الذاكرة المطلوبة؟ بشكل عام ، نحن نعلم مقدار نوع البيانات التي تشغل مساحة. ولكن ماذا عن الأوتار ذات الطول المتغير؟ لكن الأمر أكثر إثارة للاهتمام معهم. عند تخصيص ذاكرة لأنواع الصلة / التجزئة ، يعتبر SQL Server أنها نصف ممتلئة في المتوسط. وتخصص لها ذاكرة بحجم (حجم / 2) * العدد المتوقع للخطوط. لكن varchar (max) يمكنه تخزين ما يصل إلى 2 غيغابايت - كم يمكن تخصيصه؟ يعتقد SQL Server أنه سيكون هناك نصف varchar (8000) - أي حوالي 4 كيلوبايت لكل سطر.

ما هو مثير للاهتمام - هذا التخصيص للذاكرة يؤدي إلى مشاكل ليس فقط مع varchar (الحد الأقصى) - إذا تم اختيار حجم varchars الخاص بك بمحبة بحيث يكون معظمها نصف ممتلئ وأكبر ، فإن هذا يؤدي أيضًا إلى مشاكل. مشاكل خطة مختلفة ، ولكن ليس أقل خطورة. يوجد في الشكل أعلاه وصف - تعذر على SQL Server تخصيص ذاكرة بشكل صحيح لفرز varchar صغير واستخدم tempdb لتخزين النتائج الوسيطة. إذا كان tempdb يكمن على الأقراص البطيئة ، أو يتم استخدامه بنشاط من قبل الطلبات الأخرى ، فقد يصبح هذا اختناقًا كبيرًا.

ضغط استعلام SQL

لنر الآن ما يحدث عند تنفيذ الاستعلامات المجمعة. قم بتشغيل SQL Query Stress ، وقم بتوصيله بخادمنا ، وقول لتنفيذ كل هذه الاستعلامات 10 مرات في 50 سلسلة.

نتائج الاستعلام الأول:

إنه مثير للاهتمام ، ولكن بدون الفهارس ، عند البحث ، يظهر varchar (max) نفسه أسوأ من أي شخص آخر ، وهو أسوأ تمامًا من حيث وقت المعالج للتكرار ووقت التنفيذ الكلي.

لا يعرض sp_pressure_detector أي شيء مثير للاهتمام هنا ، لذلك لا أذكر مخرجاته.
نتائج الاستعلام الثاني:

كل شيء متوقع هنا - جيد بنفس القدر.

و الان الى الجزء الممتع. استعلام مع فرز الصفوف الناتجة:

اتضح أن كل شيء هو نفسه تمامًا كما هو الحال مع الطلب السابق - لا يوجد العديد من الأسطر ، والفرز لا يسبب مشاكل.

الآن الاستعلام الأخير الذي يفرز العديد من الصفوف بشكل غير معقول (أضفت أعلى 1000 إليه حتى لا تسحب القائمة المفروزة بالكامل):

وهنا ناتج sp_pressure_detector:

ماذا قال لنا؟ تتطلب كل الجلسات 489 ميجا بايت لكل منها (للفرز) ، ولكن 22 منها فقط لديها ذاكرة كافية لـ SQL Server ، حتى مع الأخذ في الاعتبار أن جميع هذه الجلسات الـ 22 تستخدم فقط 9 ميجا بايت لكل منها!
إجمالاً ، تتوفر 11 جيجا بايت من الذاكرة ، و 229 جلسة تم تخصيصها 489.625 لكل منها و SQL Server لديه 258 ميغا بايت فقط متاحة ، وهم يريدون أيضًا الحصول على جلسات جديدة لـ 489. انتظر حتى تصبح الذاكرة حرة - ينتظرون ، ولا يبدأون في الجري. ماذا سيفعل المستخدمون إذا تم إجراء هذه الطلبات في جلساتهم؟ الانتظار للغاية.

بالمناسبة ، انتبه إلى الشكل باستخدام varchar (10) - الطلبات ذات varchar (10) استغرقت وقتًا أطول من الطلبات باستخدام varchar (100) - وهذا على الرغم من أنني لدي tempdb على قرص سريع جدًا. أسوأ محرك الأقراص تحت tempdp ، سيتم تشغيل الاستعلام أبطأ.

ملاحظة منفصلة ل SQL Server 2012/2014

SQL Server 2012/2014 sort spills. char/nchar – spill’ tempdb. MS , , .

:

create table ##c6  (i int, d datetime, v char(6));
insert into ##c6 (i, d, v)
select i, d, v
from ##v10
select * from ##c6 where i between 100000 and 200000 order by d;

( ):

DBCC TRACEON (7470, -1);

, spill’ .

الموجودات

كن حذرا مع الفرز في استعلاماتك حيث لديك (n) أعمدة varchar. إذا كانت الفرز لا تزال مطلوبة ، فمن المرغوب فيه للغاية أن يحتوي عمود الفرز على فهرس.

لاحظ أنه من أجل الحصول على فرز ، ليس من الضروري استخدام الترتيب بشكل صريح - فمظهره ممكن أيضًا مع دمج الصلات ، على سبيل المثال. نفس المشكلة مع تخصيص الذاكرة ممكنة أيضًا مع روابط التجزئة ، على سبيل المثال ، مع varchar (max):

select top 100 * 
from ##vmax v1
inner hash join ##v10 v2 on v1.i = v2.i

تخصيص 2.5 جيجا بايت من الذاكرة ، 25 ميجا بايت مستعملة!

الاستنتاج الرئيسي بالنسبة لي : حجم العمود (ن) varchar - هام! إذا كان الحجم صغيرًا جدًا ، فمن الممكن حدوث انسكابات في tempdp ؛ وإذا كانت كبيرة جدًا ، تكون طلبات الذاكرة كبيرة جدًا. إذا كانت هناك أنواع ، فسيكون من الحكمة إعلان طول varchar كمتوسط طول السجل * 2 ، وفي حالة SQL Server 2012/2014 - أكثر من ذلك.

استنتاج غير متوقع بالنسبة لي : varchar (max) ، الذي يحتوي على أقل من 8000 حرف ، يعمل في الواقع بشكل أبطأ أكثر ، مع وجود فلاتر عليه. لا أعرف كيف أشرح ذلك بعد - سأحفر أكثر.

سحب المكافأة بالنسبة لي: بالفعل نقرت بالفعل على "نشر" ، اعتقدت أنه حتى مع varchar (بحد أقصى) ، يمكنك تجربة مشكلة "varchar'a الصغيرة". في الواقع ، عند التخزين في varchar (الحد الأقصى) أكثر من 4000 حرف (2000 لـ nvarchar) - يمكن أن يكون الفرز مشكلة.

insert into ##vmax(i, d, v)
select i, d, replicate('a', 4000) v
from ##v10;

select * from ##vmax where i between 200000 and 201000 order by d;

truncate table ##vmax;

insert into ##vmax(i, d, v)
select i, d, replicate('a', 4100) v
from ##v10;

select * from ##vmax where i between 200000 and 201000 order by d;

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

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

مكافأة صغيرة

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