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

Ответ 1

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

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

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

(*) Или я верну ваши очки StackOverflow. Просто отправьте конверт с самоназванием, с печатью, чтобы...

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

Ответ 2

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

Несмотря на 4,302 дней, прошедших с момента Aug 29, 1997, оптимизатор 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

Update:

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

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

DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])

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

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

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

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

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

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

  DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])
  

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

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

    SELECT TOP 1 CustomerID, COUNT (*) ОТ ТБЛИВОРЕЙЦЕВ WHERE CustomerID МЕЖДУ 1668 И 2111

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

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

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

Ответ 3

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

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

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

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

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

Ответ 4

Последнее сообщение от Kimberly охватывает именно эту тему: http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx

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

Ответ 5

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

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

Ответ 6

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

Ответ 7

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

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

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

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

Ответ 8

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

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

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

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

Ответ 9

Вы пробовали

exec sp_recompile tblInvoices

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

Ответ 10

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

Статистика SQL

Ответ 11

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

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

Ответ 12

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

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