Need high resource expensive sql queries

Question

Post reply

Need high resource expensive sql queries

sejal p gudhka

SSCrazy

Points: 2102
More actions
September 7, 2010 at 1:30 am

#239946

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.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

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)

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

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.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

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? 🙂