CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-performance
user21641787
user21641787

Reputation: 79

Stored Procedure vs Direct Execute performance

I have this stored procedure on a SQL Server database which is used to update static data. It has 40,000 table inserts/updates in it. It has no parameters.

If I execute the stored procedure in SQL Server Management Studio (SSMS), it's taking over 15 minutes to run. However, if I run its script contents directly it's taking 1 minute!

I have tried things like: OPTION(RECOMPILE) UPDATE STATISTICS DBCC FREEPROCCACHE;

Upvotes: -3

Views: 92

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

This can vary based on how you do the testing.

SQL Server will cache compiled query execution plans, so for complex procedures with lots of statements, if you test the procedure first, and then test the exact same statements outside of the procedure right after, SQL Server will detect the queries are already cached and save the compile step. This isn't likely to be 14 minutes worth of difference, but it is worth remembering.

Additionally, SQL Server will cache recently-used tables and indexes in memory. So if you run the procedure first cold, and then try running the statements just after, you've potentially just shaved a TON of disk I/O off whichever happens to go after the other. This can account for 15x change in execution time.

Finally, SSMS can show you the execution plans. So you can do both in separate windows and then examing the execution plans to see if there are any differences.

Upvotes: 3

Related Questions