Намиране на I/O тесни места за MS SQL Server

Същността на тази статия е редовното забавяне в базите данни на SQL Server. След статиите за анализ на използването на паметта и процесора, бихме искали да проучим допълнително причината за забавянето, като анализираме I/O тесните места.

Подсистемата I/O е ключов фактор за производителност в SQL Server, защото страниците на базата данни непрекъснато се преместват от диск в буферния пул или обратно. Освен това регистрите на транзакциите и системата tempDB генерират значителен I/O трафик. Имайки предвид тези фактори, трябва да сте сигурни, че използваната I/O подсистема е стабилна, в противен случай проблемите в работата на тази подсистема ще доведат до увеличено време за отговор на заявката и чести изчаквания. В тази статия ще опиша няколко начина за намиране на тесни места за I/O с помощта на вградени инструменти и ще представя някои идеи, свързани с конфигурацията на диска.

Монитор на производителността

Можете да използвате системната помощна програма Performance Monitor, за да определите натоварването на I/O подсистемата. Изброените по-долу броячи на производителността могат да бъдат полезни за тези цели:

Обект на PhysicalDisk: Ср. Дължина на дисковата опашка. Този брояч показва средния брой заявки за четене и запис, които са на опашка за посочения физически диск. Колкото по-голям е броят, толкова повече се очаква I/O на диска. Ако тази стойност често е по-голяма от две по време на пиково натоварване на SQL Server, трябва да помислите за предприемане на адекватни мерки. Ако се използват няколко диска, показанията на брояча трябва да бъдат разделени на броя на дисковете в масива и да се уверите, че получената стойност не надвишава 2. Например, имате 4 диска и дължината на дисковата опашка е 10, необходимата опашка дълбочина е както следва: 10/4 = 2, 5, това ще бъде стойността, която трябва да се анализира, а не 10.

Ср. Disk Sec/Read и Avg. Disk Sec/Write показва средното време за четене и запис на данни на диск. Добре е тази стойност да е по-малка от 10 ms, но все пак приемлива, ако стойността е по-малка от 20 ms. Стойностите, надвишаващи този праг, изискват проучвания за оптимизация.

Физически диск:% време на диска

        - времето, през което дискът е бил зает с обслужване на заявки за писане или четене. Тази стойност трябва да е под 50%.

Дисково четене/Сек. И Дисково записване/Сек - индикатор за нивото на използване на диска чрез операции за четене и запис. Стойността трябва да бъде по-малка от 85% от честотната лента на диска, тъй като когато този праг бъде надвишен, времето за достъп се увеличава експоненциално.
Пропускателната способност на диска може да се определи чрез постепенно увеличаване на натоварването на системата. Един от начините за определяне на производителността на дисковата подсистема е използването на специализираната помощна програма SQLIO. Тя ви позволява да определите точката, в която производителността спира да расте с по-нататъшно увеличаване на натоварването.

Когато избирате RAID конфигурации, можете да използвате следните формули за изчисляване на броя на I/O операции (I/O) на диск:

Raid 0: I/O на диск = (чете + пише)/брой дискове в масива
Raid 1: I/O на диск = [чете + (записва * 2)]/2
Raid 5: I/O на диск = [чете + (записва * 4)]/брой дискове в масива
Raid 10: I/O на диск = [чете + (записва * 2)]/брой дискове в масива

Ето пример за изчисляване на броя дискови IOPS за RAID 1 въз основа на стойностите на брояча:

Дискови четения/сек = 90
Дискови записи/сек = 75
Формулата за I/O на RAID-1 масив е [чете + (записва * 2)]/2 или [90 + (75 * 2)]/2 = 120 I/Os на диск.