Почему мои индексы SQL игнорируются?

Лучше всего сделать индекс покрывающим индексом, включив столбцы InvoiceTotal и AmountPaid в индекс CustomerID. (В SQL 2005 вы должны добавить их как "включенные" столбцы. В SQL 2000 вы должны добавить их как дополнительные ключевые столбцы.) Если вы это сделаете, я гарантирую оптимизатор запросов выберет ваш индекс*.

Объяснение. Индексы кажутся полезными всегда, но использование (непокрывающего) индекса сопряжено с дополнительными затратами, а именно с "поиском закладок". необходимо выполнить для извлечения любых других столбцов, которые могут понадобиться из основной таблицы. Этот поиск закладок является дорогостоящей операцией и является (одной из возможных) причиной, по которой оптимизатор запросов может не использовать ваш индекс.

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

(*) Или я верну вам баллы StackOverflow. Просто отправьте конверт с обратным адресом и маркой на...

Изменить: Да, если ваш первичный ключ НЕ является кластеризованным индексом, то, во что бы то ни стало, сделайте это тоже!! Но даже с этим изменением превращение вашего индекса CustomerID в покрывающий индекс должно повысить производительность на порядок (в 10 раз или лучше)!!

У нас возникла проблема, когда индексы в наших таблицах игнорируются, а SQL Server 2000 вместо этого выполняет сканирование таблиц.

Несмотря на то, что с Aug 29, 1997 прошло 4,302 дня, оптимизатор SQL Server еще не превратился в SkyNet и все еще может принимать неверные решения.

Индексные подсказки — это то, как вы, человек, помогаете искусственному интеллекту.

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

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

В твоем случае:

SELECT CustomerID, 
       SUM(InvoiceTotal) AS SumOfInvoiceTotal, 
       SUM(AmountPaid) AS SumOfAmountPaid 
FROM   tblInvoices 
WHERE  CustomerID = 2112 
GROUP BY
       CustomerID

, у оптимизатора есть два варианта:

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

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

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

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

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

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

Обновление:

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

Теперь, не могли бы вы выполнить следующий запрос:

DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])

, найти две соседние строки в третьем наборе результатов, где RANGE_HI_KEY меньше и больше 2112, и опубликовать строки здесь?

Обновление 2:

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

Возможно (вероятно) это потому, что это самое значение (2112) встречается в RANGE_HI_KEY и оптимизатор видит, что оно необычно плотное (3427 значений только для 2112 против только 911 для всего диапазона от 1668 до 2111)

Не могли бы вы сделать еще две вещи:

1. Запустите этот запрос:

   DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])
   

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

   • Запустите этот запрос:

     ВЫБЕРИТЕ TOP 1 CustomerID, COUNT (*) FROM tblinvoices, ГДЕ CustomerID МЕЖДУ 1668 И 2111

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

   SELECT CustomerID, 
          SUM(InvoiceTotal) AS SumOfInvoiceTotal, 
          SUM(AmountPaid) AS SumOfAmountPaid 
   FROM   tblInvoices 
   WHERE  CustomerID = @Top_Customer
   GROUP BY
          CustomerID
   

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

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

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

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

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

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

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

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

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

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

Я бы начал тестирование, чтобы увидеть, можете ли вы изменить первичный ключ на кластеризованный индекс. Прямо сейчас таблица считается "кучей". Если вы не можете этого сделать, я бы также подумал о создании представления с кластеризованным индексом, но сначала вам нужно изменить столбец «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

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

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

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

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

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

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

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

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

Ты пытался

exec sp_recompile tblInvoices

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

Вы также можете попробовать выполнить UPDATE STATISTICS для таблицы (или таблиц), участвующих в запросе. Не то чтобы я полностью разбирался в статистике в SQL, но я знаю, что иногда наши администраторы баз данных делают это (с запланированным еженедельным заданием по обновлению статистики в больших и часто изменяемых таблицах).

Статистика SQL

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

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

Вы используете "SELECT * FROM..."? Обычно это приводит к сканированию.

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