need script to generate load on tempdb databse!!!

  • sanketahir1985

    SSCarpal Tunnel

    Points: 4413

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

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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: 143327

    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: 143327

    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: 993379

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • sanketahir1985

    SSCarpal Tunnel

    Points: 4413

    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: 143327

    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: 4413

    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