عیب یابی عملکرد آهسته SQL Server ناشی از مشکلات I/O

در این مقاله راهنمایی در مورد آنچه در مورد مسائل I/O ایجاد می شود باعث عملکرد آهسته سرور SQL و نحوه عیب یابی مسائل می شود.

عملکرد آهسته I/O را تعریف کنید

از پیشخوان های مانیتور برای تعیین آهسته I/O استفاده می شود. این پیشخوان ها چقدر سریع خدمات زیر سیستم I/O را هر درخواست I/O به طور متوسط از نظر زمان ساعت اندازه می گیرند. پیشخوان های مانیتور عملکرد خاص که تأخیر I/ O را در ویندوز اندازه می گیرند ، AVG Disk Sec/ Read ، AVG هستند. دیسک sec/نوشتن ، و avg. دیسک Sec/Transfer (تجمعی هر دو خواندن و نوشتن).

در سرور SQL ، همه چیز به همان روش کار می کند. معمولاً ، شما می بینید که آیا سرور SQL هر تنگنا I/O را در زمان ساعت (میلی ثانیه) اندازه گیری می کند. SQL Server با فراخوانی توابع WIN32 مانند WritFile () ، ReadFile () ، WriteFileGather () و ReadFilescatter () درخواست های I/O را به سیستم عامل می دهد. هنگامی که درخواست I/O را ارسال می کند ، SQL Server درخواست را بار می کند و مدت زمان درخواست را با استفاده از انواع انتظار گزارش می دهد. SQL Server از انواع انتظار برای نشان دادن انتظار I/O در مکان های مختلف محصول استفاده می کند. انتظار مربوط به I/O عبارتند از:

• pageiolatch_sh / pageioLatch_ex
• نوشته
• io_completion
• async_io_completion
• دارای پشتیبان گیری

اگر این انتظار بیش از 10-15 میلی ثانیه به طور مداوم باشد ، I/O یک تنگنا محسوب می شود.

برای ارائه زمینه و دیدگاه ، در دنیای عیب یابی SQL Server ، Microsoft CSS مواردی را مشاهده کرده است که یک درخواست I/O یک ثانیه و 15 ثانیه در هر انتقال-سیستم I/O بهینه سازی می کند. در مقابل ، مایکروسافت CSS سیستم هایی را دیده است که توان آن زیر یک میلی ثانیه/انتقال است. با استفاده از فناوری SSD/NVME امروز ، نرخ توان تبلیغاتی در ده ها میکرو ثانیه در هر انتقال است. بنابراین ، شکل 10-15 میلی ثانیه/انتقال یک آستانه بسیار تقریبی است که ما بر اساس تجربه جمعی بین مهندسان ویندوز و سرور SQL در طول سالها انتخاب کردیم. معمولاً ، هنگامی که اعداد فراتر از این آستانه تقریبی پیش می روند ، کاربران SQL Server شروع به دیدن تأخیر در بارهای کاری خود می کنند و آنها را گزارش می دهند. در نهایت ، توان مورد انتظار یک زیر سیستم I/O توسط سازنده ، مدل ، پیکربندی ، حجم کار و عوامل بالقوه دیگر تعریف می شود.

روش شناسی

نمودار جریان در پایان این مقاله ، روش شناسی Microsoft CSS را برای نزدیک شدن به مسائل آهسته I/O با SQL Server شرح می دهد. این یک رویکرد جامع یا منحصر به فرد نیست اما در جداسازی مسئله و حل آن مفید بوده است.

برای حل مشکل می توانید یکی از دو گزینه زیر را انتخاب کنید:

گزینه 1: مراحل را مستقیماً در یک نوت بوک از طریق Azure Data Studio اجرا کنید

قبل از تلاش برای باز کردن این نوت بوک ، اطمینان حاصل کنید که Azure Data Studio روی دستگاه محلی شما نصب شده است. برای نصب آن ، به یادگیری نحوه نصب Azure Data Studio بروید.

گزینه 2: مراحل را به صورت دستی دنبال کنید

این روش در این مراحل بیان شده است:

مرحله 1: آیا SQL Server گزارش I/O کند؟

SQL Server ممکن است تأخیر I/O را از چند طریق گزارش دهد:

• I/O انواع منتظر
• dmv sys. dm_io_virtual_file_stats
• ورود به سیستم خطا یا ورود به سیستم رویداد

I/O انواع منتظر

