Sql-server – launch a stored procedure and immediately return without waiting for it to finish

sql serversql-server-2005stored-procedures

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.

I have a second stored procedure that should be run after the first stored procedure runs since it is based on the numbers obtained from this first stored procedure, however it takes longer to run and is for a separate process, so I don't want to make the user wait while this 2nd stored procedure gets ran.

Is there a way to have one stored procedure start a second stored procedure, and return immediately without waiting for results?

I'm using SQL Server 2005.

Best Answer

It looks like there are multiple ways to accomplish this, but I found the simplest way was Martin's suggestion of setting up the procedure in a SQL job, and starting it using the asynchronous sp_start_job command from my stored procedure.

EXEC msdb.dbo.sp_start_job @job_name='Run2ndStoredProcedure'

This only works for me because I don't need to specify any parameters for my stored procedure.

Other suggestions that may work depending on your situation are

Using the SQL Service Broker like Martin and Sebastian suggest. This is probably the best suggestion if you don't mind the complexity of setting it up and learning how it works.
Running the process asynchronously in the code that is responsible for executing the stored procedure, like Mr.Brownstone suggested.

Not a bad idea, however in my case the stored procedure gets called from multiple places, so finding all those places and ensuring they call the 2nd procedure too didn't seem that practical. Also, the 2nd stored procedure is fairly critical, and forgetting to run it could cause some major problems for our company.
Make the 1st procedure set a flag, and setup a recurring job to check for that flag and run if its set, like Jimbo suggested. I'm not a big fan of jobs that run constantly and check for changes every few minutes, but it certainly is an option worth considering depending on your situation.

Related Solutions

Sql-server – Join To a @Table Variable is running ineficiently

[Copying from my answer on SQLPerformance.com.]

Some very brief initial suggestions from discussions elsewhere:

Try creating @xmlTemp as a #temp table with a clustered index on (StartDate, EndDate) instead of a table variable. This may provide SQL Server with more accurate stats information (though questionably useful if the table only has one row).
If @xmlTemp has only one row always, use two variables instead of a table in the first place.
Try adding the (RECOMPILE) option to the statement, especially if you convert to variables instead of the #temp table (parameter sniffing).
Try using OPTION (MAXDOP 1) - parallelism is definitely in use, and at the lower end the threads seem partially imbalanced. I wonder if parallelism is helping or hurting here - can't hurt to test duration with and without.
You may need to perform more rigorous stats updates. A lot of these estimates are way, way off.
Remove the DISTINCTs. For this set of columns I find it hard to believe this is eliminating any duplicates, but the optimizer has to work as if there are dupes to remove.
Consider using Table-Valued Parameters (TVPs) instead of shredding XML for the different companies / stores.

SQL Server – Stored Procedure Slow on First Load, Fast on Subsequent Loads

The issue was the setting "Auto close" was set to true on a series of databases on the server. Even though the databases were unrelated their "auto close" setting was flushing all execution plans on the server.

Here are the types of log issues you will see with auto_close set to true:

SQL Server has encountered 24 occurrence(s) of     cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

As you can see in this article, NEVER SET AUTO CLOSE TO TRUE!!! http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Best Answer

Related Solutions

Sql-server – Join To a @Table Variable is running ineficiently

SQL Server – Stored Procedure Slow on First Load, Fast on Subsequent Loads

Related Question