Contents
Why is SQL Server stored procedure a lot slower than straight query?
Try changing your SP to using local copies of the variables passed in. I have found before that due to Parameter Snffing, a SP can run a lot slower, but the performance returns once you use copies of the variables. What is Parameter Sniffing ?
Why does SQL Server run slow with different execution plan?
But putting the same SQL in a stored procedure runs slow, and with a totally different execution plan And it still runs the same (badly), and I’ve also changed the stored procedure to And back again, trying to really trick it into recompiling.
How many SQL queries are executed during stored procedure?
Basically during execution of the stored procedure the executed 3 SQL queries, the first Select Into query takes 99% of time.
Is the stored procedure in SQL Server cached?
Since you are using sp_executesql recompiling the procedure, or clearing the cached plan for the procedure won’t actually help, the query plan for the query executed via sp_executesql is cached separately to the stored procedure.
How long does SQL Server stored procedure take?
You could force a locking level, e.g. WITH (NOLOCK) after the table name, which could resolve the issue (but note that you could get inaccurate results by doing that). Did the trick for me. The stored proc was taking 13 seconds for run and the query takes 0.00 seconds. After running the above command, they both take 0.00 seconds 🙂
How long does it take to run a Proc in SQL?
The stored proc was taking 13 seconds for run and the query takes 0.00 seconds. After running the above command, they both take 0.00 seconds 🙂 Thanks for contributing an answer to Stack Overflow!
Can you use dynamic SQL instead of proc?
Instead of using dynamic SQL, you could always just change your proc calls to: The WITH RECOMPILE forces (you guessed it!) a recompile of the execution plan whenever it is run. You can also include WITH RECOMPILE in the definition of the stored proc: CREATE PROCEDURE usp.MyProcedure (Parameters) WITH RECOMPILE AS