راهکارهای افزایش بهره وری در Stored Procedure:
1- خاموش کردن فلگ ها و تنظیمات اضافی
- در ابتدای Stored Procedure می توان برخی تنظیمات غیرضروری را خاموش کرد:
- `SET NOCOUNT ON` : باعث می شود پیام "X rows affected" برنگردد و از ترافیک اضافی جلوگیری شود.
- `SET XACT_ABORT ON` : در صورت خطا، تراکنش سریع تر Rollback می شود.
- این موارد به خصوص در پروسیجرهایی که زیاد فراخوانی می شوند، تاثیر محسوسی دارند.
2- استفاده از ایندکس مناسب
- طراحی ایندکس های Covering برای ستون هایی که در `WHERE`, `JOIN`, `ORDER BY` استفاده می شوند.
- اجتناب از ایندکس های غیرضروری یا تکراری.
3- اجتناب از SELECT *
- فقط ستون های مورد نیاز را انتخاب کن.
- این کار هم حجم داده را کم می کند و هم Execution Plan ساده تر می شود.
4- پارامترها و Sniffing
- مشکل Parameter Sniffing می تواند باعث شود Execution Plan برای یک مقدار خاص ساخته شود و برای مقادیر دیگر ناکارآمد باشد.
- راه حل:
- استفاده از `OPTION (RECOMPILE)` برای کوئری های حساس.
- یا ذخیره مقدار پارامتر در متغیر داخلی و استفاده از آن در شرط ها.
5- تراکنش ها
- تراکنش ها را تا حد امکان کوتاه نگه دار.
- از Lock طولانی روی جداول پرهیز کن.
- فقط بخش های ضروری را داخل `BEGIN TRAN` و `COMMIT` قرار بده.
6- استفاده از SET-based Operations
- به جای Loop یا Cursor، از عملیات مبتنی بر مجموعه (Set-based) استفاده کن.
- SQL Server برای کار با مجموعه داده ها بهینه تر است.
7- مدیریت خروجی ها
- اگر نیاز به بازگرداندن داده نیست، از `RETURN` یا `OUTPUT` پارامترها استفاده کن.
- اگر نیاز به چندین نتیجه داری، بهتر است از چندین SELECT با `SET NOCOUNT ON` استفاده شود.
8- مانیتورینگ و Execution Plan
- همیشه Execution Plan را بررسی کن تا بفهمی Bottleneck کجاست.
- از DMVها مثل `sys.dm_exec_query_stats` و `sys.dm_exec_cached_plans` برای مانیتورینگ استفاده کن.
بهینه سازی Stored Procedure در SQL Server
سید حامد واحدی
4 آذر 1404