3

I have several tempdb spills in queries which are currently serving business purposes. I am told by online searching that tempdb spills should never be ignored as they can cause performance problems. It's still a bit unclear to me as to why that is. Since I have also read that SQL Server by itself will request additional memory for a query if it notices its estimation was wrong for a certain operator. It would seem logical then, that there would only be a performance problem when SQL Server is actually out of memory, which would happen regardless as the memory requirement and available memory would not change if only the estimation is changed to be correct.

Do tempdb spills mean that the SQL Server needs more memory added to it or is there some other reason why tempdb spills are bad that I am not accounting for? I do notice performance drops for queries where the size of the tempdb spills is large, so I am assuming it is a problem which will only get worse as data is added.

To avoid tempdb spills I see the following mentioned online:

  1. Make sure statistics are up to date.
  2. Build indexes in a correct manner.
  3. Adjust query if possible.

I believe I've done this. Though I am definitely happy to be proven wrong. It is also worth noting that I also receive tempdb spills when converting said queries into parameterized queries.

I'd really like insights into how a sort operator, and more specifically a tempdb spill can be completely avoided. Either by table design or any other possibility I can't think of. I am finding very little info about this online apart from the rules above which I have already applied. Even a prominent book on SQL Server performance only mentions the word spill twice. Is there any good resource on this which really goes into depth?

This is the sort operator on the production server:

enter image description here

Below you will find the query I am trying to execute which leads to a sort operator, which does not spill locally with 390 thousand Attribute records, but does with 5 million Attribute records. However, the execution plan has a sort operator in it. This sort operator is what I am trying to remove due to it spilling on the production server. How can I remove the sort operator or avoid the tempdb spill it causes?

Execute this script if you wish to follow along with an example. Otherwise skip it.

USE [master]
GO

DROP DATABASE IF EXISTS [TempDbSpill]
GO

CREATE DATABASE [TempDbSpill]
GO

USE [TempDbSpill]

CREATE TABLE [Product] (
    [Id] UNIQUEIDENTIFIER NOT NULL
)

