مشکل Parameter Sniffing در SQL Server 2022

مشکل Parameter Sniffing در SQL Server 2022

نوشته شده توسط: مهدی قپانوری
تاریخ انتشار: ۱۱ خرداد ۱۴۰۱
آخرین بروزرسانی: ۲۳ اردیبهشت ۱۴۰۲
زمان مطالعه: 5 دقیقه
۵
(۲)

مقدمه

چرا اجرای یک Stored Procedure گاهی کند و گاهی سریع است؟ Plan اجرائی یک پروسیجر به ازای اولین مقداری که به پروسیجر پاس داده می شود تشکیل و در اجرای های بعدی مورد استفاده قرار می گیرد. این موضوع می تواند منجر به مشکل Parameter Sniffing شود.

در این مقاله قصد داریم با ایجاد یک مثال به بررسی مشکل Parameter Sniffing بپردازیم.

ابتدا یک Index در جدول Users در دیتابیس StackOverflow بر روی ستون Reputation ایجاد می نمائیم:

Create Index IXNCReputation On dbo.Users (Reputation)

سطح سازگاری دیتابیس را برابر با ۱۵۰ قرار می دهیم:

Alter Database StackOverflow Set Compatibility_Level = 150

و پروسیجر زیر را ایجاد می نمائیم:

Create Procedure USP_GetReputationCount
(@Reputation Int)
As
 Select Count(Reputation) As ReputationCount From dbo.Users
 Where Reputation = @Reputation

همان طور که در تصویر زیر مشاهده می نمائید Plan اجرائی پروسیجر به ازای مقدار ورودی ۲ به صورت سریال است:

اجرا به صورت سریال یعنی SQL Server از یک CPU Core جهت اجرای Procedure استفاده نموده است. زیرا که تعداد رکوردهای پردازشی کم بوده است. به ازای مقدار ورودی ۲ تنها ۹۱۴۹ رکورد پردازش شده است.

این Plan اجرائی که به صورت سریال است در Plan Cache قرار می گیرد و جهت اجراهای بعدی پروسیجر استفاده می شود.

یعنی Plan اجرائی پروسیجر به ازای اولین مقداری که اصطلاحا به پروسیجر پاس داده می شود تشکیل و در اجرای های بعدی مورد استفاده قرار می گیرد.

حالا اگر مقدار یک را به پروسیجر پاس دهیم چه اتفاقی می افتد؟ همان Plan که به شکل سریال است مورد استفاده قرار می گیرد.

تصویر زیر این موضوع را نشان می دهد:

تقریبا ۶ میلیون رکورد در جدول Users وجود دارد که Reputation آن ها برابر با یک است اما SQL Server این حجم از رکورد را تنها با یک CPU Core پردازش می نماید!

چرا که Plan به ازای تقریبا ۹ هزار رکورد تشکیل شده است و در Plan Cache قرار گرفته و از همان Plan در اجراهای بعدی استفاده می شود.

این مشکل، Parameter Sniffing است.

راه های زیادی جهت حل این مشکل وجود دارد که جدید ترین آن ها افزایش سطح سازگاری دیتابیس به ۱۶۰ می باشد.

دستور زیر سطح سازگاری دیتابیس StackOverflow را به ۱۶۰ افزایش می دهد:

Alter Database StackOverflow Set Compatibility_Level = 160

یک بار دیگر پروسیجر را به ازای مقدار ورودی ۲ اجرا می نمائیم، تصویر زیر نشان می دهد که Plan اجرائی پروسیجر به شکل سریال است:

دوباره مقدار یک را به پروسیجر پاس می دهیم، تصویر زیر را مشاهده نمائید:

Plan اجرایی به شکل موازی است. (در این نمونه خطوط زرد رنگ نشان دهنده اجرا به صورت Parallel می باشد) یعنی SQL Server تشخیص داد که به ازای مقدار ورودی ۱ تعداد بسیار زیادی رکورد باید پردازش شود و از چندین CPU Core جهت اجرای پروسیجر استفاده نمود.

سوال مهم

آیا در SQL Server 2022 مشکل Parameter Sniffing کامل برطرف شده است؟

نمونه های زیادی از مشکل Parameter Sniffing وجود دارد، مثال فوق فقط یک نمونه بود. همه نمونه های این مشکل باید با SQL Server 2022 تست شود و مشاهده گردد که، کدام یک از آن ها برطرف شده و چه نمونه هایی از این مشکل هنوز هم وجود دارد.

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۲

اولین نفر باش

title sign
دانلود مقاله
مشکل Parameter Sniffing در SQL Server 2022
فرمت PDF
4 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
مهدی قپانوری
مقالات
15 مقاله توسط این نویسنده
محصولات
1 دوره توسط این نویسنده
مهدی قپانوری

مهدی قپانوری بیش از 6 سال است که در زمینه‌های نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزه‌هایPerformance Tuning، Database Administration، Database Development و طراحی سیستم‌های OLTP می‌باشد. مهدی علاقه‌مند به R&D در حوزه‌های نوین SQL Server است.

پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • عالی و مفید

    • همکار عزیزم خیلی ممنون بابت مقاله ارزشمندت، میتونید نمونه های بیشتری مثال بزنید؟

    • ممنون بابت مطالب آموزنده ، کاربردی و ارزنده

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link