У меня возникают трудности с созданием включенного индекса для таблицы, содержащей чуть более 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: Чтобы получить полную картину, план выполнения с подсказками:
VatID
, так как это всего 20 строк, а в поиске верхних строк дляId
, и поэтому вам понадобится это как индексированное поле, нет необходимости иметьVatId
, если это всего лишь первые 20 - person James Z   schedule 03.11.2015Id
— это кластеризованный индекс. - person Ivan Sivak   schedule 03.11.2015userid = 7
другойuserid = 8
- person Juan Carlos Oropeza   schedule 03.11.2015NOLOCK
: не надо, это ужасный совет. - person deroby   schedule 05.11.2015SORT_IN_TEMPDB = ON
сегодня вечером и сообщу вам результат. - person Ivan Sivak   schedule 05.11.2015SORT_IN_TEMPDB = ON
создал индекс за 45 минут. Хороший. Пожалуйста, не стесняйтесь сделать свой комментарий ответом, я проголосую за него. - person Ivan Sivak   schedule 06.11.2015SORT_IN_TEMPDB = ON
сотворил почти чудо. - person Ivan Sivak   schedule 06.11.2015