sp_BlitzCache inserts spill data twice when using sort order all or all avg

[eschnepel]

Version of the script
SELECT @Version = '8.16', @VersionDate = '20230820';

What is the current behavior?
spill data is inserted twice when using all or all avg sort order

If the current behavior is a bug, please provide the steps to reproduce.
call sp_BlitzCahch with @sortorder = 'all' and a output table
select from the configured outputtable using where Pattern = 'spills'
the row count is twice as @top parameter

What is the expected behavior?
spill data should be inserted only once.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
This behavior is independent from the SQL Server version - the last step in the all sort order if case is inserted again. I have not tested previous versions.

I have created a patch already.

[eschnepel]

created pull request #3333

[BrentOzar]

You said pull request #3333 solves this, but it appears to be completely unrelated.

[BrentOzar]

Fixed in pull request #3336, check that one out. Thanks!

[eschnepel]

Hi, apparently this does not solve the issue.
A small example...

EXEC sp_BlitzCache
  @SortOrder          = 'all',
  @Top                = 1,
  @OutputDatabaseName = 'SomeDB',
  @OutputSchemaName   = 'SomeSchema',
  @OutputTableName    = 'SomeTable';

outputs a table with one row per pattern cpu, reads, writes, duration, executions, memory grant, spills but the target table (SomeDB.SomeSchema.SomeTable) contains an additional entry for spills because spills was the last pattern in the all sorts block and the insert in the main procedure into the target table is executed afterwards using the still existing data in ##-tables from the most recently executed sub procedure.

To avoid this the return statement from the original pull request is needed.

[BrentOzar]

Forgive me, but I'm just not seeing it here, and you didn't include a repro query, so I can't easily reproduce this.

I also can't find the pull request that's relevant to this - that's part of why I asked for one issue, one pull request.

I'm reopening the issue because I bet you're on to something, I just can't see what/where it is. Can you either submit a pull request to fix this one specific issue (and nothing else), or give me a way to reproduce what you're seeing? Thanks for your patience!

[BrentOzar]

Thanks for the separate issue & pull request! I have not been able to reproduce this for love or money, hahaha, but I'm going to merge it because I don't think it'd break anything. Merging into the dev branch, will be in the next release with credit to you in the release notes.