question

IJ5569 avatar image
IJ5569 asked

SQL Server plan cache has only Proc (stored procedure) objects, but not Prepared nor Ad-hoc ones

I have noticed that the plan cache of a SQL Server I manage has only Proc (Stored Procedure) objects. There is neither Prepared nor Ad-hoc ones, even although I have executed some dynamic parameterized queries for testing whose plans should be cached as Prepared.

The machine has enough memory so it is not due to a lack of resouces.

Any idea about to what this could be due?

Thanks in advance.

plan-cache
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rknight avatar image
rknight answered

Is 'optimize for ad hoc queries' enabled in sys.configurations?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

IJ5569 avatar image
IJ5569 answered

Yes, it is. But that server feature doesn't affect prepared objects (plans for pameterized batches). And even adhoc objects (plans for non parameterized batches) are indeed created when a non parameterized batch is executed twice.

In my tests I executed repeatedly some batches (dynamic parameterized and non parameterized) but no plan was cached. Only Proc objects (for stored procedures) are created. Thanks for your question.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rknight avatar image rknight commented ·
Do the queries show up in Query Store?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.