CREATE TABLE [AttributeType] (
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [Description] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [AttributeValue] (
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [Value] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Attribute] (
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [AttributeTypeId] UNIQUEIDENTIFIER NOT NULL,
    [ProductId] UNIQUEIDENTIFIER NOT NULL,
    [ValueId] UNIQUEIDENTIFIER NULL
)

INSERT INTO
    [Product] ([Id])
SELECT
    NEWID()
FROM
    INFORMATION_SCHEMA.COLUMNS AS [a]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [b]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [c]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [d]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [e]

INSERT INTO
    [AttributeType] ([Id], [Description])
SELECT
    NEWID(), CONCAT(NEWID(), NEWID(), NEWID(), NEWID())
FROM
    INFORMATION_SCHEMA.COLUMNS AS [a]
INSERT INTO
    [AttributeType] ([Id], [Description])
SELECT
    '960BE057-EB5B-4746-9A96-0806723433E9', 'Description'

INSERT INTO
    [AttributeValue] ([Id], [Value])
SELECT
    NEWID(), CONCAT(NEWID(), NEWID(), NEWID(), NEWID())
FROM
    INFORMATION_SCHEMA.COLUMNS AS [a]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [b]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [c]
CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS AS [d]
INSERT INTO
    [AttributeValue] ([Id], [Value])
SELECT
    '98082f59-2cbf-439a-a0c5-b3f56aa8d71a', 'Value'

INSERT INTO
    [Attribute] ([Id], [AttributeTypeId], [ProductId], [ValueId])
SELECT
    NEWID(), [ChecksumAttribute].[AttributeTypeId], [Product].[Id], [ChecksumAttribute].[AttributeValueId]
FROM
    [Product]
JOIN (
    SELECT
        [AttributeType].[Id] AS [AttributeTypeId]
        ,[AttributeValue].[Id] AS [AttributeValueId]
        ,ABS(CHECKSUM(CONCAT([AttributeType].[Id], [AttributeValue].[Id]))) % 10000 AS [Checksum]
    FROM
        [AttributeType]
    CROSS JOIN
        [AttributeValue]
) AS [ChecksumAttribute] ON ABS(CHECKSUM([Product].[Id])) % 10000 = [ChecksumAttribute].[Checksum]


ALTER TABLE [Product] ADD CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([Id])
ALTER TABLE [Attribute] ADD CONSTRAINT [PK_Attribute] PRIMARY KEY CLUSTERED ([Id])
ALTER TABLE [AttributeValue] ADD CONSTRAINT [PK_AttributeValue] PRIMARY KEY CLUSTERED ([Id])

Below is the query demonstrating the problem.

SELECT
    [GroupByResult].[AttributeTypeId]
    ,[AttributeValue].[Value]
    ,[GroupByResult].[Count]
FROM (
    SELECT
        [AttributeTypeId]
        ,[ValueId]
        ,COUNT(*) AS [Count]
    FROM
        [Attribute]
    WHERE
        [ProductId] IN 
        (
            SELECT
                [Id]
            FROM
                [Product]
            WHERE
                EXISTS (
                    SELECT
                        1
                    FROM
                        [Attribute]
                    JOIN
                        [AttributeValue] ON [Attribute].[ValueId] = [AttributeValue].[Id]
                    WHERE
                        [Product].[Id] = [Attribute].[ProductId] AND
                        ([Attribute].[AttributeTypeId] = '960BE057-EB5B-4746-9A96-0806723433E9') AND [Attribute].[ValueId] IN ('98082f59-2cbf-439a-a0c5-b3f56aa8d71a'))
        )
    GROUP BY
        [AttributeTypeId]
        ,[ValueId]
) AS [GroupByResult]
JOIN
    [AttributeValue] ON [GroupByResult].[ValueId] = [AttributeValue].[Id]
OPTION (MAXDOP 1, RECOMPILE)

As expected, clearly seen from the execution plan, an index is recommended.

enter image description here

CREATE NONCLUSTERED INDEX [IX_Attribute_SuggestedIndex1] ON [dbo].[Attribute] ([AttributeTypeId],[ValueId]) INCLUDE ([ProductId])

However after applying this index the sort operator remains. A second execution leads to another index being recommended.

enter image description here

CREATE NONCLUSTERED INDEX [IX_Attribute_SuggestedIndex2] ON [dbo].[Attribute] ([ProductId]) INCLUDE ([AttributeTypeId],[ValueId])

However after applying this index, again no luck.

enter image description here

I've pretty much tried every combination of indexes possible. I've tried making AttributeTypeId, ProductId, ValueId the primary key, to no avail.

Additionally, below is another query with a sort operator which spills on the production server.

SELECT TOP 1001
    *
FROM
    [Product]
WHERE
    EXISTS (
        SELECT
            1
        FROM
            [Attribute]
        WHERE
            [Product].[Id] = [Attribute].[ProductId] AND
            [Attribute].[AttributeTypeId] = '960BE057-EB5B-4746-9A96-0806723433E9' AND
            [Attribute].[ValueId] IN ('98082f59-2cbf-439a-a0c5-b3f56aa8d71a'))
OPTION (MAXDOP 1, RECOMPILE)

Again, I want to understand how I can avoid a tempdb spill by any means necessary.

EDIT. JSON as an alternative was suggested. This is new for me and still leads to the following problems: the sample SELECT queries are slow when compared to the ones above using the EAV model, and I need to be able to query any value, not just UNIQUEIDENTIFIER (suppose ValueId really was a JSON value in this case).

CREATE TABLE [Product2] (
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [Attributes] NVARCHAR(MAX)
)

INSERT INTO
    [Product2] ([Id], [Attributes])
SELECT
    [Product].[Id]
    ,[Json].[Value]
FROM
    [Product]
CROSS APPLY (
    SELECT (
        SELECT
            [Attribute].[AttributeTypeId]
            ,[Attribute].[ValueId]
        FROM
            [Attribute]
        WHERE
            [Product].[Id] = [Attribute].[ProductId]
        FOR JSON AUTO
    ) AS [Value]
) AS [Json]

SELECT TOP 1001
    *
FROM
    [Product2]
WHERE
    EXISTS (
        SELECT
            1
        FROM
            OPENJSON([Attributes]) WITH([AttributeTypeId] UNIQUEIDENTIFIER '$.AttributeTypeId', [ValueId] UNIQUEIDENTIFIER '$.ValueId')
        WHERE
            [AttributeTypeId] = '960BE057-EB5B-4746-9A96-0806723433E9' AND
            [ValueId] IN ('98082f59-2cbf-439a-a0c5-b3f56aa8d71a'))
OPTION (MAXDOP 1, RECOMPILE)
SELECT
    [AttributeTypeId]
    ,[ValueId]
    ,COUNT(*)
FROM
    [Product2]
CROSS APPLY OPENJSON([Attributes]) WITH([AttributeTypeId] UNIQUEIDENTIFIER '$.AttributeTypeId', [ValueId] UNIQUEIDENTIFIER '$.ValueId')
GROUP BY
    [AttributeTypeId]
    ,[ValueId]
OPTION (MAXDOP 1, RECOMPILE)

3 Answers 3

2

Ok. You've got the Entity-Attribute-Value (EAV) pattern there, and it's notorious for poor performance. If the tables are not too big, you won't notice. But don't expect blazing fast performance. You should expect TempDb spills, memory grant warnings, locking issues, and poor execution plans. So if you really care about optimal performance, choose a different way to model the data.

SQL Server has this really great feature where you model entities as rows and attributes as columns. It's called the relational model, and it's modeling approach that the SQL Server engine is optimized for.

If you can't use the relational model for your Product attributes because they are too variable, then model a subset of the attributes using XML or JSON, which SQL Server also supports. eg

CREATE TABLE [Product] 
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Name nvarchar(20) not null,
    ProductType int not null,
    Active bit not null default 1,
    Attributes nvarchar(max),
    constraint ck_is_valid_json check (isjson(Attributes) = 1)
)

