Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MSSQL медленно читает диск + создает очередь  [new]
nvv
Member

Откуда:
Сообщений: 54
Всем привет.
Гугл - слабак. Ищу помощи у вас ))

Есть тестовый сервак. Mssql2017/14.0.1000.169
Поставили в него раздельно пару ssd.
Получили такую странную картинку...

Просто в ОС копирование случайных файлов (мелкие или крупные) выполняются быстро. Скорость ~500Мб/с, что соответствует характеристикам диска. 500Мб мелких без проблем пролетают за секунду

В SQL же любое тривиальное сканирование кластерного индекса застревает на отметке 50Мб/с (иногда до 100М/с доходит).
При этом гарантировано имеем PageIoLatch_SH на страницах читаемой таблицы. При этом % загрузки диска 100%, а очередь доходит до пары тысяч! Откуда? В ОС производительность отличная. Проблема только в SQL.
Игрался с Select: with nolock, maxdop 1 or 4 or 0 - никакой погоды не делают... На сервере никакой параллельной нагрузки нет.
Если параллельно к сканированию что либо выполнять в СУБД - все явно тупит.
В топе ожидание - Buffer IO (кэп).

Что не так с экземпляром SQL? Что стоит проверить?
Наведите на мысли, плиз...
19 апр 19, 22:19    [21867555]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
nvv
Просто в ОС копирование случайных файлов (мелкие или крупные) выполняются быстро. Скорость ~500Мб/с, что соответствует характеристикам диска. 500Мб мелких без проблем пролетают за секунду

В SQL же любое тривиальное сканирование кластерного индекса застревает на отметке 50Мб/с (иногда до 100М/с доходит).
А в ресурс-мониторе что? Какая очередь к диску, к каким файлам обращается, какие responce time к файлу? Как при этом загружен процессор?

На виртуалке или в реале?

Сканирование в какой операции? То есть сервер что сервер делает с этими данными?
19 апр 19, 23:48    [21867585]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
nvv
Поставили в него раздельно пару ssd.
Да, и что делали с дискамим? Объединяли? Отдельно - это отдали оба серверу? Какие файлы положили, данные и лог вместе на один диск, на разные? ОС там же?
19 апр 19, 23:50    [21867587]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
nvv
Member

Откуда:
Сообщений: 54
alexeyvg, сканирование в операции select )
читается последовательно таблица. Результат - пару строк. CI - Period
Select Period, sum(1) From Table Where Period Between ... and ... Group by Period
Или это может быть Select Count(*) (выберите любой или предложите свой для теста)

В мониторе - то что я написал выше, в предыдущем посте.
Читается только один файл - database MDF
очередь скачет на неадекватных значениях (много сотен, иногда - тысяч), задержка - 150-250мс
Сейчас скорость скачет 40-120Мбс... Здесь ОС на отдельном диске. (при этом на любых других машинах - легко достигает 500, даже если там же ОС)
На данном диске - только базы.

Объем фактически читаемых в тесте данных судя по фактическому плану - до гига.

физическая машина, физические диски
20 апр 19, 00:36    [21867610]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7216
nvv
Поставили в него раздельно пару ssd.
Получили такую странную картинку...
Ничего странного, ссд быстрые, но в однопользовательком режиме. Для сервака нужно ставить "all flash array" минимум, а не пукалку из двух шпинделей.
20 апр 19, 02:14    [21867623]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
Данные что ли фрагментированы? Давно ребилдили?
20 апр 19, 07:43    [21867658]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
nvv
alexeyvg, сканирование в операции select )
читается последовательно таблица. Результат - пару строк. CI - Period
Select Period, sum(1) From Table Where Period Between ... and ... Group by Period
Или это может быть Select Count(*) (выберите любой или предложите свой для теста)
Ясно, я на всякий спросил, может, дело в передаче данных клиенту...
nvv
очередь скачет на неадекватных значениях (много сотен, иногда - тысяч), задержка - 150-250мс
Задержка - это задержка самого диска, то есть не от постановки в очередь до ответа, а от начала обработки первого в очереди запроса до ответа.
Для SSD это чень много, должно быть 1 мс.
Так что ИМХО это проблема с драйверами/диском. Скорее, с драйверами, т.к. диска же 2, и у обоих одна проблема. Драйверы - это не только сам драйвер, а вообще вся цепочка, т.е. скорее это что то в виндах, а не в драйвере производителя дисков.
Relic Hunter
Ничего странного, ссд быстрые, но в однопользовательком режиме.
Бытовые ссд быстрее в "многопользовательском" режиме. Вот смешанную нагрузку не все любят, это да.
nvv
при этом на любых других машинах - легко достигает 500
На других машинах такие же диски, ОС? 500 - имеется в виду то же сканирование в сиквеле?
Модель дисков какая, если не секрет?
20 апр 19, 08:34    [21867675]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
nvv
Member

Откуда:
Сообщений: 54
Relic Hunter, alexeyvg,
Коллеги, спасибо за ответы. Ввел немного в заблуждение себя и вас. Прошу прощения...

