آموزش Activity Monitoring در SQL Server

Activity Monitoring یکی از قدرتمندترین و در دسترس ترین ابزارهای مانیتورینگ داخلی SQL Server است که به شما امکان می دهد عملکرد لحظه ای Instance خود را بررسی کنید . این ابزار به صورت پیش فرض به همراه SQL Server Management Studio (SSMS) نصب می شود و نیازی به نصب جداگانه یا تنظیمات اضافی ندارد .

با استفاده از Activity Monitoring می توانید :

• Performance Bottleneck ها را شناسایی کنید .
• کوئری های سنگین و طولانی را پیدا کنید .
• مشکلات Lock و Bloking را عیب یابی کنید .
• منابع مصرفی مانند CPU ، I/O و حافظه را پایش کنید .

نحوه دسترسی به Activity Monitoring :

• SQL Server Management Studio (SSMS) را اجرا کنید .
• در پنجره Object Explorer بر روی نام Instance راست کلیک کنید .
• از منوی باز شده ، گزینه Activity Monitor را انتخاب کنید .
• صفحه Activity Monitor باز می شود  .

صفحه Activity Monitor شامل پنج قسمت می باشد :

قسمت Overview :

این قسمت شامل چهار نمودار اصلی می باشد که تصویری کلی از سلامت و بار کاری Instance را به شما نمایش می دهد :

Processor Time % :

    درصد استفاده از CPU توسط SQL Server را نشان می دهد . این معیار نشان دهنده میزان فشار محاسباتی بر روی پردازنده است ، عدد بالای 80% به معنای فشار زیاد CPU است .

Waiting Tasks :

    تعداد تسک هایی که در لحظه در حالت انتظار هستند را نمایش می دهد . اگر این عدد به طور مدارم بالا باشد ، نشان دهنده وجود Bottleneck در منابعی مانند I/O ، قفل یا حافظه است .

Database I/O (MB/sec) :

    میزان Read/Write بر روی فایل های دیتابیس را بر حسب مگابایت بر ثانیه نشان می دهد . این معیار برای بررسی فشار بر روی دیسک ها حیاتی است .

Batch Requests/sec :

    تعداد درخواست های دسته ای ( Batch ) که در هر ثانیه به SQL Server ارسال می شوند . هر دستور T-SQL  ( مانند SELECT ,  INSERT , UPDATE ) یک Batch Request محسوب می شود . این عدد نشان دهنده میزان ترافیک و بار تراکنشی است .

نکته فنی : اگر نمودار Waiting Tasks بالا باشد و Processor Time% پایین ، معمولا مشکل از I/O با Locks است ولی اگر هر دو بالا باشند احتمالا CPU گلوگاه اصلی است .

قسمت Processes :

این قسمت مهمترین بخش Activity Monitor است و تمام Sessionها و کوئری های در حال اجرا را به صورت Live نمایش می دهد . ستونهای کلیدی این بخش عبارتند از :

• Session ID : شناسه یکتای هر اتصال به SQL Server می باشد ، نکته مهم، Session ID های کمتر از 50 متعلق به فرآیندهای سیستمی خود SQL Server می باشند و Session ID های بالاتر از 50 متعلق به اتصالات کاربران می باشد .
• User Process : اگر مقدار این ستون 1 باشد نشان دهنده یک فرآیند کاربری است و اگر 0 باشد مربوط به فرآیندهای سیستمی داخلی می باشد .
• Login : نام کاربری ( Login ) که اتصال را ایجاد کرده است . برای مثال sa یا Domain\Username .
• Database : نام دیتابیسی که کوئری در حال اجرا بر روی آن می باشد .
• Task State : وضعیت اجرای تسک ها را نشان می دهد . مقادیر رایج :
  • Running : در حالت اجرا بر روی CPU .
  • Suspended : منتظر آزاد شدن یک منبع .
  • Sleeping : اتصال باز است اما هیچ درخواستی در حال اجرا نیست .
  • Pending : منتظر زمان CPU است .
• Command : نوع فرمان یا دستوری که در حال اجرا است ، مانند SELECT ، UPDATE ، DELETE ، BACKUP DATABASE یا AWAITING COMMAND ( منتظر دستور بعدی ) .
• Application : نام برنامه ای که درخواست را ارسال کرده است .
• Wait Time (ms) :مدت زمانی که تسک در حالت انتظار قرار داشته است . اگر این عدد بالاست، نشان دهنده منتظر بودن برای یک منبع خاص است ( این زمان بر حسب میلی ثانیه است ) .
• Blocked By : این ستون نشان می دهد که session  جاری توسط کدام session دیگر بلاک شده است . اگر مقدار 0 باشد یعنی بلاک نشده است . اگر عددی مانند 55 باشد ، یعنی session جاری منتظر است تا session شماره 55 کار خود را تمام کرده و قفل را آزد کند .
• Head Blocker : این ستون مشخص می کند کدام session ، ریشه زنجیره بلاکینگ است . به عبارتی دیگر ، session ی که خودش توسط هیچکس بلاک نشده، اما دیگر session ها را بلاک کرده است . برای رفع بلاکینگ باید این session را پیدا کرده و Kill کنیم .

