Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by James Travis What is the search order for Procedures prefixed sp_? RE: What is the search order for Procedures prefixed sp_?

  • November 25, 2002 at 10:03 am

    #442311

    Well, Paul, I'm rather tickled that someone else besides has also hit upon the idea of using sp__ to identify locally-written system stored procedures.

    I think I understand the performance issue people are talking about, but I don't think it matters in the restricted case we are talking about. According to bkelley (emphasis mine):

    quote:

    Quite simply, if you have an sp_ named stored procedure THAT ISN'T IN THE MASTER DATABASE, you will always get an initial SP:CacheMiss event, meaning on the initial pass through, SQL Server won't locate the cached execution plan.

    SQL Server will then institute a [COMPILE] lock on the stored procedure..... , etc.

    But you and I are talking about sp__ procedures that ARE in the master database (and not in the local DB), so according to this quote, the performance hit doesn't apply.