Почему мои индексы SQL проигнорировали?

Наиболее частые причины игнорирования индексов:

• Используемые столбцы недостаточно избирательны (оптимизатор решает, что сканирование таблиц будет быстрее из-за «посещения» большого количества строк)

• В SELECT / GROUP BY / ORDER BY задействовано большое количество столбцов, которые потребуют поиска в кластеризованном индексе после использования индекса

• Статистика устарела (или искажена из-за большого количества вставок или удалений. )

Выполняется ли у вас регулярное задание по обслуживанию индекса? (в среде Dev он довольно часто отсутствует).

Я бы начал с создания объектно-ориентированной модели запросов. Например. Критерии-объекты . Следующим шагом будет написание пользовательского интерфейса, который позволяет манипулировать этой структурой модели.

• Используйте индекс, который подразумевает вложенный цикл по индексу вместе с KEY LOOKUP , чтобы получить значения InvoiceTotal и AmountPaid
• Не используйте индекс и сканировать все строки таблиц, что быстрее в строках, извлекаемых в секунду , но дольше с точки зрения общего количества строк.

Первый метод может быть или не быть быстрее, чем второй.

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

Для выборочных индексов первый метод быстрее; для неселективных - последнее.

Не могли бы вы выполнить этот запрос:

SELECT  1 - CAST(COUNT(NULLIF(CustomerID, 2112)) AS FLOAT) / COUNT(*)
FROM    tlbInvoices

Обновление:

Поскольку CustomerID = 2112 охватывает только 1,4% ваших строк, вам следует использовать индекс.

Теперь

и опубликуйте первые два набора результатов.

• Выполните этот запрос:

  SELECT TOP 1 CustomerID, COUNT (*) ОТ tblinvoices ГДЕ CustomerID МЕЖДУ 1668 И 2111

, используйте верхний CustomerID из приведенного выше запроса в исходном запросе:

 SELECT CustomerID, 
 SUM (InvoiceTotal) как SumOfInvoiceTotal, 
 SUM (AmountPaid) как SumOfAmountPaid 
ОТ tblInvoices 
ГДЕ CustomerID = @Top_Customer
ГРУППА ПО
 Пользовательский ИД

и посмотрите, какой план он сгенерирует.

Вы пробовали добавить другие столбцы в свой индекс? то есть InvoiceTotal и AmountPaid.

Идея состоит в том, что запрос будет «покрыт» индексом и ему не нужно будет обращаться к таблице.

Последнее сообщение Кимберли посвящено именно этой теме: http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx

SQL Server использует оптимизатор на основе затрат , и если оптимизатор вычисляет, что стоимость поиска ключей индекса и последующего поиска кластерного индекса для получения остальных столбцов выше, чем стоимость сканирования таблица, то вместо этого будет сканироваться таблица. «Переломный» момент на самом деле на удивление низок.

Я бы начал тестирование, чтобы увидеть, можете ли вы изменить первичный ключ на кластерный индекс . Сейчас таблица считается «кучей». Если вы не можете этого сделать, я бы также подумал о создании представления с кластеризованным индексом, но сначала вам придется изменить столбец «AmountPaid» на NOT NULL. По умолчанию он уже равен нулю, так что это может быть легко изменить. Для представления я бы попробовал что-то похожее на это.

SET QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO

IF EXISTS 
  (
         SELECT TABLE_NAME
           FROM INFORMATION_SCHEMA.VIEWS 
          WHERE TABLE_NAME = N'CustomerInvoiceSummary'
  )
           DROP VIEW dbo.CustomerInvoiceSummary
GO

CREATE VIEW dbo.CustomerInvoiceSummary WITH SCHEMABINDING
AS

  SELECT a.CustomerID
       , Sum(a.InvoiceTotal) AS SumOfInvoiceTotal
       , Sum(a.AmountPaid)   AS SumOfAmountPaid 
       , COUNT_BIG(*)                     AS CT
    FROM dbo.tblInvoices a
GROUP BY a.CustomerID

GO
CREATE UNIQUE CLUSTERED INDEX CustomerInvoiceSummary_CLI ON dbo.CustomerInvoiceSummary ( CustomerID )
GO

Вы пробовали

exec sp_recompile tblInvoices

... просто чтобы убедиться, что вы не используете кешированный плохой план?

