آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

بررسی علت کند بودن I\O در Sql server

دوره های مرتبط با این مقاله

Investigating I/O bottlenecks

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

از آنجایی که I/O بخش مهمی از اجرای SQL Server می باشد، لازم است اطمینان حاصل کنید که دیسک زیر مجموعه پر (bottleneck) نیست. در گذشته انجام این کار بسیار آسان تر بود، زیرا بیشتر سرورها فضای ذخیره سازی داخلی داشتند. این روزها بسیاری از SQL Server ها از فضای ذخیره سازی SAN یا NAS استفاده می کنند، یا در موارد پیچیده تر بیشتر SQL Serverها در یک محیط مجازی در حال اجرا می باشند.

توضیح

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

به طور کلی دو گزینه برای نظارت I/O bottlenecks و I/O bottlenecks و اجرای شمارنده های Monitor دارید، ابزار دیگری نیز وجود دارند اما این دو گزینه در همه ی محیط های SQL Server وجود دارند.

DMV برای هر فایل به شما آمار فایل تجمعی (cumulative file stats) ارائه میدهد و هر فایل دیتابیس حاوی هم فایل های log و هم فایل های داده می باشد. براساس این داده ها می توانید تعیین کنید که کدام فایل از دیدگاه خواندن و نوشتن شلوغ ترین می باشد.

خروجی حاوی اطلاعات I/O stall برای خواندن ها، نوشتن ها و مجموع می باشد. I/O stall زمان کلی به هزارم ثانیه می باشد که یوزرها برای کامل شدن I/O روی فایل منتظر می مانند. با نگاه کردن به اطلاعات I/O stall می توانید ببینید که کامل شدن I/O و در نتیجه انتظار یوزرها چقدر طول کشید.

داده ای که ازاین DMV گزارش می شود، داده ی انباشته (cumulative) می باشد که به این معناست که هر بار که SQL Server را دوباره شروع می کنید، شمارنده ها نیز مجددا آغاز به کار می کنند. از آنجایی که داده انباشته می باشد، می توانید آن را یک بار اجرا کرده و سپس query را مجددا در آینده اجرا کنید و دلتاها را در هر دو زمان با یکدیگر مقایسه کنید. اگر I/O stalls در مقایسه با طول زمانی دفعه ی قبل طولانی باشند، ممکن است یک I/O bottleneck داشته باشید.

SELECT ‎
cast(DB_Name(a.database_id) as varchar) as Database_name,‎
b.physical_name, * ‎
FROM  ‎
sys.dm_io_virtual_file_stats(null, null) a ‎
INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = ‎b.file_id
ORDER BY Database_Name

در اینجا بخشی از خروجی مربوط به فرمان بالا را مشاهده می کنید.

آموزش SQL Server

Performance Monitor

Performance Monitor یک ویندوز ابزار می باشد که به شما اجازه ی دریافت استاتیک های مربوط به SQL Server، استفاده از حافظه، استفاده از I/O و غیره را می دهد. این ابزار می تواند به صورت تعاملی با استفاده از GUI استفاده شود و یا می توانید آن را با اطلاعات جمع شده، پشت صحنه هایی که بعدا می توانند مرور شوند، تنظیم کنید. این ابزار در Control Panel زیر Administrative tools یافت می شود.

چندین شمارنده مربوط به I/O وجود دارند که در زیر Physical Disk و Logical Disk واقع شده اند. اجرای آبجکت Physical Disk حاوی شمارنده هایی است که درایو هارد دیسک یا fixed disk را در یک کامپیوتر مانیتور می کنند. اجرای آبجکت Logical Disk حاوی شمارنده هایی است که درایوهای هارددیسک یا fixed disk را مانیتور می کند. در بیشترین قسمت، هر دو دارای شمارنده های یکشان می باشند. در بسیاری از موارد احتمالا از شمارنده های استفاده خواهید کرد. در اینجا بخشی ازلیست شمارنده های موجود را مشاهده می کنید:

آموزش SQL Server
آموزش SQL Server

اکنون که محل ذخیره سازی می تواند local, SAN, NAS و غیره باشد، اگر یک bottleneck وجود داشته باشد، مشاهده ی این دو شمارنده مفید خواهد بود.

  • Avg. Disk sec/Read: زمان میانگین به ثانیه از خواندن یک داده از دیسک می باشد.
  • Avg. Disk sec/Write: زمان میانگین به ثانیه از نوشتن یک داده روی دیسک می باشد.

پیشنهاد این است که مقادیر هر دوی این شمارنده ها از 20 ms کمتر باشد. وقتی که این داده را دریافت می کنید، مقادیر به شکل 0.000 نمایش داده خواهند شد، بنابراین یک مقدار 0.050 برابر است با 50 ms.

Resource Monitor

ابزار دیگری که می توانید استفاده کنید، Resource Monitor می باشد. این ابزار می تواند از طریق Task Manager یا Control Panel آغاز به کار کند.

در تصویر زیر تب Disk را مشاهده می کنید که فرایندهای جاری استفاده از دیسک، فایل های فعال دیسک و ذخیره سازی در سطح logical و physical را نشان می دهد. Response Time (ms) ومشاهده ی مدت زمان لازم برای ارائه ی سرویس در درخواست I/O مفید می باشد.

آموزش SQL Server

اطلاعات اضافه:

مسائل I/O ممکن است همیشه مشکلی برای زیرمجموعه ی دیسک شما محسوب شوند. تنها به خاطر اینکه یک روند آهسته و یا انتظار I/O را مشاهده می کنید، موارد دیگری نیز ممکن است وجود داشته باشند که نیاز به بررسی دارند، مانند ایندکس های مفقود، query های ضعیف نمایش داده شده، استاتیک های تکه تکه یا تاریخ گذشته. ما این موضوعات را به خوبی دراین آموزش مورد بررسی قرار خواهیم داد.

  • 2964
  •    622
  • تاریخ ارسال :   1394/07/27

دانلود PDF دانشجویان گرامی اگر این مطلب برای شما مفید بود لطفا ما را در GooglePlus محبوب کنید
رمز عبور: tahlildadeh.com یا www.tahlildadeh.com
ارسال دیدگاه نظرات کاربران
شماره موبایل دیدگاه
عنوان پست الکترونیک

ارسال

آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

تمامی حقوق این سایت متعلق به آموزشگاه تحلیل داده می باشد .