I'm having difficulties creating an included index on table that contains slightly over 200 million records. The structure of table is as follows:
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](60) NOT NULL,
[VatId] [int] NOT NULL,
[UserId] [int] NULL,
..some additional [int] columns
The problem is that when I do a following query:
set statistics time on;
select top 20 [Id] from tblArticle where UserId = 7 order by Id desc;
set statistics time off;
..then the result is retrieved in ~27ms (there is a non-clustered index
on column UserId
).
However when I try to select additional columns, e.g.:
set statistics time on;
select top 20 [Id], [VatId] from tblArticle where UserId = 8 order by Id desc;
set statistics time off;
..then the result is back in ~2,000ms.
Looking at the execution plan:
..obviously, the Key Lookup
is what takes the most of the time here.
I have tried to create an included index on VatId
, such as:
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)
..but after several hours of running this query ends up with error
Insufficient memory in pool (default)
(My SQL Server instance runs on 8GB RAM, Core i7)
My question: Are there any other possible tricks to get rid of this Clustered Key Lookup
and improve the performance?
Many thanks
EDIT:
The column Id
has a clustered index.
Calling the set statistics io on;
produces the following:
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.
EDIT 2: Just to make the full picture, execution plan with hints:
Try:
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]
Also I just came from another question where suggest create a composited index may help because wont need do the look up