sqlsql-serverquery-performancequery-tuning

Included index on table with 200+ million rows


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: enter image description here ..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: enter image description here


Solution

  • 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

    oracle Update comparing Varchar