need script to generate load on tempdb databse!!!

Question

Post reply

need script to generate load on tempdb databse!!!

sanketahir1985

SSCarpal Tunnel

Points: 4473
More actions
February 1, 2010 at 12:43 am

#209566

Hi friends
can any buddy provide script to generate load on tempdb database in sql 2005.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......

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

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Could you please refer to the article listed below and let me know if this helps?

Regards,

Kent

http://www.mssqltips.com/tip.asp?tip=1853

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Well, I am no fan of loops, but since the goal IS to slow things down, this ought to do it:

CREATE PROC EXERCISE_TEMPDB_SILLY AS

SET STATISTICS TIME ON

SET STATISTICS IO ON

CREATE TABLE #temp(RecNo INT PRIMARY KEY CLUSTERED, Data varchar(80))

INSERT INTO #temp SELECT -1, CAST(NewID() AS varchar(80))

DECLARE @cnt AS INT

SET @cnt = 0

WHILE @cnt > -1

BEGIN

Print 'Loop ' + CAST(@cnt AS VARCHAR(9))

INSERT INTO #temp

SELECT RecNo-Power(2,@cnt) , Right(Data + CAST(NewID() AS varchar(80)), 80)

FROM #temp

SET @cnt = @cnt+1

END

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

FYI, it goes ballistic around Loop 18 or 19 for me, which takes about 5-10 seconds to reach.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

sanketahir1985 (2/1/2010)
Hi friends
can any buddy provide script to generate load on tempdb database in sql 2005.

Heh... generate a load? Shoot... run one or two of the following... this will stress TempDB and it's log file to the max...

USE TempDB;

GO

SELECT TOP 1000000000

IDENTITY(INT,1,1) AS RowNum

INTO #StressTempDB

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

GO

I guarantee that it will "generate a load on TempDB".

--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)

sanketahir1985 SSCarpal Tunnel Points: 4473 More actions · Answer 5

RBarryYoung (2/6/2010)
Well, I am no fan of loops, but since the goal IS to slow things down, this ought to do it:
CREATE PROC EXERCISE_TEMPDB_SILLY AS
SET STATISTICS TIME ON
SET STATISTICS IO ON
CREATE TABLE #temp(RecNo INT PRIMARY KEY CLUSTERED, Data varchar(80))
INSERT INTO #temp SELECT -1, CAST(NewID() AS varchar(80))
DECLARE @cnt AS INT
SET @cnt = 0
WHILE @cnt > -1
BEGIN
Print 'Loop ' + CAST(@cnt AS VARCHAR(9))
INSERT INTO #temp
SELECT RecNo-Power(2,@cnt) , Right(Data + CAST(NewID() AS varchar(80)), 80)
FROM #temp
SET @cnt = @cnt+1
END

hiii RBarryYoung,

on my server its going beyond 21 loops

so finally i have to kill this SP

tell me exactly whether this SP goes in infinite loop or will finish after some time?

Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

sanketahir1985 (2/8/2010)
...
hiii RBarryYoung,
on my server its going beyond 21 loops
so finally i have to kill this SP
tell me exactly whether this SP goes in infinite loop or will finish after some time?

No, that is correct, this script will run until you kill it. That is quite typical for a "script to generate load" as you requested. For instance, Jeff's script *will* finish "sometime", but that "sometime" could take several days. If this is not what you want then please let us know.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sanketahir1985 SSCarpal Tunnel Points: 4473 More actions · Answer 7

OK

i m fine with this

Thanx a lot to both of u

Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......