قسمت Resource Waites :

این قسمت به شما نشان می دهد که SQL Server در حال انتظار چه نوع منابعی است و این انتظارها چه مدتی طول کشیده است . اطلاعات این بخش بر حسب میلی ثانیه نمایش داده می شود . انواع Resource Waite ها عبلارتند از :

• Lock : منتظر آزاد شدن قفل روی یک ردیف ، Page یا جدول می باشد . این نوع نشان دهنده بلاکینگ است .
• Latch : منتظر یک ساختار حافظه داخلی است .
• Buffer I/O : منتظر خواندن یا نوشتن از روی دیسک می باشد و نشان دهنده فشار بر روی I/O است .
• Logging : منتظر نوشتن لاگ تراکنش بر روی دیسک . معمولا در تراکنش های سنگین رخ می دهد .
• Memory : منتظر تخصیص حافظه می باشد و نشان دهنده کمبود حافظه است .

نکته : اگر بیشترین زمان انتظار مربوط به Buffer I/O باشد باید به فکر بهینه سازی دیسک ها یا ایندکس ها باشید . اگر Lock باشد باید به عیب یابی بلاکینگ بپردازید .

قسمت Data File I/O :

در این قسمت ، اطلاعات مربوط به عملیات Read و Write بر روی فایل های فیزیکی دیتابیس ( mdf , ldf , ndf ) نمایش داده می شود .

• ستون Database : نام دیتابیس .
• ستون File : نام فیزیکی فایل .
• ستون Reads(MB/sec) : میزان داده خوانده شده در ثانیه .
• Write (MB/sec) : میزان داده نوشته شده در ثانیه .
• ستون Response Time (ms) : میانگین زمان پاسخ دهی ( تاخیر ) برای هر عملیات I/O . هر چه این عدد پایین تر باشد ، عملکرد دیسک بهتر است .

نکته : اگر Response Time بالای 20 میلی ثانیه باشد ، نشان دهنده عملکرد ضعیف زیرساخت ذخیره سازی است .

قسمت Recent Expensive Queries :

این بخش لیستتی از کوئری هایی که اخیرا اجرا شده اند و Cost بالایی داشته اند را نمایش می دهد . 

• CPU Time : زمان مصرفی CPU .
• Logical Reads : تعداد Page های خوانده شده از حافظه کش .
• Logical Writes : تعداد Page های نوشته شده در حافظه .
• Physical Reads : تعداد Page های خوانده شده از دیسک .

همچنین نام دیتابیس و متن کوئری در این بخش قابل مشاده است .

قسمت Active Expensive Queries :

این بخش فقط کوئری هایی را نشان می دهد که در همین لحظه در حال اجرا هستند و هزینه بالایی دارند . اطلاعات نمایش داده شده شامل موارد زیر است :

• CPU Time : زمان مصرفی CPU تا این لحظه .
• Physical Reads : تعداد خواندن از دیسک تا این لحظه .
• Logical Reads : تعداد خواندن از حافظه کش .
• Session ID : شناسه session ی که کوئری را اجرا می کند .
• Database : نام دیتابیس مقصد .
• Plan Handle / Query Text : نمایش بخشی از متن کوئری .

کاربرد : اگر سیستم شما  به یکباره کند شد ، با مراجعه به این بخش می توانید سریعا کوئری های سنگین و مشکل ساز را پیدا کرده و آنها را Kill یا بهینه سازی کنید .

نکات طلایی برای عیب یابی با Activity Monitor :

کندی ناگهانی سیستم :

    به قسمت Processes بروید و ستون Task State را بررسی کنید . اگر تعداد زیادی Suspended دیدید ، به ستون Blocked By  نگاه کنید تا زنجیزه بلاکینگ را پیدا کنید .

مصرف بالای CPU :

    به نمودار Processor Time%  در Overview نگاه کنید . سپس به سربرگ Active Expensive Queries بروید و کوئری های با CPU Time بالا را بیابید .

مشکل در دیسک ها :

    به سربرگ Data File I/O بروید و ستون Response Time را بررسی کنید . اگر بالاست به Resource Waite بروید و میزان Buffer I/O را ببینید .

کوئری های تکراری سنگین :

    به قسمت Recent Expensive Querries بروید و الگوهای تکراری را پیدا کنید . این کوئری ها کاندیدای خوبی برای ایندکس گذاری یا بازنویسی هستند .