تعریف sniff در اس کیو ال سرور

[En_MK]

سلام
میخواستم بدانم کی میگیم sniff در sp پیش امده؟وبرای حلش چه باید کرد؟

من sp ی داشتم که در محیط اس کیو ال به سرعت execute میشد اما وقتی همان sp در برنامه صدا زده میشد 5 دقیقه طول میکشید و درنهایت ارور timeoute میداد
سرچ کردم در سایت های (غیر فارسی) به این نتیجه رسیدم که پارامترهایی که در sp تعریف کردم sniff می شوند

حالا میخوام بیشتر در این مورد بدانم یا اصلا من درست متوجه شدم؟:متفکر:

[مهدی نان شکری]

هر RDBMS ایی قبل از اجرای کوئری داده شده، طرح اجرای مناسب را انتخاب مینماید.
به عنوان نمونه در این مرحله مشخص می شود Join های موجود در کوئری با چه ترتیبی اجرا شوند.
برای اجرای کوئری از چه Index هایی استفاده شود؟
برای Join های مورد نظر از چه الگوریتمی استفاده گردد؟
برای انتخاب طرح اجرا دو روش موجود می باشد:
1) Rule base optimization
2) Cost base optimization
در روش اول بر اساس قوانین موجود ، طرح اجرا انتخاب می گردد. به عنوان نمونه در حالت Rule base، اگر ایندکسی موجود باشد حتما از ایندکس استفاده خواهد شد. حتی اگر استفاده از ایندکس سبب افزایش هزینه اجرا شود.
در روش دوم هزینه اجرا برای بهینه ساز کوئری مهم بوده و اگر استفاده از ایندکس سبب افزایش هزینه اجرایی شود استفاده از آن ایندکس کنار گذاشته می شود.
بعد مشخص شدن طرح اجرای مناسب بر اساس روش بهینه ساز RDBMS، (MS SQL از روش دوم استفاده می نماید و اوراکل از ورژن 10g به بعد از روش دوم نیز استفاده می کند) برای استفاده از طرح انتخاب شده در کوئری های مشابه بعد، آن طرح در مکانی از حافظه ذخیره می گردد.
حال به سوال زیر پاسخ دهیم:
"آیا Index seek به همراه key lookup بهتر و کارامد تر از Table scan اجرا می شود؟"
جواب صحیح به تعداد key lookup وابسته می باشد.
اگر تعداد کمی key lookup انجام شود استفاده از ایندکس بهتر می باشد در غیر این صورت استفاده از Table scan روش کم هزینه تری خواهد بود.
لذا یکی از روش های انتخاب طرح اجرا کم هزینه، بررسی پارامتر های کوئری و آمار و ارقام موجود می باشد.
زمانی که ما از Store procedure در MS SQL Server استفاده می نماییم در هنگام اولین اجرای SP، طرح اجرای آن تولید و ذخیره می شود. و در اجرا های بعد از طرح ذخیره شده استفاده مجدد می گردد.
حال ممکن است Optimizer دیتابیس بر اساس اولین پارامتر ورودی استفاده از Index seek به همراه key lookup را کم هزینه بداند و از ایندکس استفاده نماید ولی در اجرای بعد با پارامتر دیگر طرح ذخیره شده مناسب نباشد و سبب کندی اجرای آن بشود.
به این اتفاق Parameter sniffing گفته می شود.
برای حل این مشکل چندین روش موجود می باشد:


• OPTION (RECOMPILE)
• OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
• OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))



select * from Person.Address where city=@city option (recompile)
select * from Person.Address where city=@city option (optimize for (@city='X'))

[حمیدرضاصادقیان]

سلام
تعاریف بالا درست است.
همچنین این نکته رو اضافه کنم که SQL Optimizer هنگام ایجاد Estimated plan با استفاده از Paramtere های پاس شده از Statistics ها استفاده می کند.
این امر به خودی خود بد نیست و اتفاقا خوب هست و سبب افزایش Performance شما نیز خواهد شد.

شما برای اینکه دلیل کندی را متوجه بشید قبل از اجرای SP دستور set statistics io on را اجرا کنید و همچنین گزینه Include Actual plan رو انتخاب کنید و اینها رو اینجا قرار بدین تا بشه شرایط رو بررسی کرد.

[En_MK]

خیلی خیلی لطف کردید
من یه sp دارم که در محیط sql سه ثانیه ای Result را نشون میده اما در app همین sp 4 دقیقه طول میکشه و time out میده
یکی از دوستانم گفت که بخاطر sniff کردن Sql است.
اگر RDMBS همیشه به دنبال بهترین روشه پس چرا این اتفاق برای من میفته؟
در sp سه تا join دارم

پ.ن:

RDBMS چیست؟
RDBMS مخفف عبارت RelationalDatabase Management System که معادل سیستم مدیریت پایگاه داده رابطه ای درزبان فارسی است. RDBMS اساس کار SQLمحسوب می شود. و اکثر سیستمهای پایگاه داده مدرن امروزی مثل DB2,Oracle,MySql,MicrosoftAccess نیز از RDBMS استفاده می کنند. در این مدل داده ها درون جداول ذخیره می شوند.و هر جدول نیز از موجودیت های داده ای شامل ستونها و ردیفهاتشکیل شده است.

[مهدی نان شکری]

با سلام
همان طور که می دانید RDBMS در اولین اجرای SP پلن اجرای مناسب را انتخاب و برای اجرا های بعد آن را کش می کند. و در اجرای بعد در صورت وجود Plan آن را Reuse می کند.
در مورد مسئله شما، همان طور که دوست و استاد عزیزم آقای صادقیان مطرح کردند باید Plan اجرا را دید و نظر داد.
در ضمن آیا همین SP با مقادیری که سه ثانیه طول می کشه با همان مقادیر ورودی در برنامه 3 دقیقه طول می کشه؟ آیا مطمئن هستید مشکل در نحوه نمایش داده ها نمی باشد؟ یا جایی در برنامه؟ چون ممکن است دیتابیس جواب را برگردانده باشد ولی برنامه برای نمایش آن نیاز به زمان داشته باشد.

[En_MK]

سلام ممنون از توجهتون
بله یه جورایی مطمئنم
باهمون ورودیها break point میذارم قسمتی از کدم که داره sp رو execute میکنه ولی 3دقیقه روی همان خط میمونه و بعد ارور time out میده
من در sp هیچ تغییری روی ورودیهام نمیدم یعنی convert یا جمع کردن با مقادیر دیگه ندارم

[حمیدرضاصادقیان]

خوب این روش کنترل شما یک جورایی اشتباهه. اگر میخواهید مطمئن بشید که دستور اصلا ارسال میشه یانه میتونید با استفاده از اجرای Profiler یا تنظیم Extended Events روی دیتابیس مورد نظر متوجه بشید که وقتی نرم افزار کوئری رو ارسال میکنه آیا اصلا به سمت دیتابیس رفته یا خیر.شاید داره یک Lock رخ میده که شما از وجود اون بی خبر هستید.
با استفاده از این دو ابزار میتونید متوجه بشید الان مشکل کجاست.