what are memory fractions and how to get rid of them (or the sort operator) in the example below?

This execution plan has the following memory fractions:
Memory fraction input:1, Memory Fraction Output:1.

it is a very simple query:

SELECT                  [a].[activityId],
                        [a].[title],
                        [a].[description],
                        [a].[inclusions],
                        [d].[path],
                        [d].[uploadToBucket],
                        [a].[imageUriMain],
                        [a].[locationId]
FROM                    [dbo].[activity]                AS a
LEFT JOIN               [dbo].[document]                AS d 
       ON d.documentId = a.documentId 
      AND d.activityId = a.activityId

ORDER BY                title                           ASC

what are memory fractions and how to get rid of them (or the sort operator) in the above example?

Having a look at the properties of the sort operator:
it has MemoryFractions Input="1" Output="1"

getting rid of this sort operation

having a look at the indexes and definition of table dbo.activity
there are no nonclustered indexes only the primary key

CREATE TABLE [dbo].[activity] ( 
[activityId]      INT              IDENTITY(1,1)                 NOT NULL,
[title]           VARCHAR(100)                     NOT NULL,
[description]     VARCHAR(max)                     NOT NULL,
[inclusions]      VARCHAR(max)                         NULL,
[imageUriMain]    VARCHAR(255)                         NULL,
[imageUriThumb]   VARCHAR(255)                         NULL,
[imageUriTeaser]  VARCHAR(255)                         NULL,
[categoryId]      INT                              NOT NULL  CONSTRAINT [DF__activity__catego__0F975522] DEFAULT ((1)),
[locationId]      INT                                  NULL,
[documentId]      INT                                  NULL,
CONSTRAINT   [PK_activity]  PRIMARY KEY CLUSTERED    ([activityId] asc),
CONSTRAINT   [FK_activity_category]                         FOREIGN KEY ([categoryId]) REFERENCES [ref_activityCategory]([categoryId]),
CONSTRAINT   [FK_activity_location]                         FOREIGN KEY ([locationId]) REFERENCES [location]([locationId]))

GO

same with table documents

CREATE TABLE [dbo].[document] ( 
[documentId]      INT              IDENTITY(1,1)                 NOT NULL,
[uploadToBucket]  VARCHAR(200)                         NULL,
[path]            VARCHAR(200)                         NULL,
[activityId]      INT                                  NULL,
CONSTRAINT   [PK__document__EFAAAD856EBBBDCD]  PRIMARY KEY CLUSTERED    ([documentId] asc))

I create the following indexes (paying attention to the order by and all the needed columns for this query):

create index I_title_01 on [dbo].[activity] 
(title asc,documentId  asc,activityid asc)
INCLUDE(
        [description],
        [inclusions],
        [imageUriMain],
        [locationId]
)
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , 
SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, 
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, --drop_existing=on,
DATA_COMPRESSION=page, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON NONCLUSTERED_INDEXES

create index I_doc_01 on [dbo].[document] 
(documentId  asc,activityid asc)
INCLUDE(
                            [path],
                            [uploadToBucket]
)
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , 
SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, 
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
 --drop_existing=on,
DATA_COMPRESSION=page, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON NONCLUSTERED_INDEXES

And now the new execution plan looks like this (without sort operation):

Regarding what are memory fractions, I get my answer from this site below, which has explanations and even examples:

Batch mode memory fractions


There’s very little information out there about memory fractions. I
would define them as information in the query plan that can give you
clues about each operator’s share of the total query memory grant.
This is naturally more complicated for query plans that insert into
tables with columnstore indexes but that won’t be covered here. Most
references will tell you not to worry about memory fractions or that
they aren’t useful most of the time. Out of thousands of queries that
I’ve tuned I can only think of a few for which memory fractions were
relevant. Sometimes queries spill to tempdb even though SQL Server
reports that a lot of query memory was unused. In these situations I
generally hope for a poor cardinality estimate which leads to a memory
fraction which is too low for the spilling operator. If fixing the
cardinality estimate doesn’t prevent the spill then things can get a
lot more complicated, assuming that you don’t just give up.

StackExchange Database Administrators Q#217959, answer score: 5