Stored proc to hammer tempdb, useful for tests.

IF OBJECT_ID(‘dbo.heater’) IS NOT NULL

BEGIN

DROP PROCEDURE dbo.heater

IF OBJECT_ID(‘dbo.heater’) IS NOT NULL

PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.heater >>>’

ELSE

PRINT ‘<<< DROPPED PROCEDURE dbo.heater >>>’

END

go

create procedure heater @limit int

as

begin

set nocount on

declare @error                    int,

@procname                 varchar(35),

@tc                       int,

@rc                       int,

@rs                       int,

/* Timing the process */

@elapsed_time1            datetime,

@elapsed_time2            datetime,

@snapdate                 datetime,

@upd_stats_diff           int,

@seconds                  numeric (10,4),

/* Counting rows. Declaring variables */

@total_deleted            int,

@total_inserted           int,

@total_updated            int,

@inf_initial              int,

@inf_final                int

select @elapsed_time1=getdate()

print “”

print “Starting time: %1!. “, @elapsed_time1

print “”

create table #heater (col1 int, col2 char(255), col3 char(255), col4 char(255))

declare @counter int

select @counter =1

while (@counter < @limit )

begin

insert into  #heater

values (@counter, “AAAAAAAAAAAAAAAAA”, “BBBBBBBBBBBBBBBBBBBBBBB”, “CCCCCCCCCCCCCCCCCCCCCCCCCC”)

select @counter = @counter + 1

if (@counter = @limit/2)

begin

select getdate()

exec sp_helpsegment “logsegment”

exec sp_helpsegment “default”

exec sp_helpsegment “system”

end

end

/* Timing the process */

select @elapsed_time2=getdate()

select @seconds=datediff(ss, @elapsed_time1, @elapsed_time2)

print “”

print “Ending time: %1!. Elapsed time: %2! seconds”, @elapsed_time2, @seconds

end

go

IF OBJECT_ID(‘dbo.heater’) IS NOT NULL

PRINT ‘<<< CREATED PROCEDURE dbo.heater >>>’

ELSE

PRINT ‘<<< FAILED CREATING PROCEDURE dbo.heater >>>’

go

EXEC sp_procxmode ‘dbo.heater’,’unchained’

go