The JSON should be a simple list of name/value pairs, like this

{"Description":"My Product","ManufacturerName":"Acme Widgets","ValidOn":"2019-01-01T12:00:00","SafetyStockLevel":10}

You can do lookups like this

SELECT *
FROM Product p
WHERE  json_value(p.Attributes,'$.Description') = 'My Cool Product'

And if you have an performance-critical attribute lookups, put an indexed computed column on Product that "promotes" the attribute and gives you fast lookups, like this

alter table product add Description as json_value(Attributes,'$.Description') persisted

create index ix_product_description on product(Description)

On top of that, the table design for EAV is not optimal. You should not use UNIQUEIDENTIFIERS for the keys, and even if you do, the key structure should look like this:

CREATE TABLE [Product] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID()
)

CREATE TABLE [AttributeType] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    [Description] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [Attribute] (
    [ProductId] UNIQUEIDENTIFIER NOT NULL REFERENCES Product,
    [AttributeTypeId] UNIQUEIDENTIFIER NOT NULL REFERENCES AttributeType,
    [Value] NVARCHAR(MAX) NULL,
    CONSTRAINT [PK_Attribute] PRIMARY KEY CLUSTERED (ProductId, AttributeTypeId)
)

In EAV accessing the attributes for a single entity is the primary access path, so the attributes should be clustered by EntityID. And the AttributeValue table doesn't really belong.

8
  • Thanks for your reply. A product can have a near infinite number of attributes, meaning I cannot add columns Attribute1, ... AttributeN to the Product table. Am I really forced to go the JSON route then? I just tested this, but it does not yield spectacular performance. I've updated my question. May 23, 2020 at 14:41
  • The json is instead of the ATTRIBUTE table, not in addition to it. May 23, 2020 at 14:44
  • Yes, I understood what you meant by "model entities as rows and attributes as columns". You can see that the queries in the edit no longer reference the Attribute table (except the first one to fill it with data to test with based on the existing table). May 23, 2020 at 14:47
  • I'd definitely be sold on the JSON concept, if performance was equal or greater. I'm fiddling around at the moment trying to get equal performance. learn.microsoft.com/en-us/sql/relational-databases/json/… provides no solace since I'm saving an array of values. May 23, 2020 at 15:23
  • 1
    Statistics use a histogram to model 1 dimensional lookups accurately (e.g. Entity). However for 2 dimensions (e.g.E+A) sql server only adds a simple distribution at each histogram point to estimate all the attribute values which can result in poor estimates. However it is possible to manually create additional statistics histrograms that are locked to specific attribute Ids (using a where clause). We've had success with this but it would be nice if the Sql Server team would allow a table to be designated as EAV which could automatically build and maintain true 2 dimensional histograms,
    – crokusek
    Jan 19, 2021 at 5:20
2

I wanted to reply on your comment 'Since I have also read that SQL Server by itself will request additional memory for a query if it notices its estimation was wrong for a certain operator. '.

This is not true. SQL Server determines upfront how many memory it requires based on the query plan and can and will not change it dynamically (called a Memory Grant). It will not dynamically alter the memory requirement of the query (more on that later). So if the memory estimate is off, then SQL Server will use the darn slow tempdb, because it finds out in the middle of execution that it doesn't have enough memory.

And by the way; the tempdb spills will be discussed in that course.

The reason why the memory estimates are off is that the row estimates of the query plan are off. Mostly this has to do with statistics not being up-to-date, but can also have other reasons. The statistics are presented to the optimizer as a historgram of the column value ranges and how many rows are present for these ranges in the table. Also, this can be for example, when you use a user defined function (UDF). SQL Server does not have a mechanism that can determine the rows returned by the function. SQL Server 2019 can improve that with a function called 'Scalar UDF Inlining'.

Regarding the Memory Grant; in SQL Server there is a function called 'Memory Grant Feedback' in which the engine will detect that there was not enough memory granted for this query, so it will assign more for the next. A sort of tuning mechanism. However, it can make things worse.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15#batch-mode-memory-grant-feedback

If you want to learn how the estimation of rows and, moreover, the optimizer, then I would advise you to watch Brent Ozar's 'Think like the SQL Engine'. It is a free course. You can find it on his website and his Twitch channel (which is megalodonish awesome by the way..). It is a relatively short course which learns you a lot of fundatmentals of how that works.

0

Basically, you got the spill as SQL Server needed more memory as it estimated.

If you check the Sort operator screenshot you posted, you can see SQL Server estimated 5773.79 rows could be returned but the actual rows was 149146. For some reason, SQL Server did an inaccurate estimation.

Since you said you have updated statistics, my guess is it's due to your unevenly distributed data. Can't prove it without seeing data and statistics though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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