sql - Performance problem with 8 Nested Stored Procedures -

i have performance problem
need run stored procedure .net 1.1. stored procedure calls 8 stored procedures. each 1 of them process information throw comparative between old new informacion , anter afects physical table in database.

the problem comes since try run directly ssms. servers starts crashing, getting slow , impossible work. think infrastructure people has restar service directly on server.

i'm working in development enviroment there no problem, can't upload production enviroment.

i've been thinking in use procedures comparison purposes , never affect physical data. retrive information them in temporary tables in principal procedure , open try-catch , begin-end transactions blocks , affect database in principal stored informacion in temp tables.

my principal stored follows: best way can this??

create proc sptest
as
/*some processes here, temporary tables, etc...*/
begin try
begin distributed transaction
sp_nested1
sp_nested2
sp_nested3
sp_nested4
sp_nested5
sp_nested6
sp_nested7
sp_nested8
/*more processes here, updates, deletes, inserts, etc...*/
commit transaction
end try
begin catch
rollback transaction
declare @error varchar(3000)
select @error = convert(varchar(3000),error_message())
raiserror(@error,16,32)
return
end catch

the basic structure of each nested stored proc similar doesn't call other proc, each 1 has own try , catch blocks.

any appreciated... version im using sql server 2005

thank in advance....

first when things slow, there problem in wrote. first place execution plan of each stored proc. have table scans?

have run each 1 individually , seen how fast each 1 is? define whether problem 8 procs or else. appear have lot of steps involved in this, procs may or may not problem.

are processing data row-by-row using cursor or while loop or scalar user-defined function or correlated subquery? can affect speed greatly. have correct indexing? query statements sargable? see have distributed transaction, sure user running proc has correct rights on other servers? , servers exist , running? running out of room in temp db? need run in batches rather try update millions of records across multiple servers?

without seeing mess, hard determine might causing slow.

but share how work long complex procs. first have test variable use rollback transactions @ end until i'm sure i'm getting right actions happening. return results of have inserted before doing rollback. isn't going speed problem. set anyway because if can't figure out problem execution plan, want comment out first step , run proc in test mode (and rollback) keep adding steps until see 1 getting stuck on. of course may more one.