Reading content items by ID scans an index on SQL Server: ContentItemIndex has no index led by ContentItemId #19966

/*
    Repro: reads of content items by id scan ContentItemIndex on SQL Server — and an index led by ContentItemId.

    Run in an EMPTY scratch database (it creates and drops two tables, [Document] and [ContentItemIndex], shaped as Orchard Core's:
    ContentItemIndex with the five indexes OrchardCore.ContentManagement's migration creates, verbatim). About 300,000 index rows are
    seeded (230,000 items, 70,000 of them with an older draft version), roughly an established site's size.

    Measured: the shape of the query IContentManager.GetAsync issues — the documents joined to ContentItemIndex, filtered by
    ContentItemId (one id, or a batch as GetAsync(ids) sends it) and Published — as parameterized statements (sp_executesql). Then the
    proposed index is created and the same reads run again. The reads' rows go to a temporary table (INSERT ... EXEC), so only the
    summary at the end is returned.
*/
SET NOCOUNT ON;

IF OBJECT_ID('dbo.ContentItemIndex') IS NOT NULL DROP TABLE dbo.ContentItemIndex;
IF OBJECT_ID('dbo.Document') IS NOT NULL DROP TABLE dbo.Document;

CREATE TABLE dbo.Document (
    Id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Type] nvarchar(255) NULL,
    Content nvarchar(max) NULL,
    [Version] bigint NOT NULL DEFAULT 0);
CREATE INDEX IX_Document_Type ON dbo.Document ([Type]);

CREATE TABLE dbo.ContentItemIndex (
    Id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DocumentId bigint NULL,
    ContentItemId nvarchar(26) NULL,
    ContentItemVersionId nvarchar(26) NULL,
    Latest bit NULL,
    Published bit NULL,
    ContentType nvarchar(255) NULL,
    ModifiedUtc datetime NULL,
    PublishedUtc datetime NULL,
    CreatedUtc datetime NULL,
    [Owner] nvarchar(255) NULL,
    Author nvarchar(255) NULL,
    DisplayText nvarchar(255) NULL);

-- Orchard Core's own indexes on ContentItemIndex (OrchardCore.ContentManagement/Records/Migrations.cs), all led by DocumentId.
CREATE INDEX IDX_ContentItemIndex_DocumentId ON dbo.ContentItemIndex (DocumentId, ContentItemId, ContentItemVersionId, Published, Latest);
CREATE INDEX IDX_ContentItemIndex_DocumentId_ContentType ON dbo.ContentItemIndex (DocumentId, ContentType, CreatedUtc, ModifiedUtc, PublishedUtc, Published, Latest);
CREATE INDEX IDX_ContentItemIndex_DocumentId_Owner ON dbo.ContentItemIndex (DocumentId, [Owner], Published, Latest);
CREATE INDEX IDX_ContentItemIndex_DocumentId_Author ON dbo.ContentItemIndex (DocumentId, Author, Published, Latest);
CREATE INDEX IDX_ContentItemIndex_DocumentId_DisplayText ON dbo.ContentItemIndex (DocumentId, DisplayText, Published, Latest);

-- The seed: 230,000 items; the last 70,000 of them also keep an older version (not published, not latest).
DECLARE @items int = 230000, @drafts int = 70000;
;WITH n AS (SELECT TOP (@items) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c)
SELECT i,
       LOWER(LEFT(REPLACE(CONVERT(varchar(36), NEWID()), '-', ''), 26)) AS ContentItemId,
       CASE i % 5 WHEN 0 THEN N'Article' WHEN 1 THEN N'Product' WHEN 2 THEN N'Order' WHEN 3 THEN N'Customer' ELSE N'Page' END AS ContentType
INTO #items FROM n;

INSERT dbo.Document ([Type], Content, [Version])
SELECT N'OrchardCore.ContentManagement.ContentItem, OrchardCore.ContentManagement.Abstractions', REPLICATE(N'x', 200), 1 FROM #items ORDER BY i;
INSERT dbo.ContentItemIndex (DocumentId, ContentItemId, ContentItemVersionId, Latest, Published, ContentType, ModifiedUtc, PublishedUtc, CreatedUtc, [Owner], Author, DisplayText)
SELECT i, ContentItemId, LOWER(LEFT(REPLACE(CONVERT(varchar(36), NEWID()), '-', ''), 26)), 1, 1, ContentType, GETUTCDATE(), GETUTCDATE(), GETUTCDATE(), N'admin', N'admin', CONCAT(ContentType, N' ', i)
FROM #items ORDER BY i;

INSERT dbo.Document ([Type], Content, [Version])
SELECT N'OrchardCore.ContentManagement.ContentItem, OrchardCore.ContentManagement.Abstractions', REPLICATE(N'x', 200), 1 FROM #items WHERE i > @items - @drafts ORDER BY i;
INSERT dbo.ContentItemIndex (DocumentId, ContentItemId, ContentItemVersionId, Latest, Published, ContentType, ModifiedUtc, PublishedUtc, CreatedUtc, [Owner], Author, DisplayText)
SELECT @items + ROW_NUMBER() OVER (ORDER BY i), ContentItemId, LOWER(LEFT(REPLACE(CONVERT(varchar(36), NEWID()), '-', ''), 26)), 0, 0, ContentType, GETUTCDATE(), NULL, GETUTCDATE(), N'admin', N'admin', CONCAT(ContentType, N' ', i)
FROM #items WHERE i > @items - @drafts ORDER BY i;

UPDATE STATISTICS dbo.ContentItemIndex WITH FULLSCAN;
UPDATE STATISTICS dbo.Document WITH FULLSCAN;

-- The reads: one id, then batches of 61 and 500 ids (the ids spread over the table), each as one parameterized statement, 50 runs
-- after a first one. The numbers are SQL Server's own per-statement statistics (sys.dm_exec_query_stats: elapsed and CPU time, logical
-- reads, averaged per run), so the client's handling of the rows does not count. The database's plan cache is cleared at each phase
-- (database-scoped — nothing else on the server is touched).
CREATE TABLE #sink (Id bigint, [Type] nvarchar(255), Content nvarchar(max), [Version] bigint);
CREATE TABLE #results (Phase nvarchar(40), [Read] nvarchar(40), Runs bigint, AvgElapsedMs decimal(12,3), AvgCpuMs decimal(12,3), AvgLogicalReads bigint);

DECLARE @phase nvarchar(40) = N'Orchard Core''s indexes';
WHILE 1 = 1
BEGIN
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    DECLARE @size int;
    DECLARE sizes CURSOR LOCAL FAST_FORWARD FOR SELECT v FROM (VALUES (1), (61), (500)) AS s(v);
    OPEN sizes;
    FETCH NEXT FROM sizes INTO @size;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql nvarchar(max), @decl nvarchar(max), @args nvarchar(max), @call nvarchar(max);
        SELECT @sql = N'SELECT [Document].* FROM [Document] INNER JOIN (SELECT [Document].[Id] FROM [Document] INNER JOIN [ContentItemIndex] AS [ContentItemIndex_a1] ON [ContentItemIndex_a1].[DocumentId] = [Document].[Id] WHERE ([ContentItemIndex_a1].[ContentItemId] IN ('
                    + STRING_AGG(CONVERT(nvarchar(max), CONCAT(N'@p', k)), N', ') WITHIN GROUP (ORDER BY k)
                    + N')) AND ([ContentItemIndex_a1].[Published] = @pub)) AS [IndexQuery] ON [IndexQuery].[Id] = [Document].[Id]',
               @decl = STRING_AGG(CONVERT(nvarchar(max), CONCAT(N'@p', k, N' nvarchar(26)')), N', ') WITHIN GROUP (ORDER BY k) + N', @pub bit',
               @args = STRING_AGG(CONVERT(nvarchar(max), CONCAT(N'@p', k, N' = N''', ContentItemId, N'''')), N', ') WITHIN GROUP (ORDER BY k) + N', @pub = 1'
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY i) - 1 AS k, ContentItemId FROM #items WHERE i % (@items / @size) = 7) AS picked
        WHERE k < @size;
        SET @call = N'INSERT #sink EXEC sp_executesql N''' + REPLACE(@sql, N'''', N'''''') + N''', N''' + @decl + N''', ' + @args + N';';

        DECLARE @run int = 0;
        WHILE @run < 51 BEGIN EXEC (@call); SET @run += 1; END;
        TRUNCATE TABLE #sink;

        INSERT #results
        SELECT TOP 1 @phase, CASE @size WHEN 1 THEN N'one id' ELSE CONCAT(@size, N' ids') END, qs.execution_count,
               qs.total_elapsed_time / 1000.0 / qs.execution_count, qs.total_worker_time / 1000.0 / qs.execution_count,
               qs.total_logical_reads / qs.execution_count
        FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        CROSS APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = 'dbid') pa
        WHERE pa.dbid = DB_ID() AND st.text LIKE N'%ContentItemIndex_a1%'
          AND st.text LIKE N'%@p' + CONVERT(nvarchar(10), @size - 1) + N' nvarchar(26), @pub bit)%'
        ORDER BY qs.last_execution_time DESC;
        FETCH NEXT FROM sizes INTO @size;
    END;
    CLOSE sizes; DEALLOCATE sizes;

    IF @phase <> N'Orchard Core''s indexes' BREAK;

    -- The proposal: an index led by ContentItemId (every read by id names Published or Latest; DocumentId joins the document;
    -- ContentType and DisplayText keep a search that joins through it from looking each row up in the table).
    CREATE INDEX IDX_ContentItemIndex_ContentItemId ON dbo.ContentItemIndex (ContentItemId, Published, Latest, DocumentId, ContentType, DisplayText);
    SET @phase = N'+ IDX_ContentItemIndex_ContentItemId';
END;

SELECT (SELECT COUNT(*) FROM dbo.ContentItemIndex) AS IndexRows, (SELECT COUNT(DISTINCT ContentItemId) FROM dbo.ContentItemIndex) AS Items;
SELECT [Read], Phase, Runs, AvgElapsedMs, AvgCpuMs, AvgLogicalReads FROM #results ORDER BY [Read], Phase DESC;

DROP TABLE dbo.ContentItemIndex;
DROP TABLE dbo.Document;