شما یک پایگاه داده SQL Server دارید و یکی از یا چند تا از کوئریهای شما به آرامی اجرا میشوند. شما باید بفهمید چرا اینطور است. ممکن است راهحل خاص مشکلتان را در این پست پیدا نکنید، اما اولین قدم عیبیابی را خواهید آموخت که میتواند شما را از صرف روزها وقت برای مسیر اشتباه در تشخیص و حل مشکل نجات دهد.
بعد از خواندن این مطلب، شما با سه دستهبندی که یک کوئری کند میتواند در آن قرار بگیرد آشنا خواهید شد و نحوه شناسایی اینکه کدام یک از این دستهها مناسب مشکل شماست را خواهید دانست. از آنجا، شما میتوانید تحقیقات و سایر مراحل عیبیابی خود را برای یافتن راهحل مشکل سریعتر متمرکز کنید.
دستهبندی ۱: کوئری به آرامی اجرا میشود
طبق تجربه من تا کنون، بیشتر کوئریهای کند در این دسته قرار میگیرند. کوئریهایی که در این دسته قرار دارند به دلیل اینکه مراحل اجرای آنها (برنامه اجرایی) منابع زیادی مصرف میکند و یا زمان زیادی میبرد، به طور کند اجرا میشوند. به عنوان مثال، ممکن است کوئری شما در حال انجام یک Clustered Index Scan روی یک جدول با دهها میلیون ردیف باشد که این کار میتواند زمان زیادی ببرد.
علتهای مختلفی برای این نوع کوئری کند وجود دارد که شامل، اما محدود به موارد زیر میشود:
عدم وجود ایندکسها
SQL نوشتهشده ضعیف
ایندکسهای بیش از حد (در موارد درج، بهروزرسانی و حذف)
Parameter Sniffing
راهحل واقعی برای مشکل شما بستگی به علت دقیق آن دارد، اما اگر متوجه شوید که کوئری شما واقعاً کند اجرا میشود، در این صورت تحقیقات خود را محدود کردهاید و در مسیر درست برای پیدا کردن راهحل قرار میگیرید.
دستهبندی ۲: کوئری به آرامی کامپایل میشود
وقتی شما یک کوئری را اجرا میکنید، SQL Server نمیتواند آن را اجرا کند تا زمانی که برنامه اجرایی (Execution Plan) داشته باشد. یک برنامه اجرایی مجموعهای از مراحل است که SQL Server برای اجرای کوئری شما انجام میدهد — مراحل فیزیکی و منطقی واقعی. این فرایند به نام کامپایل کردن برنامه اجرایی شناخته میشود. برای مثال، فرض کنید شما کوئری زیر را اجرا کنید:
SELECT * FROM dbo.Users WHERE CreatedDate >= '۲۰۲۳-۰۱-۰۱'
برای اجرای واقعی این کوئری، SQL Server ممکن است یک برنامه اجرایی به این شکل کامپایل کند:
اسکن ایندکس غیر خوشهای (
IX_Users_CreatedDate)جستجو کلید با ایندکس خوشهای
حلقههای تو در تو برای پیوستن نتایج مراحل ۱ و ۲
موازیسازی (Gather streams)
SELECT
برای بیشتر کوئریها، زمان کامپایل برنامه اجرایی بسیار کم است، مثلاً ۳۰ میلیثانیه (ms). کوئری بالا ممکن است تنها ۱ میلیثانیه زمان ببرد؛ با این حال، کوئریهای پیچیدهتر ممکن است زمان بیشتری برای کامپایل نیاز داشته باشند. بدترین موردی که در محیط تولید دیدم ۳۰ ثانیه بود که بسیار ضعیف است! در واقع، وقتی یک کوئری زمان کامپایل بسیار طولانی دارد، ممکن است شما حتی پیامی مانند “Compilation time out” مشاهده کنید که به این معناست که SQL Server از تلاش برای پیدا کردن بهترین برنامه اجرایی دست کشیده و گفته است: “فراموشش کن، با چیزی که داریم ادامه میدهیم.”
با چنین کوئریای، ممکن است اجرای واقعی آن خیلی سریع انجام شود، اما هزینه اولیه کامپایل ممکن است شما را به شدت تحت تأثیر قرار دهد: مخصوصاً اگر برنامههای شما از دستور OPTION(RECOMPILE) استفاده میکنند تا برنامه اجرایی هر بار کامپایل شود.
توجه: زمان کامپایل طولانی ممکن است تأثیر زیادی روی سیستم نداشته باشد چون SQL Server فقط زمانی برنامه اجرایی را کامپایل میکند که در Plan Cache آن برنامهای ذخیرهشده نباشد. این بدان معناست که برای یک کوئری پیچیده، ممکن است ۲۰ ثانیه طول بکشد تا برنامه اجرایی کامپایل شود، اما بعد از آن SQL Server دیگر نیازی به صرف زمان برای کامپایل مجدد برنامه اجرایی در اجراهای بعدی همان کوئری نخواهد داشت؛ اما اگر دلایلی مانند بروزرسانی آمار جدولها اتفاق بیفتد، برنامه اجرایی از Plan Cache حذف میشود و در نتیجه ممکن است طولانی شدن زمان کامپایل به یک مشکل واقعی تبدیل شود.
در تجربه من از عیبیابی مشکلات عملکرد SQL Server، زمان کامپایل معمولاً علت اصلی کندی کوئریها نیست، اما وقتی زمان کامپایل مشکل است، میتوانید ساعتها یا روزها وقت خود را صرف کنید، اگر متوجه نشوید که کوئری فقط زمان زیادی برای کامپایل شدن نیاز دارد.
دستهبندی ۳: کوئری منتظر چیزی است
احتمالاً سرور تولیدی شما مشغول به کارهای زیادی است. کوئری که شما اجرا میکنید تنها چیزی نیست که SQL Server باید به آن توجه کند. ممکن است کوئری دیگری وجود داشته باشد که نیاز به بهروزرسانی همان دادههایی دارد که شما قصد خواندن آنها را دارید، یا کوئریهای دیگر تمام منابع CPU یا RAM سیستم را اشغال کردهاند و غیره. این مسائل، همراه با مجموعهای از رویدادهای دیگر، باعث میشود کوئری شما منتظر آزاد شدن یک قفل یا آزاد شدن مقداری RAM باشد تا بتواند شروع به اجرا کند.
به عبارت دیگر، کوئری کند شما در واقع کند نیست — تنها مجبور است مدت زمان زیادی منتظر بماند تا مجاز به شروع به اجرا شود — اما تأثیر آن روی برنامهها و کاربران نهایی شما همانند کندی کوئری است. تفاوت اصلی این است که تلاشهای عیبیابی شما احتمالاً باید روی کوئریهای دیگر متمرکز شود تا این کوئری مشکل را حل کند. بنابراین، اگر کوئری شما منتظر آزاد شدن یک قفل است، ممکن است نیاز داشته باشید کوئری دیگری را تنظیم کنید تا قفلهای کمتری بگیرد یا آنها را مدت زمان کمتری نگه دارد.
اگر کوئریهای زیادی در سیستم شما وجود داشته باشند که به آرامی اجرا میشوند یا زمانشان تمام میشود، احتمالاً مشکل شما در این دسته قرار میگیرد.
چگونه تعیین کنم که کوئری من در کدام دسته قرار میگیرد؟
بهترین راه برای تشخیص اینکه کوئری کند خاص شما در کدام دسته قرار دارد، بازتولید مشکل با اجرای دستی کوئری در SQL Server Management Studio و ضبط برنامه اجرایی واقعی آن است. برنامه اجرایی اطلاعات بسیار دقیقی دارد که به شما کمک میکند کوئری کند خود را دستهبندی کنید. در ادامه دستورالعملهایی برای این کار ارائه میدهم و سپس برخی مثالها را نشان خواهم داد.
نحوه دریافت برنامه اجرایی واقعی
SQL Server Management Studio را باز کنید و یک پنجره کوئری برای پایگاه داده مربوطه باز کنید.
کلیدهای
CTRL + Mرا فشار دهید تا ضبط برنامه اجرایی واقعی کوئری شما فعال شود. همچنین دکمهای مستقیماً بالای پنجره کوئری وجود دارد که میتوانید آن را کلیک کنید — این دکمه “Include Actual Execution Plan” را به عنوان راهنمایی زمانی که روی آن حرکت کنید نمایش میدهد.کوئری خود را اجرا کنید.
وقتی که کوئری تمام شد، یک تب “Execution Plan” کنار تبهای “Results” و “Messages” در زیر پنجره کوئری ظاهر خواهد شد. روی تب “Execution Plan” کلیک کنید تا برنامه اجرایی را مشاهده کنید.
در هر قسمت از تب برنامه اجرایی، راستکلیک کنید و “Show Execution Plan XML” را انتخاب کنید. نمای XML بهترین راه برای دسترسی به اطلاعات خاصی است که به دنبال آن هستیم.
تحلیل برنامه اجرایی
بررسی تأخیرها
زمانی که XML برنامه اجرایی خود را باز کردید، میتوانید آن را بررسی کنید تا ببینید کوئری در حال صرف وقت در انجام چه کاری است. ابتدا با استفاده از CTRL + F جستجویی برای “WaitStats” انجام دهید. وقتی این جستجو را انجام میدهید، ممکن است چیزی شبیه به این ببینید:
<WaitStats>
<Wait WaitType=”LCK_M_S” WaitTimeMs=”38265″ WaitCount=”1″ />
</WaitStats>
این بخش از XML فهرستی از “تأخیرها” را نشان میدهد که در هنگام تلاش برای اجرای کوئری اتفاق افتاده است. در این مورد، ما نوع “LCK_M_S” از تأخیر را مشاهده میکنیم و کوئری ۳۸ ثانیه منتظر مانده است! این کوئری واقعاً ۳۸ ثانیه برای اجرا در SSMS زمان برده است، بنابراین علت مشکل خود را پیدا کردهام. حالا، اگر جستجویی در موتور جستجو برای “SQL Server LCK_M_S” انجام دهم، متوجه میشوم که این به این معناست که کوئری من نیاز داشت که یک “قفل مشترک” روی یک منبع (در اینجا یک جدول) بگیرد و برای گرفتن آن قفل ۳۸ ثانیه منتظر مانده است. حالا جهت خاصی برای تحقیق و عیبیابی دارم.
در SQL Server انواع مختلفی از تأخیرها وجود دارد و شما ممکن است با توجه به نوع تأخیری که باعث کندی کوئری شما شده، در جهتهای مختلفی برای عیبیابی حرکت کنید. در مورد تأخیر قفل مانند این، ممکن است از ابزاری مانند sp_whoisactive استفاده کنم تا ببینم کدام تراکنش/کوئری دیگری کوئری من را مسدود کرده است. از سوی دیگر، اگر کوئری شما زمان زیادی را در تأخیر “PAGEIOLATCH_EX” صرف کند، ممکن است از گزارش “Top Resource Consuming Queries” در Query Store استفاده کنید تا مشخص کنید کدام کوئریها بیشترین خواندن فیزیکی را انجام میدهند.
بررسی زمان کامپایل
اگر مجموع “WaitTimeMs” در بخش WaitStats قابل توجه نیست یا اصلاً هیچ بخش “WaitStats” وجود ندارد، در مرحله بعد باید بررسی کنید که کوئری چقدر زمان میبرد تا کامپایل شود. برای این کار، در XML برنامه اجرایی خود جستجویی برای عبارت “CompileTime” انجام دهید. شما باید چیزی شبیه به این پیدا کنید:
برای یک کوئری ساده باید زمان “CompileTime” حدود ۰ میلیثانیه باشد، یا شاید تا ۱۰ میلیثانیه، اما در اینجا ما ۵۹۴۳ میلیثانیه را مشاهده میکنیم، که چیزی کمتر از ۶ ثانیه است! این بدین معناست که SQL Server 6 ثانیه صرف کرده است تا یک برنامه اجرایی برای این کوئری آماده کند. این زمان قبل از اجرای واقعی کوئری است. شما ممکن است این را برای کوئریهای پیچیده ببینید، مثلاً کوئریهایی که دارای زیرکوئریهای تو در تو یا تعداد زیادی JOIN هستند. در یک مورد، من کوئریای دیدم که زمان کامپایل آن بیش از ۳۰ ثانیه بود در حالی که تایماوت سمت اپلیکیشن ۳۰ ثانیه بود، بنابراین این کوئری خاص در حال زمانتمام شدن بود و باعث خطا برای کاربران نهایی میشد.
یک نکته در اینجا وجود دارد: SQL Server تنها باید این زمان را در اولین اجرای کوئری صرف کند. اجرایهای بعدی باید تنها از همان برنامه اجرایی استفاده کنند و زمان کامپایل را نادیده بگیرند. بنابراین چیزی که شما در صورت بروز مشکل در زمان کامپایل خواهید دید این است که کوئری در اولین بار کند است، اما در دفعات بعدی سریعتر اجرا میشود.
حل این مشکل میتواند بسیار کمزحمت باشد (آخرین باری که با این مشکل مواجه شدم، ۱۵ دقیقه زمان برد تا راهحلی پیدا کنم)، بنابراین هرچه سریعتر بتوانید این مشکل را شناسایی کنید، سریعتر به راهحل خواهید رسید.
بررسی زمان اجرا
اگر قبلاً زمان کامپایل و زمان انتظار را به عنوان علت کندی کوئری خود حذف کردهاید، تنها احتمال سوم باقی میماند — این کوئری واقعاً کند است.
نتیجهگیری
وقتی با یک مشکل عملکرد پایگاه داده در SQL Server مواجه میشوید، ممکن است تلاش برای عیبیابی آن احساس سنگینی کند، اما اولین قدم همیشه جمعآوری اطلاعات است. اگر با مراحل توضیح داده شده در این مقاله شروع کنید، میتوانید به سرعت مشکل را شناسایی کرده و سریعتر به جواب برسید. به یاد داشته باشید که اولین قدم در مسیر عیبیابی شما پیدا کردن این است که کوئری شما چه مدت زمان صرف میکند: کامپایل، انتظار یا اجرا.
عیبیابی عملکرد SQL Server فرآیندی پیچیده است که نیاز به تحلیل دقیق دادهها و استفاده از ابزارهای مختلف دارد. با استفاده از ابزارهای نظارت، بررسی برنامههای اجرایی، تحلیل تأخیرها و مشکلات منابع، میتوان به سرعت مشکلات عملکردی را شناسایی کرده و آنها را برطرف کرد.
