Post reply

Need to overload tempdb

  • November 13, 2025 at 7:47 pm

    #4684851

    Yes, you read that title correctly.

    I have a somewhat hilarious problem. We have some queries that are overloading tempdb which fills up the drive and brings everything to a halt. This is a reporting server, not OLTP, so we've been given permission from the application owner to kill all queries that fill up the drive.

    The hilarity is that I have nothing in non-prod that replicates this issue. I need to deliberately cause tempdb spills or over allocation in order to test this issue. Unfortunately, I have no idea how to deliberately force the issue and Google is telling me how to resolve it, not automate it or cause the issue deliberately.

    I don't need code for a solution. I just need code or suggestions for how to deliberately cause tempdb to overload. Does anyone have anything?

     

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • November 13, 2025 at 10:51 pm

    #4684894

    How about creating a giant temp table from a CROSS JOIN using 2 fnTally functions.  Then join the temp table to itself forcing a HASH JOIN with tiny memory grants and no parallelism.  If anyone asks where you got this from I'LL DENY IT 🙂

    declare @n1 bigint = 2000000;   -- base rows
    declare @n2 int    = 4000;      -- widen factor

    select row_number() over (order by n1.n, n2.n) as a,
           replicate('x', @n2) AS b
    from dbo.fntally(1, @n1) n1
         cross join dbo.fntally(1, @n1) n2
    into #t;

    -- hash join with tiny grant -> tempdb spills
    select count_big(*)
    from #t as t1
         join #t as t2 with (hash) on t1.a = t2.a
    option (hash join, maxdop 1, min_grant_percent = 0, max_grant_percent = 0);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 2 posts - 1 through 2 (of 2 total)

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