Tempdb stress test
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