Hi there.  I have a question regarding troubleshooting some inconsistencies with a stored procedure that we have.  The stored procedure has been scheduled as a scheduled task within the Windows environment.  It's scheduled to run every 15 minutes.  When it is run as a stored procedure it sometimes takes seconds, other times minutes and often we have to kill the database process.  How would I be able to troubleshoot what could be causing this?

Can you post the code to help finding the problem

It may be locks or blocks due to other sp or applications that access the same tabls/views which is used by the stored procedure.

Use Sql activity monitor to  catch these actions.

Try to know what is others are running during execution of that sp 

Review sql log  events for something strange.

 We are having a proc as or not it normally returns around 15K records, and front hand app is .net and whenever the result set is more than 10 K it throwing some exceptions.....Now one of our developer wants me to modify the proc in such a way as the proc should return the data in a subset of the whole data and they will show each subset in different pages with indexes on them and

It is something like if user is on page 1 then it should show 1 to 2000 records and when he clicked 5th page then it should show 8001 to 10000 records....I'm not sure if we can handle it inside the procedure.

Do you guys have any IDEA?