Включенный индекс для таблицы с более чем 200 миллионами строк

У меня возникают трудности с созданием включенного индекса для таблицы, содержащей чуть более 200 миллионов записей. Структура таблицы следующая:

    [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](60) NOT NULL,
 [VatId] [int] NOT NULL,
[UserId] [int] NULL,
..some additional [int] columns

Проблема в том, что когда я делаю следующий запрос:

set statistics time on;

 select top 20 [Id] from tblArticle where UserId = 7 order by Id desc;

set statistics time off;

.. затем результат извлекается через ~ 27 мс (в столбце UserId есть non-clustered index).

Однако, когда я пытаюсь выбрать дополнительные столбцы, например:

set statistics time on;

 select top 20 [Id], [VatId] from tblArticle where UserId = 8 order by Id desc;

set statistics time off;

.. затем результат возвращается примерно через 2000 мс.

Глядя на план выполнения: введите здесь описание изображения ..очевидно, что Key Lookup занимает здесь большую часть времени.

Я попытался создать включенный индекс для VatId, например:

CREATE NONCLUSTERED INDEX [NonClusteredIndex-UserIdIncVatId] ON [dbo].[tblArticle]
(
    [UserId] ASC
)
INCLUDE ([VatId]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

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

Недостаточно памяти в пуле (по умолчанию)

(Мой экземпляр SQL Server работает на 8 ГБ ОЗУ, Core i7)

Мой вопрос: есть ли какие-нибудь другие возможные приемы, чтобы избавиться от этого Clustered Key Lookup и улучшить производительность?

Большое спасибо

EDIT: Столбец Id имеет кластеризованный индекс.

Вызов set statistics io on; приводит к следующему:

Table 'tblArticle'. 
Scan count 1, 
logical reads 730, 
physical reads 1, 
read-ahead reads 1351, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

РЕДАКТИРОВАТЬ 2: Чтобы получить полную картину, план выполнения с подсказками: введите здесь описание изображения


person Ivan Sivak    schedule 03.11.2015    source источник
comment
Проблема заключается не в поиске ключей для VatID, так как это всего 20 строк, а в поиске верхних строк для Id, и поэтому вам понадобится это как индексированное поле, нет необходимости иметь VatId, если это всего лишь первые 20   -  person James Z    schedule 03.11.2015
comment
@JamesZ Id — это кластеризованный индекс.   -  person Ivan Sivak    schedule 03.11.2015
comment
Он ищет кластеризованный ключ, потому что вы выполняете сортировку по этому ключу. Вы можете использовать nolock для хорошей производительности. У nolock есть недостаток, например, чтение direty.   -  person Jigneshk    schedule 03.11.2015
comment
Но это всего лишь 20 поисковых запросов, которые не должны занимать 2 секунды. Что говорит статистика IO? Здесь происходит что-то странное...   -  person James Z    schedule 03.11.2015
comment
Nolock не улучшает производительность, он просто выполняет грязное чтение. Если это поможет, ваша проблема блокируется   -  person James Z    schedule 03.11.2015
comment
@JamesZ Спасибо. Я обновил ответ.   -  person Ivan Sivak    schedule 03.11.2015
comment
зачем вы сравниваете разные запросы? один использует userid = 7 другой userid = 8   -  person Juan Carlos Oropeza    schedule 03.11.2015
comment
@JuanCarlosOropeza Кэш. Когда я дважды запускаю один и тот же запрос во второй раз, статистика показывает время почти 0 мс, поскольку оно находится в кеше.   -  person Ivan Sivak    schedule 03.11.2015
comment
Я думаю, что сбой при построении индекса скорее связан с ` SORT_IN_TEMPDB = OFF`, чем с чем-либо еще. Опять же, 8 ГБ оперативной памяти маловато для такого объема ... хотя я все еще не понимаю, что это происходит; Я бы ожидал МНОГО подкачки, а не ошибки. Что касается NOLOCK: не надо, это ужасный совет.   -  person deroby    schedule 05.11.2015
comment
@deroby Спасибо за подсказку. Я собираюсь попробовать SORT_IN_TEMPDB = ON сегодня вечером и сообщу вам результат.   -  person Ivan Sivak    schedule 05.11.2015
comment
@deroby Удивительное улучшение. SORT_IN_TEMPDB = ON создал индекс за 45 минут. Хороший. Пожалуйста, не стесняйтесь сделать свой комментарий ответом, я проголосую за него.   -  person Ivan Sivak    schedule 06.11.2015
comment
Не беспокойтесь, я рад, что это сработало. Я также думаю, что это может быть простой побочный эффект того, что машина имеет немного больше свободного ОЗУ при повторном запуске. Однажды у меня была такая же ситуация на моем ноутбуке (8 ГБ оперативной памяти, максимум 3 ГБ, выделенных для MSSQL), где однажды операция (много много огромных обновлений в циклах, не спрашивайте) заняла около полутора часов. Пару дней спустя мне пришлось перезапустить эту штуку, и она заработала за 25 минут. Основное отличие состоит в том, что в первый раз у меня было открыто много программ (браузеры, Visual Studios, Compare, Excel, Outlook и т. д.), а во второй раз машина была практически «пустой».   -  person deroby    schedule 06.11.2015
comment
@deroby Хороший вопрос. В первый раз, когда я попробовал это, у меня также было открыто много задач, и создание индекса полностью сожгло мой компьютер. Пришлось перезагрузить его, и БД была в режиме восстановления. В следующий раз, когда я попробовал это, я оставил работающим только сервер sql, но создание индекса все равно закончилось ошибкой нехватки памяти (после многих часов работы). Однако на этот раз SORT_IN_TEMPDB = ON сотворил почти чудо.   -  person Ivan Sivak    schedule 06.11.2015


Ответы (1)


Пытаться:

WITH cte AS (
    select top 20 [Id] 
    from tblArticle 
    where UserId = 7 
    order by Id desc
)
SELECT t.[Id], t.[VatId]
FROM tblArticle t
JOIN cte 
  ON cte.[Id]= t.[Id]

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

обновление Oracle, сравнивающее Varchar

person Juan Carlos Oropeza    schedule 03.11.2015
comment
ах, мило. 320ms сейчас. Планы выполнения показывают Clustered Index Seek вместо Clustered Key Lookup.. - person Ivan Sivak; 03.11.2015
comment
Вы пробовали составной индекс или только cte? Поскольку я увидел этот вопрос, мне интересно, как это будет работать. - person Juan Carlos Oropeza; 03.11.2015
comment
Просто CTE. Я немного боюсь составного индекса, так как создание обычного индекса с 1 включенным столбцом VatId заняло у меня несколько часов и закончилось ошибкой. Это, однако, другой вопрос. Попробую составной ключ. - person Ivan Sivak; 03.11.2015