Некоторые другие указали, что вашей базе данных может потребоваться обновление статистики индекса. У вас также может быть такой высокий процент строк в базе данных, что будет быстрее последовательно читать таблицу, чем искать по диску, чтобы найти каждую. SQL Server имеет модный анализатор запросов с графическим интерфейсом пользователя, который сообщит вам, какова, по мнению базы данных, стоимость различных действий. Вы можете открыть его и посмотреть, о чем он думает.

Мы можем дать вам более надежные ответы, если вы дадите нам:

Select * from tblinvoices;
Select * from tblinvoices where CustomerID = 2112;

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

Думаю, я только что нашел его. Я читал комментарии к вашему вопросу, прежде чем заметил, что два запроса, которые я вам дал, должны были вызвать сканирование таблицы, и мне просто нужен был результат. Тем не менее, меня заинтересовало, когда кто-то сказал, что у вас нет кластерных индексов. Я подробно прочитал ваш SQL-оператор create и с удивлением заметил, что это так. Вот почему он не использует ваш индекс CustomerId.

Индекс CustomerId ссылается на ваш первичный ключ InvoiceNo. Однако ваш первичный ключ не кластеризован, поэтому вам придется заглянуть в этот индекс, чтобы найти, где на самом деле находится строка. Сервер SQL не будет выполнять два поиска по некластеризованному индексу, чтобы найти строку. Это просто сканирование таблицы.

Сделайте свой счет-фактуру кластеризованным индексом. Мы можем предположить, что они, как правило, будут вставляться по возрастанию, и, следовательно, стоимость вставки не будет намного выше. Однако стоимость вашего запроса будет намного ниже. Доллары в пончики, тогда он будет использовать ваш индекс.

---

Изменить: Мне также нравится предложение BradC . Это распространенный трюк администраторов баз данных. Однако, как он говорит, в любом случае сделайте этот основной кластер, поскольку это ПРИЧИНА вашей проблемы. Очень редко бывает таблица без кластерного индекса. В большинстве случаев это плохая идея. Тем не менее, его индекс покрытия - это улучшение НА ПЕРВОЙ Кластеризации, которую необходимо сделать.

однако будет намного ниже. Доллары в пончики, тогда он будет использовать ваш индекс.

---

Изменить: Мне также нравится предложение BradC . Это распространенный трюк администраторов баз данных. Однако, как он говорит, в любом случае сделайте этот основной кластер, поскольку это ПРИЧИНА вашей проблемы. Очень редко бывает таблица без кластерного индекса. В большинстве случаев это плохая идея. Тем не менее, его индекс покрытия - это улучшение НА ПЕРВОЙ Кластеризации, которую необходимо сделать.

однако будет намного ниже. Доллары в пончики, тогда он будет использовать ваш индекс.

---

Изменить: Мне также нравится предложение BradC . Это распространенный трюк администраторов баз данных. Однако, как он говорит, в любом случае сделайте этот основной кластер, поскольку это ПРИЧИНА вашей проблемы. Очень редко бывает таблица без кластерного индекса. В большинстве случаев это плохая идея. Тем не менее, его индекс покрытия - это улучшение НА ПЕРВОЙ Кластеризации, которую необходимо сделать.

плохая идея. Тем не менее, его индекс покрытия - это улучшение НА ПЕРВОЙ Кластеризации, которую необходимо сделать.

плохая идея. Тем не менее, его индекс покрытия - это улучшение НА ПЕРВОЙ Кластеризации, которую необходимо сделать.

Вы также можете попробовать выполнить ОБНОВЛЕНИЕ СТАТИСТИКИ для таблицы (или таблиц), участвующих в запросе. Не то, чтобы я полностью разбирался в статистике в SQL, но я знаю, что наши администраторы баз данных иногда делают это (с еженедельным заданием по обновлению статистики для больших и часто изменяемых таблиц).

Статистика SQL

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

Например, если статистика не обновлялась после того, как вы выполнили большой массовый импорт, компилятор все равно может подумать, что в таблице всего 10 строк, и не беспокоиться об индексе.

Вы используете "ВЫБРАТЬ * ИЗ ..."? Обычно это приводит к сканированию.

Нам понадобятся схема, индексы и образцы запросов, чтобы помочь больше