تعیین کنید که آیا تأخیر I/O گزارش شده توسط SQL Server Way Types وجود دارد یا خیر. مقادیر PageioLatch_* ، WritElog و Async_io_completion و مقادیر چندین نوع انتظار کمتر متداول دیگر باید به طور کلی زیر 10-15 میلی ثانیه در هر درخواست I/O باقی بمانند. اگر این مقادیر به طور مداوم بیشتر باشد ، یک مشکل عملکرد I/O وجود دارد و نیاز به تحقیقات بیشتر دارد. پرس و جو زیر ممکن است به شما در جمع آوری این اطلاعات تشخیصی در سیستم خود کمک کند:

#Replace با سرور  نمونه یا سرور برای نمونه پیش فرض $ sqlserver_instance = "سرور  نمونه" برای ([int] $ i = 0 ؛ $ i-lt 100 ؛ $ i ++)

آمار پرونده در sys. dm_io_virtual_file_stats

برای مشاهده تأخیر در سطح پرونده پایگاه داده همانطور که در SQL Server گزارش شده است ، پرس و جو زیر را اجرا کنید:

#Replace با سرور  نمونه یا سرور برای نمونه پیش فرض $ sqlserver_instance = "سرور  نمونه" SQLCM D-E -S $ SQLSERVER_INSTANCE "سمت چپ را انتخاب کنید (mf. physical_name ، 100) ،` readLatency = موردی که num_of_reads = 0 other 0 other دیگر 0 دیگر(io_stall_read_ms / num_of_reads) پایان ، `writelatency = موردی که num_of_writes = 0 سپس 0 other (io_stall_write_ms / num_of_writes) پایان ،` avglatency = کیس (num_of_reads = 0 و num_of_writes = 0) دیگری (Io_stall / 0)) پایان ، `latencyassessment = موردی که (num_of_reads = 0 و num_of_writes = 0) سپس" بدون داده "دیگری" مورد (io_stall / (num_of_reads + num_of_writes)))<2 THEN 'Excellent' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN 'Very good' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN 'Good' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN 'Poor' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN 'Bad' ` ELSE 'Deplorable' END END, ` [Avg KBs/Transfer] = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ` ELSE ((([num_of_bytes_read] + [num_of_bytes_written]) / (num_of_reads + num_of_writes)) / 1024) END, ` LEFT (mf.physical_name, 2) AS Volume, ` LEFT(DB_NAME (vfs.database_id),32) AS [Database Name]` FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs ` JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id ` AND vfs.file_id = mf.file_id ` ORDER BY AvgLatency DESC" 

برای درک جزئیات تأخیر به ستون های Avglatency و LatencyAssessment نگاه کنید.

خطا 833 گزارش شده در خطای خطا یا ورود به سیستم برنامه

در بعضی موارد ، شما ممکن است خطای 833 SQL Server را مشاهده کرده اید که در مورد I/O درخواست های I/O با بیش از ٪ D ثانیه برای تکمیل پرونده [٪ LS] در پایگاه داده [٪ LS] (٪ D) در این پرونده روبرو شده است. ورود به خطابا اجرای دستور PowerShell زیر می توانید سیاهههای خطای سرور SQL را روی سیستم خود بررسی کنید:

Ge t-ChildIte m-Path "C:  Files Program  Microsoft SQL Server  MSSQL*" -Recurs e-include ErrorLog |رشته انتخاب "وقوع (های) درخواست I/O بیشتر از 15 ثانیه طول می کشد" 

همچنین ، برای اطلاعات بیشتر در مورد این خطا ، به بخش MSSQLSERVER_833 مراجعه کنید.

مرحله 2: آیا پیشخوان های Perfmon نشان دهنده تأخیر I/O هستند؟

اگر SQL Server I/O Latence را گزارش می کند ، به پیشخوان های سیستم عامل مراجعه کنید. می توانید با بررسی پیشخوان تأخیر AVG دیسک/انتقال ، مشخص کنید که آیا مشکل I/O وجود دارد یا خیر. قطعه کد زیر یکی از راه های جمع آوری این اطلاعات از طریق PowerShell را نشان می دهد. این پیشخوان ها را در تمام حجم دیسک جمع می کند: "_total". تغییر به یک حجم درایو خاص (به عنوان مثال ، "D:"). برای پیدا کردن کدام حجم میزبان پرونده های پایگاه داده خود ، پرس و جو زیر را در سرور SQL خود اجرا کنید:

#Replace با سرور  نمونه یا سرور برای نمونه پیش فرض $ sqlserver_instance = "سرور  نمونه" sqlcm d-e -s $ sqlserver_instanc e-q "انتخاب چپ مجزا (volume_mount_point ، 32) را به عنوان volue_mount_point` از sys. master_files f `صلیب استفاده کنید. dm_os_volume_stats (f. database_id ، f. file_id) در مقابل " 

جمع آوری معیارهای SEC/انتقال Disk/انتقال بر روی حجم مورد نظر خود:

پاک کردن $ cntr = 0 # با نام سرور خود جایگزین کنید ، مگر اینکه رایانه محلی $ serveame = $ env: computeame # با نام حجم خود جایگزین کنید - c: ، d :، $ volumename = "_total" $ پیشخوان = @("" $ serveame " +"  logicaldisk ($ volumename)  avg. disk sec/transfer ")) $ diskectransfer = ge t-counte r-counter $ پیشخوا ن-maxsamples 1 $ avg = $ ($ diskectransfer. countersamples | انتخا ب-اوت پخته شده). CookedValue Ge t-Counte r-conter $ پیشخوا ن-sampleinterval 2-maxsamples 30 |سرنخ<$_.CounterSamples | ForEach-Object <[pscustomobject]@| Format-Table>>نوشت ن-میزبان "Final_Running_a AVERAVE: $ ([MATH] :: دور ($ avg ، 5)) Sec/Transfer`n" اگر ($ AV G-GT 0. 01)دیگر

اگر مقادیر این پیشخوان به طور مداوم بالاتر از 10-15 میلی ثانیه باشد ، باید بیشتر به مسئله نگاه کنید. سنبله های گاه به گاه در بیشتر موارد به حساب نمی آیند ، اما حتماً مدت زمان سنبله را دو بار بررسی کنید. اگر سنبله یک دقیقه یا بیشتر به طول انجامید ، بیشتر از یک سنبله یک فلات است.

اگر پیشخوان های مانیتور عملکرد تأخیر را گزارش نمی کنند ، اما SQL Server چنین می کند ، مشکل بین SQL Server و مدیر پارتیشن ، یعنی درایورهای فیلتر است. مدیر پارتیشن یک لایه I/O است که در آن سیستم عامل پیشخوان های Perfmon را جمع می کند. برای پرداختن به تأخیر ، از محرومیت های مناسب درایورهای فیلتر و رفع مشکلات درایور فیلتر اطمینان حاصل کنید. درایورهای فیلتر توسط برنامه هایی مانند نرم افزار ضد ویروس ، راه حل های پشتیبان ، رمزگذاری ، فشرده سازی و غیره استفاده می شوند. می توانید از این دستور برای لیست درایورهای فیلتر روی سیستم ها و حجم هایی که به آنها وصل می شوند استفاده کنید. سپس می توانید نام درایور و فروشندگان نرم افزار را در مقاله تخصیص داده شده در ارتفاعات جستجو کنید.

نمونه های FLTMC 

از استفاده از سیستم فایل رمزگذاری (EFS) و فشرده سازی سیستم فایل خودداری کنید زیرا باعث می شود I/O ناهمزمان همزمان شود و در نتیجه کندتر شود. برای اطلاعات بیشتر ، به دیسک ناهمزمان I/O به عنوان همزمان در مقاله ویندوز ظاهر می شود.

مرحله 3: آیا زیر سیستم I/O فراتر از ظرفیت است؟

اگر SQL Server و سیستم عامل نشان می دهد که زیر سیستم I/O کند است ، بررسی کنید که آیا علت این سیستم فراتر از ظرفیت است. شما می توانید با نگاه کردن به پیشخوان های I/O Disk Bytes/Sec ، Disk Read Bytes/Sec یا Disk Writ Bytes/Sec ، ظرفیت را بررسی کنید. برای مشخصات توان پیش بینی شده برای SAN (یا سایر زیر سیستم I/O) با مدیر سیستم یا فروشنده سخت افزار خود تماس بگیرید. به عنوان مثال ، شما می توانید بیش از 200 مگابایت در ثانیه از I/O را از طریق کارت 2 گیگابایتی در ثانیه HBA یا درگاه اختصاصی 2 گیگابایتی/ثانیه در سوئیچ SAN فشار دهید. ظرفیت توان پیش بینی شده تعریف شده توسط یک سازنده سخت افزار ، نحوه عملکرد شما از اینجا را مشخص می کند.