Проблемный диск оказался из raid0 из 4x hdd. Бюджетная тестовая машинка. Нагрузки выросли. Вместо десятка уже сотня разношерстных копий баз - поэтому стали обращать внимание на подтормаживания.
Базы и данные не повторяются. Это тестовые копии заказчиков. Какие ребилды на копиях?)) Фрагментация прямо в исходнике клиента - очень вероятна...
Чтение данных другой произвольной базы может давать немного лучшие результаты. А может и не давать.
Вроде как все теперь понятно...

Именно в однопользовательском режиме и только из sql и только на этой машине неудовлетворительная производительность.
Очень странно, что ОС умудряется читать очень быстро прозвольный пакет файлов и не собирая таких очередей. Опять же вопрос к фрагментированности данных...

Однако, провел такой опыт:
создал пустые базы на разных дисках. Выполнил Select Into одинаковых данных в целевые базы (25 млн записей до 2016 года). Последовательная куча, индекс нигде не создавал.
Читаю все Select Count(*)
Перед каждым чтением сбрасывал буфер для обеспечения физических чтений. (У меня это как-то не очень получилось, DROPCLEANBUFFERS не помогает, физических чтений - нет, есть логические и упреждающие. ЧЯДНТ?)
Как-то так: число просмотров 1, логических чтений 492581, физических чтений 0, упреждающих чтений 492581, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Чтения на первый взгляд ничего нового не показали, но цифра была все же выше... SSD показал до 500Мбс, hhd x4 raid0 - 250Мбс, hdd x4 raid10 - 200Мбс
Создал кластерные индексы для чистоты эксперимента - все тоже самое ;)

Ну и напоследок еще раз прочитал те же данные (что и вставлял в отдельные базы) из исходной базы:
сама база 450+Гб, топовая таблица 140Гб - читаю из нее.
Число просмотров 1, логических чтений 395701, физических чтений 5, упреждающих чтений 395929, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Скорость не более 50-100 (база на диске hhd x4 raid0)


Итого наиболее вероятный ответ: фрагментация.

PS: почему на одних и тех же данных (исходных и скопированных) разное количество чтений? Почему в скопированных данных нет физических чтений, а в исходных - есть? Почему их только 5, или целых 5?(запрос выполнялся многократно со сбросом буфера - цифры не менялись)
20 апр 19, 10:18    [21867696]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
nvv
Что не так с экземпляром SQL? Что стоит проверить?
Наведите на мысли, плиз...
Читайте - https://www.sqlshack.com/how-to-analyze-storage-subsystem-performance-in-sql-server/
nvv
Итого наиболее вероятный ответ: фрагментация.
Гадание на кофейной гуще никогда не давало результата. Проверьте фрагментацию с помощью sys.dm_db_index_physical_stats
nvv
Почему в скопированных данных нет физических чтений, а в исходных - есть? Почему их только 5, или целых 5?(запрос выполнялся многократно со сбросом буфера - цифры не менялись)
Читайте что такое "упреждающие чтения"
20 апр 19, 11:39    [21867719]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
nvv
логических чтений 492581, физических чтений 0, упреждающих чтений 492581
Упреждающие чтения - это физические чтения, выполненные в режиме упреждающих.
Так что всё нормально, никакого чуда :-)
20 апр 19, 17:08    [21867829]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
по марке ссд надо на техфоруме спрашивать

возможна специфика по быстродействию на разной глубине очереди и при большом количестве параллельных операций
также надо убедиться в активации ncq/trim
ну и идти туда лучше с указанием матплаты и ос
может и в техподдержке что ответят

как вариант - можно запилить себе виртуальный диск, например ImDisk
и посмотреть, если там тоже тормоза будут - значит, не в дисках дело
тогда в разделе операционок спрашивать
20 апр 19, 19:04    [21867883]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
nvv
Member

Откуда:
Сообщений: 54
invm, спасибо напомнили про dmv.
Всего 41 минута и я получил ее вывод ) Такие дела.
На исследуемой таблице (она самая большая, но не самая проблемная): из 7 млн страниц - 32% фрагментировано.

автор
Гадание на кофейной гуще никогда не давало результата.
Целиком согласен. Но общий процент фрагментации очень слабо характеризует причины деградации IO. Из 300млн+ строк таблицы я исследую поведение только первых 25 млн. Поэтому общий показатель - так себе характеристика.
20 апр 19, 23:18    [21867963]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
nvv
Но общий процент фрагментации очень слабо характеризует причины деградации IO. Из 300млн+ строк таблицы я исследую поведение только первых 25 млн. Поэтому общий показатель - так себе характеристика.
32% - это достаточно много.
Если фрагментация равномерная, то на первых 25 млн она работает точно так же, как на остальных данных.
Понятно, что это только возможность. Может, первые 25 млн не дефрагментированы? Тем не менее, она есть, скорее всё таки они фрагментированы так же, как остальное.
20 апр 19, 23:32    [21867967]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
nvv
Почему в скопированных данных нет физических чтений, а в исходных - есть?
В скопированных данных нет фрагментации, поэтому там физические чтения делаются с упрежджением, а в исходных есть фрагментация, поэтому там без упреждения.
20 апр 19, 23:35    [21867969]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL медленно читает диск + создает очередь  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Имо проблема в особенностях SSD. Производители идут на разного рода ухищрения, чтобы получить высокие показатели на синтетических тестах.
22 апр 19, 11:21    [21868654]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить