Hi All, I am new to this forum and this is my first thread. I have one pretty messy stored procedure which in turns call around 8-10 stored procedures. Now I have been asked to optimize it. For this big procedure I don't understand where to start.
Here are few points:
1. This procedure takes around 4-5 parameters but the processing happens mostly based on one parameter for which the processing rows can vary from as low as few hundreds to as high as thousands of records.
2. For biggest size of processing records, the procedure takes almost an hour to complete in production but when we take fresh dump from production to equivalent (in hardware and resources) non-production environment, it takes around 20-25 minutes.
During the running of the stored procedure in the production environment, you can keep running this query to figure out which piece of query and in which specific SP it is taking long time.
SELECT A.spid, A.hostname, A.program_name, A.hostprocess, A.cmd, DB_NAME(A.dbid), B.name, A.stmtnum, A.linenum
FROM master..sysprocesses A,
AND A.spid=21 -- If you give SPID you will get specific result.