Spills into tempdb poor performing query fixed by converting to SP

Question

Spills into tempdb poor performing query fixed by converting to SP

SQL Bandit

SSCommitted

Points: 1757
More actions
April 20, 2018 at 3:55 am

#344486

Hi Guru's
I had a poor performing query that took and average almost 4 seconds to execute. In the execution plan there was a sort with a warning and a Hash Match with a warning, both stating that the operator had to spill data during the execution into the tempdb.
Now the now statistics where to date and updated again for good measure. But that didn't resolve the issue or improve the execution time of the query.
So I thought ok maybe the query really is having to spill into the tempdb because it ran out of the memory resource it had. So I converted it query into a stored procedure because I know it has its own procedure cache to see if that helps.
Not only did the execution plan of the stored procedure not use a sort or a hash match, but the execution went from 4 seconds to 0.3 seconds.
So I'm intrigued about is what could cause a spill into tempdb issue that's not caused by statistics and what piece of memory must the query have been using in order to run and out need to spill into tempdb? It goes without saying that my db server is well resourced.
I only say that spills can be caused but outdated statistics because that's what pops up when I google spills into tempdb.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL Bandit SSCommitted Points: 1757 More actions · Answer 1

You can semi-ignore me in a way. It is a statistics issue in a way. The estimated Number of Rows was 211 , but the actual number of rows was 248807. So I guess the initial allocation of memory for the query was intended for 211 rows. When sql server then realised it had to deal with 248807 rows is ran out of memory allocated for that query and spilled into tempdb.

Well that's guessing with my rudimentary understanding.

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 2

Can you post the queries and the execution plans?
😎

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 3

My first-blush guess would be that the original code used values only known at execution time and the stored procedure used parameters known prior to execution time.

However, that's just a guess. As Eirikur posted, we'd need the code and the actual execution plans to figure out exactly why,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Bandit SSCommitted Points: 1757 More actions · Answer 4

declare @accountId0 bigint = 1719,
@status1 int = 11,
@senderDone2 bit = 0,
@PreviewMode3 bit = 0,
@OutsideRetention4 bit = 0;

declare @documents TABLE(
          Id bigint not null primary key,
           RowNum bigint not null,
          CommentsCount bigint null,
          CompanyName nvarchar(250) null);

        with t as
          (select
          d.[Id],
           ROW_NUMBER() over(order by d.id desc, d.[Id] desc) as RowNum,
          dv.[CommentsCount],
          s.[CompanyName]
        from [Document] d
        inner join [DocumentProfile] mp on d.[ProfileId] = mp.[Id]
        inner join [Mailing] m on m.[Id] = d.[MailingId]
        inner join [Subscription] s on s.[Id] = d.[AccountId]
        left join [DocumentDiscussionTotalsIndexedView] dv with (noexpand) on dv.[DocumentId]=d.[Id]
              WHERE (d.AccountId = @accountId0 AND status<> @status1 AND senderDone = @senderDone2 AND m.PreviewMode = @PreviewMode3 AND m.OutsideRetention = @OutsideRetention4)
        )
        insert into @documents
        select * from t where
        rownum > 0 AND rownum <= 50;

        select
          docs.[RowNum],
          d.[Id], d.[ContactId], d.[AccountId], d.[MailingId] , d.[SecretId], d.[SecretPin] , d.[DocumentType], d.[DocumentDate],
          d.[RecipientReference], d.[SenderDocumentReference], d.[RecipientDocumentReference], d.[Amount], d.[Currency], d.[CreationDate],
          d.[PageCount], [SentDate], d.[ViewedDate], d.[RecipientDone], d.[SenderDone], d.[Read], d.[Subject], d.[Status], d.[RecipientId],
          d.[RecipientCompanyNumber], d.[RecipientVATNumber], d.[RecipientTelephoneNumber], d.[PreviousStatus], d.[From], d.[SentTo],
          d.[RecipientEmailAddress], d.[LastStatusChangeDate], d.[Idle], d.[TimeUntilActioned],
          d.[MailingName], d.[ProfileId],
          d.[DiscussionOpen],
          d.[Owner], d.[TestMode],
          docs.[CommentsCount] as Comments,
          (select count(1) FROM DocumentAdditionalRecipient r where r.[DocumentId] = d.[Id] and (r.[AccountId] = d.[AccountId] OR r.[AccountId] = d.[RecipientId])) as AdditionalRecipientsTotal,
          (select count(1) FROM [DocumentSupportingFile] dsf where dsf.[DocumentId] = d.[Id] and dsf.[AccountId] = d.[AccountId]) as SupportingFileTotal,
          d.[PaidStatus],
          docs.[CompanyName]
        from @documents docs
              inner join [Document] d on docs.[Id] = d.[Id]
        order by docs.[RowNum] asc;

