Почему SQL Server не использует мой индекс?

В нашей базе данных у нас есть эта таблица с 200 000 строк

CREATE TABLE dbo.UserTask (
    UserTask_ID int NOT NULL IDENTITY (1, 1),
    UserTask_SequenceNumber int NOT NULL DEFAULT 0,
    UserTask_IdEntitat uniqueidentifier NOT NULL,
    UserTask_Subject varchar(100) NOT NULL,
    UserTask_Description varchar(500) NOT NULL,
            .....
            .....
    CONSTRAINT [PK_UserTask] PRIMARY KEY CLUSTERED 
    (
        [UserTask_ID] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

Я создал индекс в столбце UserTask_IdEntitat с

CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat ON dbo.UserTask 
(
    UserTask_IDEntitat
)

Выполняя следующий запрос, план выполнения показывает нам, что для выполнения запроса используется индекс UserTask_IdEntitat:

SELECT UserTask_ID
  FROM UserTask   
 WHERE UserTask_IdEntitat = @IdEntitat 
 ORDER BY UserTask_LastSendSystemDateTime desc

Но Если мы добавим еще один столбец в список Select, тогда индекс не будет использоваться

SELECT UserTask_ID, UserTask_SequenceNumber, UserTask_IDEntitat, ....., UserTask_Subject
  FROM UserTask   
 WHERE UserTask_IdEntitat = @IdEntitat 
 ORDER BY UserTask_LastSendSystemDateTime desc

Почему добавление столбца, отличного от первичного ключа, приводит к тому, что в плане выполнения SQL Server не используется индекс в столбце UserTask_IdEntitat?

Следуя этой ссылке http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index, кажется, что количество раз, когда отфильтрованное значение повторяется в столбце, может сделать, что индекс не используется, но я попытался выполнить запрос с значением @IdEntitat, которое повторяется 60 000 раз, а другое повторяется только 175 раз, а результаты те же, индекс в столбце IDEntitat игнорируется.

Это сходит с ума!!!

Спасибо за вашу помощь.

Ответ 1

OK - пока вы выбираете только столбец, который в индексе или что-то из ключа кластеризации (обычно это первичный ключ), тогда будет использоваться индекс, поскольку SQL Server может найти всю информацию, которую он (столбец UserTask_IDEntitat и кластерный индексный столбец (столбцы)) на уровне листа структуры навигации индекса. Таким образом, он может возвращать данные, необходимые для этого запроса SELECT, непосредственно на страницах уровня листа индекса.

Однако:, если вам нужно выбрать второй столбец, ни в определении индекса, и часть ключа кластеризации, то SQL Server должен будет сделать так называемый поиск закладки на фактические страницы данных.

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

Закладки поиска отлично подходят для небольшого количества хитов - они полностью разрушительны для производительности, если вы выбираете тысячи строк. В этом случае оптимизатор запросов SQL Server правильно использует кластерное сканирование индексов, поскольку в кластерном индексе на уровне листа он имеет все строки, доступные сразу.

Итак: если у вас есть индекс на UserTask_IDEntitat, и вам иногда понадобится второй столбец UserTask_SequenceNumber тоже - тогда вы можете включить этот столбец в этот некластеризованный индекс вашего:

CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat 
ON dbo.UserTask(UserTask_IDEntitat)
INCLUDE(UserTask_SequenceNumber)

При этом этот дополнительный столбец присутствует только в уровне листа этого некластеризованного индекса (он не может использоваться в предложении WHERE - он не является частью структуры навигации index!) - и ваш второй SELECT может быть снова удовлетворен с узлами листового уровня некластеризованного индекса → нет дорогих поисков по закладкам → ваш индекс будет использоваться снова.

Короче говоря:, если ваш некластеризованный индекс высокоселективный (например, возвращает 1% ваших строк или меньше), и если ваш некластеризованный индекс не является охватывающий индекс (индекс, содержащий все столбцы, необходимые для удовлетворения конкретного запроса), тогда изменения довольно высоки, что SQL Server НЕ использует ваш некластеризованный индекс.

Для получения дополнительной информации:

Ответ 2

Вы можете использовать подсказки запросов в запросе, чтобы использовать индекс. Ниже приведена ссылка для получения дополнительной информации: http://msdn.microsoft.com/en-us/library/ms181714.aspx

Ответ 3

Я столкнулся с ситуацией, когда один и тот же запрос создает разные планы для разных баз данных. На одной БД он использует некластеризованный индекс, а на другой - сканирование таблицы.

Также этот индекс не имеет всех полей в INCLUDE, и лучшим решением здесь было бы добавить все необходимые выбранные поля в индекс INCLUDE. В моем случае, хотя drop free cache помогает.

DBCC freeproccache

Иногда построитель плана запросов игнорирует индекс, если его фрагментация превышает 50%, поскольку он тратит больше времени на поиск строки в индексе, чем на сканирование всей таблицы.