need script to generate load on tempdb databse!!!

  • 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......

  • 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/

  • 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]

  • 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]

  • 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)
    Intro to Tally Tables and Functions

  • 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......

  • 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]

  • 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......

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

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