SQL: Increase performance by fully qualifying procedure names

There is a “best practice” when programming in T-SQL that consists in fully qualifying any object referenced in your code. And this article explains one of the reasons why this is recommended.

In short: if you don’t write “dbo.my_proc” when calling a procedure, SQL will have to search the cache to see if there is an object with this name in your schema or if you are referencing the dbo schema. This may cause blocking because SQL doesn’t know witch object you are calling yet, and will acquire a lock in order to prepare to compile the procedure while it searches the execution plan cache – if there is some execution plan found on the cache at this moment it releases the lock and never compiles the procedure, but until then your query was delayed for some moments.

Description of SQL Server blocking caused by compile locks: http://support.microsoft.com/kb/263889

Some other tips to stored procedure optimization may be found here: http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/ and here: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

cya!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s