Killer scripts

I am trying to understand how to monitor SQL Server for various performance
problems and would like to find some scripts to cause performance problems.
For example:
A script causing high CPU usage
A script causing high memory usage
A script causing intensive IO
Can anyone point me in the right direction or give me an idea of what kind
of sql I could write to produce the above effects.The 'min server memory' and 'max server memory' configuration settings can
be used to restrict the amount of RAM available to SQL Server. Bumping this
down to 50 - 100mb or so would not only impact memory, but could affect disk
I/O and CPU due to disk swapping.
set transaction isolation level SERIALIZABLE or specifying the HOLDLOCK
option on a query will cuase a dataset to be exclusively locked.
The WAITFOR DELAY 'hh:mm:ss' statement can be used to pause processing in
the middle of a transaction, thus holding a lock on resources.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:35D5D71C-688D-4EBA-A3B5-0C9B8117F01C@.microsoft.com...
> I am trying to understand how to monitor SQL Server for various
performance
> problems and would like to find some scripts to cause performance
problems.
> For example:
> A script causing high CPU usage
> A script causing high memory usage
> A script causing intensive IO
> Can anyone point me in the right direction or give me an idea of what kind
> of sql I could write to produce the above effects.|||This may do..
========================================
===============
Create Procedure ResourceHog As
Set NoCount On
Declare @.i int
Set @.i = 0
Create Table #hog ( num int )
While (1=1)
Begin
While (@.i < 1000)
Begin
Insert #hog Values (@.i)
Select @.i = @.i + 1
End
Select h1.num n1, h2.num n2, h3.num n3, h4.num n4, h5.num n5
From #hog h1
Full Outer Join #hog h2 On h1.num <> h2.num
Full Outer Join #hog h3 On h1.num <> h3.num
Full Outer Join #hog h4 On h1.num <> h4.num
Full Outer Join #hog h5 On h1.num <> h5.num
End
========================================
===============