1

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?

1

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

enter image description here

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):

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.