• This article is very misleading on one important point.  The article comes very close to stating, or at least implying, that you must specify owner names for cache reuse to work.  Although the author didn't say that explicitly, I think that unless a reader already understood cache reuse (before reading the article), then they would walk away from this article thinking that you must supply owner names or you will not get cach reuse.  This is simply not true.

    What is true is that cache reuse is based on the text string being executed, after adjusting for parameters.  In other words, if SQL has seen your parameterized string before (even if SQL had to auto-parameterize it), it will obtain a cache hit (assuming for the moment a stable set of session settings for both invocations).

    If you look at the way the author's script is layed out, all it proves is that if you compile two different SQL strings (adjusted for parameterization), you get two different cache inserts.  This is true whether or not user names are explicitly used.  The simple script below shows that you can obtain cache hits using default object names.  Trace the following script and you will see a cache insert for the first sp_executesql call followed by a cache hit for the second call, even though the script's SQL string does not specify an object owner name

    USE NORTHWIND

    DECLARE @sql nvarchar(200)

    DECLARE @params nvarchar(100)

    DECLARE @intvar int

    -- This will generate SP:CacheInsert in SQL Profiler:

    SET @sql = N'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE EmployeeID = @empid'

    SET @params = N'@empid int'

    SET @intvar = 5

    EXEC sp_executesql @sql, @params, @empid = @intvar

    -- This will generate SP:ExecContextHit in SQL Profiler:

    SET @intvar=6

    EXEC sp_executesql @sql, @params, @empid = @intvar

    There is enough misunderstanding about this topic out in the wild that we should really be careful how we talk about it here, where lots of people come looking for SQL expertise.  Otherwise, we may wind up reading an article some day entitled (and I hope the other Chris takes this in good humor) Worst Practices:  Potentially Misleading Worst-Practice Articles.  

    I didn't have time to read through all the other posts, so I apologize if anybody else has covered this. 

    Cheers,

    Chris