پاک کردن $ serveame = $ env: computeame $ پیشخوان = @("\ $ serveame" +" ficaldisk (*)  disk bytes/sec") ، ("\ $ serveame" +" physicalDisk (*) دیسک خواندن بایت/ثانیه ") ، (" \ $ serveame " +"  ficaldisk (*)  دیسک نوشتن بایت/sec ")) Ge t-Counte r-counter $ پیشخوا ن-sampleinterval 2-maxsamples 20 |سرنخ<$_.CounterSamples | ForEach-Object <[pscustomobject]@>> 

مرحله 4: آیا سرور SQL فعالیت I/O سنگین را هدایت می کند؟

اگر زیر سیستم I/O فراتر از ظرفیت غرق شده است ، با مراجعه به مدیر بافر ، SQL Server مقصر است: صفحه خواندن/SEC (متداول ترین مقصر) و صفحه (بسیار کمتر رایج) برای مثال خاص. اگر SQL Server اصلی ترین درایور I/O است و حجم I/O فراتر از آنچه سیستم می تواند اداره کند ، سپس با تیم های توسعه برنامه یا فروشنده برنامه کار کنید:

• به عنوان مثال ، نمایش داده شد: شاخص های بهتر ، آمار به روزرسانی ، بازنویسی نمایش داده ها و طراحی مجدد پایگاه داده.
• حافظه سرور MAX را افزایش دهید یا RAM بیشتری را روی سیستم اضافه کنید. RAM بیشتر داده ها یا صفحات فهرست را بدون خواندن مجدد از دیسک ذخیره می کند ، که باعث کاهش فعالیت I/O می شود.

دلیل

به طور کلی ، موضوعات زیر دلایل سطح بالایی است که چرا نمایش داده شدگان سرور SQL از تأخیر I/O رنج می برند:

• مشکلات سخت افزاری:
  • یک تنظیم غلط سان (سوئیچ ، کابل ، HBA ، ذخیره سازی)
  • بیش از ظرفیت I/O (نامتعادل در کل شبکه SAN ، نه فقط ذخیره سازی پشتی)
  • درایور یا مشکلات سیستم عامل

  فروشندگان سخت افزار و/یا مدیران سیستم باید در این مرحله درگیر شوند.

  نمایش گرافیکی روش شناسی

  

  اطلاعات مربوط به انواع انتظار مربوط به I/O

  در زیر توضیحات مربوط به انواع انتظار مشترک مشاهده شده در SQL Server هنگام گزارش Disk I/O گزارش شده است.

  pageiolatch_ex

  هنگامی اتفاق می افتد که یک کار در یک درخواست I/O در یک صفحه داده یا صفحه فهرست (بافر) منتظر باشد. درخواست قفل در حالت اختصاصی است. از حالت اختصاصی هنگام نوشتن بافر روی دیسک استفاده می شود. انتظار طولانی ممکن است مشکلات زیر سیستم دیسک را نشان دهد.

  pageiolatch_sh

  هنگامی اتفاق می افتد که یک کار در یک درخواست I/O در یک صفحه داده یا صفحه فهرست (بافر) منتظر باشد. درخواست قفل در حالت مشترک است. حالت مشترک هنگام خواندن بافر از دیسک استفاده می شود. انتظار طولانی ممکن است مشکلات زیر سیستم دیسک را نشان دهد.

  pageiolatch_up

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

  نوشته

  هنگامی اتفاق می افتد که یک کار در انتظار تکمیل یک ورود به سیستم معامله باشد. گرگرفتگی هنگامی اتفاق می افتد که مدیر ورود به سیستم محتوای موقتی خود را روی دیسک بنویسد. عملیات متداول که باعث گرگرفتگی می شوند ، تعهدات معامله و پاسگاه ها هستند.

  دلایل مشترک برای انتظار طولانی در Writelog عبارتند از:

  • تأخیر دیسک ورود به سیستم معاملات: این شایع ترین علت Waitleog Waits است. به طور کلی ، توصیه این است که داده ها و پرونده های ورود به سیستم را در حجم جداگانه نگه دارید. ورود به سیستم معاملات در هنگام خواندن یا نوشتن داده ها از یک فایل داده به طور تصادفی نوشته های متوالی است. مخلوط کردن داده ها و پرونده های ورود به سیستم در یک حجم درایو (به ویژه درایوهای دیسک چرخش معمولی) باعث حرکت بیش از حد سر دیسک می شود.
  • بسیاری از VLF ها: بسیاری از پرونده های ورود به سیستم مجازی (VLF) می توانند باعث انتظار WritElog شوند. بسیاری از VLF ها می توانند انواع دیگری از مسائل مانند بهبودی طولانی را ایجاد کنند.
  • تراکنش های کوچک بسیار زیاد: در حالی که تراکنش های بزرگ می توانند منجر به مسدود شدن شوند، بسیاری از تراکنش های کوچک می توانند به مجموعه دیگری از مشکلات منجر شوند. اگر صراحتاً تراکنش را شروع نکنید، هرگونه درج، حذف یا به روزرسانی منجر به تراکنش خواهد شد (ما به این تراکنش خودکار می گوییم). اگر 1000 درج در یک حلقه انجام دهید، 1000 تراکنش ایجاد می شود. هر تراکنش در این مثال نیاز به commit دارد که منجر به یک تراکنش log و 1000 تراکنش فلاش می شود. در صورت امکان، به روزرسانی فردی را گروه بندی کنید، حذف کنید، یا در یک تراکنش بزرگ تر وارد کنید تا تراکنش های تراکنش کاهش یابد و عملکرد افزایش یابد. این عملیات می تواند به انتظارات WRITELOG کمتری منجر شود.
  • مشکلات زمان بندی باعث می شود که رشته های Log Writer به اندازه کافی سریع برنامه ریزی نشوند: قبل از SQL Server 2016، یک رشته Log Writer همه نوشتن های گزارش را انجام می داد. اگر مشکلاتی در زمان بندی رشته ها وجود داشت (مثلاً CPU بالا)، هم thread Log Writer و هم flush log ممکن است به تأخیر بیفتند. در SQL Server 2016، حداکثر چهار رشته Log Writer برای افزایش توان عملیاتی نوشتن گزارش اضافه شد. SQL 2016 را ببینید - فقط سریعتر اجرا می شود: کارگران Log Writer Multiple. در SQL Server 2019، حداکثر هشت رشته Log Writer اضافه شده است که باعث بهبود عملکرد بیشتر می شود. همچنین، در SQL Server 2019، هر رشته کارگر معمولی می تواند به جای ارسال به رشته Log writer، به طور مستقیم لاگ رایت کند. با این پیشرفت ها، انتظارات WRITELOG به ندرت با مشکلات زمان بندی ایجاد می شوند.

  async_io_completion

  زمانی رخ می دهد که برخی از فعالیت های ورودی/خروجی زیر اتفاق بیفتد:

  • ارائه دهنده درج انبوه ("Insert Bulk") از این نوع انتظار هنگام انجام I/O استفاده می کند.
  • خواندن فایل Undo در LogShipping و هدایت Async I/O برای Log Shipping.
  • خواندن داده های واقعی از فایل های داده در حین پشتیبان گیری از داده ها.

  io_completion

  هنگام انتظار برای تکمیل عملیات I/O رخ می دهد. این نوع انتظار عموماً شامل I/Oهایی است که به صفحات داده (بافرها) مرتبط نیستند. مثالها عبارتند از:

  • خواندن و نوشتن نتایج مرتب سازی/هش سازی از/به دیسک در طول نشت (عملکرد ذخیره سازی tempdb را بررسی کنید).
  • خواندن و نوشتن قرقره های مشتاق روی دیسک (ذخیره سازی tempdb را بررسی کنید).
  • خواندن بلوک های گزارش از گزارش تراکنش (در طول هر عملیاتی که باعث می شود گزارش از روی دیسک خوانده شود - به عنوان مثال، بازیابی).
  • خواندن یک صفحه از دیسک زمانی که پایگاه داده هنوز تنظیم نشده است.
  • کپی کردن صفحات در یک عکس فوری پایگاه داده (کپی در نوشتن).
  • بستن فایل پایگاه داده و غیرفشرده شدن فایل.

  دارای پشتیبان گیری

  زمانی اتفاق می افتد که یک کار پشتیبان در انتظار داده است یا منتظر یک بافر برای ذخیره داده است. این نوع معمولی نیست، مگر زمانی که یک کار در انتظار نصب نوار باشد.