Need to debug SP's.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Need to debug SP's....

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-14 : 02:32:35

Hi,

I need to debug my Store Procedures with diff parameters at regaular intervals which shows me memory usage, CPU usage and other usage.

Is there any way to do this?

-- Regards
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 04:15:18

http://www.expresscomputeronline.com/20021118/techspace1.shtml

http://articles.techrepublic.com.com/5100-10878_11-1043670.html

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-16 : 02:05:34

Thanks visakh,

I need to run debug some store procedures from back end side [i.e. Database] only.

I need some way from sql server 2005 which executes particular SP Continuously with different parameters.

Is there any scripts or say any way which fulfill my requirenments?

Thanks
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 02:10:28

quote:
Originally posted by hirani_prashant

Thanks visakh,

I need to run debug some store procedures from back end side [i.e. Database] only.

I need some way from sql server 2005 which executes particular SP Continuously with different parameters.

Is there any scripts or say any way which fulfill my requirenments?

Thanks
Prashant Hirani

Do you mean generating random param values and running sp passing them?

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-16 : 02:31:11

yes...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 02:37:15

Is your param values coming from fields of some table. Or do you just want to give some values. A small background explanation of your sp & what it does might help.

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-16 : 02:41:22

Hi,

Ya mine params values coming from fields of some table.

Thanks
Prashant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 03:41:34

quote:
Originally posted by hirani_prashant

Hi,

Ya mine params values coming from fields of some table.

Thanks
Prashant

Then loop through the table in a random manner and get param values and pass it to sp to get result in each case.

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-06-16 : 04:45:15

Hi,

Thanks...

Not getting you. Can you please explain in detail??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:35:50

If you table is like

(ID int primary key,
field1,field2,....)

then use

SELECT TOP 1 @ID=ID
FROm Table
ORDER BY NEWID()

WHILE @ID IS NOT NULL
BEGIN 
SELECT @Field1=Field1,@Field2=Field2,...
FROm Table
WHERE ID = @ID

EXEC YourProc @Field1,@Field2,....

INSERT INTO #Processed
SELECT @ID

SELECT TOP 1 @ID=ID
FROm Table
WHERE ID NOT IN
(SELECT ID FROM #Processed)
ORDER BY NEWID()
END

it will loop trough taking random records from table and executing procedure with them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources