We use SQL Server with SQLAlchemy in this case. When looking at our plan cache we noticed tens of thousands of single use adhoc compiled plans. As you know this takes up a ton of memory, and every single query that comes back looks similar to this, with it's value hard coded in the Query Text:
SELECT first_name, last_name FROM dbo.[user] WHERE username = 'abc123'
The python code used to generate the query above looks like this:
@classmethod
def get_by_name(cls, name):
sql = '''
SELECT first_name, last_name FROM dbo.[user] WHERE username = :name
'''
obj = DBSession.query(cls).from_statement(text(sql)).params(
name=name.encode()).first()
return obj
My Question: Does anyone know why this is happening or how to make sure that we get a prepared statement sent from SQLAlchemy ORM? We do not want to enable Forced Parameterization if possible.
Query to find single use adhoc compiled plans:
SELECT TOP(500) DB_NAME(t.[dbid]) AS [Database Name], t.[text] AS [Query Text],
cp.objtype AS [Object Type], cp.cacheobjtype AS [Cache Object Type], cp.usecounts,
cp.size_in_bytes/1024 AS [Plan Size in KB]
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC, DB_NAME(t.[dbid]) OPTION (RECOMPILE);