How to manage long execution time taken by the sp's in sql-server

Question

We are using sp's to convert a uploaded file into corresponding master values and their combination as the business rule demands. For this we have made 4 sp's . All these sp's are called from a single sp which the user calls from application along with the data from the file.

Note: Each sp must be called after the previous one has finished execution.

Now each of the 4 sp's is computing lot of calculations and hence are slow and at time the server hangs. Now I ask for your advice for the following:

a) Is there any way by which we can give these sp's high priority of execution ?

b) Does sql server 2008 provide a better turn-around time for this than sql-server 2005 (currently being used by us) ?

c) Is there any other approach we could follow that would increase the turn-around time for the whole process.

Any suggestions in this regard are welcome.

Thanks in advance.

Answer 1

Regarding parts (a) and (b) of your question in SQL Server 2008 the resource governor allows you to assign different priorities to different workloads.

Regarding (c) are you sure that the stored procedures themselves can not be optimised any further? What do they do? What is the bottle neck for them?