locked
Contents of PLan Cache: CacheType is "prepared"... are these Parameterized queries? RRS feed

  • Question

  • Are these ONLY paramaterized queries?

    When i run: Select TOP(100)  p.* FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) p WHERE cp.cacheobjtype = 'Compiled Plan'  AND cp.objtype = 'prepared'  AND cp.usecounts = 1

     

    If so, when you look at the sql command text, one would expect to see something like:

    @var1, @Var2  select  * from table where col1 = @var1 and col2 = @var3

    Actually, I do see many like the above.  But, I also see queries like:

    1. (@ POBO varchar(9),@FiscalYear varchar(5))exec usp_GetCntrctsDocTypes 'PO052728','FY01'
    2. select max(costlinenum) from pocost where polineid =  19  and siteid =  'abc'

    Neither of those look like "parameterized queries"... The first looks like it should be a stored proc... the second looks like a non-parameterized query.

    Thanks for any assistance.


    steve
    Wednesday, July 28, 2010 6:58 PM

Answers

  • When I talk about parameterized queries, I am talking about auto-parameterized, where SQL Server determines that some of the values should actually be parameters.

    However, through your application, you can use the PREPARE method to prepare a query for SQL Server even if you haven't specified any parameters. That will then show up in the metadata as a PREPARED query.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Wednesday, July 28, 2010 7:25 PM

All replies

  • When I talk about parameterized queries, I am talking about auto-parameterized, where SQL Server determines that some of the values should actually be parameters.

    However, through your application, you can use the PREPARE method to prepare a query for SQL Server even if you haven't specified any parameters. That will then show up in the metadata as a PREPARED query.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Wednesday, July 28, 2010 7:25 PM
  • Kalen,

      Thank you for your answer.  That directly explains #2 in my original question.   Can the prepare method somehow be used to send Stored procs to Sql server?  If not, how else do stored procs get stored in the "prepared" portion of the cache (as in example #1, above)?

      In a related question, there are a number of internet posters suggesting that if you get a lot of usecount =1 queries in the cache that these are examples of 'wasted' cache memory.  HOwever, is it not so that for an auto parameterized query, the 'base' query gets cached as is (usecount=1) ... a pointer is then established to an "auto-parameterized" plan, and then the "auto-parameterized" plan is then used (repeatedly; usecount=>1).  The 'base' query is left in cache as a permanent pointer to the "auto-generated" plan.   If the foregoing is true, then the presence of numerous "usecount =1" queries in cache is not Wasted memory, but instead suggests the imprint of SQL performing Auto parameterization. 

      Furthermore, (if the above is true), then, clearning the cache for a particular database based simply on a high # of Usecount=1 queries might not be such a good idea.

    Steve


    steve
    Thursday, July 29, 2010 11:33 AM