Всем привет.
Гугл - слабак. Ищу помощи у вас ))

Есть тестовый сервак. 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? Что стоит проверить?
Наведите на мысли, плиз...

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, даже если там же ОС)
На данном диске - только базы.

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

физическая машина, физические диски

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?(запрос выполнялся многократно со сбросом буфера - цифры не менялись)

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

авторГадание на кофейной гуще никогда не давало результата. Целиком согласен. Но общий процент фрагментации очень слабо характеризует причины деградации IO. Из 300млн+ строк таблицы я исследую поведение только первых 25 млн. Поэтому общий показатель - так себе характеристика.