آشنایی با Cardinality Estimation در SQL Server

ممکن است شما پس از بروزرسانی نسخه‌ی SQL Server خود به 2014 با کندی شدید در برخی از query های خود مواجه شده باشید، با جستجویی ساده متوجه می‌ شوید که مایکروسافت در SQL Server 2014 معماری برخی از قسمت ها که سالیان سال دست نخورده باقی مانده بود، تغییر داده است و یکی از قسمت هایی که تغییر کرده و در سرعت query ها تاثیر گذار است Cardinality Estimation است، در این مقاله شما را با این بخش مهم  از SQLServer آشنا می‌کنیم و توضیحاتی پیرامون امکانات قابل دسترس برای کنترل این قسمت ارائه می‌دهیم.

CE برآورد کردن (تخمین زدن) تعداد سطرهایی است که query شما به احتمال زیاد بر‌می‌گرداند. Query Optimizer از این برآورد برای انتخاب بهینه ترین (کم هزینه ترین) روش، استفاده می‌کند. هرچه CE دقیق‌تر عمل کند، راه بهینه‌تری انتخاب می‌شود.
برای درک بهتر موضوع لازم است ابتدا با Query Optimizer و نحوه‌ی عملکرد آن آشنا شویم.

در هسته‌ی اصلی SQL Server Database Engine دو کامپوننت اصلی وجود دارد، یکی Storage Engine و دیگری Query Processor که به آن Relation Engine هم گفته می‌شود.

• Storage Engine: مسئول خواندن اطلاعات بین دیسک و حافظه است.
• Query Processor: مسئول پذیرش query ها، پیدا کردن plan بهینه، اجرای plan انتخاب شده و ارائه‌ی نتیجه‌ی نهایی است.

query ها با استفاده از زبان SQL (‌یا T-SQL ) نوشته می‌شود. SQL یک زبان سطح بالاست که در آن فقط مشخص می‌شود چه اطلاعاتی از دیتابیس برگردانده شودودر آن مراحلی که برای بدست آوردن دیتا لازم است یا الگوریتم هایی که برای پردازش درخواستها لازم است، مشخص نمی‌شود. بنابراین، برای هر query ، اولین کار Query Processor این است که یک plan تا جای ممکن سریع برای اجرای آن پیدا کند و کار دوم آن این است که query را براساس plan انتخاب شده اجرا کند. در Query Processor ، دو کامپوننت جدا وجود دارد که هریک مسئول یکی از این کارهاست. Query Optimizer مسئول پیدا کردن plan و Execution Engine وظیفه‌ی اجرا و برگرداندن نتیجه را به عهده دارد.

 

در تصویر زیر کارهایی که Query Processor انجام می‌دهد، مشخص شده است.

Parsing and binding اولین کارهایی هستند که بر روی یک query انجام می‌شوند. Parsing مطمئن می‌شود که query نوشته شده syntax درستی دارد و query را به صورت یک درخت ارائه می‌دهد. به طور مشخص، درخت ارائه شده در این مرحله براساس عملگرهای منطقی است و گامهای سطح بالای اجرای query را مشخص می‌کند.در واقع این عملگرهای منطقی خیلی به query،syntax نوشته شده نزدیک است، به عنوان مثال این قسمت چنین دستورهایی را مشخص می‌کند: "get data from Customer table", "get data from Contact table”, “perform an inner join"

Binding مطمئن می‌شود همه‌ی Object name های ذکر شده در query وجود داشته باشند.خروجی این بررسی را algebrized tree می‌نامند، که به Query Optimizer فرستاده می‌شود.

گام بعدی Optimization Process است که کارش تولید plan های ممکن و انتخاب plan اجرایی براساس هزینه است. SQL Server از بهینه‌ترین (براساس هزینه) استفاده می‌کند و از یک مدل تخمین هزینه برای برآورد هزینه‌ی هر plan کاندید شده، استفاده می‌کند. در پایان، زمانیکه plan اجرایی مشخص شد آنرا به execution engine می‌دهد تا نتیجه را بر‌گرداند.

طبق توضیحات داده شده، هدف اصلی Query Optimizer پیدا کردن plan اجرایی موثر برای query شماست. حتی برای query های ساده نیز ممکن است تعداد plan های زیادی برای دسترسی به دیتا وجود داشته باشد که همگی نتیجه یکسان دارند. Query Optimizer تلاش می‌کند از میان تعداد زیاد plan های کاندید شده، بهترین plan را انتخاب کندو بسیار اهمیت دارد که plan انتخاب شده در کمترین زمان نتیجه را به کاربر بدهد (در واقع سریع‌ترین plan باشد)

کار Query Optimizer ایجاد و ارزیابی plan های بسیار زیادی است که ما را به نتیجه می‌رساند. تمام plan های اجرایی ممکن برای یک query که همه نتیجه‌ی یکسان بر‌می‌گرداند را Search Space می‌نامیم.

به صورت تئوری برای پیدا کردن بهینه ترین plan اجرایی، باید هزینه‌ی تک تک plan ‌های موجود را محاسبه کنیم، سپس کم هزینه‌ترین را انتخاب کنیم، اما از آنجاییکه برخی از query های پیچیده ممکن است چند هزار یا حتی چند میلیون plan کاندید شده داشته باشند، محاسبه ی هزینه‌ی تک تک plan ها بسیار زمانبر خواهد بود. بنابراین Query Optimizer باید تعادلی میان Optimization Time و Plan Quality برقرارکند.برای مثال اگر Query Optimizer برای پیدا کردن یک plan خوب یک ثانیه زمان بگذارد، که آن در یک دقیقه اجرا می‌شود، به صرفه نیست که برای پیدا کردن بهینه‌ترین plan ، پنج دقیقه زمان بگذاردو هزینه‌ها را بررسی کند که آن هم به زمان اجرا اضافه می‌شود یعنی 5 دقیقه + 1 دقیقه در مقابل 1 ثانیه + 1 دقیقه.

بنابراین SQL Server یک جستجوی کامل انجام نمی‌دهد،بلکه سعی می‌کند مناسب‌ترین plan را در کمترین زمان پیدا کند. در واقع Query Optimizer در یک محدودیت زمانی کار می‌کند، بنابراین plan انتخاب شده ممکن است بهترین plan یا نزدیک به بهترین plan باشد.

پیدا کردن plan های کاندید تنها یکی از وظایف Query Optimizer است. Query Optimizer نیاز دارد هزینه‌ی این plan ها را تخمین بزند و ارزانترین‌شان را انتخاب کند. برای برآورد هزینه یک plan ، هزینه هر Query Operator (and, or, like …)در آن plan را با استفاده از فرمولهای محاسبه‌ی هزینه با در نظر گرفتن منابعی مانند I/O, CPU, Memory برآورد می‌کند. این برآورد هزینه اغلب به تعداد رکوردهایی که نیاز به پردازش دارند وابسته است، این برآورد تعداد رکوردها را Cardinality Estimationمی‌نامیم.

به بیان دیگر Query Optimizer از علم‌ آمار کمک می‌گیرد و تعداد رکوردهایی که در نتیجه‌ی query برمی‌گردد را پیش‌بینی می‌کند که آن را Cardinality Estimation می‌نامیم واز نتایج آن در محاسبه هزینه plan استفاده می‌کند. بنابراین هرچه CE دقیق‌تر باشد، plan انتخابی بهینه‌تر است.

Session مربوط به محیط دیتابیس مشخص کننده‌ی ورژن CE است.در SQL Server 2014 به صورت پیش فرض در اجرای query ها از CE جدید استفاده می‌شود. اما حالت‌هایی وجود دارد که در آن با اینکه شما در SQL 2014 هستید و ورژن CE را تغییر نداده ‌اید، اما دیتابیس شما از CE نسخه‌ی قدیمی استفاده می‌کند، این مورد در یکی از حالت‌های زیر می‌تواند رخ دهد :

1. شما با استفاده از in-place upgrade دیتابیس را به نسخه ی 2014 انتقال می‌دهید.
2. شما دیتابیس با نسخه‌ی پایین‌تر را به نسخه‌ی جدید attach می‌کنید.
3. شما دیتابیس با نسخه‌ی پایین‌تر را در نسخه‌ی جدید restore می‌کنید.

SQL Server از Compatibility Level برای نشان دادن ورژن CE استفاده می‌کند. شما می‌توانید Compatibility Level دیتابیس را با استفاده از query روی sys.database بررسی کنید. query زیر تمام دیتابیس‌ها را همراه با Compatibility Level آنها نمایش می‌دهد.

 

برای انتقال یک پایگاه داده به SQL 2014 لازم است ورژن Compatibility Level آنرا به آخرین ورژن یعنی "120" تغییر دهید.

