Posted on Leave a comment

Optimizing Stored Procedure Calls

  • Execute Stored Procedure with Fully Qualified Name (include schema, for example “EXEC dbo.mystoredproc”)
  • Execute Stored Procedure with same capitalization (upper/lower case) as it was created
  • Do not name stored procedures with “sp_” prefix unless you are specifically intending to create a global stored procedure (preferably in master database)
  • Make sure that the Stored Procedure execution query is sent to SQL as an RPC instead of a Language Event. For example, for the .Net command object be sure to explicitly set CommandType to StoredProcedure

Additional information:

https://support.microsoft.com/en-us/help/263889/how-to-troubleshoot-blocking-caused-by-compile-locks

http://c-bit.org/kb/263889/EN-US/