Need high resource expensive sql queries

  • Hi All,

    I need sql queries or bad tsql code for producing high expensive resource utilization queries to test some performance reports. I ahve adventure works database in my environment. Please let me know as from where will I get high expensive queries for my testing..

    thanks in advance.

  • Errrmmm... that's a requirement not seen very often... 😉

    One option would be to create a c.u.r.s.o.r. on a rather large table (to "avoid" a set based operation).

    Inside the c.u.r.s.o.r.:

    Join a large number of (large) tables on predicates that are not supported by any index (to force a table scan). Take as many columns as you like and build a large comma delimited string. Insert results into a table variable and use a loop to split the string again by stepping through each character.

    Most important: make sure not to run it on a production system!



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Have a million row table and do running total using a triangular join

    select col,

    (select sum(col) from table as t1 where t1.pkcol<=t2.pkcol) as run_sum

    from table as t2


    Madhivanan

    Failing to plan is Planning to fail

  • Madhivanan-208264 (9/7/2010)


    Have a million row table and do running total using a triangular join

    select col,

    (select sum(col) from table as t1 where t1.pkcol<=t2.pkcol) as run_sum

    from table as t2

    Heh... ya beat me to it, Madhivanan.

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

  • Also force maxdop to 0 if your server isn't already set to it....a horrible, awful query like that should eat up every available core.

  • Jeff Moden (9/7/2010)


    Madhivanan-208264 (9/7/2010)


    Have a million row table and do running total using a triangular join

    select col,

    (select sum(col) from table as t1 where t1.pkcol<=t2.pkcol) as run_sum

    from table as t2

    Heh... ya beat me to it, Madhivanan.

    Yes. Thats the good example that I know which takes lot of time.

    The avatar you used is very good

    Are you a designer too? 🙂


    Madhivanan

    Failing to plan is Planning to fail

  • Madhivanan-208264 (9/8/2010)


    The avatar you used is very good

    Are you a designer too? 🙂

    No... someone else did most of the work. I just overlaid the word "RBAR" and converted it to a GIF.

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

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

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