مثال :

هرزمان که بخواهید می‌توانید به نسخه‌ی قبلی CE بازگردید، تنها کافی است Compatibility Level دیتابیس را به کمتر از 120 تغییر دهید.

مثال :

بااستفاده از (trace flag (9481 (که در ادامه توضیح می دهم) نیز می‌توانید از CE قدیمی برای اجرای query استفاده کنید در حالیکه Compatibility Level دیتابیس روی نسخه‌ی جدید تنظیم شده است.

علاوه بر اینکه Compatibility Level دیتابیس را می توانید چک کنید، این امکان وجود دارد که ورژن CE برای یک query مشخص را نیز بدست آورید. این مورد در تست‌هایی که برروی query ها انجام می‌دهید، کاربرد دارد.

برای مثالCompatibility Level کوئری زیر را روی SQL Server 2014 بررسی می‌کنیم. با استفاده از SET STATISTICS XML ON می‌توانید علاوه بر نتیجه کوئری actual execution plan را هم مشاهده کنید.

نتیجه ای به صورت زیر مشاهده می‌کنید.

 

بر روی لینک داده شده کلیک کنید تا cardinality estimation model version را برای این plan مشاهده کنید.

روی root (سمت چپ) در query plan tree (در این مثال select است ) کلیک کنید و کلید F4 را بزنید تا مشخصات آنرا مشاهده کنید، در این قسمت می‌توانید CardinalityEstimationModelVersion را مشاهده کنید.

 

مقدار 120 یعنی از CE جدید برای انتخاب plan استفاده می شود. اگر این مقدار مانند تصویر زیر 70 باشد یعنی از CE قدیمی برای انتخاب plan استفاده می شود.

در زمان تست query هایتان ممکن است به این نتیجه برسید که برخی از آنها در CE جدید راندمان بهتری دارند و برخی دیگر در CE قدیمی و بخواهید یک query را به طور همزمان در هر دو وضعیت مقایسه و بررسی کنید بدون اینکه مجبور باشید در هر بررسی Compatibility Level دیتابیس را تغییر دهید.

برای اینکار می‌توانید از امکان جدید SQL 2014 با فعال کردن trace flag در سطح سرور، استفاده کنید. جهت فعالسازی کافیست مقدار DBCC TRACEON را -1 قرار دهید.

برای کنترل CE از trace flag های زیر می‌توانید استفاده کنید:

• 9481 : بررسی و اجرای query با CE قدیمی انجام می‌شود.
• 2312 : با CE جدید بررسی و اجرای query را انجام می‌دهد.

در query می‌توانید با استفاده از QUERYTRACEON، trace flag مورد نظرتان را مشخص کنید.

مثال زیر نحوه‌ی استفاده از trace flag برای تغییر ورژن CE در یک query را نشان می‌دهد.

در سال 1998، بروزرسانی بر روی CE مربوط به نسخه‌ی SQL 7.0 انجام شد. در طی این سال‌ها مایکروسافت یک سری تغییرات جزئی با Hotfixes, patches, trace flags ها ارائه کرده است. از نسخه‌ی 12 (Sql Server 2014) مایکروسافت جهت بهبود کیفیت query plan ها و در نتیجه بهبود performance اقدام به طراحی دوباره‌ی CE کرده است. CE جدید شامل مفروضات و الگوریتم‌هایی است که روی OLTP مدرن و سیستم‌های انبارداری خیلی خوب کار می‌کند. در واقع تغییرات ارائه شده حاصل تجربه ای چندین ساله و تحقیقات عمیقی است که روی سیستم‌های انبارداری مدرن انجام شده است.

طبق بازخوردهایی که از مشتریان در رابطه با تغییرات جدید گرفته اند، متوجه شدند که در بیشتر موارد تغییرات تاثیر مثبت داشته است، در برخی موارد بی‌تاثیر بوده و در تعداد کمی از حالت‌ها نیز تاثیر منفی گذاشته است یعنی عملکرد CE قبلی بهتر بوده است.
بهمین دلیل تست query ها قبل از مهاجرت به CE جدید امری حیاتی است.

منابع

• https://www.red-gate.com/simple-talk/sql/sql-training/the-sql-server-query-optimizer/
• https://channel9.msdn.com/events/TechEd/NorthAmerica/2014/DBI-B331#fbid=
• https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server
• https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx