Почему запрос может не использовать индекс в SQL Server?
Что проверяют
Умеете ли вы читать план выполнения и понимаете ли, что решение принимает оптимизатор по оценке стоимости, а не по факту наличия индекса. Для java-позиции добавляется свой слой: запрос обычно собирает драйвер или ORM, и ломает индекс чаще всего то, чего в исходнике не видно.
Как отвечать
Разложите причины по группам.
Первая, несаргируемый предикат: колонка завёрнута в функцию или выражение. WHERE YEAR(created_at) = ?, WHERE UPPER(name) = ?, name LIKE ? со значением, начинающимся с подстановочного знака. Индекс упорядочен по значению колонки, а не по результату функции, поэтому поиск по диапазону невозможен. Лечится переписыванием на диапазон дат или вычисляемой колонкой с индексом.
Вторая, неявное приведение типов, и это главная java-ловушка. Драйвер MS SQL Server по умолчанию отправляет строковые параметры как NVARCHAR, и если колонка объявлена как VARCHAR, сервер приводит к типу параметра колонку, а не параметр. Поиск превращается в сканирование. В плане это видно как предупреждение о конвертации, лечится свойством соединения sendStringParametersAsUnicode=false либо совпадением типов.
Третья, честный выбор по стоимости. Некластерный индекс без нужных колонок требует обращения к таблице за каждой строкой, и на широкой выборке сканирование дешевле. Ответ здесь покрывающий индекс с INCLUDE, а со стороны кода проекция в DTO вместо выборки всей сущности.
Где спотыкаются
Называют порог вроде трети таблицы, после которого индекс якобы перестаёт использоваться. Фиксированной доли нет, точка переключения зависит от ширины строки, числа страниц и стоимости обращений к таблице.
Второе: думают, что важен порядок условий в WHERE. Оптимизатору он безразличен, важен левый префикс составного индекса: поиск по второй колонке без первой в него не попадает.
Третье: смотрят оценочный план и делают выводы. Настоящие причины видны в фактическом, где рядом с оценкой числа строк стоит факт, и расхождение на порядки сразу указывает на устаревшую статистику или на приведение типов.