Thanks for reading the article. When you use sp without an underscore, there are no issues. This happens only when it is sp_. The basic reason is because the system generated stored procs all start with sp_.
Mark, Yes, it is designed by SQL server to take an exclusive lock. But it is actually a good design. When there is no execution plan, Only one SPID needs to compile it. Others will use this compiled cache plan. So when there is no Object Qualifier and the Querry engine does not see a Cached query plan, it automatically takes an exclusive lock so that no other process can try to compile it while this is compiling. I would say the design is not faulty. The design is good if we follow the recommendations.