Добрый день.
Есть запрос. возвращает 0 записей (отрабатывает за секунду, блокировок нет):



Если к этому запросу добавить where d.SourceID = 30:



то он возвращает 10 записей.
Как такое может быть? Это я в этой жизни что то не понимаю? Или с базой проблемы?
Ошибочно отрабатывает первый запрос

ДваждыДва,

нет, не делал. Для проверки DBCC CHECKDB запустил перевод базы в SINGLE_USER режим. пока висит

msLex,
без nolock результат то же

Владислав Колосов и TaPaK,
Вы скорее всего правы. Дело в индексах и вот почему:
изменив запрос на


запрос выполнился правильно, вывелось 10 записей.
добавлением +0 убрал поиск по индексу
Пришел к выводу, что дело в индексе PK_client (поле id таблицы SMART_DWH.customers)
вот эта конструкция




Попытка его реорганизовать


завершилась с ошибками вида
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00009872efc000 in file 'D:\Data\SMART_DWH.mdf:MSSQL_DBCC17'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

попытка выполнить
exec('DBCC CHECKDB (''SMART_DWH'') WITH tableresults')
закончилась, после 5 часов выполнения, следующей ошибкой:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


После чего решил запустить REBUILD индекса:


Данный скрипт отработал за 30 минут успешно.
Проверил результат изначальным запросом


Вернул 10 записей. На этом, казалось что все - вопрос решен.
Но запустив этот же запрос не на top 10, а на top 100 получил 0 строк.
В планах выполнения этих 2 запросов появилось 1 отличие:
во втором запросе появился Key Lookup(Сlustered) [customers].[PK_client] [c]

Владислав Колосов,

Диспетчер устройст пишет
vmware virtual disk scsi disk device
Объем 5.18TB

В файле журнала SQL сервера ничего нового не обнаружил:

Куча ошибок вида
"The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00009872efc000 in file 'D:\Data\SMART_DWH.mdf:MSSQL_DBCC17'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online."
где меняется только адреса (0x00009872efc000)
Очень похоже что моя ошибка описана https://support.microsoft.com/en-us/help/2002606/os-errors-1450-and-665-are-reported-for-database-data-files
Файловая система NTFS
Размер файл D:\Data\SMART_DWH.mdf 1.7TB

TaPaK,
нет, диски не переразмечал

Проблема решилась созданием новой файловой группы на отдельном диске. с последующем переносом индексов в эту группу.


на странице https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/move-an-existing-index-to-a-different-filegroup?view=sql-server-2017 указанны следующие ограничения:
Если таблица имеет кластеризованный индекс (а PK_client кластеризованный), то перемещение кластеризованного индекса в новую файловую группу перемещает в эту файловую группу саму таблицу.
В среде Среда Management Studioнельзя перемещать индексы, созданные с помощью ограничения UNIQUE или PRIMARY KEY. Для перемещения этих индексов используйте инструкцию CREATE INDEX с параметром (DROP_EXISTING=ON) в Transact-SQL.


Учитывая эту информацию для переноса кластеризованного индекса [PK_client] (как и всей таблицы [customers]) пришлось использовать параметр DROP_EXISTING=ON: