Why is this frequent SQL not in the SQL Server procedure cache? I frequently query the SQL Server procedure cache (dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan) to get execution stats and execution plans.

We have a new application (with a new development team, etc.) and none of its SQL is showing up in the procedure cache. 


The cache has SQL from Idera and from internal SQL Server processes; 
Idera is reporting some long-running SQL from the application; 
I can see application SQL in the sessions in the activity monitor.


But nothing from this application in the procedure cache. So I am almost blind here.
This is SQL Server 2017 - but I do not have this issue with other SQL Server 2017 databases.
This application is .Net (using Core .Net SqlClient Data Provider to connect) and this is a bit of an oddity for us, as most of our applications use Java, Tomcat and JDBC.

I have seen articles saying that RECOMPILE hints/options will not save the SQL in the procedure cache. I don't think the dev team is deliberately doing that. Is it possible that there is a setting in the provider that is forcing that?

Can anyone suggest avenues of investigation for this?
There's nothing in the Core .Net SqlClient Data Provider (AKA System.Data.SqlClient) that would explicitly prevent plans from being cached.  

It would be helpful to know exactly what queries you're running to check the plan cache.  

For instance, depending on whether the developers are parameterizing their queries, you could see entries in sys.dm_exec_cached_plans with different objtype values:


"Prepared" - the query had parameters added using the SqlParameter classes
"Adhoc" - the query did not have any parameters, or the parameters were embedded directly into the query text


But I expect that would be true of other data providers as well.

Since you're on SQL Server 2017, another option available to you is to enable Query Store for this database, and then see if the expected SQL text is being captured there.  This will include even queries that use OPTION (RECOMPILE) (see Erin Stellato's blog post Queries with OPTION (RECOMPILE) and Query Store for details).  This could help confirm whether or not developers are using that hint.

Finally, you could use Extended Events to temporarily capture executing queries while the application is running.  A very simple way to do this is with Erik Darling's sp_HumanEvents.  Here's an example usage from the site, which runs for 20 seconds, capture all queries against the "YourDatabaseName" database that ran for at least 1 second:

EXEC dbo.sp_HumanEvents 
    @event_type = 'query', 
    @query_duration_ms = 1000, 
    @seconds_sample = 20, 
    @database_name = 'YourDatabaseName';