SQL Bandit SSCommitted Points: 1757 More actions · Answer 5

Hi There I've posted the query and the execution plan.

As always any help to be greatly appreciated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SQL Bandit - Monday, April 23, 2018 4:35 AM
Hi There I've posted the query and the execution plan.
As always any help to be greatly appreciated.

Add OPTION(RECOMPILE) to the first query - with so many parameters, it's unlikely that a single plan will be best for all combinations.
Put the view into the second query. I guess the view definition includes a scalar UDF which is inhibiting parallelism. With the row count substantially reduced, it will matter less in the second query than in the first. You might also eliminate the optimiser timeout with this step.
Create this index

CREATE INDEX ix_Stuff ON [Mailing] (PreviewMode, OutsideRetention) INCLUDE (Id)

to give the optimiser a better set of choices. Since it's responsible for most of the rowcount elimination, it may change place to become the first table read.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 7

SQL Bandit - Monday, April 23, 2018 4:33 AM
declare @accountId0 bigint = 1719,
@status1 int = 11,
@senderDone2 bit = 0,
@PreviewMode3 bit = 0,
@OutsideRetention4 bit = 0;
declare @documents TABLE(
          Id bigint not null primary key,
           RowNum bigint not null,
          CommentsCount bigint null,
          CompanyName nvarchar(250) null);
        with t as
          (select
          d.[Id],
           ROW_NUMBER() over(order by d.id desc, d.[Id] desc) as RowNum,
          dv.[CommentsCount],
          s.[CompanyName]
        from [Document] d
        inner join [DocumentProfile] mp on d.[ProfileId] = mp.[Id]
        inner join [Mailing] m on m.[Id] = d.[MailingId]
        inner join [Subscription] s on s.[Id] = d.[AccountId]
        left join [DocumentDiscussionTotalsIndexedView] dv with (noexpand) on dv.[DocumentId]=d.[Id]
              WHERE (d.AccountId = @accountId0 AND status<> @status1 AND senderDone = @senderDone2 AND m.PreviewMode = @PreviewMode3 AND m.OutsideRetention = @OutsideRetention4)
        )
        insert into @documents
        select * from t where
        rownum > 0 AND rownum <= 50;
        select
          docs.[RowNum],
          d.[Id], d.[ContactId], d.[AccountId], d.[MailingId] , d.[SecretId], d.[SecretPin] , d.[DocumentType], d.[DocumentDate],
          d.[RecipientReference], d.[SenderDocumentReference], d.[RecipientDocumentReference], d.[Amount], d.[Currency], d.[CreationDate],
          d.[PageCount], [SentDate], d.[ViewedDate], d.[RecipientDone], d.[SenderDone], d.[Read], d.[Subject], d.[Status], d.[RecipientId],
          d.[RecipientCompanyNumber], d.[RecipientVATNumber], d.[RecipientTelephoneNumber], d.[PreviousStatus], d.[From], d.[SentTo],
          d.[RecipientEmailAddress], d.[LastStatusChangeDate], d.[Idle], d.[TimeUntilActioned],
          d.[MailingName], d.[ProfileId],
          d.[DiscussionOpen],
          d.[Owner], d.[TestMode],
          docs.[CommentsCount] as Comments,
          (select count(1) FROM DocumentAdditionalRecipient r where r.[DocumentId] = d.[Id] and (r.[AccountId] = d.[AccountId] OR r.[AccountId] = d.[RecipientId])) as AdditionalRecipientsTotal,
          (select count(1) FROM [DocumentSupportingFile] dsf where dsf.[DocumentId] = d.[Id] and dsf.[AccountId] = d.[AccountId]) as SupportingFileTotal,
          d.[PaidStatus],
          docs.[CompanyName]
        from @documents docs
              inner join [Document] d on docs.[Id] = d.[Id]
        order by docs.[RowNum] asc;

You said one of them was a stored procedure. Where's the code for the stored procedure?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)